PostgreSQL Wire Protocol Compatibility

Problem Being Solved

Core Problem Statement

Development teams invest heavily in PostgreSQL expertise, tooling, and application code, but face prohibitive complexity when attempting to run similar workloads in embedded, edge, or microservice environments where full PostgreSQL server deployment is impractical.

---

HeliosDB Lite Solution

Architecture Overview

┌─────────────────────────────────────────────────────────────────────┐
│              PostgreSQL Client Ecosystem (Unchanged)                 │
├─────────────────────────────────────────────────────────────────────┤
│  psql │ pgAdmin │ DBeaver │ psycopg2 │ node-postgres │ JDBC │ sqlx  │
├─────────────────────────────────────────────────────────────────────┤
│            PostgreSQL v3.0 Wire Protocol (TCP/SSL)                   │
├─────────────────────────────────────────────────────────────────────┤
│  HeliosDB-Lite Protocol Handler (Async Tokio)                        │
│  ├── Authentication (SCRAM-SHA-256, MD5)                             │
│  ├── Simple Query Protocol                                           │
│  ├── Extended Query Protocol (Parse → Bind → Execute)                │
│  └── Prepared Statement Manager (LRU Cache, 100 statements)          │
├─────────────────────────────────────────────────────────────────────┤
│  SQL Query Engine & Execution                                        │
├─────────────────────────────────────────────────────────────────────┤
│  Embedded RocksDB LSM Storage (No External Database)                 │
└─────────────────────────────────────────────────────────────────────┘

---

Concrete Examples with Code, Config & Architecture

Example 2: Node.js with node-postgres - ORM Compatibility (Prisma)

Architecture:

┌─────────────────────────────────────┐
│   Retail Store (Offline-Capable)    │
├─────────────────────────────────────┤
│  Next.js Frontend                   │
├─────────────────────────────────────┤
│  Prisma ORM (Unchanged)             │
├─────────────────────────────────────┤
│  node-postgres Driver               │
├─────────────────────────────────────┤
│  HeliosDB-Lite Embedded Server      │
│  (Runs in Electron/Desktop App)     │
├─────────────────────────────────────┤
│  Local SQLite-style Storage         │
│  (Syncs to Cloud When Online)       │
└─────────────────────────────────────┘

Prisma Schema (schema.prisma - UNCHANGED):

