PostgreSQL Wire Protocol

Protocol Overview

Absolute DB implements PostgreSQL frontend/backend protocol version 3 (introduced in PostgreSQL 7.4) in full. This is the wire protocol used by every modern PostgreSQL client library. The server listens on port 5433 by default; port 5432 is supported via the -p 5432 flag.

Property	Value
Protocol version	PostgreSQL wire protocol v3
Default port	5433 (configurable, `-p 5432` supported)
TLS	Native TLS 1.3 (no OpenSSL); post-quantum hybrid optional
Authentication	MD5, SCRAM-SHA-256, trust, peer, LDAP, OAuth2/OIDC JWT
Extended Query Protocol	Full: Parse / Bind / Describe / Execute / Sync / Close
Simple Query Protocol	Full
COPY protocol	Text, CSV, binary (PostgreSQL binary COPY format)
Notifications	NotificationResponse ('A' message); full LISTEN/NOTIFY

Absolute DB does not require any special driver. Use the standard postgresql:// connection string and any PostgreSQL client library. Set the port to 5433 (or 5432 if you started the server with -p 5432).

Compatible Clients

The following tools and libraries are fully compatible with Absolute DB's PostgreSQL wire implementation. Automated compatibility regression tests run against each of these in CI.

psql

pgAdmin 4

DBeaver

TablePlus

Beekeeper Studio

DataGrip

psycopg2

psycopg3

asyncpg

node-postgres (pg)

Prisma

Drizzle ORM

Sequelize

TypeORM

JDBC (pgjdbc)

Hibernate

jOOQ

Npgsql (.NET)

EF Core

pgx (Go)

lib/pq (Go)

GORM

Django ORM

SQLAlchemy

Rails ActiveRecord

PgBouncer

Odyssey

Flyway

Liquibase

Alembic

Connect: psql

The standard PostgreSQL interactive terminal connects directly to Absolute DB. Use the -p 5433 flag (or -p 5432 if you launched the server with that port).

bash — psql connections

# Connect using flags
psql -h localhost -p 5433 -U absdb -d mydb

# Connect using a connection string
psql "postgresql://absdb:password@localhost:5433/mydb"

# Connect with SSL
psql "postgresql://absdb:password@localhost:5433/mydb?sslmode=require"

# Once connected
mydb=# SELECT version();
mydb=# \l            -- list databases
mydb=# \dt           -- list tables in current schema
mydb=# \d orders     -- describe a table
mydb=# \timing       -- show query execution time
mydb=# \x            -- expanded display for wide results

pgAdmin, DBeaver, TablePlus, and Beekeeper Studio all connect using the same host/port/credentials — just select PostgreSQL as the database type.

Connect: Python

psycopg2 (synchronous)

python — psycopg2

import psycopg2

conn = psycopg2.connect(
    host="localhost", port=5433,
    dbname="mydb", user="absdb", password="password"
)
cur = conn.cursor()
cur.execute("SELECT id, name FROM users WHERE active = %s", (True,))
rows = cur.fetchall()
for row in rows:
    print(row)
conn.close()

asyncpg (async)

python — asyncpg

import asyncio, asyncpg

async def main():
    conn = await asyncpg.connect(
        "postgresql://absdb:password@localhost:5433/mydb"
    )
    rows = await conn.fetch("SELECT id, name FROM users LIMIT 10")
    for row in rows:
        print(dict(row))
    await conn.close()

asyncio.run(main())

SQLAlchemy

python — SQLAlchemy

from sqlalchemy import create_engine, text

engine = create_engine(
    "postgresql+psycopg2://absdb:password@localhost:5433/mydb"
)
with engine.connect() as conn:
    result = conn.execute(text("SELECT count(*) FROM orders"))
    print(result.scalar())

Connect: Node.js

node-postgres (pg)

javascript — node-postgres

const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost', port: 5433,
  database: 'mydb', user: 'absdb', password: 'password',
});

const { rows } = await pool.query(
  'SELECT id, name FROM users WHERE active = $1', [true]
);
console.log(rows);

Prisma

env — .env

DATABASE_URL="postgresql://absdb:password@localhost:5433/mydb?schema=public"

prisma — schema.prisma

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id    Int    @id @default(autoincrement())
  name  String
  email String @unique
}

Drizzle ORM

typescript — Drizzle ORM

import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';

const pool = new Pool({
  connectionString: 'postgresql://absdb:password@localhost:5433/mydb'
});
const db = drizzle(pool);

