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.

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]

Number 12 "COPY IN protocol" was implemented in #112. Design discussion: #104.