Speaking fluent PostgreSQL

Over the last couple of years I have ended up working with the PostgreSQL wire protocol from three different directions: building Postgres-compatible servers (DataFusion Postgres, where my merged work covered transaction semantics, parameter handling, connection limits, and statement timeouts), embedding a PGWire layer in a time-series database, and contributing to tapgres, a tool by the maintainer of the pgwire crate that taps a live connection and decodes the raw traffic back into something readable. My work there spans live connection metrics and a typed, Wireshark-inspired display-filter language.

Serving the protocol teaches you what you must say. Decoding it teaches you what clients actually say. The two lessons are not the same, and the gap between them is where every “Postgres-compatible” database quietly breaks.

This post is the field guide I wish I’d had at the start.

”Postgres-compatible” is a protocol claim, not a SQL claim

When a new database advertises Postgres compatibility, people hear “it understands my SQL.” That’s the smaller half. The bigger half is: psql connects to it, every driver in every language connects to it, your BI tool connects to it, because they all speak one binary protocol on port 5432. The protocol is the network effect. SQL dialects can be translated; a driver that hangs during startup cannot.

Which means the real compatibility surface is not the SQL grammar. It’s a state machine of typed binary messages, and clients are merciless about it. They don’t read your documentation. They send what libpq would send and expect what Postgres would answer, byte for byte.

The startup dance

Before a single query flows, a surprising amount of theatre happens. A client connects and usually sends an SSLRequest: eight bytes asking “do you speak TLS?” The server answers with a single raw byte, S or N, one of the very few places in the protocol where a naked byte crosses the wire outside a framed message. Get this wrong and nothing else ever happens.

Then comes the StartupMessage (protocol version 3.0, user, database, options), authentication (cleartext, MD5, or SCRAM-SHA-256 these days), and then the part most people don’t know exists: the server volunteers a barrage of ParameterStatus messages announcing server_version, server_encoding, client_encoding, DateStyle, TimeZone, integer_datetimes, and friends. Drivers cache these and change their behaviour based on them. After a BackendKeyData (for query cancellation) the server finally says ReadyForQuery, and the conversation can begin.

Here’s the thing a compatible server learns immediately: you have to lie a little. You must report a plausible server_version, because drivers parse it and gate features on it. And the moment psql connects it starts interrogating pg_catalog: introspection queries against pg_class, pg_namespace, pg_type, powering every \d command and half of every ORM’s boot sequence. If you don’t serve a convincing catalog, clients don’t degrade gracefully. They break, confidently. A meaningful fraction of “implement the Postgres protocol” is actually “implement enough of the catalog to survive introspection.”

There are two protocols in there

The protocol has two query modes, and clients split cleanly across them.

The simple protocol is what psql uses: one Query message carrying SQL text, answered by RowDescription, some DataRows, CommandComplete, ReadyForQuery. In tapgres output (F→B is client to server, B→F the reverse):

[F→B] Query: SELECT id, name FROM users
[B→F] RowDescription: id(oid=23), name(oid=25)
[B→F] DataRow: { id=1, name='alice' }
[B→F] CommandComplete: SELECT 1
[B→F] ReadyForQuery: txn=idle

The extended protocol is what almost every driver and ORM uses, and it’s a different animal: Parse (create a prepared statement), Bind (attach parameter values to make a portal), Describe (ask what the results will look like), Execute (run the portal, optionally row-limited), Sync (commit the pipeline and get a fresh ReadyForQuery). Five messages where the simple protocol had one, and they pipeline: a driver can fire Parse/Bind/Describe/Execute/Sync in a single write and read all the responses at once.

The subtleties live here. Parameters arrive with declared type OIDs, or OID zero meaning “you figure it out,” so the server needs real type inference. Every parameter and every result column can independently be text or binary format, negotiated per message. Binary timestamps are microseconds since 2000-01-01, not the Unix epoch, a fact I now know at a permanent, cellular level from implementing timestamp parameters and getting the ordering of type resolution wrong before getting it right.

