PostgreSQL Architecture: Protocol vs. Feature Layer Separation

Document Version: 1.0 Created: November 21, 2025 Purpose: Explain architectural separation between heliosdb-postgres and heliosdb-protocols/src/postgres

Quick Answer

Component	Purpose	Layer	Status
heliosdb-protocols/src/postgres	PostgreSQL wire protocol handler (network communication)	Protocol Layer	95% Complete (43 files)
heliosdb-postgres	PostgreSQL 17 feature compatibility layer (SQL, extensions, functions)	Application Layer	⚠ 5% Skeleton

TL;DR: heliosdb-protocols handles how to talk to PostgreSQL clients, while heliosdb-postgres handles what PostgreSQL features to support.

Architectural Diagram

┌─────────────────────────────────────────────────────────┐
│                PostgreSQL Client                         │
│           (psql, pgAdmin, SQLAlchemy, etc.)             │
└─────────────────────┬───────────────────────────────────┘
                      │ PostgreSQL Wire Protocol
                      │ (Binary Messages)
                      ▼
┌─────────────────────────────────────────────────────────┐
│         heliosdb-protocols/src/postgres/                │
│                                                          │
│  ┌──────────────────────────────────────────────────┐  │
│  │  PROTOCOL LAYER (Wire Protocol Handler)          │  │
│  │                                                   │  │
│  │  • Message parsing (Startup, Query, Parse, etc.) │  │
│  │  • Binary format encoding/decoding               │  │
│  │  • Connection management (auth, SSL)             │  │
│  │  • Transaction protocol (BEGIN, COMMIT, etc.)    │  │
│  │  • Extended query protocol (prepared statements) │  │
│  │  • COPY protocol (bulk data transfer)            │  │
│  │  • Notification protocol (LISTEN/NOTIFY)         │  │
│  │                                                   │  │
│  │  Status:  95% Complete (43 files, ~80K LOC)    │  │
│  └──────────────────────────────────────────────────┘  │
└─────────────────────┬───────────────────────────────────┘
                      │ Parsed SQL + Protocol Requests
                      ▼
┌─────────────────────────────────────────────────────────┐
│              heliosdb-postgres/                         │
│                                                          │
│  ┌──────────────────────────────────────────────────┐  │
│  │  FEATURE LAYER (PostgreSQL 17 Compatibility)     │  │
│  │                                                   │  │
│  │  • PostgreSQL 17 new features                    │  │
│  │  • Advanced SQL features (CTEs, Window, JSON)    │  │
│  │  • System catalogs (pg_catalog, pg_type, etc.)   │  │
│  │  • Extensions (PostGIS, TimescaleDB, etc.)       │  │
│  │  • Built-in functions (500+ PostgreSQL functions)│  │
│  │  • PL/pgSQL procedural language                  │  │
│  │  • Row-level security (RLS)                      │  │
│  │  • Foreign Data Wrappers (FDW)                   │  │
│  │                                                   │  │
│  │  Status: ⚠ 5% Skeleton (5 files, 2.3K LOC)      │  │
│  └──────────────────────────────────────────────────┘  │
└─────────────────────┬───────────────────────────────────┘
                      │ Normalized Queries
                      ▼
┌─────────────────────────────────────────────────────────┐
│              heliosdb-compute/                          │
│         (Query Executor & Storage Engine)               │
└─────────────────────────────────────────────────────────┘

Detailed Breakdown

1. heliosdb-protocols/src/postgres/ (Protocol Layer)

Location: heliosdb-protocols/src/postgres/ Size: 43 files, ~80,000 LOC Status: 95% Complete Purpose: Implement the PostgreSQL wire protocol (network communication)

Responsibilities

Network Protocol Implementation:

Parse PostgreSQL binary messages from TCP streams
Encode responses in PostgreSQL binary format
Handle connection lifecycle (startup, authentication, termination)
Implement extended query protocol (prepared statements, portals)
Support COPY protocol for bulk data transfer
Implement LISTEN/NOTIFY pub/sub mechanism

Protocol-Level Features:

pub struct PostgresHandler {
    stream: TcpStream,              // TCP connection
    state: ConnectionState,          // Protocol state machine
    prepared_statements: HashMap,    // Prepared statement cache
    portals: HashMap,                // Portal (cursor) management
    transaction_status: TransactionStatus,
    // ... protocol-specific state
}

Key Files (from actual codebase):

handler.rs (178,002 bytes) - Main protocol handler
binary_format.rs - Binary encoding/decoding
extended.rs - Extended query protocol (prepared statements)
catalogs.rs (79,388 bytes) - System catalog queries
advisory_locks.rs - PostgreSQL advisory locks
backup_protocol.rs - Backup/replication protocol
json_constructors.rs - JSON protocol support
auth/ - Authentication mechanisms (SCRAM-SHA-256, MD5, etc.)
extensions/ - Extension protocol support

