Feature Roadmap: Wire Protocol and Driver Comparison

[SeanTAllen]

Research comparing the ponylang/postgres driver against the PostgreSQL wire protocol (v3) and popular PostgreSQL drivers from other languages (pgx for Go, tokio-postgres/sqlx for Rust, asyncpg/psycopg3 for Python, PgJDBC for Java, node-postgres for Node.js, Postgrex for Elixir).

Currently Supported

• Simple query protocol
• Extended query protocol (unnamed prepared statements, text-format params)
• MD5 password authentication
• Type conversion: bool, int2, int4, int8, float4, float8, String, None
• Full ErrorResponseMessage parsing (18 fields)
• Query queueing (serial execution)
• Session state machine with illegal-state guards

Tier 1 — Protocol Gaps / Table Stakes

Every mature driver has these.

1. SSL/TLS negotiation

SSLRequest + TLS handshake. The ssl dependency is already present (used for MD5 hashing) but not used for connection encryption. Every surveyed driver supports this.

2. SCRAM-SHA-256 authentication

The modern default since PostgreSQL 10. MD5 is deprecated. All 8 surveyed drivers support SCRAM. The protocol involves a multi-step SASL exchange (SASLInitialResponse, SASLContinue, SASLFinal).

3. Named prepared statements

Already on the roadmap as next priority. Current implementation uses unnamed only. Named statements persist across queries, enabling prepared statement caching for repeated queries (7/8 drivers support this).

4. Graceful termination

Send Terminate (X) message before closing TCP. Currently the connection is hard-closed without notifying the server.

5. BackendKeyData handling

The server sends process ID + secret key during startup. Currently silently consumed. Needed for query cancellation.

6. Query cancellation

CancelRequest on a separate TCP connection using the BackendKeyData. Supported by 6/8 drivers. Important for long-running queries.

7. ParameterStatus tracking

Server sends key-value pairs during startup and on changes (server_version, integer_datetimes, standard_conforming_strings, etc.). Currently silently consumed. Some values affect type encoding behavior.

8. Close message

Never sent currently. Needed for named prepared statement cleanup and explicit portal destruction.

9. Flush message

Force server output without Sync. Useful for getting Parse/Bind responses before sending Execute.

10. NoticeResponse handling

Informational messages from the server (same format as ErrorResponse). Currently silently consumed. Should be surfaced to the user via a callback.

11. Expanded type conversions

Currently 7 types. High-value additions based on real-world usage:

• numeric (1700) — arbitrary precision, no native Pony type, return as String
• date (1082) — ISO format String
• timestamp (1114) / timestamptz (1184) — ISO format String
• uuid (2950) — String
• json (114) / jsonb (3802) — String
• bytea (17) — Array[U8] val
• interval (1186) — String
• varchar (1043) / bpchar (1042) — String (already handled by default, but explicit OID mapping is cleaner)

Tier 2 — Important Features

5-6 out of 8 mature drivers have these.

12. COPY IN protocol

Bulk data loading via COPY ... FROM STDIN. Supported by 7/8 drivers. Orders of magnitude faster than individual INSERTs for bulk loads. Requires CopyInResponse, CopyData, CopyDone/CopyFail message handling.

13. COPY OUT protocol

Bulk data export via COPY ... TO STDOUT. Requires CopyOutResponse, CopyData, CopyDone message handling.

14. LISTEN/NOTIFY

Asynchronous notification support. All 8 drivers support this. Requires handling NotificationResponse messages that can arrive between any other messages. Needs a notification callback on Session or a separate listener interface.

15. Portal-based cursors (partial result fetching)

Execute with max_rows > 0, handle PortalSuspended, re-Execute to continue. Currently max_rows is hardcoded to 0. Essential for large result sets (6/8 drivers).

16. Describe for statements

Currently only Describe for portals. Statement describe returns ParameterDescription (input param types), which is useful for validating parameter types before binding.

17. Transaction status tracking

ReadyForQuery includes I/T/E status. Currently used only to determine query readiness (idle vs not). Exposing transaction status to the user enables better transaction management.

