14. PostgreSQL protocol

• Category: Tutorial

What you will learn

• How kdb serve speaks the PostgreSQL protocol on port 8832.
• How to connect from Go (pgx), Python, Node, psql and DBeaver.
• How KDB types become Postgres types. How times work.
• Transactions, error codes, and what does not work yet.

KDB is not PostgreSQL. But it speaks the same "wire protocol" (the messages between a program and the database). So a normal Postgres driver can send SQL to KDB and read the rows. You write KDB SQL (chapter 11). Much everyday PostgreSQL SQL works too: SERIAL, ON CONFLICT, RETURNING, WITH, arrays, information_schema (chapters 22 and 23).

Start the server

kdb serve starts the Postgres port together with the HTTP port:

kdb serve --token my-long-secret

KDB 0.2.0-dev
  console   http://127.0.0.1:9900
  postgres  127.0.0.1:8832 (PostgreSQL protocol, no TLS; password = the token)
  ...

Options:

Security: read this first

• No TLS. KDB answers "no" when a client asks for TLS (SSL). The password is safe (SCRAM-SHA-256 never sends it), but the data is not encrypted.

• So keep port 8832 on 127.0.0.1 (the default). Your Go backend on the same VPS connects to 127.0.0.1.

• From your PC, use an SSH tunnel:

  ssh -N -L 8832:127.0.0.1:8832 user@your-vps

  Then connect to 127.0.0.1:8832 on your PC. The tunnel encrypts everything.

• Without --token, only programs on the same computer (127.x.x.x) can connect, and no password is asked. Other computers are refused.

• With --token, every user name works, and the password is the token.

• With users (chapter 16), log in with a KDB user name and its password (SCRAM-SHA-256). Each statement runs with that user's role; a refused one gets SQLSTATE 42501. Connecting needs at least read on the database. With users and --token, a name that is not a KDB user may still log in with the token. Failed logins are slowed down like HTTP logins.

• Clients must use sslmode=disable or sslmode=prefer (the default of most drivers). sslmode=require fails, because there is no TLS.

Connection settings

A connection string:

postgres://app:my-long-secret@127.0.0.1:8832/market?sslmode=disable

Go (pgx)

This is how the NChartPro backend connects. Nothing special is needed.

package main

import (
	"context"
	"fmt"
	"time"

	"github.com/jackc/pgx/v5/pgxpool"
)

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, "postgres://app:my-long-secret@127.0.0.1:8832/market?sslmode=disable")
	if err != nil {
		panic(err)
	}
	defer pool.Close()

	// Parameters $1, $2, ... work. The values never become part of the SQL text.
	_, err = pool.Exec(ctx, "INSERT INTO securities (symbol, name, sector) VALUES ($1, $2, $3)",
		"COAST", "Coastal Bank", "Banking")
	if err != nil {
		panic(err)
	}

	rows, err := pool.Query(ctx,
		"SELECT time, close, volume FROM ochl_day WHERE symbol = $1 AND time >= $2 ORDER BY time",
		"CITY", time.Date(2026, 9, 1, 0, 0, 0, 0, time.UTC))
	if err != nil {
		panic(err)
	}
	defer rows.Close()
	for rows.Next() {
		var t time.Time
		var close, volume float64
		if err := rows.Scan(&t, &close, &volume); err != nil {
			panic(err)
		}
		fmt.Println(t.Format("2006-01-02"), close, volume)
	}
}

Everything pgx does works: the pool, QueryRow, pgx.CollectRows with RowToStructByName, Begin/Commit/Rollback, batches (SendBatch), and all query modes (the default cached statements, QueryExecModeSimpleProtocol, Exec, DescribeExec, CacheDescribe).

The test program tests/pg_go/main.go checks all of this against a real server. Its real output:

ok   connect and ping (SCRAM-SHA-256 login)
ok   version() = "PostgreSQL 14.0 (KDB 0.2.0-dev), compatible server", database "default"
ok   create table
ok   insert with parameters (int, text, float, numeric, bool, time, NULL)
ok   select with a parameter: {ID:1 Email:asha@example.com Balance:10.5 Active:true Created:2026-09-11 05:15:00.123 +0000 UTC} price 512.40
ok   pgx.CollectRows into structs: 2 rows
ok   LIKE, IN and LIMIT parameters
ok   transactions: rollback and commit
ok   unique violation: SQLSTATE 23505 (a row with id='1' already exists in 'users'. Use INSERT OR REPLACE to overwrite it, or INSERT OR IGNORE to skip it)
ok   unknown table 42P01; error inside a transaction, then rollback
ok   batch of 4 statements
ok   query modes: simple protocol, exec, describe exec, cache describe
ALL OK

