---
title: "PostgreSQL Wire Protocol Implementation"
---

# PostgreSQL Wire Protocol Implementation

## Overview

HeliosDB Lite implements the PostgreSQL wire protocol v3, enabling compatibility with PostgreSQL clients including psql, pgAdmin, and various ORMs.

## Architecture

```
┌─────────────┐
│ psql/Client │
└──────┬──────┘
       │ TCP Connection
       │ Port 5432
┌──────▼──────────────────┐
│   Network Server        │
│  - TCP Listener         │
│  - Session Management   │
└──────┬──────────────────┘
       │
┌──────▼──────────────────┐
│   Session Handler       │
│  - Authentication       │
│  - Message Decoder      │
│  - Message Encoder      │
└──────┬──────────────────┘
       │
┌──────▼──────────────────┐
│   SQL Engine            │
│  - Parser               │
│  - Planner              │
│  - Executor             │
└──────┬──────────────────┘
       │
┌──────▼──────────────────┐
│   Storage Engine        │
│  - Storage backend      │
│  - MVCC                 │
│  - WAL                  │
└─────────────────────────┘
```

## Components

### 1. Protocol Messages

Implements message encoding/decoding for the PostgreSQL wire protocol.

**Key Features:**
- Message parsing (Frontend messages from client)
- Message encoding (Backend messages to client)
- Type OID mapping (PostgreSQL data types)
- Field descriptions for result sets

**Supported Messages:**

Frontend (Client → Server):
- Startup - Initial connection setup
- Query - Simple query protocol
- Parse - Prepare statement (extended protocol)
- Bind - Bind parameters to statement
- Execute - Execute prepared statement
- Describe - Get statement/portal metadata
- Close - Close statement/portal
- Sync - End of extended query
- Terminate - Close connection

Backend (Server → Client):
- Authentication* - Authentication challenges
- ReadyForQuery - Server ready for next command
- RowDescription - Column metadata for results
- DataRow - Result row data
- CommandComplete - Command execution complete
- ErrorResponse - Error occurred
- ParameterStatus - Server parameters
- BackendKeyData - Cancellation key

### 2. Authentication

Implements authentication mechanisms.

**Supported Methods:**
- Cleartext Password (for testing)
- SCRAM-SHA-256

**SCRAM-SHA-256 Flow:**
1. Client sends initial message with username and nonce
2. Server responds with salt, iteration count, and combined nonce
3. Client sends proof of password knowledge
4. Server verifies proof and sends signature
5. Authentication complete

### 3. Sessions

Manages individual client connections.

**Responsibilities:**
- Connection lifecycle management
- Authentication state machine
- Message routing
- Query execution coordination
- Transaction state tracking
- Prepared statement cache
- Portal (bound statement) cache

**Session State:**
- Unauthenticated
- Authenticating (SCRAM in progress)
- Authenticated
- Transaction (Idle/InTransaction/Failed)

### 4. Server

Asynchronous TCP server.

**Features:**
- Asynchronous I/O
- Concurrent session handling
- Graceful shutdown support
- Session ID management

## Protocol Details

### Connection Flow

1. **Client Connects**
   ```
   Client → Server: TCP Connection
   ```

2. **Startup Message**
   ```
   Client → Server: StartupMessage {
       protocol_version: 196608 (3.0)
       user: "postgres"
       database: "heliosdb"
   }
   ```

3. **Authentication**
   ```
   Server → Client: Authentication(CleartextPassword)
   Client → Server: PasswordMessage("password")
   Server → Client: Authentication(Ok)
   ```

4. **Parameter Exchange**
   ```
   Server → Client: ParameterStatus("server_version", "17.0 (HeliosDB Lite)")
   Server → Client: ParameterStatus("client_encoding", "UTF8")
   Server → Client: BackendKeyData { process_id, secret_key }
   Server → Client: ReadyForQuery(Idle)
   ```

5. **Query Execution**
   ```
   Client → Server: Query("SELECT * FROM users")
   Server → Client: RowDescription([field1, field2,...])
   Server → Client: DataRow([value1, value2,...])
   Server → Client: DataRow([value1, value2,...])
   Server → Client: CommandComplete("SELECT 2")
   Server → Client: ReadyForQuery(Idle)
   ```

### Simple Query Protocol

Used by default for interactive queries.

**Flow:**
1. Client sends Query message
2. Server parses and executes
3. Server sends results:
   - RowDescription (for SELECT)
   - DataRow (one per result row)
   - CommandComplete
4. Server sends ReadyForQuery

**Example:**
```sql
-- Client sends
Query("SELECT id, name FROM users WHERE id = 1")

-- Server responds
RowDescription([
    Field { name: "id", type_oid: INT4, ... },
    Field { name: "name", type_oid: TEXT, ... }
])
DataRow([b"1", b"Alice"])
CommandComplete("SELECT 1")
ReadyForQuery(Idle)
```

### Extended Query Protocol

Used for prepared statements and parameter binding.

**Flow:**
1. Parse - Prepare statement
2. Bind - Bind parameters
3. Describe - Get metadata (optional)
4. Execute - Run query
5. Sync - Commit

**Example:**
```sql
-- Prepare
Parse("stmt1", "SELECT * FROM users WHERE id = $1", [INT4])
ParseComplete

-- Bind
Bind("portal1", "stmt1", params=[b"42"])
BindComplete

-- Execute
Execute("portal1", max_rows=0)
RowDescription([...])
DataRow([...])
CommandComplete("SELECT 1")

-- Sync
Sync
ReadyForQuery(Idle)
```

## Data Type Mapping

