Developer builds Terox CLI with AI in a couple of days to query 128 PostgreSQL shards at once

When you have a hundred PostgreSQL shards, the simple question “Which one holds this record?” turns into a quest: find the host, remember the port and database, open psql, run the query, and move on to the next one. A backend developer who previously worked as a DBA grew tired of putting up with this. Working with AI, he built Terox in a couple of days: an interactive CLI for sharded clusters.

The problem it solves

In psql and pgAdmin, the unit of work is a connection. In a sharded system, the unit of work is an entire set of connections. A bash loop around psql works for a one-off task, but falls apart as an everyday tool: there are no concurrency limits, query cancellation, saved errors for individual shards, or safeguards against accidentally accessing production.

Terox introduces higher-level entities: service → storage → one, several, or all shards. Hosts and databases are defined using templates; for example, pgbouncer-shop-rs{p1:03}.example.net expands into 128 addresses. A query targeting all runs across every shard, the results merge into a single table with a shard column, and errors appear separately.

How it works in practice

The command \find items item_id = 424242 searches across the shards, reports that the record was found on shard_37, and automatically switches the context to that shard. No copying hostnames or guesswork required.

The internals are carefully implemented. The fanout_concurrency parameter prevents the client from hitting all 128 shards at once. Each shard's connection pool is created lazily, each shard has its own deadline, and Ctrl+C cancels running queries. Reads run inside an actual PostgreSQL READ ONLY transaction, rather than relying on SQL text parsing. Writes must be explicitly enabled with \write on, and that setting resets when the context changes.

There are also dedicated diagnostics (\activity, \blockers, \longtx, \sizes, \diff, \doctor) and a linter that catches DELETE without WHERE or NOT IN with nullable values.

Migrations for exceptional cases

The author emphasizes that routine migrations ship with deployments. Terox is intended for exceptions involving large, heavily used tables. Without the --allowed flag, \migrate operates as a dry-run. It supports canary runs, batches, and --resume, while a checksum prevents a rollout from continuing with a modified file.

There is no shared transaction across 128 servers, so some shards may already have committed their changes. The report explicitly identifies partial application, and migrations must be idempotent. For CREATE INDEX CONCURRENTLY and VACUUM, which cannot be wrapped in a transaction with SET LOCAL, Terox warns about unprotected execution and requires separate confirmation in production.

What this says about vibe coding

The workflow was to describe the pain point, identify a minimal behavioral model, get an implementation from AI, check it manually and with tests, and turn each edge case into a new test. This led to connection pools being closed on exit, a history that excludes passwords, and tests for race conditions.

The author's main takeaway: AI will implement a flawed idea just as quickly and neatly, across twelve files at once. So define the invariants first: no connections remain after exit, PostgreSQL enforces read-only access, changing context disables writes, and a partial failure never looks like success. That turns the conversation with AI into engineering work.

Limitations

Terox's static analysis is heuristic. The security boundaries still come from role permissions, READ ONLY transactions, TLS, and timeouts. The tool does not replace monitoring, backups, or an understanding of query plans, and it does not fix the sharding architecture.

The practical takeaway: if you regularly work across dozens of databases, wrap your loop in a tool that remembers the rules for you. And when working with AI, start with invariants rather than code.