PostgreSQL and CodeWords: automating without disturbing production

The Postgres instance you want to automate against is usually the one your application depends on, which changes the calculation. Every query competes with live traffic, every connection occupies a slot, and every write bypasses whatever validation your application performs.

So the useful framing is not what you can do but what you can do without making the application worse. Most of the answer is: read from somewhere other than the primary, react to changes rather than polling for them, and be extremely careful about writing.

What we'll cover

• Reading without competing
• Reacting without polling
• Connections, which run out
• Connecting it to CodeWords
• Seven automations worth building
• Writing, which deserves caution
• Reaching the database at all
• Building it so it survives
• Limits worth knowing about
• What to build first
• Frequently asked questions

Reading without competing

Use a read replica if you have one. Analytical queries belong there rather than on the primary. Most managed Postgres offerings make replicas straightforward and most teams have one they are not using for this.

Accept replication lag. A replica is slightly behind, usually by milliseconds and occasionally by more under load. For reporting this is irrelevant; for anything checking whether a write just happened, it is not.

Set a statement timeout. An automation's query that runs away should be killed rather than holding resources. Setting this on the automation's role costs nothing and prevents a whole category of incident.

Read what you need. Selecting every column because it is easier moves far more data than necessary, and on a wide table that is the difference between a fast query and a noticeable one.

Schedule heavy work off-peak. An analytical query competing with your busiest hour is the most common avoidable cause of latency complaints.

Reacting without polling

Polling a table every minute to see what changed is the default approach and usually the wrong one. Postgres offers better.

LISTEN and NOTIFY is a lightweight publish-subscribe mechanism built into Postgres. A trigger sends a notification on change, and a listener receives it immediately. It is simple, it is low overhead, and its limitation is that notifications are not durable: a listener that is disconnected misses them entirely.

Logical replication streams changes durably, which is what you want when missing an event is unacceptable. It is more setup and it is the right answer for anything feeding another system.

A polled change column remains perfectly reasonable when neither of the above is available. An indexed updated_at with a cursor is simple and predictable, and it cannot see deletions, which is the gap that bites.

Choose by what a missed event costs. For a nice-to-have notification, NOTIFY is fine. For keeping another system in step, use logical replication, because NOTIFY will eventually drop something and you will not know.

Connections, which run out

The most common way automation harms a Postgres instance.

Each connection costs memory on the server, and the limit is lower than people expect.

Automation runs are bursty. Fifty concurrent runs each opening a connection is fifty connections, and they arrive together.

Use a pooler. PgBouncer or whatever your managed provider offers sits between and multiplexes. This is the single most important operational decision for automating against Postgres at any volume.

Know your pooling mode. Transaction pooling is efficient and breaks anything relying on session state, including prepared statements in some drivers and, notably, LISTEN. Session pooling preserves that behaviour and holds connections longer.

Close what you open. An automation leaking connections will exhaust the limit and take the application down with it, which is a memorable way to learn this.

Connecting it to CodeWords

CodeWords connects to more than 3,000 integrations, and the connection is made once and reused.

1. Open CodeWords and start a new automation.
2. Describe what should happen in plain language to Cody, the automation builder: what to read, when, and what to do with it.
3. Authorize the connection using a connection string pointing at a replica where possible, with TLS and a role scoped to the specific tables.
4. Describe the exceptions: an empty result, a figure outside a plausible range, a connection failure.
5. Run it against a copy or a replica before anything touches the primary.

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. The query somebody runs weekly, delivered before they ask. Unglamorous, reliably used, and rarely built.

Data quality checks. Orphaned rows, duplicates by business key, values outside expected ranges, and records in states that should not persist. Run read-only, report findings rather than fixing them, because the findings usually indicate an application bug.

Reconciliation against other systems. The database against your billing platform, your warehouse, or a third party. Where real data problems are actually discovered.

Threshold alerting. A query returning rows only when something is wrong, with a daily confirmation when it is not.

Change-driven sync. Feed another system from logical replication, so the downstream stays current without anybody scheduling anything.

Growth and capacity reporting. Table and index sizes, row counts, and growth rates, so scaling is planned rather than urgent.

Slow query and bloat reporting. From the statistics extensions, which most instances have enabled and nobody reads.

Writing, which deserves caution

Prefer not to. Where the data belongs to an application, writing behind it skips validation, skips its own automation, and skips whatever assumptions its code makes about state. The application will not expect the row you inserted.

Where you must, use the application's API instead if one exists. It is slower and it respects the rules.

Scope write access to specific tables, never to the whole database, and never with a superuser role.