18. Row streaming

Currently all rows are buffered in memory before delivery. A streaming API would let users process rows as they arrive without buffering the full result set. Supported by 7/8 drivers.

19. Custom type codec registry

Let users register their own OID-to-Pony-type converters without modifying the driver. 7/8 drivers support this.

20. Pipelining

Send multiple Parse/Bind/Execute/Sync sequences without waiting for responses. Currently queries are strictly serialized. 3/8 drivers support this, but it's the most performance-focused ones (pgx, tokio-postgres, psycopg3).

Tier 3 — Nice to Have / Longer Term

21. Binary format for parameters and results

Currently text-only. Binary is more efficient for numeric types (no parse/format overhead). 6/8 drivers support binary.

22. Array type support

PostgreSQL arrays mapped to Pony arrays. Supported by all 8 drivers surveyed.

23. Composite/record type support

PostgreSQL composite types mapped to structured Pony types.

24. Enum type support

User-defined PostgreSQL enums.

25. SCRAM-SHA-256-PLUS (channel binding)

Binds authentication to TLS session, prevents MITM.

26. Connection timeout

Timeout on TCP connection establishment.

27. Statement/query timeout

Server-side or client-side timeout for long queries.

28. Multi-host connection with failover

Connect to first available host from a list. Demonstrated as a user-space pattern in examples/failover/ rather than built into the driver.

29. Automatic type OID discovery

Query pg_type catalog to handle custom/extension types dynamically.

30. Large object API

For storing/retrieving large binary data via server-side lo_* functions.

31. Cleartext password authentication

Simple but sometimes needed (e.g., behind SSL proxies).

API Improvements (not protocol features)

32. Pass session to ResultReceiver callbacks

Already noted as TODO. Would let receivers execute follow-up queries.

33. Equality on Rows/Row/Field

Already noted as TODO. Needed for testing and programmatic comparison.

34. Docstrings on all public types

Several public types lack documentation.

[SeanTAllen]

I opened an issue on Lori for item 1 (SSL/TSL Negotiation)... ponylang/lori#170

[SeanTAllen]

"3. Named prepared statements" has a design discussion at #77

[SeanTAllen]

Item 3 (named prepared statements) and item 8 (Close message) are now implemented — PR #78. Design discussion: #77.

[SeanTAllen]

Item 1 (SSL/TLS negotiation) has been implemented in #79

[SeanTAllen]

Design plan for SCRAM-SHA-256 authentication (the second Tier 1 item): #83

[SeanTAllen]

Number 32 "Pass session to ResultReceiver callbacks" added in #84

[SeanTAllen]

Number 33 "Equality on Rows/Row/Field" added in #85

[SeanTAllen]

Number 4 "Graceful termination" was implemented in #86

[SeanTAllen]

Number 5 "BackendKeyData handling" was implemented in #87

[SeanTAllen]

Design document for item #6 (Query cancellation): #88

[SeanTAllen]

Query cancellation was implemented by #89

[SeanTAllen]

Number 2 "SCRAM-SHA-256 authentication" was implemented in #94. Design discussion: #83.

[SeanTAllen]

Close message (statement closing) was added in PR #78 (named prepared statement support). Portal closing is not needed yet — nothing in the driver uses named portals or partial result fetching. Considering this done.

[SeanTAllen]

Transaction status tracking design: #102

[SeanTAllen]

LISTEN/NOTIFY design: #103

[SeanTAllen]

Design for COPY IN protocol posted: #104

[SeanTAllen]

Item #11 — Expanded type conversions is resolved.

After analysis in #118, only bytea (OID 17) → Array[U8] val was added (PR #119). The remaining types (uuid, date, timestamp, json, numeric, interval, varchar, bpchar) stay as String — there are no representative Pony stdlib types for them, and domain modeling is the caller's responsibility at the application boundary.

[SeanTAllen]