// Prisma schema works identically with HeliosDB-Lite
datasource db {
  provider = "postgresql"  // No change needed!
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model Product {
  id          Int      @id @default(autoincrement())
  sku         String   @unique
  name        String
  description String?
  price       Decimal  @db.Decimal(10, 2)
  stock       Int      @default(0)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  sales       Sale[]

  @@index([sku])
}

model Sale {
  id         Int      @id @default(autoincrement())
  productId  Int
  quantity   Int
  totalPrice Decimal  @db.Decimal(10, 2)
  soldAt     DateTime @default(now())
  synced     Boolean  @default(false)

  product    Product  @relation(fields: [productId], references: [id])

  @@index([synced, soldAt])
}

Environment Configuration (.env):

# Local embedded database (change from cloud)
DATABASE_URL="postgresql://pos_user:password@localhost:5432/retail_store_001?sslmode=disable"

# Production (cloud sync endpoint)
CLOUD_SYNC_URL="https://api.mystore.com/sync"

Application Code (TypeScript):

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient({
  datasources: {
    db: {
      url: process.env.DATABASE_URL,
    },
  },
});

// CRUD operations identical to PostgreSQL
async function createProduct(data: {
  sku: string;
  name: string;
  price: number;
  stock: number;
}) {
  return await prisma.product.create({
    data: {
      sku: data.sku,
      name: data.name,
      price: data.price,
      stock: data.stock,
    },
  });
}

async function recordSale(productId: number, quantity: number) {
  // Transaction support (uses extended query protocol)
  return await prisma.$transaction(async (tx) => {
    // Check stock
    const product = await tx.product.findUnique({
      where: { id: productId },
    });

    if (!product || product.stock < quantity) {
      throw new Error('Insufficient stock');
    }

    // Update stock
    await tx.product.update({
      where: { id: productId },
      data: { stock: product.stock - quantity },
    });

    // Record sale
    return await tx.sale.create({
      data: {
        productId,
        quantity,
        totalPrice: product.price * quantity,
        synced: false,  // Mark for cloud sync
      },
    });
  });
}

async function getSalesToSync() {
  return await prisma.sale.findMany({
    where: { synced: false },
    include: { product: true },
    orderBy: { soldAt: 'asc' },
    take: 100,
  });
}

async function markSalesSynced(saleIds: number[]) {
  return await prisma.sale.updateMany({
    where: { id: { in: saleIds } },
    data: { synced: true },
  });
}

// $ npx prisma migrate dev --name init
// $ npx prisma migrate deploy

Offline Sync Logic:

import { PrismaClient } from '@prisma/client';
import axios from 'axios';

const prisma = new PrismaClient();

async function syncToCloud() {
  try {
    // Get unsynced sales from HeliosDB-Lite
    const sales = await getSalesToSync();

    if (sales.length === 0) {
      console.log('No sales to sync');
      return;
    }

    // Upload to cloud
    const response = await axios.post(
      process.env.CLOUD_SYNC_URL!,
      { sales },
      { timeout: 30000 }
    );

    if (response.status === 200) {
      // Mark as synced
      const saleIds = sales.map((s) => s.id);
      await markSalesSynced(saleIds);
      console.log(`Synced ${sales.length} sales to cloud`);
    }
  } catch (error) {
    // Stay offline—retry later
    console.warn('Sync failed (offline?), will retry:', error.message);
  }
}

// Sync every 5 minutes when online
setInterval(syncToCloud, 5 * 60 * 1000);

---

Example 3: Java JDBC Integration - Enterprise Spring Boot Application

Scenario: Enterprise logistics company with 200 Java microservices using Spring Boot + PostgreSQL. PostgreSQL server too heavy; SQLite breaks existing JDBC code.

Spring Boot Configuration (application.yml - MINIMAL CHANGES):

spring:
  datasource:
    # Changed from cloud PostgreSQL to embedded HeliosDB-Lite
    url: jdbc:postgresql://localhost:5432/warehouse_inventory
    username: warehouse_app
    password: ${DB_PASSWORD}
    driver-class-name: org.postgresql.Driver

    # Connection pool (HikariCP)
    hikari:
      maximum-pool-size: 10
      minimum-idle: 2
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000

  jpa:
    database-platform: org.hibernate.dialect.PostgreSQLDialect
    hibernate:
      ddl-auto: validate
    properties:
      hibernate:
        jdbc:
          batch_size: 50
        order_inserts: true
        order_updates: true

# HeliosDB-specific tuning (optional)
heliosdb:
  memory_limit_mb: 128
  wal_enabled: true
  prepared_statement_cache: 100

JPA Entity (UNCHANGED from PostgreSQL):

package com.logistics.inventory.entity;

import javax.persistence.*;
import java.math.BigDecimal;
import java.time.LocalDateTime;

@Entity
@Table(name = "inventory_items", indexes = {
    @Index(name = "idx_sku", columnList = "sku"),
    @Index(name = "idx_warehouse_sku", columnList = "warehouse_id, sku")
})
public class InventoryItem {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, length = 50)
    private String sku;

    @Column(nullable = false)
    private String warehouseId;

    @Column(nullable = false)
    private Integer quantity;

    @Column(precision = 10, scale = 2)
    private BigDecimal unitPrice;

    @Column(nullable = false)
    private LocalDateTime lastUpdated;

    @Column(nullable = false)
    private Boolean synced = false;

    // Getters and setters (unchanged)
    // ...
}

Repository (Spring Data JPA - UNCHANGED):

package com.logistics.inventory.repository;

import com.logistics.inventory.entity.InventoryItem;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

@Repository
public interface InventoryItemRepository extends JpaRepository<InventoryItem, Long> {

    // Query methods work identically with HeliosDB-Lite
    Optional<InventoryItem> findBySkuAndWarehouseId(String sku, String warehouseId);

