CockroachDB and CodeWords: automating against distributed SQL
Reading and writing from automations, why retries are normal rather than exceptional, and the operational reporting a distributed database makes possible.
Aymeric Zhuo11 min read
On this page
CockroachDB speaks the PostgreSQL wire protocol, so most tools connect to it as though it were Postgres and most of the time that works. The places it does not work are where automation gets interesting, and they come down to one thing: it is a distributed database, so a transaction can be told to try again for reasons that have nothing to do with your query being wrong.
An automation that treats a retry as a failure will look unreliable against a database that is working exactly as designed.
What we'll cover
- What changes when the database is distributed
- Connecting it to CodeWords
- Seven automations worth building
- Retries are normal
- Writing queries that behave well
- Building it so it survives
- Limits worth knowing about
- Frequently asked questions
What changes when the database is distributed
Transactions can be retried by the database. Under contention, CockroachDB may abort a transaction and expect the client to try again. This is routine rather than exceptional, and client libraries frequently handle it, though an automation assembling its own SQL may not.
Latency depends on geography. A cluster spread across regions pays a round trip for consensus. A query that is instant in a single-region test can be noticeably slower in production, and an automation doing many small queries in a loop feels that acutely.
Some Postgres features differ. Most things work identically. Certain extensions, some locking behaviour, and a few system catalog details do not. Anything relying on Postgres internals rather than standard SQL is worth checking rather than assuming.
Schema changes are online. They do not lock the table the way they might elsewhere, which is genuinely useful, and they take longer to complete, so an automation that alters schema and immediately depends on the result needs to wait properly.
Time is approximate across nodes. Ordering by timestamps written from different places is less reliable than it looks. Where strict ordering matters, use the database's own ordering rather than wall-clock values.
Connecting it to CodeWords
CodeWords connects to more than 3,000 integrations, and a Postgres-compatible connection covers CockroachDB.
- Open CodeWords and start a new automation.
- Describe what should happen in plain language to Cody, the automation builder: what to read, what to write, and when it should run.
- Authorize the connection using your cluster's connection string, with TLS and a database user scoped to the tables involved rather than a superuser.
- Describe the exceptions: a retryable transaction error, an empty result, a figure outside a plausible range.
- Run it against a non-production cluster or a copy first, particularly for anything that writes.
You describe the outcome; Cody builds it, connects it, and deploys it. The free plan covers light use, with Pro at $39 per month and Business at $100 per month as usage grows; details are on the pricing page.
Seven automations worth building
Operational reporting. A query answering the question somebody asks weekly, delivered to a channel before they ask it. The most reliably used automation against any database and the least interesting to build, which is why it rarely gets built.
Data quality checks. Orphaned rows, duplicates by business key, values outside expected ranges, and records in states that should not persist. Run on a schedule, report the findings rather than fixing them silently, because the findings usually indicate something upstream.
Reconciliation against other systems. Compare what the database holds against your billing platform, your warehouse, or a third party, and report differences. This is where most real data problems are actually discovered.
Threshold alerting. A query returning rows only when something is wrong, with a daily confirmation that the check ran when it is not.
Scheduled maintenance with reporting. Archiving old rows, cleaning expired records, recomputing derived tables. Each one reporting what it did, because unreported maintenance is indistinguishable from maintenance that stopped running.
Export for downstream use. Periodic extracts to a warehouse or an operational system, with a cursor so each run handles only what changed.
Capacity and growth reporting. Table sizes, row counts, and growth rates over time, so that scaling decisions are made against a trend rather than at the moment something becomes urgent.
Retries are normal
The single most important thing to build correctly, and the thing most automations get wrong.
Understand what the error means. A retryable transaction error is the database saying this transaction conflicted, try again. It is not a bug, not a connection failure, and not something to alert a human about.
Retry with backoff and jitter. Retry immediately and you recreate the contention that caused it. Add a small randomised delay and most conflicts resolve on the second attempt.
Cap the attempts. Five is generous. Beyond that you have a contention problem in your data access pattern rather than a transient conflict.
Keep transactions short. The longer a transaction is open, the more likely it conflicts. An automation that opens a transaction, calls an external API, and then commits is holding a transaction open across a network call to somebody else's service, which is the worst version of this.
Do not retry non-retryable errors. A constraint violation will violate the constraint identically on every attempt. Distinguish the two, or an automation will burn five attempts on something that was never going to work.
Log retries separately from failures. An automation quietly retrying half its transactions is doing far more work than it appears to, and that is visible only if you count them.
Writing queries that behave well
Filter on indexed columns, and check that your index is actually used. A full scan on a large distributed table is expensive in a way it is not on a single node.
Select the columns you need, because moving data between nodes costs more than it does within one.
Paginate with a key, not an offset. Offset pagination reads and discards everything before the offset, which gets worse as you go deeper.
Batch writes. One statement inserting many rows beats many statements inserting one row, and the difference is larger here than on a single-node database.
Avoid the loop of single lookups. An automation fetching a thousand rows and then querying once per row is a thousand round trips. Fetch what you need in one query with a join or an array parameter.
Use the database's ordering for sequence, rather than sorting by a timestamp written by the application, which may have come from a different node's clock.
Building it so it survives
Least privilege. A read-only user for reporting automations. Write access only where writing happens, scoped to the specific tables.
Never let an automation delete without a record. Log what was removed and why, and prefer a soft delete or an archive move for anything you might want back.
Validate before acting on a result. An empty result may mean nothing is wrong or may mean a join broke. Decide which, explicitly, per query.
Report the outcome with counts. Rows read, rows written, retries incurred. The retry count is the early warning that your access pattern is contending.
Keep the SQL readable and where people can find it. Query logic buried inside an automation nobody opens becomes untrustworthy the moment somebody asks how a number was produced.
Limits worth knowing about
Connection limits apply, and many concurrent automation runs each opening connections will find them. Connection pooling matters more here than on a single-node database.
Transaction size has practical limits. An enormous single transaction is more likely to conflict and slower to commit. Break bulk work into batches.
Cross-region latency is real on multi-region clusters, and it is a property of where your data lives rather than of your query.
Not every Postgres extension is available, so anything depending on one is worth confirming before designing around it.
Changefeeds exist and are the right tool for streaming changes out continuously. Polling a table for changes is the wrong shape when a changefeed is available.
Frequently asked questions
Can I connect to it as though it were Postgres?
Mostly, yes, and that is the point of the wire compatibility. Standard SQL and standard drivers work. What differs is retryable transaction errors, some locking behaviour, certain extensions, and performance characteristics that come from being distributed. Automations that stick to standard SQL rarely notice; ones relying on Postgres internals sometimes do.
What is a retryable transaction error?
The database telling you the transaction conflicted with another and should be attempted again. It is a normal part of operating a distributed database under contention rather than a fault. Handle it with a bounded retry and backoff, and keep it out of your alerting, or you will alert on the database working correctly.
How do I avoid contention in the first place?
Keep transactions short, avoid external calls inside them, batch writes rather than looping, and look at whether many runs are updating the same rows. Most contention in automation comes from a scheduled job touching the same hot rows as live traffic, which is usually solvable by scheduling it elsewhere.
Should automations write to the database?
Where they own the data, yes. Where the data is owned by an application, be careful: writing behind an application's back skips its validation and its own assumptions about state. Reporting and reconciliation are the safest automations against somebody else's database.
How do I stream changes rather than polling?
Use a changefeed, which is built for exactly that and is far more efficient than repeatedly asking what changed. Polling is reasonable for low-frequency checks and the wrong shape for anything that needs to react promptly.
What permissions should the automation have?
The least that works. Read-only for reporting, which covers most automations. Write access scoped to specific tables where it genuinely writes. A superuser connection string in an automation is a credential worth more than the automation and will outlive the reason it was created.
Will a long-running query affect production traffic?
It can, particularly if it scans widely. Schedule heavy analytical work outside peak, and consider whether the question would be better answered from a warehouse than from the operational database. A reporting query competing with live traffic is a common and avoidable source of latency complaints.
Should reporting run against the operational database at all?
For light queries, yes. For anything heavy or analytical, a warehouse is the better home, because a reporting query competing with live traffic is a common and entirely avoidable cause of latency complaints. The rule of thumb: if the query takes longer than a second, it probably belongs elsewhere.
How do I handle schema changes safely?
They run online here, which is genuinely useful, and they take time to complete. An automation that alters schema and immediately depends on the new shape needs to wait for completion rather than assuming, and anything reading the schema should tolerate it being mid-change.
What does a rising retry count tell me?
That something is contending, usually a scheduled job touching the same rows as live traffic. It is the earliest warning you get and it appears long before anything looks broken, which is why logging retries separately from failures is worth the small effort.
What to build first
Start with a data quality check, running read-only and reporting its findings. It needs no write access, it usually finds something on the first run, and it teaches you the connection and the retry behaviour without risk.
Two things make the difference between a first automation that sticks and one that gets abandoned. Run it alongside whatever happens now for a cycle or two and compare the outputs, since the differences are nearly always genuine discoveries rather than bugs. And have it report that it ran even when it found nothing, because an automation whose only output is silence is one nobody can distinguish from a broken one.