What it does:

PostgreSQL Client sends: "SELECT * FROM users WHERE id = $1"
                         ↓
heliosdb-protocols parses:
  - Message type: Parse
  - Query string: "SELECT * FROM users WHERE id = $1"
  - Parameter types: [INTEGER]
                         ↓
Forwards to heliosdb-postgres for SQL interpretation
                         ↓
Receives result rows
                         ↓
Encodes in PostgreSQL DataRow format
                         ↓
Sends binary response to client

Does NOT Handle

❌ PostgreSQL-specific SQL syntax interpretation
❌ PostgreSQL built-in functions (generate_series, array_agg, etc.)
❌ PostgreSQL system tables structure
❌ PostgreSQL extensions (PostGIS, pg_trgm, etc.)
❌ PL/pgSQL procedural language
❌ PostgreSQL 17 new features

2. heliosdb-postgres/ (Feature Layer)

Location: heliosdb-postgres.SKELETON_NOT_IMPLEMENTED/ Size: 5 files, 2,327 LOC Status: ⚠ 5% Skeleton (needs implementation) Purpose: Implement PostgreSQL 17 feature compatibility

Responsibilities

PostgreSQL 17 New Features:

Incremental backup improvements
Logical replication enhancements
JSON path improvements
SQL/JSON constructors (JSON_OBJECT, JSON_ARRAY)
MERGE statement enhancements
Vacuum improvements

Advanced PostgreSQL Features:

// Example: heliosdb-postgres/src/lib.rs (current skeleton)
pub struct PostgreSQL17Compat {
    // PostgreSQL-specific features
    system_catalogs: PgSystemCatalogs,  // pg_catalog.*, pg_type, etc.
    extensions: ExtensionManager,       // PostGIS, TimescaleDB, etc.
    functions: BuiltinFunctions,        // 500+ PostgreSQL functions
    plpgsql: PlPgSqlEngine,            // PL/pgSQL interpreter
    fdw: ForeignDataWrappers,          // External data sources
    rls: RowLevelSecurity,             // Row-level security policies
}

Key Responsibilities (to be implemented):

SQL Dialect Features:
- PostgreSQL-specific syntax (SELECT DISTINCT ON, RETURNING, etc.)
- Array operations (array_agg, unnest, etc.)
- JSON/JSONB operations (jsonb_path_query, etc.)
- Full-text search (tsvector, tsquery, to_tsquery)
- Pattern matching (SIMILAR TO, posix regex)
System Catalogs:
- pg_catalog schema (metadata tables)
- pg_type (type system)
- pg_proc (functions)
- pg_class (tables/indexes)
- pg_attribute (columns)
- 100+ system tables
Built-in Functions (500+):
- String functions: substring, regexp_replace, etc.
- Math functions: generate_series, random, etc.
- Date/time functions: date_trunc, age, etc.
- Array functions: array_agg, array_length, etc.
- JSON functions: json_agg, jsonb_set, etc.
- Aggregate functions: percentile_cont, mode, etc.
Extensions:
- PostGIS (geospatial)
- pg_trgm (trigram matching)
- pg_stat_statements (query statistics)
- hstore (key-value store)
- uuid-ossp (UUID generation)
- TimescaleDB compatibility
PL/pgSQL:
- Procedural language interpreter
- Variables, control flow (IF, LOOP, etc.)
- Exception handling
- Triggers
- Functions and procedures
Foreign Data Wrappers (FDW):
- postgres_fdw (connect to other PostgreSQL)
- file_fdw (read CSV/text files)
- Custom FDW support
- Pushdown optimization
Row-Level Security (RLS):
- CREATE POLICY support
- Policy evaluation
- USING and WITH CHECK clauses
- Role-based filtering

What it does:

heliosdb-protocols receives parsed query: "SELECT generate_series(1, 10)"
                         ↓