    List<InventoryItem> findByWarehouseIdAndSyncedFalse(String warehouseId);

    @Query("SELECT i FROM InventoryItem i WHERE i.quantity < :threshold AND i.warehouseId = :warehouseId")
    List<InventoryItem> findLowStockItems(String warehouseId, Integer threshold);

    // Native query (PostgreSQL syntax works)
    @Query(value = "SELECT * FROM inventory_items WHERE synced = false ORDER BY last_updated LIMIT :limit", nativeQuery = true)
    List<InventoryItem> findUnsyncedItemsForBatch(int limit);
}

Service Layer (Business Logic - UNCHANGED):

package com.logistics.inventory.service;

import com.logistics.inventory.entity.InventoryItem;
import com.logistics.inventory.repository.InventoryItemRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.time.LocalDateTime;
import java.util.List;

@Service
public class InventoryService {

    @Autowired
    private InventoryItemRepository repository;

    @Transactional
    public InventoryItem updateQuantity(String sku, String warehouseId, int delta) {
        InventoryItem item = repository.findBySkuAndWarehouseId(sku, warehouseId)
            .orElseThrow(() -> new IllegalArgumentException("Item not found"));

        item.setQuantity(item.getQuantity() + delta);
        item.setLastUpdated(LocalDateTime.now());
        item.setSynced(false);  // Mark for sync

        return repository.save(item);
    }

    @Transactional
    public void processScan(String sku, String warehouseId, int scannedQuantity) {
        // Prepared statement reused via JDBC (cached by HeliosDB-Lite)
        InventoryItem item = repository.findBySkuAndWarehouseId(sku, warehouseId)
            .orElseGet(() -> {
                InventoryItem newItem = new InventoryItem();
                newItem.setSku(sku);
                newItem.setWarehouseId(warehouseId);
                newItem.setQuantity(0);
                newItem.setLastUpdated(LocalDateTime.now());
                return newItem;
            });

        item.setQuantity(scannedQuantity);
        item.setLastUpdated(LocalDateTime.now());
        item.setSynced(false);

        repository.save(item);
    }

    public List<InventoryItem> getUnsyncedItems(String warehouseId) {
        return repository.findByWarehouseIdAndSyncedFalse(warehouseId);
    }

    @Transactional
    public void markSynced(List<Long> itemIds) {
        List<InventoryItem> items = repository.findAllById(itemIds);
        items.forEach(item -> item.setSynced(true));
        repository.saveAll(items);
    }
}

JDBC Batch Operations (for high throughput):

package com.logistics.inventory.batch;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
public class BatchInventoryUpdater {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public void batchUpdateFromScanner(List<InventoryUpdate> updates) {
        // Uses prepared statement (cached by HeliosDB-Lite)
        String sql = """
            INSERT INTO inventory_items (sku, warehouse_id, quantity, last_updated, synced)
            VALUES (?, ?, ?, NOW(), false)
            ON CONFLICT (sku, warehouse_id)
            DO UPDATE SET
                quantity = EXCLUDED.quantity,
                last_updated = EXCLUDED.last_updated,
                synced = false
        """;

        jdbcTemplate.batchUpdate(sql, updates, updates.size(),
            (ps, update) -> {
                ps.setString(1, update.getSku());
                ps.setString(2, update.getWarehouseId());
                ps.setInt(3, update.getQuantity());
            });
    }
}

---

Example 4: Rust sqlx Integration - High-Performance Microservice

Architecture:

┌───────────────────────────────────┐
│  Analytics Microservice           │
├───────────────────────────────────┤
│  Actix-web HTTP Server            │
├───────────────────────────────────┤
│  sqlx (Compile-time Checked SQL)  │
├───────────────────────────────────┤
│  HeliosDB-Lite (In-Process)       │
│  PostgreSQL Protocol Adapter      │
├───────────────────────────────────┤
│  RocksDB LSM Storage (2GB)        │
└───────────────────────────────────┘

---

Example 5: Cross-Platform Desktop App - Electron + PostgreSQL Ecosystem