ParameterStatus tracking (#7) is done — implemented in PR #120.

[SeanTAllen]

#13 COPY OUT protocol — Implemented in #122.

[SeanTAllen]

Row streaming design: #123

[SeanTAllen]

Items 9 (Flush message) and 15 (Portal-based cursors) are both covered by the row streaming design (#123). Flush is required for between-batch communication, and portal-based cursors are the protocol mechanism that streaming is built on.

[SeanTAllen]

Pipelining (item #20) implemented in #138.

[SeanTAllen]

Binary types (#21) and Custom codecs (#19) implemented in #150

[SeanTAllen]

1D array support number 22, implemented in #156.

[SeanTAllen]

Statement/query timeout implemented in #178

[SeanTAllen]

Connection timeout implemented in #179

[SeanTAllen]

Item 28 (multi-host connection with failover) is demonstrated as a user-space pattern in examples/failover/. The driver's Session manages a single connection, and failover orchestration doesn't belong in the driver — it's straightforward to build on top by creating multiple sessions and using the first to authenticate.

[SeanTAllen]

#180

[SeanTAllen]

Cleartext password support added in #181

[SeanTAllen]

Remaining Items — Driver Support Survey

Research across the 8 surveyed drivers (pgx, tokio-postgres, sqlx, asyncpg, psycopg3, PgJDBC, node-postgres, Postgrex) for the 5 items that haven't been implemented yet:

#	Feature	Drivers that support it	Count
23	Composite/record type support	pgx, tokio-postgres, sqlx, asyncpg, psycopg3, Postgrex	6/8
24	Enum type support	all 8	8/8
25	SCRAM-SHA-256-PLUS (channel binding)	pgx, tokio-postgres, psycopg3, PgJDBC, node-postgres	5/8
29	Automatic type OID discovery	tokio-postgres, sqlx, asyncpg, PgJDBC, Postgrex (+ psycopg3 partial)	5–6/8
30	Large object API	pgx, PgJDBC	2/8

Details

Composite types (#23): PgJDBC and node-postgres are the two that don't — PgJDBC returns raw strings in PGobject (open issue since 2015), node-postgres returns unparsed string representations. The other six all provide structured decoding with varying levels of automation (asyncpg is fully automatic; pgx/tokio-postgres/sqlx/psycopg3 use registration or derive macros; Postgrex decodes to tuples by default).

Enum types (#24): Universal support, but depth varies significantly. tokio-postgres, sqlx, and psycopg3 offer rich compile-time or registration-based mapping between language enums and PostgreSQL enums. pgx and asyncpg provide runtime registration. PgJDBC, node-postgres, and Postgrex treat enum values as strings at the driver level and leave structured mapping to the application or ORM.

SCRAM-SHA-256-PLUS (#25): sqlx detects the mechanism but has it hardcoded off (rustls only recently added the necessary API). asyncpg deliberately chose not to implement it (noted in a source comment). Postgrex has no implementation at all. The 5 that support it all use tls-server-end-point channel binding. node-postgres added it relatively recently (pg 8.14.0, March 2025).

Automatic type OID discovery (#29): pgx had this in v3 but deliberately removed it in v4 for performance — now provides semi-manual LoadType + RegisterType (queries pg_type but requires explicit user calls). node-postgres uses only hardcoded OID-to-parser mappings. psycopg3 provides TypeInfo.fetch() which queries the catalog but requires the user to call it per type. The fully automatic drivers (tokio-postgres, sqlx, asyncpg, PgJDBC, Postgrex) all query pg_catalog.pg_type transparently during prepare or on encountering unknown types.

Large object API (#30): Only pgx (Go's LargeObjects type with io.Reader/io.Writer) and PgJDBC (org.postgresql.largeobject package) have built-in support. sqlx closed its feature request as "not planned." asyncpg and psycopg3 have open issues since 2021 with no implementation (psycopg2 had it but psycopg3 dropped it). node-postgres and Postgrex recommend calling PostgreSQL's lo_* SQL functions directly.

[SeanTAllen]

Enum type support (item #24) design discussion: #185

[SeanTAllen]

Item 24 (Enum type support) implemented in #186. Design: #185.