Forwards to heliosdb-postgres:
  - Recognize: generate_series is PostgreSQL function
  - Interpret: Set-returning function, generates rows 1-10
  - Execute: Call PostgreSQL-compatible implementation
  - Return: Result set [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
                         ↓
Returns to heliosdb-protocols for binary encoding

Does NOT Handle

❌ Network protocol parsing (handled by heliosdb-protocols)
❌ Binary format encoding/decoding (handled by heliosdb-protocols)
❌ Connection management (handled by heliosdb-protocols)
❌ Authentication (handled by heliosdb-protocols)

🔄 Communication Flow

Example: Query Execution

1. Client: psql -c "SELECT array_agg(id) FROM users WHERE created_at > '2024-01-01'"

2. heliosdb-protocols/src/postgres/handler.rs:
   ┌─────────────────────────────────────────┐
   │ • Receive TCP message                   │
   │ • Parse: Query message                  │
   │ • Extract SQL: "SELECT array_agg..."    │
   │ • Validate protocol state               │
   └────────────┬────────────────────────────┘
                │
                ▼
3. heliosdb-postgres/ (SHOULD HANDLE):
   ┌─────────────────────────────────────────┐
   │ • Recognize: array_agg (PG function)    │
   │ • Parse: PostgreSQL-specific syntax     │
   │ • Validate: Date literal format         │
   │ • Translate: To internal representation │
   └────────────┬────────────────────────────┘
                │
                ▼
4. heliosdb-compute/ (Query Executor):
   ┌─────────────────────────────────────────┐
   │ • Execute: Scan users table             │
   │ • Filter: created_at > '2024-01-01'     │
   │ • Aggregate: Collect IDs into array     │
   │ • Return: Result rows                   │
   └────────────┬────────────────────────────┘
                │
                ▼
5. heliosdb-protocols/src/postgres/binary_format.rs:
   ┌─────────────────────────────────────────┐
   │ • Encode: Array as PostgreSQL format    │
   │ • Create: DataRow message               │
   │ • Send: Binary response to client       │
   └─────────────────────────────────────────┘

📋 Current Implementation Status

heliosdb-protocols/src/postgres/ (95% Complete)

Implemented (43 files, ~80K LOC):

Feature	File	LOC	Status
Protocol Handler	handler.rs	178,002	Complete
System Catalogs	catalogs.rs	79,388	Complete
Binary Format	binary_format.rs	32,204	Complete
Advisory Locks	advisory_locks.rs	35,068	Complete
JSON Support	json.rs, json_constructors.rs	46,361	Complete
Backup Protocol	backup_protocol.rs	19,199	Complete
Extended Protocol	extended.rs	17,207	Complete
Authentication	auth/	Multiple	Complete
Extensions Protocol	extensions/	Multiple	Complete
Transaction Coord	global_txn_coordinator.rs	14,508	Complete

What Works:

Connection establishment
Authentication (SCRAM-SHA-256, MD5)
Simple query protocol
Extended query protocol (prepared statements)
Binary format encoding/decoding
Transaction management (BEGIN, COMMIT, ROLLBACK)
COPY protocol
LISTEN/NOTIFY pub/sub
Advisory locks
Replication protocol

heliosdb-postgres/ ⚠ (5% Skeleton)

Current State (5 files, 2,327 LOC):

Module	Status	LOC	Completion
lib.rs	Skeleton	32	5%
wire_protocol.rs	Skeleton	~500	10%
sql_parser.rs	Skeleton	~600	10%
system_catalogs.rs	Skeleton	~700	10%
extensions.rs	Skeleton	~500	5%

Current Code (skeleton):

// heliosdb-postgres/src/lib.rs (current state)
pub struct PostgreSQL17Compat;

impl PostgreSQL17Compat {
    pub fn new() -> Self {
        todo!("Initialize PostgreSQL 17 compatibility layer")
    }
}

What’s Missing (95% of functionality):

❌ PostgreSQL 17 new features
❌ PostgreSQL-specific SQL syntax
❌ 500+ built-in functions
❌ System catalog structure (pg_catalog, pg_type, etc.)
❌ PL/pgSQL interpreter
❌ Extension support (PostGIS, TimescaleDB, etc.)
❌ Foreign Data Wrappers
❌ Row-level security
❌ Advanced SQL features (CTEs, Window functions, etc.)
❌ JSON path expressions
❌ Full-text search

Why This Separation?

1. Separation of Concerns

Protocol Layer (heliosdb-protocols):

Focused on network communication
Deals with bytes, TCP, binary formats
Generic across database features
Changes rarely (protocol is stable)

Feature Layer (heliosdb-postgres):

Focused on database features
Deals with SQL, functions, extensions
Specific to PostgreSQL compatibility
Changes frequently (new PG versions, features)

2. Code Reusability

The protocol layer can be shared:

heliosdb-protocols/src/postgres/  ──┬──> PostgreSQL 17 compatibility
                                     ├──> PostgreSQL 16 compatibility
                                     └──> PostgreSQL 15 compatibility

3. Maintainability

Clear boundaries:

Protocol bugs → Fix in heliosdb-protocols
Feature bugs → Fix in heliosdb-postgres
New PostgreSQL feature → Add to heliosdb-postgres (protocol unchanged)

4. Testing Isolation

Protocol tests: Focus on message parsing, encoding
Feature tests: Focus on SQL correctness, function behavior

Implementation Priority (Phase 3)

Week 1-2: heliosdb-postgres Foundation

Priority 1: Core Infrastructure (Week 1):

System catalog implementation (pg_type, pg_class, pg_attribute)
Type system mapping (PostgreSQL types → HeliosDB types)
Basic function registry (top 50 functions)
SQL parser enhancement (PostgreSQL-specific syntax)

Priority 2: Essential Features (Week 2): 5. Array operations (array_agg, unnest, array_length) 6. JSON operations (json_agg, jsonb_set, jsonb_path_query) 7. String functions (substring, regexp_replace, split_part) 8. Date/time functions (date_trunc, age, extract)

Week 3-4: Advanced Features

Priority 3: Advanced Features (Week 3-4): 9. PL/pgSQL interpreter (basic support) 10. Foreign Data Wrappers (postgres_fdw, file_fdw) 11. Row-level security (CREATE POLICY, policy evaluation) 12. Extension protocol (PostGIS compatibility) 13. Full-text search (tsvector, tsquery) 14. PostgreSQL 17 new features (incremental backup, JSON path, etc.)

Dependency Graph

┌────────────────────────────────────────────────────┐
│  heliosdb-postgres (Feature Layer)                 │
│  Status: 5% - Needs Implementation                 │
│  Depends on:                                       │
│    • heliosdb-protocols (uses protocol handler)    │
│    • heliosdb-compute (uses query executor)        │
│    • heliosdb-storage (uses storage engine)        │
└───────────────────┬────────────────────────────────┘
                    │
                    ▼
┌────────────────────────────────────────────────────┐
│  heliosdb-protocols/src/postgres (Protocol Layer)  │
│  Status: 95% - Production Ready                    │
│  Depends on:                                       │
│    • heliosdb-common (common utilities)            │
│    • heliosdb-compute (query executor interface)   │
│    • tokio (async networking)                      │
└───────────────────┬────────────────────────────────┘
                    │
                    ▼
┌────────────────────────────────────────────────────┐
│  heliosdb-compute (Query Executor)                 │
│  Status: 95% - Production Ready                    │
└────────────────────────────────────────────────────┘

Example: Adding a PostgreSQL Function

Bad Approach (violates separation):

// ❌ DON'T put in heliosdb-protocols/src/postgres/handler.rs
impl PostgresHandler {
    fn execute_generate_series(&self, start: i32, end: i32) -> Vec<i32> {
        // This is a FEATURE, not protocol logic!
        (start..=end).collect()
    }
}

Good Approach (proper separation):

//  DO put in heliosdb-postgres/src/functions/generators.rs
pub mod generators {
    pub fn generate_series(start: i32, end: i32) -> impl Iterator<Item = i32> {
        start..=end
    }
}

// heliosdb-protocols/src/postgres/handler.rs just forwards:
impl PostgresHandler {
    fn execute_query(&mut self, sql: &str) -> Result<QueryResult> {
        // Parse SQL, detect function call
        if is_postgres_function(sql) {
            // Forward to PostgreSQL compatibility layer
            heliosdb_postgres::execute(sql)
        } else {
            // Execute as normal SQL
            self.query_executor.execute(sql)
        }
    }
}

Summary Table

Aspect	heliosdb-protocols/src/postgres	heliosdb-postgres
Purpose	Wire protocol implementation	Feature compatibility
Layer	Network/Protocol	Application/SQL
Size	43 files, ~80K LOC	5 files, 2.3K LOC (skeleton)
Status	95% Complete	⚠ 5% Skeleton
Handles	Message parsing, binary format, connection	SQL features, functions, extensions
Doesn’t Handle	PostgreSQL-specific SQL/functions	Network protocol details
Changes	Rarely (protocol stable)	Frequently (new features)
Tests	Protocol compliance tests	SQL correctness tests
Dependencies	Low-level (tokio, bytes)	High-level (protocols, compute)
Timeline	Already complete	Phase 3: 4 weeks
Investment	Already invested	$300K needed

Action Items

Immediate (Week 1):

Understand architectural separation (this document)
🔄 Create heliosdb-postgres implementation plan
🔄 Define PostgreSQL 17 feature checklist
🔄 Setup integration tests between layers

Short-term (Weeks 2-4):

🔄 Implement core system catalogs
🔄 Implement top 100 PostgreSQL functions
🔄 Add PostgreSQL 17 new features
🔄 Integrate with heliosdb-protocols handler

Long-term (Months 2-3):

🔄 PL/pgSQL support
🔄 Extension compatibility (PostGIS, etc.)
🔄 Full PostgreSQL regression test suite (15,000+ tests)
🔄 100% PostgreSQL 17 compatibility

📚 References

PostgreSQL Wire Protocol Documentation
PostgreSQL 17 Release Notes
heliosdb-protocols/src/postgres/handler.rs - Protocol implementation
heliosdb-postgres/src/lib.rs - Feature layer skeleton
Phase 3 Completion Plan - Implementation timeline

Document Version: 1.0 Last Updated: November 21, 2025 Status: COMPREHENSIVE Review: Q1 2026