Rex Liu

Everything but the answer

SydraDB, my time-series database in Zig, and the week I taught it to pass for Postgres.

I started SydraDB with a sensible plan: a small time-series database in Zig, one static binary, a write-ahead log in front of columnar segments, and a tiny query language of its own called sydraQL. Clients would talk to it over simple wire APIs, HTTP first and Arrow later, and a full SQL surface was on my list of things not to build yet.

The next night I wrote a long plan for passing as PostgreSQL. SydraDB would speak the Postgres wire protocol and look like a Postgres server to the clients that already exist: psql, libpq, JDBC, Npgsql, pgx, Prisma, Django, Rails. Every language and most tools already have a Postgres driver. If SydraDB could pass for Postgres, they'd all work on it without changing a line, and nobody would have to learn sydraQL to try it.

I built three layers of that plan. They're easiest to follow in the order a query meets them, from the client inwards.

The first eight bytes are already a negotiation. A Postgres connection normally opens with a length-prefixed startup packet whose first field is the protocol version, 3.0, sent as 3 << 16. A client that wants TLS sends something else first: an eight-byte packet whose "version" is 80877103, a number the protocol docs say "is chosen to contain 1234 in the most significant 16 bits, and 5679 in the least significant 16 bits." A cancel request is 1234 and 5678. They're fake version numbers, picked so they can never collide with a real one, and the server has to recognise them before it knows what kind of conversation it's in:

pub const ssl_request_code: u32 = 80877103;
pub const cancel_request_code: u32 = 80877102;
pub const protocol_version_3: u32 = 3 << 16; // 3.0 (196608)

SydraDB answers the TLS question with a single byte, N, and the client carries on in plain text. Then come the startup parameters and the message loop, where a simple query arrives as a Q. (Version 3.0 of the protocol is the one described in the Postgres 17 docs; newer servers also speak 3.2.)

Before most tools send a query of their own, they ask the database about itself: what schemas exist, which tables, which columns, of which types. psql's \d does it, and so does every ORM that reads a schema. The questions are queries against Postgres's system catalog, pg_namespace, pg_class, pg_attribute, pg_type, and they expect Postgres's own answers. Types in particular are identified by number. A driver decoding a result row sees a column of type 20 and decodes an int8, sees 1184 and decodes a timestamptz, so a fake Postgres that invents its own numbers gets its values misread. My catalog hands out the real ones:

// excerpt from the type table; every row also carries .namespace = "pg_catalog"
.{ .name = "bool",        .oid = 16,   .length = 1,  .by_value = true,  .category = 'B', .array_type_oid = 1000 },
.{ .name = "int8",        .oid = 20,   .length = 8,  .by_value = true,  .category = 'N', .array_type_oid = 1016 },
.{ .name = "float8",      .oid = 701,  .length = 8,  .by_value = true,  .category = 'N', .array_type_oid = 1022 },
.{ .name = "text",        .oid = 25,   .length = -1, .by_value = false, .category = 'S', .array_type_oid = 1009 },
.{ .name = "timestamptz", .oid = 1184, .length = 8,  .by_value = true,  .category = 'D', .array_type_oid = 1185 },

The numbers for my own tables and namespaces had to stay put across restarts too, so that a cast like 'users'::regclass means the same thing tomorrow.

The query itself goes to a translator from SQL to sydraQL. sydraQL puts from first, so most of the work is moving clauses around. I kept the cases in a JSONL file, SQL in and expected sydraQL out:

SELECT name FROM users WHERE id = 42   ->   from users where id = 42 select name

INSERT, UPDATE and DELETE came next, with RETURNING, and whatever the translator couldn't handle came back as a Postgres error with a real SQLSTATE.

So by the third night a client could connect (the one in my tests, at least), be told no to TLS, send SELECT name FROM users WHERE id = 42, and have it translated correctly. The extended protocol, which many drivers use for prepared statements, answered "not implemented yet". A simple query, once translated, got this:

try protocol.writeErrorResponse(writer.*, "ERROR", "0A000", "execution bridge not implemented yet");

0A000 is Postgres's code for "feature not supported". SydraDB itself could take writes and answer range queries over its own HTTP API, but nothing behind the Postgres door could run a sydraQL query, because there was no sydraQL to run. Its parser was a file with one comment in it. The language I was translating into existed in exactly one place, the right-hand side of my test cases.