Watching real drivers through tapgres is humbling. A driver “just running a query” typically parses an unnamed statement, describes it to learn the result shape, binds with a mix of formats, executes, and syncs, and may prepare the same statement again on another connection because prepared statements are per-connection state. Multiply by connection pools and you understand why pgbouncer’s transaction mode has a whole FAQ section about prepared statements.

Transactions are the hard part

Ask anyone who has built a Postgres-compatible layer what hurt most and you will not hear “parsing.” You’ll hear transactions.

Every ReadyForQuery carries a one-byte transaction status: I (idle), T (in a transaction), or E (in a failed transaction). Drivers watch this byte like a hawk. Connection pools use it to decide whether a connection is safe to hand out. If your server reports it wrong, you don’t get an error; you get a pool silently handing dirty connections to other requests, which is worse.

The semantics fan out from there. A multi-statement simple Query runs as one implicit transaction: any statement failing rolls back all of it. Once a transaction fails, the server must answer everything except ROLLBACK with “current transaction is aborted”, and in the extended protocol, errors skip all messages until the next Sync, a rule that exists so pipelined batches fail predictably. None of this is optional garnish. It’s load-bearing behaviour that clients rely on without knowing they do.

And if the engine underneath you is an analytical query engine with no real transactions, as in DataFusion’s case, you still have to speak transaction protocol honestly: accept BEGIN and COMMIT, track the state machine faithfully, report T and E truthfully, and refuse what you cannot actually deliver rather than nodding along. Alongside that sit the production behaviours nobody writes blog posts about: connection limits, statement timeouts, and permission checks done properly against the query’s AST rather than by regexing SQL strings. That was much of what my merged work there amounted to, and the maintainer feedback rounds taught me more about protocol edge cases than any document.

Debugging by wiretap

The reason I started contributing to tapgres is that at some point in every one of these projects, the question stops being “what should the protocol do” and becomes “what did that driver actually send.” Reading driver source code answers it slowly. Capturing the wire answers it instantly.

Passively capturing loopback traffic works until TLS gets involved, so tapgres also runs as a small TLS-terminating proxy: point the client at it, and it decrypts, decodes, and forwards. Add a Wireshark-style display filter (-Y 'message.type == "Query"', the filter language I’ve been building for it) and you can watch one connection’s life: the startup barrage, the catalog interrogation, the Parse/Bind/Execute rhythm, the transaction status flipping between I and T.

Once you can see the conversation, entire classes of bugs become trivial. The mystery hang is a driver waiting for a ReadyForQuery that your server forgot to send after an error. The “it works in psql but not in production” bug is the extended protocol taking a path your simple-protocol testing never exercised. The intermittent type error is one connection in the pool that never saw the Parse its neighbour did. I no longer consider a Postgres-compatible server debuggable without a wire view.

A field guide to faking Postgres

If you’re building (or evaluating) a Postgres-compatible system, here is the minimum honest subset, ranked by what breaks when you skip it:

1. Startup, TLS negotiation, auth, ParameterStatus, ReadyForQuery. Skip anything here and nothing connects at all.
2. The simple query protocol with correct RowDescription OIDs. Now psql one-liners work.
3. Enough pg_catalog to survive introspection. Now \d works, ORMs boot, BI tools list tables.
4. The extended protocol with real type inference and both parameter formats. Now drivers and applications work.
5. Honest transaction status and error/Sync semantics. Now connection pools stop corrupting state under load.
6. SQLSTATE error codes on ErrorResponse. Now applications’ retry logic behaves the way it does on real Postgres.

Most “compatible” databases stall between 3 and 5, which produces the worst possible outcome: the demo works and production doesn’t. Half-speaking Postgres is worse than not speaking it, because everything downstream trusts the accent completely.

The elephant has earned that trust over thirty years. If you borrow the trunk, you’re signing up to carry it.

Part two is up: Faking pg_catalog, on surviving the introspection interrogation.