Connect: Java (JDBC)

java — JDBC connection

import java.sql.*;

String url = "jdbc:postgresql://localhost:5433/mydb";
Properties props = new Properties();
props.setProperty("user", "absdb");
props.setProperty("password", "password");

try (Connection conn = DriverManager.getConnection(url, props)) {
    PreparedStatement stmt = conn.prepareStatement(
        "SELECT id, name FROM users WHERE active = ?"
    );
    stmt.setBoolean(1, true);
    ResultSet rs = stmt.executeQuery();
    while (rs.next()) {
        System.out.println(rs.getInt("id") + " " + rs.getString("name"));
    }
}

Hibernate and Spring Data JPA use the same JDBC URL. Add spring.datasource.url=jdbc:postgresql://localhost:5433/mydb to your application.properties.

Connect: .NET (Npgsql)

csharp — Npgsql

using Npgsql;

var connString = "Host=localhost;Port=5433;Database=mydb;Username=absdb;Password=password";

await using var conn = new NpgsqlConnection(connString);
await conn.OpenAsync();

await using var cmd = new NpgsqlCommand("SELECT id, name FROM users WHERE active = $1", conn);
cmd.Parameters.AddWithValue(true);

await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync()) {
    Console.WriteLine($"{reader.GetInt32(0)} {reader.GetString(1)}");
}

Entity Framework Core with Npgsql uses the same connection string:

csharp — Entity Framework Core

services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(
        "Host=localhost;Port=5433;Database=mydb;Username=absdb;Password=password"
    ));

Connect: Go

pgx (recommended)

go — pgx v5

package main

import (
    "context"
    "fmt"
    "github.com/jackc/pgx/v5"
)

func main() {
    conn, err := pgx.Connect(context.Background(),
        "postgresql://absdb:password@localhost:5433/mydb")
    if err != nil {
        panic(err)
    }
    defer conn.Close(context.Background())

    rows, err := conn.Query(context.Background(),
        "SELECT id, name FROM users WHERE active = $1", true)
    if err != nil {
        panic(err)
    }
    defer rows.Close()

    for rows.Next() {
        var id int
        var name string
        rows.Scan(&id, &name)
        fmt.Printf("%d %s\n", id, name)
    }
}

GORM

go — GORM

import (
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

dsn := "host=localhost user=absdb password=password dbname=mydb port=5433 sslmode=disable"
db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})

Connect: Rails / Django / Sequelize

Rails ActiveRecord

yaml — config/database.yml

production:
  adapter:  postgresql
  host:     localhost
  port:     5433
  database: myapp_production
  username: absdb
  password: <%= ENV['ABSDB_PASSWORD'] %>

Django

python — settings.py

DATABASES = {
    'default': {
        'ENGINE':   'django.db.backends.postgresql',
        'HOST':     'localhost',
        'PORT':     '5433',
        'NAME':     'myapp',
        'USER':     'absdb',
        'PASSWORD': os.environ['ABSDB_PASSWORD'],
    }
}

Sequelize (Node.js)

javascript — Sequelize

const { Sequelize } = require('sequelize');

const sequelize = new Sequelize('mydb', 'absdb', 'password', {
  host:    'localhost',
  port:    5433,
  dialect: 'postgres',
});

Prepared Statements & Plan Cache

Absolute DB implements the full PostgreSQL Extended Query Protocol: Parse / Bind / Describe / Execute / Sync / Close. Prepared statements are stored in a plan cache keyed by the query text. The cache holds up to 1,024 plans with LRU eviction. Cached plan execution runs at approximately 2 µs — 40× faster than a cold SQL parse (~85 µs).

Plans are automatically invalidated on DDL changes (ALTER TABLE, CREATE INDEX, DROP TABLE, etc.).

sql — prepared statements

-- Prepare a statement (plan compiled and cached)
PREPARE get_user (INTEGER) AS
    SELECT id, name, email FROM users WHERE id = $1;

-- Execute (uses cached plan, ~2 µs)
EXECUTE get_user(42);
EXECUTE get_user(99);

-- Describe (returns result set metadata)
-- (done automatically by the Extended Query Protocol)

-- Release the prepared statement
DEALLOCATE get_user;

-- List current prepared statements
SELECT name, statement FROM pg_prepared_statements;

Most client libraries use the Extended Query Protocol automatically when you call parameterised queries. You get the plan cache benefit without writing explicit PREPARE statements.

Query Result Cache

For read-heavy queries with predictable inputs, use the result cache hint to cache the entire result set:

sql — result cache

-- Cache result for 60 seconds (invalidated on any write to 'products')
SELECT /*+CACHE(ttl=60)*/ id, name, price
FROM products
WHERE category = 'electronics'
ORDER BY price;

COPY TO / FROM

The PostgreSQL COPY command is fully supported for bulk data loading and export. The binary COPY format is 3× faster than text/CSV for large imports. Absolute DB supports COPY TO STDOUT and COPY FROM STDIN for streaming bulk operations.

sql — COPY examples

-- Bulk load from a CSV file
COPY products (id, name, price, category)
FROM '/data/products.csv'
WITH (FORMAT csv, HEADER true, DELIMITER ',');

-- Export to CSV
COPY (SELECT id, name, price FROM products WHERE in_stock = true)
TO '/data/active_products.csv'
WITH (FORMAT csv, HEADER true);

-- Streaming COPY from stdin (used by psql \copy and most ORMs)
COPY orders (customer_id, total, created_at)
FROM STDIN WITH (FORMAT csv);
42,199.99,2026-04-01
43,49.99,2026-04-01
\.

-- Binary COPY (3× faster for large imports)
COPY orders FROM '/data/orders.bin' WITH (FORMAT binary);

bash — psql bulk load

# Use \copy (client-side) to load a local file via psql
psql -h localhost -p 5433 -U absdb -d mydb \
    -c "\copy products FROM '/local/path/products.csv' CSV HEADER"

LISTEN / NOTIFY

Absolute DB delivers LISTEN/NOTIFY notifications using the standard PostgreSQL NotificationResponse message ('A' message type). Every library that handles PG async notifications works without modification. See the Live Queries & Streaming documentation for complete examples and framework integrations.

sql — LISTEN/NOTIFY basics

LISTEN my_channel;
NOTIFY my_channel, '{"event": "order_placed", "id": 42}';
UNLISTEN my_channel;

Advisory Locks

All PostgreSQL advisory lock functions are implemented, enabling compatibility with migration tools (Flyway, Liquibase, Alembic, golang-migrate) and any application using session or transaction-scoped application locks. Up to 65,536 concurrent advisory locks are supported per server.

sql — advisory locks

-- Used by Flyway, Liquibase, Alembic automatically
SELECT pg_advisory_lock(-5432805064034915249);   -- migrate lock key
-- ... run migrations ...
SELECT pg_advisory_unlock(-5432805064034915249);

-- Application-defined session lock
SELECT pg_advisory_lock(hash_record(('myapp', 'job_processor')::record));

-- Transaction-scoped (auto-released at COMMIT/ROLLBACK)
BEGIN;
SELECT pg_advisory_xact_lock(99999);
-- ... exclusive work ...
COMMIT;

Trigram Similarity (pg_trgm)

pg_trgm functions and operators are built into Absolute DB — no extension needed. Trigram GIN indexes and the similarity() / % operator work exactly as they do in PostgreSQL, so any code or ORM query using pg_trgm features runs without changes.

sql — pg_trgm compatibility

-- GIN trigram index (identical syntax to PostgreSQL)
CREATE INDEX idx_products_name_trgm
    ON products USING GIN (name gin_trgm_ops);

-- Fuzzy name search
SELECT name, similarity(name, $1) AS score
FROM products
WHERE name % $1
ORDER BY score DESC
LIMIT 10;

-- Check if pg_trgm is "installed" (returns true for compatibility)
SELECT extname FROM pg_extension WHERE extname = 'pg_trgm';

Tools that check pg_extension for pg_trgm will find it listed. Absolute DB reports pg_trgm as a built-in feature via the pg_extension system view for maximum compatibility with ORMs and tools that probe for extensions.

PostgreSQL Wire Protocol

Contents

Protocol Overview

Compatible Clients

Connect: psql

Connect: Python

psycopg2 (synchronous)

asyncpg (async)

SQLAlchemy

Connect: Node.js

node-postgres (pg)

Prisma

Drizzle ORM

Connect: Java (JDBC)

Connect: .NET (Npgsql)

Connect: Go

pgx (recommended)

GORM

Connect: Rails / Django / Sequelize

Rails ActiveRecord

Django

Sequelize (Node.js)

Prepared Statements & Plan Cache

Query Result Cache

COPY TO / FROM

LISTEN / NOTIFY

Advisory Locks

Trigram Similarity (pg_trgm)

Continue Reading

Ready to run Absolute DB?