Run it yourself:

kdb serve --data-dir D:\kdb-pg-test --port 9950 --pg-port 8850 --token secret-token
# in another window:
cd tests\pg_go
go run . "postgres://app:secret-token@127.0.0.1:8850/default"

Python

psycopg (version 3) and psycopg2 use the normal Postgres protocol, so they work the same way. The pure-Python driver pg8000 was tested (tests/pg_python/check_pg8000.py):

import pg8000.native

con = pg8000.native.Connection("app", host="127.0.0.1", port=8832,
                               database="market", password="my-long-secret")
rows = con.run("SELECT time, close FROM ochl_day WHERE symbol = :s ORDER BY time", s="CITY")
for t, close in rows:
    print(t, close)
con.close()

With psycopg:

import psycopg

with psycopg.connect("host=127.0.0.1 port=8832 dbname=market user=app password=my-long-secret sslmode=disable") as con:
    for row in con.execute("SELECT symbol, close FROM ochl_day WHERE close > %s", (500,)):
        print(row)

(psycopg was not run on the test PC: it has no build for its Python version yet.)

Node (pg)

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

const client = new Client({
  host: '127.0.0.1', port: 8832, user: 'app',
  password: 'my-long-secret', database: 'market',
});
await client.connect();
const res = await client.query('SELECT symbol, close FROM ochl_day WHERE symbol = $1', ['CITY']);
console.log(res.rows);
await client.end();

This was tested. Note about times: node-pg reads a timestamp (without time zone) as your computer's local time. On a PC set to Nepal time (+05:45), 05:15 UTC comes back as 23:30 of the day before. Start the server with --pg-timestamptz and times come back right.

Numbers: node-pg gives LONG and DECIMAL values as strings ('182390', '512.40'), as with PostgreSQL.

psql

$env:PGPASSWORD = "my-long-secret"
psql "host=127.0.0.1 port=8832 dbname=market user=app sslmode=disable"

market=> SELECT symbol, close FROM ochl_day WHERE symbol = 'CITY' LIMIT 2;
market=> BEGIN;
market=*> INSERT INTO brokers VALUES (99, 'Test Broker');
market=*> ROLLBACK;

Plain SQL works. KDB also answers the Postgres catalog (pg_catalog, information_schema), so \l (databases), \dt (tables), \di (indexes) and \du work. A full \d ochl_day still fails on one of the queries psql sends; use SHOW COLUMNS FROM ochl_day; and SHOW CREATE TABLE ochl_day; instead. \du shows one user, kdb: use SHOW USERS; for the real list.

\copy works too, because psql sends it as COPY ... FROM STDIN (see Loading many rows: COPY).

(psql is not installed on the test PC, so this example was not run.)

DBeaver

1. New Database Connection → PostgreSQL.
2. Host 127.0.0.1, port 8832, database market, user app, password = the token.
3. Driver properties: set sslmode to disable.
4. Connection settings → Initialization: turn off "Show all databases".
5. Open an SQL editor and run KDB SQL.

The SQL editor and the result grid work. The Database Navigator (the tree of schemas and tables) reads the Postgres catalog, which KDB answers: it lists the tables, their columns with types and defaults, indexes and constraints. Sizes stay empty, and row counts are not known.

(DBeaver was not run on the test PC.)

Types

Values go both ways in text and binary form. A driver picks the form; you do not have to.

Parameters get their type from where you use them. In WHERE volume > $1, $1 is LONG because volume is LONG. In INSERT INTO t (a, b) VALUES ($1, $2), each parameter gets its column's type. A parameter KDB cannot place (SELECT $1) has no type; the driver then sends it as text.

Computed columns. Before a query runs, KDB works out the type of each result column. For columns, count(*), sum, avg, arithmetic and most functions it knows the type. For a few (a saved function, say) it runs the query once with empty values to learn it. If a type is still unknown, the column is text. When Go then says cannot scan text into *float64, add a cast: SELECT my_func(close)::DOUBLE AS x ....

