Should postgres.js fetch the type catalog at all?

[okomarov]

Should postgres.js fetch PostgreSQL's type catalog at all? Dropping it would remove a recurring, billable cost and a whole class of connection-startup bugs, in exchange for an explicit cast or a JSON.stringify() in a few places in user code.

postgres.js downloads the array-type catalog on every new connection (fetch_types: true, the default) so it can serialize and parse arrays by their server type. node-postgres (pg) never does. Below: what fetching buys, what it costs, and what users would do instead. Code links are pinned to c109bc3.

What fetching the catalog buys

The query (fetchArrayTypes) registers every pg_type with typcategory = 'A'. That gives exactly three things beyond the 22 built-in array types the fork already knows without it (builtInArrays):

1. Writes to arrays of enums, extension types and built-in types outside the table (inet[], money[], …). Without a serializer for the OID the server reports, a JS array goes out as '' + x (a,b) and the server rejects it as a malformed array literal. That is the error people hit when they turn discovery off (Transactions always re-query all types leading to huge delay porsager/postgres#903).
2. Reads of those arrays as JS arrays: an enum array becomes ['a', 'b'] instead of the string '{a,b}'. Elements stay strings, because nothing parses an enum or composite element unless a parser is registered by OID.
3. sql.array(x) without a type for those element types, through the element-to-array map (index.js).

It does not cover domains over arrays, which are not in category 'A' (#80, porsager#592).

What it costs

When it is fetched. On every connection the client opens or re-opens: authentication resets the flag (authenticate) and the catalog query runs before the first query is handed over (initialized).

• Serverless (Workers, Lambda, Vercel Functions and the like) opens a connection per request, often for one or two queries, so the catalog is fetched per request and close to per query.
• A persisted pool fetches it once per connection, and again on every reconnect: every max_lifetime rotation (30–60 minutes by default), every idle_timeout close and every dropped connection. A pool of 10 downloads the same catalog 10 times, then again on each rotation, roughly 320 times a day per process from lifetime rotation alone, multiplied by every replica.

The catalog is the same for every connection to the same database. The most a discovery-based design can get is one fetch per pool (#7), and that does nothing on serverless, where the pool lives for one request.

Bytes. porsager#1219 measured a bare select 1 receiving 10,994 bytes with discovery and 686 without: 94% of what a fresh connection reads is the catalog. On a production Cloudflare Worker behind Hyperdrive, talking to a Supabase database, the query returns 448 rows (about 11 KB) and ran 9,064 times in the Worker's first ~21 hours: about 100 MB, against 110 MB of result bytes for everything. That is billable database egress, billable Workers CPU to parse (type registration was 4–6% of profile samples), and 17% of the statements counted against Hyperdrive's Free plan limit of 100,000 queries a day.

A round trip before the first query of every connection; porsager#903 and porsager#952 report 200 ms or more of added delay per transaction from the type query.

Bugs and maintenance. The code is small; its failure surface is not, because it runs inside connection startup and reconnect ordering. Fixed in the fork, all caused by discovery:

• the first query after a reconnect rejected with the closed session's error (fetch_types: Connection killed during startup replays its 57P01 onto the first query after reconnect and an unhandled rejection porsager/postgres#1223, Stale FATAL error from a terminated backend rejects the first query after reconnect (3.4.9) porsager/postgres#1226, Connection reconnect rejects the next query with a stale ErrorResponse from before the socket closed porsager/postgres#1249)
• sql.array() inferred the wrong array type on first use (Wrong inferred array types when invoking sql.array() for the first time. porsager/postgres#789)
• sql.reserve() hung on a pool that had not opened a connection, which also made fetch_types: false unusable for reserve() users (Trying to reserve a connection without previously executing any sql hangs indefinitely. porsager/postgres#751, fetch_types: false, deadlocks reserve() and alternatively pulls ~10KB from server before any work and can burn GBs of egress per month porsager/postgres#1219, Cloudflare/workerd: sql.reserve() never resolves on a pool that has not yet opened a connection porsager/postgres#1203)
• a failed or cancelled type query crashed the process with an unhandled rejection (fetchArrayTypes() promise is discarded in ReadyForQuery: a failing fetch_types query crashes the process via unhandled rejection porsager/postgres#1192, Unhandled rejection kills the process when the internal array-types query is cancelled (57014) - repro for #279 porsager/postgres#1205, Postgres Error: server login has been failing, try again later (server_login_retry) porsager/postgres#1086)

Still open: the per-transaction delay reports (porsager#903, porsager#952), types created by a migration after a connection opened stay unknown until it reconnects, and the two proposals in this area both fetch more, not less: named types (#114, porsager#1162) fetch the whole pg_type, and the domain fix (porsager#1187) adds a second lookup.

What dropping it would ask of users

Case	Without the catalog	Burden
Write a JS array to an enum, domain or extension array	Serialize from the JS shape when no serializer is known for the reported OID, as pg does for every array (arrayString)	None, once the fork does this; it also fixes domains (#80), which discovery never did
Read built-in arrays outside the table (inet[], cidr[], macaddr[], money[], timetz[], point[], ranges)	Widen the static table, at least to pg-types' coverage	None
Read enum, composite or extension arrays	The value arrives as the string '{a,b}', as in pg	Select col::text[], or register a parser
sql.array(x) with a non-built-in element type	No map from element to array type	Pass the type, or cast in SQL
Types added by migrations	Nothing to go stale	None (an improvement)

The same question one level down: describe-first

The catalog exists because postgres.js serializes by server type. For a statement not yet prepared on the connection, any value whose type JS can't tell (strings, numbers, every array) makes it send Parse + Describe + Flush and wait for the server's parameter types before Bind (describeFirst). pg sends Parse, Bind, Describe, Execute and Sync in one round trip with types unspecified and lets the server infer them (query.js). Where connections live for one request, every parameterized statement pays that round trip. parameter_types: 'client' already skips it for scalars; arrays still describe first.

Serializing arrays from their JS shape without describing would drop that round trip too. The burden is pg's: a JS array bound to a json/jsonb parameter becomes a PostgreSQL array literal, so users JSON.stringify arrays for JSON columns (plain objects are still sent as JSON), and a few contexts where the server cannot infer a type need an explicit cast.

Options

• A. Keep the default. Document the per-connection cost and the fetch_types: false trade-off.
• B. Default to false (a breaking change for readers of enum arrays, so 5.0), with the JS-shape fallback for writes and a wider static table; keep fetch_types: true as an opt-in for apps that want enum arrays read as arrays.
• C. Remove discovery: delete fetchArrayTypes, the startup state around it and the discovered part of the type map; reads of non-built-in arrays go through casts or registered parsers.
• D. B or C, plus client-typed arrays: no describe-first for arrays, with the JSON.stringify rule for JSON columns.

Measurements before deciding

• Bytes, CPU and wall time per connection with fetch_types on and off, on Node.js and on Workers (the edge A/B tooling covers the Worker side).
• Whether Hyperdrive counts a describe-first statement as one query or two: it counted ~90k queries where pg_stat_statements at the origin saw ~53k over the same window.
• A test reproducing the enum-array write failure with fetch_types: false, before any fix.

Related: #7, #45, #80, #114.

[okomarov]

Three more reasons to drop describe-first (option D), from testing PR #177 on Hyperdrive

As a reminder, describe-first works like this: the first time a connection runs a statement with a string, number or array value, postgres.js asks the server for the value types and waits for the answer before it sends the values.

1. It's the one thing that breaks Hyperdrive. Suppose a statement in a transaction fails, and a describe-first statement comes next. PostgreSQL answers with its usual "transaction is aborted" error, but Hyperdrive closes the connection instead. Nothing is committed, but the caller gets a connection error and the client has to reconnect. Everything else we tried behaves normally through Hyperdrive, including node-postgres, which never waits like this. Reported to Cloudflare as Hyperdrive closes the client connection when a named Parse + Describe + Flush arrives in an aborted transaction cloudflare/workers-sdk#16421.

2. It makes the connection code harder. While a statement waits for its types, it is half sent, and the connection has to handle that state everywhere: when it queues the statements behind it, when the answer arrives, and when something fails. Most of the tricky cases in the [FIXED in 4.7.0] A statement that fails to serialize does not abort its transaction, so COMMIT succeeds without it #70 fix were this state.

3. It's slow on serverless. A Worker opens a new connection on every request, so every statement with a string or number value pays an extra round trip to the database.

Test runs: postgres.js, node-postgres.