Scenario: Healthcare records desktop app (Windows/macOS/Linux) needs HIPAA-compliant local storage with existing PostgreSQL tooling (pgAdmin for admin tasks, DataGrip for developers). SQLite breaks ecosystem; full PostgreSQL too heavy for desktops.

Architecture:

┌─────────────────────────────────────────┐
│  Electron Desktop App (Windows/Mac)     │
├─────────────────────────────────────────┤
│  React Frontend                          │
├─────────────────────────────────────────┤
│  IPC Bridge (Electron Main Process)     │
├─────────────────────────────────────────┤
│  HeliosDB-Lite Embedded Server           │
│  ├── PostgreSQL Protocol (localhost)    │
│  └── AES-256 Encryption (HIPAA)         │
├─────────────────────────────────────────┤
│  Local Encrypted Database File          │
│  (~/.healthapp/patients.db.encrypted)   │
└─────────────────────────────────────────┘

External Tools (Same Machine):
- pgAdmin 4 → localhost:5432
- DataGrip → localhost:5432
- psql → localhost:5432

Electron Main Process (TypeScript):

import { app, BrowserWindow, ipcMain } from 'electron';
import { spawn, ChildProcess } from 'child_process';
import path from 'path';
import { Client } from 'pg';

let heliosdbProcess: ChildProcess | null = null;
let dbClient: Client | null = null;

async function startHeliosDB() {
  const dbPath = path.join(app.getPath('userData'), 'healthrecords.db');
  const configPath = path.join(app.getPath('userData'), 'heliosdb.toml');

  // Start HeliosDB-Lite embedded server
  heliosdbProcess = spawn('./bin/heliosdb-lite', [
    '--config', configPath,
    '--data-dir', dbPath,
    '--port', '5432',
    '--host', '127.0.0.1',
  ]);

  heliosdbProcess.stdout?.on('data', (data) => {
    console.log(`HeliosDB: ${data}`);
  });

  // Wait for server ready
  await new Promise((resolve) => setTimeout(resolve, 2000));

  // Connect via standard PostgreSQL client
  dbClient = new Client({
    host: 'localhost',
    port: 5432,
    database: 'healthrecords',
    user: 'healthapp',
    password: process.env.DB_PASSWORD, // Encrypted in OS keychain
  });

  await dbClient.connect();
  console.log('Connected to HeliosDB-Lite via PostgreSQL protocol');
}

// IPC handlers use standard pg client
ipcMain.handle('db:query', async (event, sql: string, params: any[]) => {
  if (!dbClient) throw new Error('Database not initialized');
  const result = await dbClient.query(sql, params);
  return result.rows;
});

ipcMain.handle('db:transaction', async (event, queries: Array<{ sql: string; params: any[] }>) => {
  if (!dbClient) throw new Error('Database not initialized');

  await dbClient.query('BEGIN');
  try {
    const results = [];
    for (const { sql, params } of queries) {
      const result = await dbClient.query(sql, params);
      results.push(result.rows);
    }
    await dbClient.query('COMMIT');
    return results;
  } catch (error) {
    await dbClient.query('ROLLBACK');
    throw error;
  }
});

app.on('ready', async () => {
  await startHeliosDB();
  createWindow();
});

app.on('will-quit', () => {
  dbClient?.end();
  heliosdbProcess?.kill('SIGTERM');
});

React Frontend (TypeScript):

import { useState, useEffect } from 'react';

const { ipcRenderer } = window.require('electron');

interface Patient {
  id: number;
  mrn: string;
  name: string;
  dob: string;
  ssn_encrypted: string;
}