Times

KDB keeps a TIMESTAMP as one exact moment in UTC (chapter 4).

By default it is sent as Postgres timestamp (without time zone), like QuestDB does. With this type, drivers drop the time zone of a value you send:

• pgx: a Go time.Time in a local time (for example 11:00 +05:45) is stored as 11:00 UTC. Always send UTC times: t.UTC().
• node-pg: times you read come back in the computer's local time zone (see above).

Start the server with --pg-timestamptz to send TIMESTAMP as timestamptz. Then every driver keeps the exact moment, in any time zone. pgx then gives time.Time values in the local time zone of the Go program (the same moment). Choose one setting and keep it.

Times have milliseconds. Microseconds from a driver are cut.

Transactions

BEGIN, COMMIT and ROLLBACK work across messages, one transaction per connection. Postgres forms are accepted too: BEGIN ISOLATION LEVEL ..., START TRANSACTION, END, ABORT. (Every KDB transaction is serializable; the level is ignored.)

When a statement fails inside a transaction, the transaction is aborted, like in PostgreSQL. Every next statement gets error 25P02 until you send ROLLBACK (a COMMIT then also rolls back, and its tag is ROLLBACK). pgx does this for you: tx.Rollback(ctx).

• One writer per table (chapter 15). While one connection has an open transaction, a change of one of its tables from another connection waits up to 5 seconds, then fails with 55P03 (table 'x' is busy). Changes of other tables go on; a deadlock fails at once with 40P01. Reads do not wait. Keep transactions short.
• A transaction with no statement for 30 seconds is rolled back. The connection then gets 25P02 until it sends ROLLBACK.
• When a connection closes, its open transaction is rolled back.

INSERT, UPDATE and DELETE with RETURNING give rows, like a SELECT (in Go: pool.QueryRow(ctx, "INSERT ... RETURNING id", ...).Scan(&id)).

Errors

Errors carry a SQLSTATE code, so errors.As(err, &pgErr) and pgErr.Code work in Go:

What drivers send, and KDB answers

Drivers send a few Postgres commands that KDB SQL does not have. KDB answers them itself:

SHOW TABLES, SHOW COLUMNS FROM t and the other KDB SHOW statements still work as KDB statements.

Loading many rows: COPY

COPY is the fast way to load many rows over this port. pgx CopyFrom sends it, and so does psql's \copy. It loads hundreds of thousands of rows a second, about 1,000 times faster than one INSERT per round trip.

rows := [][]any{{int64(101), "Everest Capital"}, {int64(102), "Summit Traders"}}
n, err := pool.CopyFrom(ctx, pgx.Identifier{"brokers"}, []string{"id", "name"}, pgx.CopyFromRows(rows))

• All or nothing. One bad row (a wrong type, a key used twice, an empty NOT NULL column) fails the whole COPY, and nothing is written. The error names the row: COPY brokers: row 301: ....
• Inside BEGIN ... COMMIT the rows join the transaction; ROLLBACK removes them.
• A file name instead of STDIN or STDOUT is refused: KDB never reads or writes files on the server for a client.

Not supported yet

• TLS (use localhost or an SSH tunnel).
• A few parts of the Postgres catalog: a full psql \d <table> (use SHOW COLUMNS), sizes and row counts in the catalog tables, the real user list in pg_roles (use SHOW USERS).
• Postgres SQL that KDB does not have (for example ::regclass, MERGE). Chapters 22 and 23 list what works.
• LISTEN/NOTIFY (accepted, but no notifications come), functions calls of the old protocol, SAVEPOINT.
• Parameters in FILL(...), SAMPLE BY and other places where KDB needs a fixed value. LIMIT $1 and OFFSET $1 work.
• A simple query with several statements is not one transaction: statements before an error stay done. Use BEGIN ... COMMIT.
• SET ROLE, CREATE ROLE, PostgreSQL's full GRANT syntax: KDB has users with roles per database (chapter 16) and GRANT SELECT|INSERT|UPDATE|DELETE ON TABLE t TO user (chapter 21); no column or row rights.
• Users with two-factor login (chapter 21) cannot log in on this port (a driver cannot ask for the code). Give programs their own user.