| PostgreSQL OID | Type Name | HeliosDB DataType |
|----------------|-----------|-------------------|
| 16 | BOOL | Boolean |
| 20 | INT8 | BigInt |
| 21 | INT2 | SmallInt |
| 23 | INT4 | Integer |
| 25 | TEXT | Text |
| 700 | FLOAT4 | Real |
| 701 | FLOAT8 | Double |
| 1043 | VARCHAR | Varchar |
| 1114 | TIMESTAMP | Timestamp |
| 1184 | TIMESTAMPTZ | TimestampTz |
| 2950 | UUID | Uuid |
| 3802 | JSONB | Jsonb |

## Usage

### Starting the Server

```bash
export HELIOSDB_JWT_SECRET="$(openssl rand -hex 32)"
heliosdb-lite start --memory
# or, with persistent storage
heliosdb-lite start --data-dir ./mydb
```

### Connecting with psql

```bash
# Connect to server
psql -h localhost -p 5432 -U postgres

# Enter password: postgres

# Run queries
SELECT * FROM users;
INSERT INTO users VALUES (1, 'Alice');
UPDATE users SET name = 'Bob' WHERE id = 1;
```

### Connecting with ORMs

**SQLAlchemy (Python):**
```python
from sqlalchemy import create_engine

engine = create_engine('postgresql://postgres:postgres@localhost:5432/heliosdb')
```

**Diesel (Rust):**
```rust
let database_url = "postgres://postgres:postgres@localhost:5432/heliosdb";
let connection = PgConnection::establish(&database_url)?;
```

## Configuration

Server configuration options:

```bash
export HELIOSDB_JWT_SECRET="$(openssl rand -hex 32)"
# Listen on all interfaces (use password authentication and TLS)
heliosdb-lite start --data-dir ./mydb --listen 0.0.0.0 \
  --auth scram-sha-256 --password "$HELIOSDB_ADMIN_PASSWORD"

# IPv6 localhost
heliosdb-lite start --data-dir ./mydb --listen ::1

# Custom port
heliosdb-lite start --data-dir ./mydb --port 54321
```

The same settings are available in `config.toml` under `[server]` (`listen_addr`,
`port`, `max_connections`). See the [CLI Reference](/docs/lite/reference/cli/).

## Security

### Authentication

- **Trust and Cleartext Password**: Useful for local development and controlled networks
- **MD5 and SCRAM-SHA-256**: Password-based authentication (`--auth md5` or `--auth scram-sha-256`)
- **SSL/TLS**: Available when the server is started with `--tls-cert` and `--tls-key`

### Best Practices

1. Always use SCRAM-SHA-256 in production
2. Enable SSL/TLS for network encryption
3. Use strong passwords
4. Limit network exposure (bind to localhost or firewall)
5. Record changes to sensitive tables with triggers (built-in audit logging is planned)

## Performance

### Benchmarks

Figures below are indicative, measured on representative fixtures; reproduce on your own hardware.

Connection handling:
- 1,000+ concurrent connections supported
- Sub-millisecond query latency for simple queries
- Async I/O prevents blocking

Query execution:
- Uses existing SQL engine
- Performance matches embedded mode
- No overhead from protocol translation

### Tuning

Internal buffers size themselves. For many short-lived clients, pool connections on
the client side or put [HeliosProxy](/docs/lite/guides/heliosproxy_integration_guide/)
in front of the server.

## Limitations

Current implementation limitations:

1. **Protocol coverage**: Lite targets common PostgreSQL client workflows, but it is not a byte-for-byte PostgreSQL server clone.

2. **Catalog coverage**: `pg_catalog` and `information_schema` support common introspection paths, not every PostgreSQL catalog edge case.

3. **Binary result formats**: Result format preferences are tracked by portals, but most query paths still emit text-encoded row values.

4. **Cancellation**: Query cancellation requests are not part of the common execution path.

## Troubleshooting

### Connection Refused

```
psql: error: connection to server at "localhost", port 5432 failed:
Connection refused
```

**Solutions:**
- Verify server is running
- Check bind address (use 0.0.0.0 for all interfaces)
- Check firewall rules

### Authentication Failed

```
psql: error: FATAL:  password authentication failed for user "postgres"
```

**Solutions:**
- Verify username/password
- Check authentication configuration
- Review server logs

### Protocol Errors

```
ERROR:  Protocol error: Invalid message type
```

**Solutions:**
- Ensure client is using PostgreSQL protocol
- Check for version mismatch
- Review server debug logs

### Query Errors

```
ERROR:  Query execution error: Table not found
```

**Solutions:**
- Verify table exists: `\dt`
- Check SQL syntax
- Review query execution logs

## Compatibility

### PostgreSQL Compatibility

HeliosDB Lite implements PostgreSQL wire protocol v3:
- Compatible with PostgreSQL 8.0+
- Tested with psql 14, 15, 16, 17
- Works with most ORMs and drivers

### Client Compatibility

Tested clients:
- psql (PostgreSQL command-line)
- pgAdmin 4
- DBeaver
- DataGrip
- psycopg2 (Python)
- pg (Node.js)
- sqlx (Rust)
- Diesel (Rust)

## References

- [PostgreSQL Wire Protocol Documentation](https://www.postgresql.org/docs/current/protocol.html)
- [SCRAM-SHA-256 RFC 7677](https://tools.ietf.org/html/rfc7677)
- [PostgreSQL Message Formats](https://www.postgresql.org/docs/current/protocol-message-formats.html)
- [PostgreSQL Error Codes](https://www.postgresql.org/docs/current/errcodes-appendix.html)

## Contributing

When contributing to the network protocol implementation:

1. Follow PostgreSQL wire protocol specification exactly
2. Test with real PostgreSQL clients
3. Add error handling
4. Update documentation
5. Add tests for new features