Write to your own tables freely. An automation that owns a table — a log of what it did, a cache, a report output — can write there without any of the above concerns, and this is a good reason to give automations their own schema.

Never let an automation run migrations. Schema changes belong in your application's migration process where they are reviewed and versioned.

Reaching the database at all

Worth settling before anything else, because it is the step that most often stalls.

Databases are rarely on the public internet, and correctly so. An automation running elsewhere needs a route in, and the options are an allowlist of source addresses, private networking within the same cloud, or a bastion.

Allowlisting needs a stable source address. Confirm what your automation platform egresses from before promising your infrastructure team a list.

Require TLS and verify it. A connection string permitting an unverified certificate will connect to anything claiming to be your database, and the difference between the permissive and verifying modes is one word in the connection string.

Use a dedicated role per automation, not the application's credentials. When something misbehaves, you want to know which automation it was, and you want to revoke one thing rather than rotate everything.

Rotate credentials on a schedule, and confirm the automation survives it. A rotation that breaks a reporting job on a Friday is how people learn this was never tested.

Building it so it survives

Make it safe to run twice. Use upsert with a conflict target rather than insert, and derive keys from the source rather than generating them.

Set a statement timeout and a connection timeout. Both default to permissive, and an automation without them can hang holding resources.

Paginate with a key, not an offset. Offset pagination re-reads everything before the offset and degrades as you go deeper.

Log the query and the row count. When a figure is questioned, you want the exact SQL, and a sudden change in row count is the earliest signal something upstream broke.

Report the outcome. Rows read, rows written, duration. A reporting automation that silently stopped looks identical to a quiet week.

Limits worth knowing about

Connection limits are the binding constraint for most automation, and a pooler is the answer rather than a higher limit.

Long transactions block vacuum, which causes table bloat and degrades performance for everybody. An automation holding a transaction open while it calls an external API is the classic case.

NOTIFY payloads are size-limited and notifications are lost if nobody is listening, which is the trade for its simplicity.

Logical replication requires configuration — a publication, a slot, and appropriate settings — and an unconsumed replication slot retains WAL indefinitely, which has filled more than one disk.

Managed providers restrict some things, including superuser and certain extensions, so check what yours permits before designing around a feature.

What to build first

A data quality check, running read-only against a replica. It needs no write access, it usually finds something on the first run, and it teaches you the connection, the pooling behaviour, and your own data's shape without any risk to the application.

Two habits make the difference. Run it against a replica rather than the primary from the beginning, so the habit is established before anything heavy exists. And report even when it finds nothing, because a silent check cannot be distinguished from a broken one.

Frequently asked questions

Should automations write to our application database?

Generally no. Writing behind an application skips its validation and its assumptions about state, and the bugs that result are hard to trace because the data looks legitimate. Use the application's API where one exists, or give the automation its own schema to write into.

How do I react to changes without polling?

LISTEN and NOTIFY for simple, low-stakes reactions, since it is trivial to set up and notifications are lost if nobody is listening. Logical replication where missing an event is unacceptable, which is anything keeping another system in step.

Why is my automation causing connection errors?

Almost certainly concurrent runs each opening connections and exhausting the limit. Put a pooler in front, reuse connections within a run, and make sure every path closes what it opened, including the error paths.

What is transaction pooling and why does it matter?

A pooling mode that assigns a connection per transaction rather than per session, which is far more efficient and breaks anything depending on session state — including LISTEN, some prepared statement handling, and temporary tables. If your automation listens for notifications, it needs session pooling or a direct connection.

Can I run reporting against the primary?

For light queries, yes. For anything analytical, use a replica, because a reporting query competing with live traffic is a common and entirely avoidable source of latency complaints. Most teams already have a replica they are not using for this.

What permissions should the automation have?

A dedicated role with select on the specific tables it reads, plus write access only on tables it owns. A statement timeout set on the role is worth adding at the same time, since it costs nothing and prevents a runaway query holding resources.

How do I avoid long transactions?

Never call an external API inside one, keep them short, and commit rather than holding a transaction open across a loop. Long transactions block vacuum, which causes bloat, which degrades performance for the whole database rather than just your automation.

How should the automation reach a database that is not publicly accessible?

Through an allowlisted egress address, private networking within the same cloud, or a bastion — decided with whoever owns the infrastructure before you build. Confirm the egress addresses first, since that is the detail the request will hinge on.

Which TLS mode should the connection string use?

One that verifies the certificate. The permissive modes encrypt the connection without confirming what is on the other end, they look identical in normal operation, and the difference is a single word in the connection string.

Related reading

• CockroachDB and CodeWords
• Redis and CodeWords
• CodeWords integrations and templates