export function PatientSearch() {
  const [patients, setPatients] = useState<Patient[]>([]);
  const [searchTerm, setSearchTerm] = useState('');

  const searchPatients = async () => {
    const results = await ipcRenderer.invoke('db:query',
      'SELECT id, mrn, name, dob, ssn_encrypted FROM patients WHERE name ILIKE $1 LIMIT 50',
      [`%${searchTerm}%`]
    );
    setPatients(results);
  };

  return (
    <div>
      <input
        type="text"
        value={searchTerm}
        onChange={(e) => setSearchTerm(e.target.value)}
        placeholder="Search patients..."
      />
      <button onClick={searchPatients}>Search</button>

      <table>
        <thead>
          <tr>
            <th>MRN</th>
            <th>Name</th>
            <th>Date of Birth</th>
          </tr>
        </thead>
        <tbody>
          {patients.map((p) => (
            <tr key={p.id}>
              <td>{p.mrn}</td>
              <td>{p.name}</td>
              <td>{p.dob}</td>
            </tr>
          ))}
        </tbody>
      </table>
    </div>
  );
}

Admin Access via pgAdmin (No Code—Standard Tool):

# Administrator connects with pgAdmin to localhost:5432
# Uses HeliosDB-Lite exactly like PostgreSQL:
# - View tables, run queries, export data
# - Create indexes, run VACUUM, check stats
# - No custom tools needed—standard PostgreSQL ecosystem

Developer Access via psql:

# Developer debugging on Windows/Mac
psql -h localhost -p 5432 -U healthapp -d healthrecords

healthrecords=# \dt
              List of relations
 Schema |     Name     | Type  |  Owner
--------+--------------+-------+----------
 public | patients     | table | healthapp
 public | visits       | table | healthapp
 public | prescriptions| table | healthapp

healthrecords=# SELECT COUNT(*) FROM patients;
 count
-------
  1523
(1 row)

healthrecords=# EXPLAIN ANALYZE SELECT * FROM patients WHERE mrn = 'MRN001234';
-- Works identically to PostgreSQL

---

Adoption Strategy

Phase 1: Proof of Concept (Weeks 1-2)

Target: Validate PostgreSQL protocol compatibility with existing application

Tactics:

• Connect Existing Client: Point psql or pgAdmin to HeliosDB Lite on localhost:5432

   psql -h localhost -p 5432 -U myapp -d test

• Run Simple Queries: Execute basic SELECT/INSERT/UPDATE to verify protocol
• Test Existing Application: Change connection string, run integration tests
• Benchmark Performance: Compare latency vs cloud PostgreSQL

Deliverable: Internal POC report with performance comparison

Phase 2: Pilot Deployment (Weeks 3-8)

Tactics:

• Migrate Low-Risk Service: Start with read-heavy or non-critical microservice
• Export data: pg_dump postgres://prod-db/service | psql -h localhost/heliosdb
• Switch connection string in config
• Monitor for protocol errors, performance regression
• Deploy to Edge Subset: Roll out to 10-50 edge devices with monitoring
• Validate ORMs: Test Prisma/SQLAlchemy/Hibernate migrations and queries
• Measure Business Impact: Track infrastructure cost reduction, latency improvements

Risk Mitigation:

• Keep PostgreSQL connection string in config for fast rollback
• Automated alerts for protocol errors or performance degradation

Phase 3: Full Rollout (Weeks 9-24)

Target: Organization-wide adoption for compatible workloads

Tactics:

• Tenant-by-Tenant Migration (SaaS):
• Validate data integrity (checksum comparison)
• Gradually increase to large tenants
• Microservice Migration (Enterprise):
• Automate deployment with Helm charts/Terraform
• Update CI/CD to use HeliosDB Lite for tests
• Edge Fleet Deployment (IoT):
• Containerize HeliosDB Lite with app (Docker/Kubernetes)
• Deploy via fleet management (AWS IoT Greengrass, Balena)
• Implement cloud sync for edge data aggregation
• Team Training:
• Document connection string changes
• Share migration runbooks
• Highlight "no code changes" to reduce friction

Long-Term Governance:

• Quarterly reviews of cost savings and performance

---

Conclusion

Yet this ecosystem has been locked to server-class deployments, forcing teams to abandon PostgreSQL expertise when workloads shrink below cloud database minimums or move to edge/embedded environments. SQLite and DuckDB offer lightweight alternatives but require complete application rewrites, break tool compatibility, and waste accumulated PostgreSQL knowledge.