DEV Community: Pranay Ravi

What to Keep in Mind for a Cloud Migration

Pranay Ravi — Thu, 12 Nov 2026 07:09:07 +0000

I have led a couple of platform migrations, from on-premises to AWS and from one AWS account structure to another. Each time my job was to coordinate the application, database, DevOps and network teams from scoping to cutover.

This is the list I would want you to keep in mind before you start one. It is grouped into seven categories. Under each I say what to think about and, where it helps, what we did.

1. Scope: decide what changes and what waits

A migration is a chance to modernize, and it is also the easiest place to over-engineer. Start from the rules of the target environment. Ours were no static credentials, TLS on database connections, nothing public by default, autoscaling, and tags on every resource so cost splits by application.

Then list every improvement someone wants and make a call on each one.

Area	Think about	What we did
Database	Move to a managed engine? Upgrade the major version in the same move?	Moved to Aurora with an engine upgrade and TLS
Runtime	Can VMs, ECS and EKS collapse into one?	Consolidated on EKS
Secrets	Use a secrets manager and take passwords out of code and config files	Secrets moved into AWS
Configuration	Externalize parameters so the same build deploys to every environment	Config maps
Logging	Send application logs to a central store instead of local disk	Central search cluster
Access	Use roles between services and drop long-lived keys	Role-based access
Pipeline	Can one pipeline deploy everything? Is GitOps in reach?	One CI server in a shared account. GitOps came later, once the runtime was standard
Search and loaders	Upgrade or rewrite now, or carry them over as they are?	Deferred. Both needed application code changes
Backups	Does going live need a new backup design?	Deferred. Snapshots were enough

Two columns in the task tracker kept this honest: "key project goal" and "blocker for the first environment". A task with a no in both waited.

2. Discovery: get the complete picture

Get a complete application architecture diagram before anyone builds. Expect the first version to be partial, and keep asking. A platform that has run for years has connections nobody wrote down.

Build an inventory for every environment:

Which component runs where, and why it runs there.
Every DNS name, with its zone, load balancer, protocol, target group and the resource behind it.
Every inbound caller and every outbound dependency.
Every loader, job and script that reads or writes the data.

Check your assumptions two and three times, including the convenient ones. We worried that bucket names were stored in the database, which would have made renaming buckets painful. We checked, and they were in a properties file. We also assumed a transit gateway would cover traffic between accounts, and we needed VPC peering as well.

Compare the environments against each other. Production and non-production are rarely built the same way, and the differences are where the cutover steps change.

Even with all of this, three things found us late: another product's web client that read our object storage directly, a data warehouse job that read a logging table we planned to retire, and a monitoring script that counted documents in the old search cluster. Leave room in the plan for your own version of these.

3. People and calendar: one scrum, one plan

Each team has its own backlog, its own way of working and other projects competing for the same people. Someone has to keep those plans moving as one.

Run one scrum across all teams. Ours started weekly, went to twice a week to clear blockers, and became daily near the first cutover.
Ask every team the same three questions. What are you working on, and what is the estimate? What is blocked or likely to slip, and what is the alternative? What still has to happen for the next environment to cut over on its date?
Keep one board. One epic, with each team's own tickets linked in.
Spell out requirements early. When one team settles on a solution, share what it needs from the others the same day.
Post outcomes in the shared channel. Two-person working sessions are fine, but the result belongs where every team sees it.
Follow up instead of waiting. It never hurts to over-communicate.
Plan around the calendar. Change freezes, public holidays and time off for key people all punch holes in a quarter. Keep a tab for who is out and when, and set each environment window around it.

Dates will still move, and ours did on the first environment. Build slack around the known gaps so one slip does not push every later window.

4. Data: the part that decides the cutover

Compute can be redeployed. Data has to be moved, kept in sync and proven correct, so it shapes the whole cutover. There is no single right method, only questions you have to answer.

Can you take downtime? Decide this first, with the product owner. A short window removes a whole class of design. We could take one.

One direction or two? Bidirectional replication buys an easy rollback and costs you conflict handling and weeks of testing. We tested replicating back from the upgraded engine to the old one and hit problems. We chose one direction: seed the new database from a shared snapshot, replicate old to new with native replication, and roll back by export and import. That rollback carries downtime, so tell the product team before they agree.

Native or heterogeneous tooling? Every engine has its own replication. Tools such as AWS DMS or Oracle GoldenGate cover moves across engines. Pick the option with the fewest steps to script and test. For us that was native.

Where else does the data live? Rarely in one place. Ours was in three, each with its own method:

Store	How it moved	Rollback
Relational database	Snapshot seed, then one-way native replication	Export and import
Search cluster	Snapshot to a shared bucket and restore, then a remote reindex of the current day during the window	Reindex back
Object storage	Bidirectional bucket replication, left on for days after cutover	Files already in both accounts

What feeds it and what reads it? List every inbound loader and every downstream reader. We stopped the loaders before the window and repointed them after. The downstream warehouse stayed on the old database for one more day.

What can testing break? Readiness tests that write to the new database while replication is running can break the replication. Plan which tests write, and when.

5. Network and edges: where it usually breaks

The application and the database get the attention. The surprises come from DNS, load balancers and allowlists.

DNS. If the domain's hosting moves too, stage it so a rollback never touches the registrar. We moved ours into Route 53 like this:

Create the new hosted zone as an empty shell. Not live, and no records created early.
Add temporary names in the old zone that point at the new load balancers, so the new stack can be tested before cutover.
At cutover, repoint the old zone's records to the new load balancers.
The next day, sync the records into the new zone.
After 24 hours, publish the new name servers at the registrar next to the old ones.
After another 24 hours, remove the old name servers and watch the URL monitors.

Load balancers. Decide where TLS terminates, what the listener rules are, and whether each endpoint gets its own load balancer or shares one with host and path routing. Have services call each other through a load balancer or a DNS name, not through the cluster's internal networking.

Allowlists. A new account means new outbound addresses. Every partner and internal service that allowlisted the old ones needs the new ones before cutover. Give every outbound call (email delivery, analytics, partner APIs) its own line in the inventory. Check inbound routes too, such as loaders that still run on-premises.

Object storage. If browser or mobile clients read buckets directly, the CORS rules move with the buckets. If bucket names change, find every place a name is stored.

Edge protection. Check what sits in front of production that the lower environments lack. One of our APIs was behind a web application firewall only in production, and a deployment that rewrote its DNS record would have broken it.

6. Cutover: rehearse it the same way every time

Cut over the lowest environment exactly the way you will cut over production. People will ask why QA needs this much ceremony. QA is the only rehearsal production gets.

Use one runbook format for every environment. Ours had a preparation tab, a cutover tab, a rollback tab and a DNS tab. Every row had a date, time, duration, team, owner and status.
Write the application test cases before the first environment. Run the same set in every environment.
Get a sign-off from each team. Once before the window and again after it.
Script every cutover and rollback command. Store the scripts before the night.
Say ahead of time what a lower environment cannot prove. A production-only firewall, a registrar change and a disaster-recovery copy are typical.
Give cleanup its own change request. We had two people present for every bucket deletion.

Our rehearsals showed that deployments had to finish before the window, that published DNS had to be checked against expected results, and that the cutover needed an application redeploy. Each of those would have been a surprise in production.

For a sense of scale, our production runbook budgeted about forty minutes from cutting traffic to sign-off.

7. Monitoring, retention and cost: decide early

Monitoring. Set up monitoring and central logging in the new environment before the cutover. Put full coverage of databases and application URLs on the production readiness list. Stay on one approved logging path. If the cluster auto-instruments applications, a new service comes up already covered.

Retention. Autoscaling without retention is a bill that grows quietly. Set retention at every layer as it is built: log indices, database logging tables, buckets and container images. We left some of these until after cutover, and I would not do that again.

Cost. The cost lessons are mostly about deciding early:

Pick instance classes before the build, so reserved capacity can be planned.
Run the consolidated cluster on reserved instances.
Create temporary instances with volumes that delete on termination.
Question any design that copies data between accounts more than it needs to.
Ask whether every log line is needed before paying to ship and store it.

My take

A migration is mostly coverage. Know what you have, decide what changes now and what waits, move the data with a rollback you have tested, and rehearse the cutover in every environment. In my experience the hard moments come from things nobody wrote down far more often than from the technology.

It is also never one person's work. Application, database, DevOps and network engineers build it. The lead's job is to keep the scope honest, the plan visible and the teams talking to each other.

With a clear scope, a complete architecture diagram, a regular scrum and a firm timeline, and with AI tools to speed up the inventory and runbook work, three to five months is a fair plan for most platform migrations.

Auditing Aurora PostgreSQL Without Drowning in Logs (or Leaking PII Into Them)

Pranay Ravi — Fri, 06 Nov 2026 05:46:56 +0000

When we moved a set of Oracle workloads to Aurora PostgreSQL, the compliance ask sounded simple: audit every statement that touches sensitive tables. Set pgaudit.log = 'all' everywhere and you have it covered, right?

Not quite. Turning auditing all the way up creates two new problems. The log stream gets so noisy that nobody reads it, so a real incident hides among thousands of driver and IDE catalog queries. And with the wrong parameter, the audit log quietly becomes another copy of the sensitive data you were trying to protect.

This is a follow-up to part 1, which covered the alerting pipeline: per-user pgAudit, the Logs Insights noise filter, and the Lambda that turns query results into a CloudWatch alarm.

Pranay Ravi

May 21

What It Actually Takes to Audit Aurora PostgreSQL on AWS

#aws #postgres #security #devops

7 min read

This post covers what came next: testing two pgAudit configurations side by side, four parameters that behaved differently than we expected, and the layers that protect the data itself.

Cluster names, accounts, users and data in every example are placeholders.

The architecture at a glance

No single tool both logs everything and protects sensitive text, so the design has three layers: capture (pgAudit), protect (masking and access control) and store and alert (CloudWatch, S3 and friends).

                    Aurora PostgreSQL cluster
          (pgAudit writes AUDIT lines · pg_columnmask masks reads)
                 │                                   │
            log export                        activity stream
                 ▼                                   ▼
          CloudWatch Logs                 Database Activity Streams
                 │                        (Kinesis, KMS, DBAs can't alter)
        ┌────────┴─────────┐                         │
        ▼                  ▼                         ▼
 Lambda (every 5 min)   Firehose                  Firehose
        │                  │                         │
        ▼                  ▼                         ▼
 Metric + alarm      S3 audit archive         S3 activity archive
        │            (SSE-KMS, Glacier)       (separate KMS key)
        ▼                  │
       SNS                 ▼
                     Athena + Macie

The left two columns share one CloudWatch log group: one path turns it into alerts, the other into a long-term archive. The right column runs alongside and depends on nothing a database user can configure.

Setup, briefly

Part 1 walks through enabling pgAudit, including the silent failure when CREATE EXTENSION pgaudit is missing. The short version: preload pgaudit in the cluster parameter group, reboot, create the extension in each database, set pgaudit.log per role, and export the postgresql log to CloudWatch Logs.

Audit lines land in the log group /aws/rds/cluster/<cluster>/postgresql, one stream per instance. Each one starts with AUDIT: SESSION, followed by the statement class (READ, WRITE, DDL, ROLE, FUNCTION, MISC), the command, the object and the statement text.

Opt-in or baseline? We tested both

A cluster-wide baseline of ddl, role, write, function plus all for a few named roles beat opt-in auditing in our tests. Part 1 scoped auditing to named human accounts only. Another team's cluster used a cluster-wide baseline instead, so we ran the same script against both.

Opt-in: pgaudit.log = none in the parameter group; ALTER ROLE ... SET pgaudit.log = 'all' for selected roles.
Baseline: pgaudit.log = 'ddl,role,write,function' in the parameter group; the same all override for selected roles.

The script created a schema and table, ran INSERT/UPDATE/DELETE, a SELECT, a CREATE/ALTER/DROP, an anonymous DO block, a GRANT and DISCARD PLANS. We ran it as a flagged role and as a brand-new application role nobody had configured.

Statement class	Flagged role (`all`)	New role, opt-in	New role, baseline
DDL (CREATE / ALTER / DROP)	✅ Logged	❌ Not logged	✅ Logged
WRITE (INSERT / UPDATE / DELETE)	✅ Logged	❌ Not logged	✅ Logged
ROLE (GRANT)	✅ Logged	❌ Not logged	✅ Logged
FUNCTION (`DO` block)	✅ Logged	❌ Not logged	✅ Logged
READ (SELECT)	✅ Logged	❌ Not logged	❌ Not logged
MISC (`DISCARD PLANS`)	✅ Logged	❌ Not logged	❌ Not logged

Opt-in has a blind spot exactly where you least want one: any account nobody remembered to flag is invisible, including one created yesterday. The baseline still records every schema change, data change and privilege change from every role, and keeps high-volume SELECT logging to the roles you name.

One caveat applies to both: these are role and session settings. Anyone with enough privilege to change role settings can turn their own auditing down, which is the gap Database Activity Streams (covered below) closes.

Four parameters that changed our design

`pgaudit.log_parameter`: the audit log becomes a data store

With log_parameter = on, pgAudit writes bind values into the last field of each line. That is what makes the audit trail useful for reconstructing who changed what. It is also how a patient name or card number ends up in CloudWatch Logs. The same prepared INSERT, logged both ways:

# log_parameter = on
AUDIT: SESSION,17,1,WRITE,INSERT,,,"PREPARE test_insert(INT, TEXT) AS
    INSERT INTO pgaudit_test.audit_test (id, name) VALUES ($1, $2);","101,Jane Example"

# log_parameter = off
AUDIT: SESSION,17,1,WRITE,INSERT,,,"PREPARE test_insert(INT, TEXT) AS
    INSERT INTO pgaudit_test.audit_test (id, name) VALUES ($1, $2);",<not logged>

We keep it off by default. Where an auditor needs values, we enable it per role with ALTER ROLE ... SET pgaudit.log_parameter = on, and treat that log group like the source table: KMS-encrypted, narrow IAM read access, short retention.

⚠️ Statements written with inline literals carry their values in the statement text regardless of this setting.

`pgaudit.log_relation`: answering "who touched this table?"

With it off, the object-type and object-name fields of a SELECT or DML line are empty (WRITE,INSERT,,,). With it on, pgAudit fills them (WRITE,INSERT,TABLE,pgaudit_test.customer) and logs a line per relation the statement references.

"Who touched this table in the last 24 hours" becomes a field filter instead of a regex over SQL text. It can be scoped per database (ALTER DATABASE app_db SET pgaudit.log_relation = on); the cost is extra lines for joins and multi-table statements.

`log_statement`: everything logged twice

Native log_statement and pgAudit run independently. On a cluster with log_statement = 'ddl', every CREATE and DROP appeared twice: once as a plain LOG: statement: line and once as an AUDIT: line. Set it to none in the cluster parameter group. For a debugging session, scope it and clean up afterwards:

ALTER ROLE app_admin IN DATABASE app_db SET log_statement = 'all';
-- ...debug...
ALTER ROLE app_admin IN DATABASE app_db RESET log_statement;

`log_connections` and `log_disconnections`: cluster level only

These are read when a session starts, so in our tests per-database and per-role settings had no effect. They have to be on in the cluster parameter group. One of our two clusters had them off, which meant its audit trail showed statements but not session start, end or duration.

What a baseline means for alerting

The alerting path is the one from part 1: EventBridge runs a Lambda every 5 minutes, the Lambda runs the Logs Insights noise filter and publishes a count, and a CloudWatch alarm notifies SNS. Code, IAM and SNS/KMS policies and a Terraform module are in the repo:

pcraavi / PostgreSQL-Audit

Setup PostgreSQL Audit in AWS and get alerted based on Cloudwatch ,Lambda and SNS

Aurora PostgreSQL User Audit Monitor

Detect and alert on individual user DML activity on Aurora PostgreSQL clusters using pgAudit, Lambda, CloudWatch, and SNS.

Audit scope: Individual human users making direct DML changes (SELECT, INSERT, UPDATE, DELETE) on application tables. Application service accounts are deliberately excluded — their DML is expected. The risk surface is direct human access: a developer connected via psql, a support engineer running an ad-hoc update, a credential that should never have had direct database access.

Repository Structure

├── README.md
├── deploy-all.sh                        # Full CLI deployment sequence (single account)
│
├── lambda/
│   ├── lambda_function.py               # Core audit monitor — Log Insights query, metric publish
│   └── deploy-lambda.sh                 # Package and deploy / update Lambda
│
├── iam/
│   ├── lambda-trust-policy.json         # Lambda execution role trust policy
│   ├── lambda-permissions-policy.json   # Least-privilege: Log Insights read + CW metric write
│   └── deploy-iam.sh                    # Create role and attach

…

View on GitHub

One thing changes if you adopt the baseline: application accounts now log their writes too. A count > 0 alarm built for human-only auditing would fire on normal traffic. Either filter on the human accounts' user names (they appear in each line's user@database prefix), or alert on DDL and ROLE statements only for everyone else.

Protecting the data and keeping the trail

pgAudit answers "what happened." Three more pieces cover "who could see it" and "can we prove it later."

Mask at query time with `pg_columnmask`

Aurora PostgreSQL 16.10+ and 17.6+ ship pg_columnmask, which masks column values at query time based on the caller's role and leaves stored data unchanged (AWS docs). A support role can see a partial email while the application role sees the real value, and weights decide which policy wins when several apply. Pair it with PostgreSQL row-level security when you also need to limit which rows a role sees.

Three details from the AWS troubleshooting guide are worth knowing before rollout:

Table owners and rds_superuser members are exempt, so test as the actual target role.
pg_dump run as a masked role produces masked data, which can break constraints on restore.
A materialized view is masked according to its owner's policy at refresh time, regardless of who reads it.

Close the insider gap with Database Activity Streams

Anyone who can change role settings can turn pgAudit down for themselves. Part 1 explained why we passed on Database Activity Streams for alerting: Kinesis as a dependency, per-event cost and a consumer layer to build. For the insider case they are still the answer, because the stream is KMS-encrypted and DBAs can't read or alter it.

Aurora PostgreSQL supports synchronous mode, where a session waits until its event is durable, trading latency for completeness. Before turning it on, check that your instance class is supported, and plan for a restart: starting the stream with "apply immediately" restarts the cluster.

Keep the trail queryable, and scan it

CloudWatch Logs is a poor long-term archive. A subscription filter sends the PostgreSQL log through Firehose to an S3 bucket encrypted with a dedicated KMS key, where Athena can query years of history and a lifecycle rule moves old objects to Glacier.

Point an Amazon Macie classification job at that bucket on a schedule. If sensitive values slip into the logs through log_parameter or inline literals, Macie is how you find out before an auditor does.

# Bucket policy essentials: deny unencrypted writes and non-TLS access
# "Condition": {"StringNotEquals": {"s3:x-amz-server-side-encryption": "aws:kms"}}
# "Condition": {"Bool": {"aws:SecureTransport": "false"}}

aws macie2 create-classification-job \
  --name audit-log-pii-scan \
  --job-type SCHEDULED \
  --schedule-frequency '{"weeklySchedule":{"dayOfWeek":"MONDAY"}}' \
  --s3-job-definition '{"bucketDefinitions":[{"accountId":"111122223333","buckets":["my-audit-log-bucket"]}]}'

The short version

If you are about to turn on auditing for Aurora PostgreSQL, this is the checklist we would hand you:

Preload pgaudit and set a cluster-wide baseline of ddl, role, write, function, not none.
Reserve pgaudit.log = 'all' for the human and break-glass roles you name.
Leave pgaudit.log_parameter off unless an auditor needs values, and then scope it to a role.
Turn on pgaudit.log_relation where you need per-table answers.
Set log_statement = none; set log_connections and log_disconnections on, at the cluster level.
If you move to a baseline, scope alerts to human accounts or to DDL and ROLE statements, or application writes will page you.
Encrypt, retain and scan the archive; add masking for readers and Activity Streams for the insider case.

Auditing is easy to switch on and hard to make useful. Most of the work is deciding what not to log, and making sure the log itself doesn't become the next thing you need to protect.

Have you run pgAudit with a cluster-wide baseline, or put pg_columnmask in front of a support team yet? Drop a comment. I'm curious how others are handling log_parameter in regulated environments.

Not Every Slow Query Needs a New Index

Pranay Ravi — Wed, 04 Nov 2026 02:53:03 +0000

What a SQL review taught me about assumptions, AI-written queries, and common-sense tuning

A development team asked me to review the SQL behind a change-data publishing job before they moved it from once a day to once an hour. In QA, one full run took 10 to 12 minutes. After the review, the same run took under 30 seconds. In production, most of its queries now finish in under a millisecond.

Almost none of that came from adding indexes. It came from reading the queries, asking what the job actually needed, and checking each assumption against real data. This post walks through what we found, in the order we found it, and what I would tell any team (or any AI assistant) writing SQL against a busy production database.

The short version:

You don't always need to tune the database. Often you need to understand the use case and explain how the database behaves.
Functions on indexed columns and "this column might be null" assumptions are the most common silent performance killers.
What is harmless in QA can be dangerous in production, because the data is shaped differently.
An index can make a query slower. Measure after every change.

The setup

The job polls a PostgreSQL read replica for rows that changed since its last run and publishes them as events. It is a classic checkpoint pattern:

Read a ceiling: the newest change timestamp in the source tables.
Count the rows changed between the last checkpoint and that ceiling.
Page through them with keyset pagination, 500 rows at a time.
Store the ceiling as the next checkpoint.

I'll use a generic schema for the examples:

Table	Rows (largest)	What it holds
`accounts`	~1M	Users, organizations and partners, with an `account_type` column
`contracts`	~1,100	Partner contracts, keyed by the owning account
`memberships`	~650k	Which accounts belong to which organization, with a `membership_type`
`org_members`	~9M	Account-to-location assignments
`locations`	~hundreds of k	Organization addresses

Every table has created_at and updated_at. The source system also maintains updated_at_num, a numeric YYYYMMDDHHMISS copy of updated_at, and that column is indexed on every table. Keep that last detail in mind.

Lesson 1: A function on an indexed column throws the index away

Every change-window filter in the job looked like this:

WHERE COALESCE(a.updated_at, a.created_at) >  :checkpoint
  AND COALESCE(a.updated_at, a.created_at) <= :snapshot

The intent is reasonable: if a row was never updated, fall back to its creation time. But PostgreSQL can only use a B-tree index when the predicate matches the indexed expression. Wrap the column in COALESCE, and the index on updated_at (or updated_at_num) is no longer a candidate. Every window query became a sequential scan of the whole table.

The COALESCE also encoded an assumption: that updated_at can be null. So before changing anything, we checked:

SELECT count(*) FROM accounts WHERE updated_at IS NULL;      -- 0
SELECT count(*) FROM accounts WHERE updated_at_num IS NULL;  -- 0

Zero on every table. The source system sets both timestamps when a row is created, so the fallback could never fire. The fix was to drop the function and filter on the indexed column directly:

WHERE a.updated_at_num >  :checkpoint
  AND a.updated_at_num <= :snapshot

That one change turned every window query into an index range scan.

Two things to take from it:

Don't defend against a condition until you've checked whether it exists. A one-line count(*) settled a question the code had been paying for on every run.
If you really do need the fallback, write it so it can use an index. Either (updated_at > x AND updated_at <= y) OR (updated_at IS NULL AND created_at > x AND created_at <= y), or an expression index on exactly COALESCE(updated_at, created_at).

Lesson 2: What is fine in QA can bite in production

The COALESCE wasn't the only defensive habit. One query filtered on TRIM(a.account_type) = 'STAFF'. In QA that is harmless: small tables, fast scans, correct results. In production it does two quiet things:

It stops any index on account_type from being used.
It hints that some values might be padded with spaces. If that were true, every other query that compares account_type without TRIM would silently miss those rows.

Again, the answer was in the data:

SELECT count(*) FROM accounts WHERE account_type <> TRIM(account_type);  -- 0

No padded values, so the TRIM came out, and the queries became consistent with each other.

I want to be clear about where this comes from. The developers I work with write good SQL. But we are now in an era where a lot of SQL is drafted by AI assistants, and they are very good at producing queries that return the right answer. They are much less good at knowing your data. A model will happily add COALESCE, TRIM, LOWER or DISTINCT "just in case", because in the abstract each one is safer. On a 10,000-row QA table you'll never notice. On a 10-million-row production table, each one can turn an index lookup into a full scan.

The result being correct does not mean the assumptions behind it are. Before a query ships, check it against the data's real shape: null counts, value distributions, duplicates, and how skewed the busiest keys are. Or ask a database engineer to review it. It never hurts.

Lesson 3: Carry every filter you know into the subquery

The partner-count query looked like this:

SELECT count(*)
FROM contracts c
JOIN accounts a ON a.account_id = c.account_id
WHERE c.org_id = :org
  AND a.updated_at_num > :checkpoint AND a.updated_at_num <= :snapshot
  AND c.account_id IN (
        SELECT DISTINCT m.parent_account_id
        FROM memberships m
        WHERE m.membership_type = 'PRIMARY'
          AND m.is_deleted = false
          AND (m.end_date IS NULL OR m.end_date >= :as_of));

The outer query was narrowed to one organization and a one-hour window: 59 candidate rows. The subquery wasn't narrowed at all. PostgreSQL read 1.77 million membership rows, built a distinct list of 831 IDs, then matched that list against the 59 candidates. That was 31,395 buffers and 83 ms to return a count of zero.

Two changes fixed it:

  AND EXISTS (
        SELECT 1
        FROM memberships m
        WHERE m.parent_account_id = c.account_id   -- correlate to the outer row
          AND m.org_id = :org                      -- the filter the outer query already had
          AND m.membership_type = 'PRIMARY'
          AND m.is_deleted = false
          AND (m.end_date IS NULL OR m.end_date >= :as_of));

	`IN (SELECT DISTINCT …)`	Correlated `EXISTS` with filters
Execution time	83 ms	0.8 ms
Buffers	31,395	181
Memberships access	Parallel sequential scan	Index lookup per candidate

A note on IN versus EXISTS: PostgreSQL already turns IN (subquery) into a semi-join, so the two often plan the same way. The DISTINCT adds nothing, since a semi-join never returns a row twice, and it can push the planner toward building the full list first. Switching to EXISTS helps. The bigger win is adding the filter. If the outer query is limited to something you also know about the subquery's rows, say so in the subquery too. Don't rely on the planner to infer it.

Lesson 4: Ask what a query is for before you tune it

The slowest query in the job didn't return any data. It computed the run's ceiling:

SELECT max(ts) FROM (
    SELECT max(updated_at_num) AS ts FROM accounts WHERE account_type IN (...)
    UNION ALL
    SELECT to_char(max(password_changed_at), 'YYYYMMDDHH24MISS')::numeric FROM accounts WHERE account_type IN (...)
    UNION ALL
    SELECT max(updated_at_num) FROM org_members
    UNION ALL
    SELECT max(s.updated_at_num)
    FROM staff_links s JOIN accounts a ON a.account_id = s.account_id AND a.account_type = 'STAFF'
) x;

It took 853 ms and about 1 million buffers to return one number. The plan showed three different behaviours:

Branch	How PostgreSQL ran it	Cost
`max(updated_at_num)` on one table	Backward index scan, stops at the first row	0.2 ms
`max(password_changed_at)`	Sequential scan; the column has no index	~590 ms
`max(...)` over a join	Full join of every staff account, then aggregate	~270 ms

PostgreSQL has a neat shortcut here. As the CREATE AGGREGATE documentation explains, a MIN- or MAX-like aggregate can be answered from an index, because SELECT max(col) FROM tab is equivalent to SELECT col FROM tab ORDER BY col DESC LIMIT 1. The planner walks the index backward and takes the first non-null value. It also works on partitioned tables and UNION ALL views, by merging each child's index. It does not work through a join. The planner source (planagg.c) limits the shortcut to queries that reference exactly one table, so the join branch had to build the whole join just to find its largest value.

That is where understanding the use case matters more than tuning. This value is an upper bound for the next change window. It doesn't have to be the exact newest staff row; it only has to be at least as high. Dropping the join and taking max(updated_at_num) over all of staff_links gives a bound that is never lower, and costs a single index probe. The join filter still applies in the query that reads the window.

The same review turned up a second easy win. The ceiling was global, but the job ran it once per scope, inside a loop, so it returned the same value several times per run. Computing it once per run cost nothing to change.

The password_changed_at branch I left alone. More on that in Lesson 6.

Lesson 5: A test that returns zero rows proves nothing

My first round of EXPLAIN ANALYZE used a one-hour window, the job's steady state. Most queries looked great: under a millisecond. Then I noticed how often the plans said (never executed). In that hour, no organization had changed, so every join after the first step was skipped. The plan was cheap because there was no work, not because the work was cheap.

Widening the window to 30 days told a different story. One query that ran in 0.7 ms on the hourly window chose a completely different plan:

It started from the membership table instead of the changed rows.
It read every one of the 15,368 primary memberships, then probed accounts for each.
It found 31 changed rows, after 72,522 buffers.

The cause was a row estimate. The planner expected 188 primary memberships and got 15,368, about 80 times more. With 188, starting from memberships looks cheap. A likely reason is how PostgreSQL combines filters: by default it multiplies each condition's selectivity as if they were independent, so correlated filters (type, deleted flag, end date) can be badly underestimated. Check that statistics are fresh with ANALYZE first. If the columns really are correlated, CREATE STATISTICS can tell the planner how they relate.

So I now test every change-data query with three windows:

Window	What it tells you
Normal (one hour)	Steady-state cost
Wide (30 days)	What happens after an outage or a missed run
Full history	The very first run, when the checkpoint is empty

The first run is easy to forget. Here it was fine: page one of the full-history query took about 20 ms, because the planner could stop as soon as it had 500 rows. But you only know that by running it.

Lesson 6: "Good enough" is a valid result. Index only what doesn't scale

After the rewrites, I had a list of index candidates that would have made every remaining query near-instant: one on password_changed_at, a partial index for a rare account type, and a few more. Before building any of them, I added up what an hourly run actually cost without them:

Part of the run	Cost
Ceiling queries, once per run	~1.5–1.8 s
Change-window queries, per scope	~1.2 s for the largest
Whole run	~6–7 s, every buffer from cache

The slowest single statement was under a second, far from any timeout. Every index you add costs writes, storage and maintenance forever, and here the database was a replication target that would pay for each one on every write. So most of the candidates were dropped. That is a fine outcome. Not every slow-looking query needs an index; sometimes it needs nothing.

One query was different, because its cost multiplied. It loaded full account details in chunks of 100 IDs, and each chunk joined org_members on account_id, a column with no index. Every chunk scanned a 9-million-row table: about 0.7 s.

Scenario	Accounts to load	Chunks	Time
Hourly	~50	1	~0.7 s
30-day catch-up	~20,000	~200	~2.5 min
First run	~870,000	~8,700	~1.7 hours

That is the test I use now: don't ask whether a query is slow, ask whether its cost grows with the data or the backlog. Fixed costs can usually be tolerated. Costs that multiply need an index. We added exactly one: org_members (account_id).

Lesson 7: A new index can make a query slower

The new index worked exactly as planned: the org_members step went from scanning 5.4 million rows to reading 2,217 buffers. Then I looked at the total for the chunk query. It had gone from 723 ms to 26.9 seconds.

	Before the index	After the index
`org_members`	Parallel sequential scan, 174k buffers	Index scan, 2,217 buffers
`memberships`	One scan of the 15k primary rows, hashed	7,191 index probes, each discarding ~14,000 rows
Total	723 ms, 243k buffers	26,872 ms, 22.7M buffers

With org_members suddenly cheap, the planner switched to a nested loop and probed memberships once per organization row through an index on org_id. That index holds every membership in the organization, one per member account. The accounts in this chunk belonged to very large organizations, so each probe walked about 14,000 entries to find the one primary membership it needed.

The planner expected roughly one row per probe. When it knows a value in advance, PostgreSQL can look it up in its most-common-values statistics. Here each probe's value came from the outer row at run time, so the planner estimated an average number of rows per organization. The handful of very large organizations didn't fit that average, and re-running ANALYZE doesn't change how the estimate is made.

The fix was another index, but a very small one: a partial index containing only the rows the query asks for.

CREATE INDEX CONCURRENTLY memberships_primary_org_idx
    ON memberships (org_id) INCLUDE (parent_account_id, end_date)
    WHERE membership_type = 'PRIMARY' AND is_deleted = false;

It holds about 15,000 rows instead of 650,000, so it is small and cheap to maintain, and each probe now lands directly on the row it needs. PostgreSQL uses a partial index only when it can prove, at planning time, that the query's WHERE clause implies the index predicate. The docs point out that a bind parameter such as membership_type = $1 can't prove that. Because this query uses membership_type = 'PRIMARY' AND is_deleted = false as literal values, the planner can match the partial index without any code change.

The lesson isn't "avoid indexes". It's that an index changes the plan around it, not just the step it targets. Re-run the full EXPLAIN ANALYZE after every index, and read the total, not just the line you were fixing.

Building the indexes online

Both indexes went in with CREATE INDEX CONCURRENTLY, so writes kept flowing. A few things I check every time:

1. Look for old transactions first. Per the CREATE INDEX docs, a concurrent build waits before each table scan for transactions that have modified the table, and after the second scan for any transaction holding a snapshot older than that scan. My pre-check found 65 sessions from a replication tool sitting idle in transaction for 28 days. Before cancelling anything, check whether they actually matter:

SELECT count(*) AS sessions,
       count(backend_xmin) AS holding_snapshot,
       count(backend_xid)  AS holding_xid
FROM   pg_stat_activity
WHERE  state = 'idle in transaction'
  AND  now() - xact_start > interval '1 day';

Ours held no snapshot, no transaction ID and no locks on the target tables, so they couldn't block the build or hold back vacuum. That's a cleanup item, not a blocker.

2. Run with autocommit on. CREATE INDEX CONCURRENTLY cannot run inside a transaction block, and some SQL clients wrap scripts in one.

3. Decide on lock_timeout deliberately. It lets a build fail fast instead of queueing behind a long transaction, but a build that fails partway leaves an invalid index behind. I left it unset and watched the progress view instead.

4. Check for invalid indexes afterwards. CREATE INDEX ... IF NOT EXISTS sees an invalid leftover's name and skips it with only a NOTICE. Drop it with DROP INDEX CONCURRENTLY and build again, or rebuild it with REINDEX INDEX CONCURRENTLY.

SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

5. Build smallest to largest, and watch replication lag during the big ones.

A review checklist

This is what I now run through for any SQL headed to a large production table, whether a person or an AI wrote it.

Predicates

No functions wrapped around indexed columns in WHERE or JOIN (COALESCE, TRIM, LOWER, casts, date math).
Every "defensive" expression is backed by a data check: null counts, padded values, duplicate keys.
Filters the outer query knows are repeated inside subqueries and on every joined table.
IN (SELECT DISTINCT ...) replaced with a correlated EXISTS, or at least the DISTINCT dropped.
Partial-index conditions written as literals, not bind parameters, so the planner can match them.

Purpose

For each query, what is it for? A ceiling, a count or a page may not need the precision it was written with.
Values that don't change within a run are computed once per run, not inside a loop.
Keyset pagination sorts on a unique key, or ends with a unique tie-breaker.

Evidence

EXPLAIN (ANALYZE, BUFFERS) on production-sized data, for normal, wide and first-run windows.
No (never executed) hiding the parts you care about.
Estimated versus actual rows compared; a gap of 10x or more is worth chasing.
Costs that multiply (per chunk, per page, per backlog hour) indexed; fixed costs judged against the schedule.
The full plan re-run after every new index.

Closing: tuning is mostly a conversation

Looking back, the biggest gains came from questions, not configuration:

"Can this column actually be null?" No, so the COALESCE went, and the existing index started working.
"What is this MAX used for?" An upper bound, so it didn't need the expensive join.
"Does this cost grow with the backlog?" Only in one place, so we added one index instead of six.

None of that needed database expertise the developers couldn't learn. It needed someone to explain how the planner reads a predicate, why a function hides an index, and why QA data isn't evidence for production. Once that was clear, the developer rewrote the queries himself, and the run time in QA went from 30–50 minutes to under 30 seconds.

Some tuning really is just common sense and basic troubleshooting: read the plan, check the data, and ask what the query is for. If your team writes SQL, especially with an AI assistant drafting it, get a database engineer to look at it before it meets production data. It never hurts, and sometimes it saves you an hour-long first run, or a 27-second query you didn't know you had.

References

CREATE AGGREGATE: how MIN/MAX-like aggregates use an index
planagg.c: the MIN/MAX optimization and its single-table restriction
Indexes on Expressions
Partial Indexes
Index-Only Scans and Covering Indexes
Row Estimation Examples: MCV lists, averages and combining conditions
Statistics Used by the Planner: including extended statistics
Using EXPLAIN
CREATE INDEX: Building Indexes Concurrently

Aurora PostgreSQL Backups at Scale, Part 1: Designing AWS Backup Across Multiple Accounts

Pranay Ravi — Mon, 02 Nov 2026 17:52:46 +0000

AWS Backup + AWS Organizations lets you define Aurora PostgreSQL backups once and stamp them into every account. But the org policy won't create your vaults or IAM roles, continuous backup quietly takes over RDS's native backups, Aurora silently ignores cold-storage settings, the default aws/rds key blocks cross-account and air-gapped copies, and your 5-6 year old snapshot will outlive its PostgreSQL version. Here's how we designed around all of it.

Every Aurora cluster has backups turned on from day one. That's the trap: "it's backed up" becomes a sentence nobody verifies.

When you run Aurora PostgreSQL across 80+ AWS accounts, multiple regions, and retention measured in years, the real questions are:

Who owns the backup configuration?
Where does the copy live if a region goes dark?
Can an admin (or an attacker with admin) delete it?
Will a seven-year-old snapshot actually restore?

This is part 1 of a series. Part 1 = design. Part 2 covers monitoring and reporting failures. Part 3 covers restores.

1. The five nouns of AWS Backup

Noun	What it is	Where it lives
Vault	Encrypted container for recovery points	Each member account, each region you back up to
Plan	A named bundle of rules	Generated in member accounts by the org policy
Rule	Schedule + retention + target vault + copy actions + continuous on/off	Inside the plan
Selection	Which resources get protected (we use a tag)	Inside the plan; references an IAM role in the member account
Policy	AWS Organizations backup policy that generates the plan	Management (or delegated admin) account

Key insight: retention, target vault and replication are per rule, not per plan. The plan is just a container.

2. Account-level vs organization-level settings

Think of it as governing vs holding.

Management account (governs)

# 1. Let AWS Backup act across the organization
aws organizations enable-aws-service-access --service-principal backup.amazonaws.com

# 2. Turn on the backup policy type on the org root
aws organizations enable-policy-type --root-id r-xxxx --policy-type BACKUP_POLICY

# 3. Allow cross-account backup copies
aws backup update-global-settings --global-settings isCrossAccountBackupEnabled=true

In the AWS Backup console (Settings), also enable backup policies and cross-account monitoring. If you'd rather not run this from the management account itself, register a delegated administrator for AWS Backup.

Member accounts (hold)

Each account that owns databases needs:

A vault in every region the policy targets (and in the DR region if you copy there).
An IAM role that the policy's selection references, trusted by backup.amazonaws.com.
Aurora opted in for that region:

aws backup update-region-settings --resource-type-opt-in-preference Aurora=true

The tag on the cluster, e.g. backup-enable = yes.

⚠️ The sentence I wish I'd read on day one: an org backup policy creates the plan in the member account, but not the vault and not the IAM role. If either is missing, the job never starts, and nothing shouts about it.

Policy inheritance

Backup policies attach to the org root, an OU, or a single account, and AWS merges them into an effective policy per account using inheritance operators like @@assign. A common layout:

Root: baseline policy (e.g. continuous-only for everything tagged)
Production OU: the full ladder with DR copy
Specific account: an override, if you really must

You can stop child policies from overriding the parts you care about with @@operators_allowed_for_child_policies. To see what an account actually ended up with:

aws organizations describe-effective-policy --policy-type BACKUP_POLICY --target-id 111122223333

3. Vaults: standard vs logically air-gapped

Standard vault

Lives in your own account, encrypted with the KMS key you choose. You can optionally add Vault Lock:

Mode	Who can delete early / remove lock	Use it for
Governance	Principals with the right IAM permissions can bypass	Day one, while you tune retention
Compliance	Nobody after the grace period (minimum 72 hours): not admins, not root, not AWS	Regulated / ransomware-proof retention

resource "aws_backup_vault_lock_configuration" "primary" {
  backup_vault_name  = aws_backup_vault.primary.name
  min_retention_days = 7
  max_retention_days = 2555 # 7 years

  # Uncomment to switch to COMPLIANCE mode. Omitted = governance mode.
  # After this grace period the lock is permanent.
  # changeable_for_days = 3
}

💡 Start in governance mode, prove your min/max retention against real recovery points, then flip to compliance. A compliance lock with a mistake in it is a mistake you keep.

Logically air-gapped vault

A separate vault type that is always compliance-locked, encrypted with an AWS-owned key by default, and shareable to a recovery account via AWS RAM, even an account in a different organization. The goal is logical separation: owning your production account (or even your org) isn't enough to reach those copies.

Two Aurora-specific notes:

Aurora can't back up directly into an air-gapped vault; AWS Backup takes a temporary recovery point in a standard vault and copies it across.
Clusters encrypted with the AWS-managed aws/rds key can't be copied into an air-gapped vault. You need a customer-managed KMS key (more on this in the traps section).

4. Continuous (PITR) vs snapshot backups

	Continuous (PITR)	Snapshot
Restore to	Any second in the window	The moment it ran
Max retention	35 days	Whatever the rule says
Per cluster	One continuous recovery point	One per scheduled run
Cross-region copy	Arrives as a snapshot (no PITR in DR)	Copies as a snapshot
Appears in RDS as	Automated backups	Manual snapshots

The handover nobody tells you about

Once AWS Backup enables continuous backup on an Aurora cluster, AWS Backup takes over Aurora's native automated backups. You can no longer change the backup window or retention from the RDS console or API. In the RDS console, those recovery points are named like continuous:cluster-<job-id>.

Lessons from production:

One owner for PITR. Two org policies that both enable continuous backup on the same cluster will conflict. To move a cluster between policies, exclude it from the old policy first (NotResources).
Zero jobs can be healthy. A continuous-only plan doesn't create a job per scheduled run once it's set up. An empty job list is not a failure.
Full snapshot first. Make sure a full snapshot exists alongside the continuous recovery point before you rely on it.
RDS console ≠ AWS Backup status. EarliestRestorableTime on the RDS console reflects native PITR, not your backup policy's activity.

5. Several policies and respective jobs

Instead of one giant policy that tries to do everything, we can split protection into several org policies, each with a single job and its own tag to opt a cluster in.

Policy	Runs in	Job	Retention idea	Leaves the region?
Primary site	Primary region (`us-east-1`)	Continuous PITR, the fast "undo" button	Short rolling window (days)	No
Long-term ladder	Primary region	Weekly / monthly / yearly snapshots	5 weeks → 1 year → 7 years	Every rule copies to a vault in `us-west-2`
DR site	DR region (`us-east-2`)	Weekly / monthly / yearly snapshots of the headless global-database secondary	5 weeks → ~13 months → 7 years	Already in the DR region

Why split it this way

Independent layers. Tuning the long-term ladder never risks your PITR window, and vice versa.
Opt in per layer, by tag. A dev cluster can take PITR only; production takes all three. A new database is protected the moment it's tagged. No ARN lists to maintain.
No accidental cross-matching. The DR policy matches a DR-only tag value, so it can never pick up a primary cluster. The primary-site policy uses a different tag key, so it's independent of the ladder.

The selections are the whole trick. Trimmed down, the three policies differ mainly here:

// Primary site policy (us-east-1)
"selections": { "tags": { "primary": {
  "tag_key":   { "@@assign": "backup-enable-site" },
  "tag_value": { "@@assign": ["primary-only"] } } } }

// Long-term ladder policy (us-east-1, copies to us-west-2)
"selections": { "tags": { "ladder": {
  "tag_key":   { "@@assign": "backup-enable" },
  "tag_value": { "@@assign": ["yes"] } } } }

// DR site policy (us-east-2) when you have a DR Global cluster / headless/serverless
"selections": { "tags": { "dr": {
  "tag_key":   { "@@assign": "backup-enable" },
  "tag_value": { "@@assign": ["dr-e2-only"] } } } }

A production database ends up with three independent histories: a short PITR window in its own region, long-term snapshots copied to a second region where DR cluster is not available, or a separate snapshot history built in a DR region where there is a DR Global cluster (headless/serverless) from replicated storage. Lose any one region and the other remain.

Click for a trimmed long-term ladder rule with a cross-region copy

{
  "plans": {
    "plan-e1": {
      "regions": { "@@assign": ["us-east-1"] },
      "rules": {
        "yearly": {
          "schedule_expression": { "@@assign": "cron(0 5 1 1 ? *)" },
          "lifecycle": { "delete_after_days": { "@@assign": "2555" } },
          "target_backup_vault_name": { "@@assign": "vault-e1" },
          "recovery_point_tags": {
            "backup-rule": {
              "tag_key": { "@@assign": "backup-rule" },
              "tag_value": { "@@assign": "yearly" }
            }
          },
          "copy_actions": {
            "arn:aws:backup:us-west-2:$account:backup-vault:vault-w2": {
              "target_backup_vault_arn": {
                "@@assign": "arn:aws:backup:us-west-2:$account:backup-vault:vault-w2"
              },
              "lifecycle": { "delete_after_days": { "@@assign": "2555" } }
            }
          }
        }
      },
      "selections": {
        "tags": {
          "ladder": {
            "iam_role_arn": { "@@assign": "arn:aws:iam::$account:role/org-backup-role" },
            "tag_key": { "@@assign": "backup-enable" },
            "tag_value": { "@@assign": ["yes"] }
          }
        }
      }
    }
  }
}

$account is substituted with each member account's ID, so one policy works everywhere. An empty lifecycle on a copy action means the copy inherits the source rule's retention. Set it explicitly only when the remote copy should live longer or shorter than the original.

Watch out when you split

Only one policy should own PITR. Continuous backup can be enabled in more than one policy. If a cluster carries tags that match two policies with enable_continuous_backup: true, two plans claim the single continuous recovery point a cluster is allowed. Pick the owner (we use the primary-site policy) and keep continuous off everywhere else.
Tag recovery points. Every rule stamps backup-policy and backup-rule tags on what it creates, so during an incident someone can filter for "the yearly from January" instead of guessing from timestamps.
Next up: air gap. A logically air-gapped copy in a separate recovery account is the next layer, for backups that must survive even a compromised organization.

6. Backing up a headless/serverless DR cluster

With Aurora Global Database, the primary region replicates to a secondary region at the storage layer. A headless secondary is that cluster with zero DB instances: storage keeps up, compute costs nothing until you need it. In our tests, adding an instance to a headless cluster took under 10 minutes.

Instead of copying every snapshot across regions, we point the DR-site policy at the headless cluster itself, in us-east-2. The DR region builds its own backup history from its own replicated storage, with no cross-region copy charges, and becomes a genuinely separate recovery site.

Creating a headless Aurora PostgreSQL secondary (the console can't do zero instances for PostgreSQL):

aws rds create-db-cluster \
  --region us-east-2 \
  --db-cluster-identifier mydb-e2 \
  --global-cluster-identifier mydb-global \
  --engine aurora-postgresql \
  --engine-version 16.4 \
  --db-subnet-group-name mydb-subnets-e2 \
  --vpc-security-group-ids sg-0123456789abcdef0 \
  --storage-encrypted \
  --kms-key-id arn:aws:kms:us-east-2:111122223333:key/your-cmk-id

Gotchas:

No burstable classes (db.t3, db.t4g) in a global database. Your emergency instance is r- or m-class.
Separate Terraform state for DR backup infra. Running the same backup module from the same directory for a second region will plan to destroy the first region's vault, and the account-global IAM role will collide with EntityAlreadyExists.
On PostgreSQL, creating and deleting secondary readers can affect vacuum on the primary; watch it after DR tests.

7. The traps that show up years later

🕰️ Trap 1: Your yearly backup will outlive its PostgreSQL version

A 5-6 year old snapshot taken on today's major version will almost certainly be restored (if ever) after that version leaves standard support. When that happens, Aurora tries to upgrade the snapshot during restore to a version still in standard support. If that fails, it enrolls the restored cluster in RDS Extended Support, which is billed.

What we do about it:

Know what you're holding. We tag every cluster with Engine and EngineVersion when it's enrolled (section 7b), and snapshots record their engine version too:

aws rds describe-db-cluster-snapshots --query "DBClusterSnapshots[].[DBClusterSnapshotIdentifier,EngineVersion,SnapshotCreateTime]" --output table

Yearly restore drill. Restore the oldest long-term snapshots, upgrade them, and confirm the data is usable, before an auditor asks.
Logical safety net. For archives that truly matter, keep a pg_dump alongside the snapshot. A dump doesn't care which engine version produced it.

🧊 Trap 2: Cold storage settings that silently do nothing

A rule can say move_to_cold_storage_after_days: 30, and the policy will happily accept it. It looks like a cost win. But AWS Backup's cold tier covers services like EBS, EFS and DynamoDB, not Aurora, and AWS Backup ignores the transition for resource types that don't support it. No error, no warning: seven years of yearlies stay at warm-storage prices.

If you see cold-storage settings in a policy that only selects Aurora clusters, they're documentation, not behavior. If long-term cost matters, a pg_dump in S3 with a lifecycle rule to Glacier is the real cheap archive (and doubles as the Trap 1 safety net).

🔑 Trap 3: The default KMS key blocks your exit doors

Clusters encrypted with the AWS-managed aws/rds key can't be shared or copied to another account, and can't be copied into a logically air-gapped vault. Use a customer-managed key from day one. Changing an existing cluster's key means snapshot → copy with new key → restore.

🏚️ Trap 4: Missing vaults fail silently

The org policy happily creates a plan that targets a vault that doesn't exist. Audit every member account:

aws backup list-backup-vaults --query "BackupVaultList[].BackupVaultName"

⏱️ Trap 5: "Last runtime" is not success

The console's last-runtime timestamp moves when the scheduler evaluates the plan, not when a job succeeds. Real health comes from job states, which is exactly what Part 2 is about.

What's next

Part 2 — Monitoring & reporting: EventBridge + SNS for failures, and consolidating AWS Backup reports from every account into one queryable place.
Part 3 — Restores: snapshot vs point-in-time restores, the parameter-group gotcha, and a scripted, interactive restore flow.

If you run Aurora at scale, which trap got you first? Drop it in the comments 👇

15-Minute RPO to 7-Year Retention: Elasticsearch Backups on S3 with SLM (No Scripts, No Keys)

Pranay Ravi — Wed, 28 Oct 2026 16:03:47 +0000

Four Elasticsearch SLM policies (15-min, hourly, monthly, yearly) write to four repositories in one S3 bucket.
Nodes authenticate with an EC2 instance profile. Zero access keys in the keystore.
SLM handles retention, from 7 days up to ~7 years. S3 lifecycle rules stay off, on purpose.
The whole thing is about 30 lines of JSON and a handful of AWS CLI commands.

Three questions every backup should answer

Elasticsearch cluster runs on EC2 in a regulated environment. I wanted backups that answer three questions in one sentence each: how much data can we lose, how far back can we go, and what credentials does the job use?

Elasticsearch already ships a scheduler for this: Snapshot Lifecycle Management (SLM). So I moved everything into SLM and let S3 be dumb, durable storage.

The architecture

Read it left to right. Nodes get temporary credentials from an IAM role. SLM fires snapshots on cron schedules. The S3 repository writes objects into an SSE-S3 encrypted bucket, one folder per tier. SLM comes back around to delete expired snapshots, and restores go the other way.

Tier	Runs	Keeps	What's in it
`15min/`	every 15 min	7 days	critical app indices only
`daily/`	hourly	35 days	everything + cluster state
`monthly/`	1st of month	~13 months	everything + cluster state
`yearly/`	Jan 1	~7 years	everything + cluster state

The daily/ folder holds the hourly snapshots. It's the day-to-day recovery tier.

Step 1: IAM, the keyless way

One role, one instance profile, attached to every node: data, master and Kibana. The permissions policy needs bucket-level and object-level actions in separate statements:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "s3:ListBucket",
        "s3:GetBucketLocation",
        "s3:ListBucketMultipartUploads",
        "s3:ListBucketVersions"
      ],
      "Resource": "arn:aws:s3:::my-es-snapshots"
    },
    {
      "Effect": "Allow",
      "Action": [
        "s3:GetObject",
        "s3:PutObject",
        "s3:DeleteObject",
        "s3:AbortMultipartUpload",
        "s3:ListMultipartUploadParts"
      ],
      "Resource": "arn:aws:s3:::my-es-snapshots/*"
    }
  ]
}

Create the role with an EC2 trust policy, then wire it to each node:

aws iam create-role --role-name es-snapshot-role \
  --assume-role-policy-document '{"Version":"2012-10-17","Statement":[{"Effect":"Allow","Principal":{"Service":"ec2.amazonaws.com"},"Action":"sts:AssumeRole"}]}'

aws iam put-role-policy --role-name es-snapshot-role \
  --policy-name es-snapshot-s3 --policy-document file://s3-policy.json

aws iam create-instance-profile --instance-profile-name es-snapshot-profile
aws iam add-role-to-instance-profile \
  --instance-profile-name es-snapshot-profile --role-name es-snapshot-role

# Repeat for every data, master and Kibana node
aws ec2 associate-iam-instance-profile --instance-id i-0123456789abcdef0 \
  --iam-instance-profile Name=es-snapshot-profile

If a node already has an instance profile, add the S3 policy to that role instead. An instance can only have one profile.

Then leave the Elasticsearch keystore alone. With no s3.client.default.access_key configured, the S3 client uses the instance profile automatically.

Step 2: Repositories

On 7.x, run this on each node and do a rolling restart. On 8.x, S3 support is built in, so skip it.

sudo /usr/share/elasticsearch/bin/elasticsearch-plugin install repository-s3

Then register one repository per tier:

PUT _snapshot/es-backup-15min
{
  "type": "s3",
  "settings": {
    "bucket": "my-es-snapshots",
    "base_path": "15min",
    "server_side_encryption": true
  }
}

Do the same for daily, monthly and yearly.

Step 3: SLM policies

The 15-minute policy covers only the critical indices:

PUT _slm/policy/snap-15min
{
  "schedule": "0 5/15 * * * ?",
  "name": "<snap-15min-{now/d}>",
  "repository": "es-backup-15min",
  "config": {
    "indices": ["app-critical-*"],
    "ignore_unavailable": true,
    "include_global_state": false
  },
  "retention": { "expire_after": "7d", "min_count": 10 }
}

The hourly policy snapshots everything:

PUT _slm/policy/snap-hourly
{
  "schedule": "0 0 * * * ?",
  "name": "<snap-hourly-{now/d}>",
  "repository": "es-backup-daily",
  "config": { "indices": ["*"], "include_global_state": true },
  "retention": { "expire_after": "35d", "min_count": 24 }
}

The monthly and yearly policies are the same shape, with schedules like 0 0 2 1 * ? and 0 0 3 1 1 ?, and expire_after values of 396d and 2575d. SLM appends a unique suffix to every snapshot name, so the date-based names never collide.

Kick the tires:

POST _slm/policy/snap-15min/_execute
GET _slm/policy/snap-15min?human
GET _slm/stats
GET _cat/snapshots/es-backup-15min?v&s=end_epoch:desc

The policy response shows last_success, last_failure and next_execution. Those are the fields to alert on.

Five things that bit me (so they don't bite you)

1. "Cannot create snapshot repository" on a healthy cluster.
Repository registration is verified from every master and data node. One node without the instance profile fails the whole request. Check every node, not just the one you're curling.

2. Access denied with a policy that looked right.
I had object permissions on bucket/* but forgot the bucket ARN itself. ListBucket and GetBucketLocation live at the bucket level.

3. I wanted a 5-minute RPO.
SLM refuses schedules under 15 minutes by default (slm.minimum_interval). The fix was scoping: a 15-minute policy for only the critical indices, and hourly for everything else. If you need tighter than 15 minutes, you need replication, not snapshots.

4. I almost added an S3 lifecycle rule.
Don't. Snapshots are incremental and share files, so expiring objects by age can corrupt the repository. SLM retention knows which files are still referenced. If cleanup seems slow, remember it runs once a day by default; force it with POST _slm/_execute_retention.

5. Schedules colliding at the top of the hour.
Starting the 15-minute policy at minute 5 (0 5/15 * * * ?) keeps it out of the hourly run's way.

Bonus check: the 15-minute tier keeps about 672 snapshots (96 a day for 7 days). Make sure your version's repository snapshot limit can hold that.

Restoring (the part that actually matters)

Restore to new names first, compare, then swap:

POST _snapshot/es-backup-daily/<snapshot-name>/_restore
{
  "indices": "app-critical-orders",
  "rename_pattern": "(.+)",
  "rename_replacement": "restored-$1",
  "include_global_state": false
}

For a full-cluster restore, close or delete the target indices first, then restore without the rename.

What I'd add next

Alerting on last_failure and on the failure counts in _slm/stats, plus a monthly restore drill into a scratch cluster. A backup you haven't restored is a hypothesis.

If you run self-managed Elasticsearch on EC2, I'd love to hear how you handle long-term retention. Drop it in the comments.

Stop Running psql Commands by Hand — Build a REST API for PostgreSQL User Management

Pranay Ravi — Fri, 29 May 2026 21:45:12 +0000

If you manage PostgreSQL databases across multiple environments, you've probably done this:

SSH to the DB host (or connect via psql)
Run CREATE USER jsmith CONNECTION LIMIT 20 PASSWORD '...'
Slack the password to the developer
Forget to log it anywhere
Repeat for every environment, every onboarding, every access request

It's tedious, error-prone, and leaves zero audit trail. Here's a better way.

What I Built

pg-user-api is a lightweight Flask REST API that wraps PostgreSQL user provisioning in clean HTTP endpoints. You register your databases once in a SQLite inventory, then any tooling — CI pipelines, internal portals, Ansible playbooks, or a plain curl — can create and manage users across environments without ever touching psql.

GitHub: pcraavi/PostgreSQL-user-creation-API

The Problem It Solves

In teams that span dev, QA, UAT, and prod, you end up with different patterns of users:

App service accounts — named after the host/port combo (web01_8080)
Kubernetes workload accounts — named after env prefix + farm (dv_gearservice)
Individual dev/QA accounts — low connection limits, scoped to non-prod
Read-only analyst accounts — prod only, no DDL
DBA accounts — CREATEDB CREATEROLE LOGIN, rarely provisioned

Each type has different CONNECTION LIMIT values, privilege levels, and naming conventions. Encoding these patterns in an API means the rules are consistent, repeatable, and auditable.

Architecture

The project is intentionally small — five Python files and a requirements list:

pg_user_api/
├── app.py              # Flask app — all endpoints
├── auth.py             # HTTP Basic Auth (constant-time compare)
├── database.py         # SQLite registry + audit log
├── notifications.py    # Notification stubs (Webex / Slack / Email)
├── seed_db.py          # One-time setup: creates DB + sample records
└── requirements.txt

Two credential pairs, clearly separated:

PG_API_USER / PG_API_PASS — who can call this API (your team/tooling)
PG_ADMIN_USER / PG_ADMIN_PASS — the PostgreSQL DBA role that executes DDL

The DBA credentials never appear in API URLs or response bodies. Callers only need the API credentials plus env and database.

SQLite as a config-free registry:

Rather than a static YAML or environment file listing every hostname, databases are registered once in a db_registry table:

Column	Description
`env`	`dev` / `qa` / `uat` / `prod`
`db_name`	PostgreSQL database name
`hostname`	FQDN or IP of the host
`port`	PostgreSQL port (default 5432)
`active`	`1` = active, `0` = skip

Every endpoint looks up the hostname dynamically from this registry. No hardcoded connection strings anywhere in application code.

The Endpoints

All endpoints are GET with query params (by design — simple to curl, simple to call from automation scripts).

GET /                            # Health check, no auth required
GET /api/v1/registry             # List registered databases
GET /api/v1/users/all            # List all PostgreSQL roles on a database
GET /api/v1/users/app            # Create VM/container service account
GET /api/v1/users/app-k8s        # Create Kubernetes workload account
GET /api/v1/users/devqa          # Create individual dev/QA user
GET /api/v1/users/devlead        # Create dev-lead user
GET /api/v1/users/readonly       # Create read-only user
GET /api/v1/users/dba            # Create DBA user (CREATEDB + CREATEROLE)
GET /api/v1/users/reset          # Reset a user's password
GET /api/v1/users/search-path    # Update search_path for a user
GET /api/v1/users/find           # Look up a specific user

Every user-creation endpoint returns the same structured response:

{
  "username": "web01_8080",
  "password": "generatedSecurePassword",
  "status": "user created",
  "hostname": "pg-dev-01.example.com",
  "database": "myapp_dev",
  "port": "5432",
  "env": "dev"
}

Password is returned once at creation time. The API uses secrets.token_urlsafe(16) for generation — no insecure random module.

Running It

1. Clone and install

git clone https://github.com/pcraavi/PostgreSQL-user-creation-API.git
cd PostgreSQL-user-creation-API
pip install -r requirements.txt

2. Seed the registry

python seed_db.py

This creates pg_registry.db with sample entries. Edit SAMPLE_RECORDS in seed_db.py to point at your real PostgreSQL hosts.

3. Set credentials via environment variables

# Windows PowerShell
$env:PG_API_USER   = "pgadmin"
$env:PG_API_PASS   = "Ch@ngeMe2024!"
$env:PG_ADMIN_USER = "role_create"
$env:PG_ADMIN_PASS = "your_pg_password"

# Linux / macOS
export PG_API_USER="pgadmin"
export PG_API_PASS="Ch@ngeMe2024!"
export PG_ADMIN_USER="role_create"
export PG_ADMIN_PASS="your_pg_password"

4. Start

python app.py
# Listening on http://localhost:5000

Example Calls

Create a service account for a VM running on port 8080:

curl -u pgadmin:Ch@ngeMe2024! \
  "http://localhost:5000/api/v1/users/app?env=dev&database=myapp_dev&servername=web01&port=8080"

Creates user web01_8080 with CONNECTION LIMIT 200.

Create a Kubernetes workload account:

curl -u pgadmin:Ch@ngeMe2024! \
  "http://localhost:5000/api/v1/users/app-k8s?env=dev&database=myapp_dev&env_prefix=dv&farmname=gearservice"

Creates user dv_gearservice.

Reset a forgotten password:

curl -u pgadmin:Ch@ngeMe2024! \
  "http://localhost:5000/api/v1/users/reset?env=prod&database=myapp_prod&username=analyst01"

Generates a new secrets.token_urlsafe(16) password and applies it immediately. Returns the new password in the response.

Idempotency

All create endpoints check pg_catalog.pg_roles before issuing CREATE USER. If the role already exists, the API returns "status": "user already exists" and exits cleanly. Safe to call from automation without worrying about duplicate creation errors.

Audit Log

Every operation (create, reset, search_path change) is written to a audit_log table in the same pg_registry.db SQLite file:

sqlite3 pg_registry.db \
  "SELECT * FROM audit_log ORDER BY performed_at DESC LIMIT 10;"

You get a timestamped record of who called what, on which database, with what outcome. Useful for access reviews and incident investigations.

Notification Hooks

notifications.py ships with ready-to-uncomment stubs for Webex Teams, Slack, and email. Wire in your webhook URL or SMTP config, then call send_notification() from any endpoint to push alerts to your team when accounts are created or passwords reset.

Security Notes

Intended for internal / intranet use — put it behind a VPN or API gateway, not on the open internet.
For internet-facing deployments, swap HTTP Basic Auth for JWT or an API key header.
pg_registry.db contains your real hostnames — it's in .gitignore and should stay off version control.
The DBA password never appears in URLs, query strings, or logs.

What I'd Add Next

A few things on the roadmap:

GRANT/REVOKE endpoints — privilege management beyond account creation
Schema-level grants — GRANT SELECT ON ALL TABLES IN SCHEMA patterns
Token-based auth — drop-in replacement for Basic Auth
Docker packaging — docker run for teams that don't want to manage Python deps
Structured audit export — JSON or CSV export of the audit log for compliance workflows

Wrapping Up

If your team provisions PostgreSQL users more than a few times a month, wrapping it in an HTTP interface pays for itself quickly. The audit trail alone is worth it.

The full source is at github.com/pcraavi/PostgreSQL-user-creation-API. It's MIT-licensed — fork it, adapt the user types to your org's naming conventions, and wire in your notification channels.

Questions or suggestions? Drop them in the comments or open an issue on GitHub.

PostgreSQL VACUUM Tuning: A Technical Deep Dive Into Autovacuum Configuration

Pranay Ravi — Sun, 24 May 2026 09:37:29 +0000

Author's Note: This article documents a production incident investigation and the technical findings that emerged from returning to foundational documentation. The fix was implemented by a colleague; this article captures the learning journey through proper documentation review.

The Incident: High CPU Due to Autovacuum Contention

A production Aurora PostgreSQL cluster experienced sustained CPU utilization between 85-90% over a 3-4 hour window. CloudWatch Performance Insights identified the primary wait event as CPU (not I/O or lock contention), and the top consuming operation was autovacuum VACUUM processes running on two large tables.

Observed State:

Table A (593 GB, 623M rows): 124 million dead tuples (16.6% dead ratio)
Table B (465M rows): 74 million dead tuples (13.7% dead ratio)
Autovacuum workers: 2 running concurrently
CPU utilization: 85-90%
Autovacuum frequency: Single massive vacuum operation per 4-6 hours
Status of tables: Never manually vacuumed since instance creation

Root Cause: Autovacuum thresholds were configured at system defaults, which were inappropriate for high-churn tables receiving bulk updates every 2-3 hours via scheduled data loader processes.

Understanding MVCC: PostgreSQL vs. Oracle's Undo Architecture

Before tuning can be effective, the fundamental difference in how PostgreSQL and Oracle manage concurrent access must be understood.

Oracle's Approach: Automatic Undo Retention Management (AUM)

In Oracle, Multi-Version Concurrency Control (MVCC) is implemented via undo tablespace segments:

Update operation: When a row is updated, the old version is written to undo tablespace (not to the table itself).
Undo retention: Oracle's Automatic Undo Retention Management (AUM) manages undo tablespace as a circular buffer. Undo extents are recycled automatically based on the UNDO_RETENTION parameter and available tablespace.
Space reclamation: Undo space is automatically freed when either (a) the retention period expires, or (b) tablespace pressure forces rollback of older undo data. The DBA's responsibility is limited to allocating sufficient undo tablespace upfront.

Key characteristic: The DBA tunes this mechanism once (setting retention period and tablespace size) and then relies on Oracle's background processes to manage undo lifecycle automatically.

[IMAGE 2: Oracle vs PostgreSQL MVCC Architecture Diagram]

PostgreSQL's Approach: Heap-Based MVCC with Explicit VACUUM

In PostgreSQL, MVCC is implemented at the table (heap) level:

Update operation: When a row is updated, a new version of the row is inserted into the same table. The old version is marked as "dead" but remains physically in the table.
Space reclamation: VACUUM must scan the table, identify dead tuples, and mark their space as reusable. Dead tuples are not automatically removed.
Autovacuum trigger: Autovacuum is a background process that decides when to vacuum based on tunable thresholds. Unlike Oracle's automatic undo recycling, PostgreSQL requires explicit configuration of when vacuum should trigger.

Key characteristic: The DBA must actively tune VACUUM parameters based on table churn patterns. There is no "set it and forget it" mechanism comparable to Oracle's AUM.

Implication: On Oracle, a table with high UPDATE volume simply generates more undo, which Oracle's AUM handles. On PostgreSQL, the same UPDATE volume generates more dead tuples, and if autovacuum thresholds are too conservative, dead tuples accumulate until autovacuum finally triggers—often at high volume, causing CPU spikes.

The Vacuum Threshold Formula: The Mathematical Foundation

When autovacuum decides to run, it evaluates the following formula for each table:

VACUUM_TRIGGER_THRESHOLD = autovacuum_vacuum_threshold + 
                           (autovacuum_vacuum_scale_factor × n_live_tup)

Where:

autovacuum_vacuum_threshold: Absolute minimum dead tuples (default: 50)
autovacuum_vacuum_scale_factor: Percentage of table size (default: 0.1 = 10%)
n_live_tup: Current number of live tuples in the table

Pre-Investigation Configuration

System defaults on the cluster:

autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.1

Calculation for Table A (725M rows):

THRESHOLD = 50 + (0.1 × 725,000,000)
          = 50 + 72,500,000
          = 72.5 million dead tuples

Interpretation: Autovacuum would not trigger on Table A until 72.5 million dead tuples accumulated. This is the critical misconfiguration.

For comparison, Table A actually accumulated 124 million dead tuples before the vacuum completed—well beyond this threshold, indicating autovacuum had already triggered much earlier in the lifecycle but was running continuously against an accumulating workload.

The Root Cause: Loader Pattern and Threshold Mismatch

The data loader process (running every 2-3 hours with 4 parallel workers) updated rows using a COALESCE merge pattern:

UPDATE table_a t SET 
  column_1 = COALESCE(s.column_1, t.column_1),
  column_2 = COALESCE(s.column_2, t.column_2),
  column_3 = COALESCE(s.column_3, t.column_3)
FROM staging_table s
WHERE t.id = s.id

This pattern creates a dead tuple for every row touched, regardless of whether values actually changed. Over a 2-3 hour window with millions of rows, dead tuple generation rate significantly exceeded autovacuum's ability to reclaim space given the high threshold values.

The collision:

High dead tuple generation rate from bulk UPDATE operations
Autovacuum thresholds calibrated for the default use case (moderate churn on tables of typical size)
No table-level overrides to account for this specific workload pattern

Result: Dead tuples accumulated to 16.6% of table size before stabilizing.

Investigation: Diagnostic Queries and Findings

Three queries provided diagnostic clarity:

Query 1: Current dead tuple status

SELECT 
  relname, 
  n_live_tup,
  n_dead_tup,
  ROUND(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 2) AS dead_ratio_pct,
  last_autovacuum
FROM pg_stat_user_tables
WHERE relname IN ('table_a', 'table_b')
ORDER BY n_dead_tup DESC;

Result:

Table A: 623M live, 124M dead (16.6%)
Table B: 465M live, 74M dead (13.7%)

Query 2: Active autovacuum processes

SELECT 
  pid, 
  query, 
  query_start,
  EXTRACT(EPOCH FROM (NOW() - query_start))::INT AS runtime_seconds
FROM pg_stat_activity
WHERE query LIKE '%VACUUM%' 
  AND query NOT LIKE '%pg_stat%';

Result: Two autovacuum workers running concurrently, one on each table, both active for 55+ minutes and 3+ minutes respectively at time of investigation.

Query 3: Cumulative churn analysis

SELECT 
  relname,
  n_live_tup,
  n_dead_tup,
  n_tup_upd + n_tup_del AS total_modifications,
  ROUND(100.0 * (n_tup_upd + n_tup_del) / n_live_tup, 1) AS churn_ratio_pct
FROM pg_stat_user_tables
WHERE relname IN ('table_a', 'table_b');

Result:

Table A: 16 billion total modifications on 725M rows (2203% cumulative churn)
Table B: 11.9 billion total modifications on 465M rows (2566% cumulative churn)

Interpretation: These represent lifetime cumulative statistics since instance creation. The 2200%+ ratio indicates every row has been touched approximately 22 times on average over the instance lifetime.

The Configuration Adjustment: Formula-Based Approach

Rather than implementing ad-hoc changes, a formula-based approach was used to calculate appropriate thresholds.

The colleague conducting the fix referenced textbook formulas for maintenance memory allocation and threshold calculation, confirming that the system defaults were inappropriate for tables with this churn profile.

Applied changes (table-level only):

ALTER TABLE table_a SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 10000,
  autovacuum_analyze_scale_factor = 0.005,
  autovacuum_analyze_threshold = 5000,
  autovacuum_vacuum_cost_delay = 2,
  autovacuum_vacuum_cost_limit = 5000
);

ALTER TABLE table_b SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 10000,
  autovacuum_analyze_scale_factor = 0.005,
  autovacuum_analyze_threshold = 5000,
  autovacuum_vacuum_cost_delay = 2,
  autovacuum_vacuum_cost_limit = 5000
);

Rationale for each parameter:

Scale factor (0.1 → 0.01): Lowers the percentage-based trigger from 10% to 1% of table size, causing autovacuum to start at 7.25 million dead tuples instead of 72.5 million. This increases vacuum frequency but reduces per-operation work volume.
Threshold (50 → 10000): Sets an explicit minimum to prevent excessive vacuum triggering on very small tables, but still allows reasonable triggering on large tables.
Analyze scale factor (0.05 → 0.005): ANALYZE (which updates table statistics for the query planner) triggers more frequently, preventing stale statistics during high-churn periods.
Cost parameters: cost_delay = 2ms and cost_limit = 5000 distribute vacuum work into smaller increments with longer pauses, reducing per-operation CPU spike while still completing the work.

New threshold calculation for Table A:

THRESHOLD = 10,000 + (0.01 × 725,000,000)
          = 10,000 + 7,250,000
          = 7.26 million dead tuples

This represents a 10× reduction in the threshold value, causing autovacuum to trigger 10× more frequently but with proportionally smaller work loads.

Critical Design Decision: Table-Level Configuration, Not Cluster-Level

A deliberate choice was made to apply all tuning parameters at the table level only, not at the system/cluster level.

Why table-level configuration is necessary:

Heterogeneous workloads: Not all tables in a cluster have the same churn pattern. Table A and Table B are high-churn bulk-update targets. Other tables in the same cluster may be mostly static or read-heavy.
Preventing cascade effects: A cluster-wide reduction in autovacuum_vacuum_scale_factor would cause autovacuum to trigger more frequently on all tables, including those with minimal churn. This could result in:
- Unnecessary vacuum operations consuming CPU and I/O
- More frequent ANALYZE operations on stable tables
- Increased lock contention if many vacuums run concurrently
Isolation of risk: By tuning only the affected tables, the change is isolated to the problem source and does not introduce unexpected side effects on other database objects or applications.

This is a deliberate engineering discipline: tune at the smallest scope that solves the problem.

Results: Before and After Metrics

Before Configuration (April 13, 09:51 AM):

Metric	Table A	Table B
Dead tuples	124M	74M
Dead ratio	16.6%	13.7%
Last autovacuum	N/A (first vacuum)	N/A (first vacuum)
CPU utilization	85-90%	85-90%
Vacuum frequency	1 per 4-6 hours	1 per 4-6 hours

After Configuration (April 13, 12:07 PM - 13:45 PM):

Metric	Table A	Table B
Dead tuples	0 (vacuum completed)	21M (declining)
Dead ratio	0%	7.8%
Last autovacuum	2026-04-13 12:07:08	2026-04-13 13:45:44
CPU utilization	30-40%	30-40%
Vacuum frequency	Multiple per loader cycle	Multiple per loader cycle

The vacuum operations completed naturally without manual intervention. Dead tuple levels stabilized well below the new thresholds. CPU alerts cleared.

Monitoring: Proactive Visibility

To prevent recurrence, a monitoring table was implemented to capture dead tuple trends:

CREATE TABLE pg_table_stats_history (
  captured_at TIMESTAMP DEFAULT NOW(),
  table_name TEXT,
  n_live_tup BIGINT,
  n_dead_tup BIGINT,
  dead_ratio_percent NUMERIC,
  n_tup_upd BIGINT,
  n_tup_del BIGINT,
  last_autovacuum TIMESTAMP
);

SELECT cron.schedule('capture_table_stats', '*/30 * * * *', $$
  INSERT INTO pg_table_stats_history
  SELECT 
    NOW(), relname, n_live_tup, n_dead_tup,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2),
    n_tup_upd, n_tup_del, last_autovacuum
  FROM pg_stat_user_tables
  WHERE relname IN ('table_a', 'table_b');
$$);

This captures a snapshot every 30 minutes, allowing observation of daily patterns: "During loader window (12:00-14:00), dead tuples climb to 5-8M, then autovacuum brings them back to 0.5M."

Key Concepts: What the Investigation Revealed

1. The Autovacuum Cost Mechanism

VACUUM has two cost-control parameters:

autovacuum_vacuum_cost_limit: Units of work (reading a page = 1 unit, writing a page = 20 units) allowed before autovacuum pauses
autovacuum_vacuum_cost_delay: Milliseconds to pause when cost limit is reached

Lower cost_limit + higher delay = slower vacuum, less CPU spike

Higher cost_limit + lower delay = faster vacuum, more CPU spike

The pre-incident configuration had cost_delay = 5ms and cost_limit = 1800 (already aggressive). The post-incident configuration used cost_delay = 2ms and cost_limit = 5000, allocating higher work budgets but still enforcing frequent pauses for distribution.

When autovacuum triggers, it scans the entire table. If dead tuples are being created faster than they're being reclaimed (because the loader is still running), the vacuum operation takes longer and consumes sustained CPU.

In the incident:

Table A vacuum started at 09:51:54 and completed at 12:07:08 (2h 15m)
Table B vacuum started at 08:59:39 and completed at 13:45:44 (4h 46m)
During this window, the loader was also running (starting 12:37:27), creating additional dead tuples

The concurrent activity created contention for CPU and I/O resources, explaining the 85-90% CPU utilization.

3. Wraparound Protection: A Critical Background Mechanism

While not the active problem in this incident, understanding wraparound protection is essential context:

PostgreSQL uses 4-byte transaction IDs (2^32 = 4.3 billion possible values). For MVCC visibility comparison to work correctly, only a 2-billion value range is usable at any given time. If autovacuum falls so far behind that unfrozen tuples approach the 2-billion transaction boundary, PostgreSQL will:

Log warnings at 200M transactions remaining
Shift to READ-ONLY mode at 1M transactions remaining
Force shutdown if the limit is reached

This is not a theoretical concern—it's a safety mechanism that has forced database shutdowns in under-monitored systems. Autovacuum must keep up, or the database fails.

The Documentation Recovery: Why AI Alone Is Insufficient

When the incident was first encountered, AI-based diagnostic tools provided generic suggestions: "lower cost_delay," "increase cost_limit," "check maintenance_work_mem."

These suggestions were not wrong, but they were not calibrated to the specific situation. The threshold formula, the rationale for scale factor adjustment, and the risks of cluster-level configuration changes were not apparent from AI output alone.

The fix required returning to foundational documentation:

PostgreSQL VACUUM documentation (official): Explained the cost mechanism and threshold formula
AWS Aurora PostgreSQL tuning guide: Provided context-specific guidance for managed Aurora instances
Textbook references on MVCC: Clarified why dead tuples accumulate and how autovacuum prevents wraparound

The colleague's insistence on reading the documentation forced a deeper investigation that revealed:

The mathematical formula driving autovacuum trigger decisions
The specific interaction between bulk update workloads and default thresholds
The risks and benefits of table-level vs. cluster-level configuration

The lesson: Base concepts—MVCC, dead tuples, autovacuum thresholds, wraparound protection—are non-negotiable foundations. Understanding these requires reading documentation. Once the foundations are understood, AI tools can accelerate diagnosis and suggest configurations. But without foundations, suggestions are just knobs to turn without understanding consequences.

Combining both—foundational reading with AI-assisted diagnosis—yields better outcomes than either alone.

Diagnostic Queries for Future Incidents

When autovacuum CPU spikes occur, these queries provide immediate visibility:

Dead tuple status:

SELECT 
  relname, n_live_tup, n_dead_tup,
  ROUND(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 2) AS dead_pct,
  last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Active autovacuum activity:

SELECT pid, query, query_start, 
  EXTRACT(EPOCH FROM (NOW() - query_start))::INT AS runtime_sec
FROM pg_stat_activity
WHERE query LIKE '%VACUUM%';

Churn rate analysis:

SELECT 
  relname, n_live_tup, 
  n_tup_upd + n_tup_del AS total_mods,
  ROUND(100.0 * (n_tup_upd + n_tup_del) / n_live_tup, 1) AS churn_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Transaction age (wraparound risk):

SELECT 
  datname,
  age(datfrozenxid) AS txid_age,
  (SELECT setting::int FROM pg_settings 
   WHERE name = 'autovacuum_freeze_max_age') - age(datfrozenxid) 
   AS txid_remaining
FROM pg_database
WHERE datallowconn
ORDER BY txid_age DESC;

Conclusion

This incident demonstrated that PostgreSQL's VACUUM mechanism requires active tuning based on workload patterns. Unlike Oracle's Automatic Undo Retention Management—which automatically recycles undo tablespace based on retention policies—PostgreSQL requires explicit configuration of autovacuum thresholds calibrated to specific table churn patterns.

The resolution came not from tweaking random parameters, but from understanding the mathematical formulas governing autovacuum behavior and applying them methodically at table scope.

The broader lesson is methodological: when facing production database performance issues, foundational understanding of mechanisms (documented in official sources) combined with diagnostic data yields better outcomes than parameter suggestions alone.

References

PostgreSQL Official: VACUUM
PostgreSQL Official: Autovacuum
AWS Database Blog: Understanding autovacuum in Amazon RDS for PostgreSQL
Percona: Overcoming VACUUM WRAPAROUND
CYBERTEC: Autovacuum wraparound protection

From Zombie Connections to XactSync: A Post-Mortem on Aurora Postgres CPU Spikes

Pranay Ravi — Sat, 23 May 2026 20:54:26 +0000

The alert fires at 1:28 PM. By the time you open CloudWatch, CPU is already back to normal. No application errors. No user complaints. Nothing obviously broken.

Do you close the ticket and move on, or dig in?

This post is about why you should always dig in — and exactly how we traced a "self-resolving" CPU spike on Aurora PostgreSQL 16 all the way back to a mismatch between pipeline commit logic and the database storage engine.

The Environment

Engine: Aurora PostgreSQL 16.1 on db.x2g.xlarge (4 vCPUs, 128GB RAM)
Workload: Kafka stream-based pipeline inserting ~365 rows/sec average into a daily-partitioned table, with burst peaks near 825/sec
Replication: Oracle GoldenGate
Observability: AWS CloudWatch Database Insights + AppDynamics Database Agent

What the Dashboard Showed

The first stop is always CloudWatch $\rightarrow$ Database Insights $\rightarrow$ Database Load. This chart shows Average Active Sessions (AAS) broken down by wait type:

Color	Wait Type	What It Means
🟢 Green	`CPU`	Active computation
🟠 Orange	`IO:AuroraStorageLogAllocate`	WAL/storage log writes
🟣 Purple	`LWLock:BufferContent`	Buffer manager contention
🟤 Brown	`Timeout:SpinDelay`	Spinlock spin waits
🩷 Pink	`LWLock:WALInsert`	WAL insert lock contention

Our spike was pure green—CPU only, no I/O or lock contention. The Top SQL tab showed a high-volume INSERT into a partitioned event log table running at ~825 calls/sec with 0.17ms average latency. Fast and normal individually. The volume was the question.

However, the Database Telemetry tab revealed two ominous signals that often catch a developer's attention:

Max time idle in transaction: Climbing linearly for 3.2 hours straight.
Vacuum: Max used transaction IDs: On a steady linear climb.

When you see both of those linear trends on a single chart, it usually points to a common suspect in relational databases: something has been holding an open transaction for a very long time, threatening your autovacuum health.

Background: The Core Mechanics of Postgres MVCC

To understand why long-running transactions are so closely tracked, it helps to look at how PostgreSQL manages Multi-Version Concurrency Control (MVCC).

Unlike database engines that keep old record data in a separate utility tablespace (like Oracle's Undo Tablespace), PostgreSQL relies on a Heap-Based Approach.

The Postgres Heap Reality: There is no separate undo space. When a row is updated or deleted, the old version (called a "dead tuple") remains physically stored inside the exact same table page. It stays there until the background VACUUM process scans the table and marks that dead space as reusable for future inserts.

If an application leaves a transaction open, it anchors the xmin (the baseline transaction ID floor for that snapshot). VACUUM cannot safely clean up any dead tuples that were generated after the oldest active transaction's `xmin`. This can trigger a problematic domino effect:

Session opens → acquires xmin snapshot
       ↓
Session goes idle (no explicit commits or rollbacks)
       ↓
Dead tuples accumulate → VACUUM cannot clear them out
       ↓
Autovacuum loops continuously, wasting CPU scanning bloated tables
       ↓
Transient CPU spikes alert operations

Because autovacuum periodically wakes up to evaluate tables, the resulting CPU spikes look completely intermittent. The alert clears, engineers close the ticket as a glitch, and the underlying architectural friction persists.

Step 1: Find the Stuck Sessions

To hunt down the culprit, we ran a quick diagnostic query against pg_stat_activity:

SELECT
  pid,
  usename,
  application_name,
  client_addr,
  state,
  query,
  now() - state_change AS idle_duration,
  now() - xact_start   AS transaction_age
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY xact_start ASC;

The results: 35 sessions, all originating from the same connection IP, belonging to the replication user. The oldest transaction had been open for 133 days.

The queries they were idling on:

SELECT count(*) from pg_settings where name='ansi_force_foreign_key_checks';
SELECT count(*) from pg_type where typtype='e' and oid in (...);

These are classic compatibility probe queries issued during connection initialization by integration tooling like GoldenGate. The integration platform had been connecting, executing its basic discovery probes, and abandoning the sessions without explicitly closing them every time it restarted or reconnected over a four-month period.

Step 2: Verify the Vacuum Blocker (The Twist)

Finding idle in transaction sessions is a major red flag, but we had to verify if they were genuinely blocking data cleanup. Not all idle sessions hold an active database snapshot.

We executed the source-of-truth query to find actual vacuum blockers:

SELECT
  pid,
  usename,
  backend_xmin,
  age(backend_xmin) AS xmin_age_in_transactions,
  now() - xact_start AS session_age,
  state
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 10;

⚠️ This is where our initial MVCC-bloat hypothesis collapsed.

The zombie integration sessions did not appear in this list; their backend_xmin fields were entirely NULL. Because they had only executed basic, read-only catalog queries during initialization, they never acquired a persistent data snapshot transaction ID. They were not blocking vacuum. The actual oldest xmin holders were normal application sessions with transaction ages under 3,000—completely standard for a busy transaction engine.

While these zombie connections were a clear operational hazard (consuming connection slots and wasting system memory), they weren't driving our CPU alert.

Step 3: Check the Partitions

With zombie connections ruled out as vacuum blockers, we turned our focus to the physical table health. Because our target event log table is a partitioned table, statistics are tracked natively at the individual child partition level, not the parent logical table. We targeted the table prefix:

SELECT schemaname, relname, n_live_tup, n_dead_tup,
  ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio_pct,
  last_autovacuum
FROM pg_stat_user_tables
WHERE relname LIKE 'event_log%'
ORDER BY n_dead_tup DESC;

Result: 0 dead tuples across all active partitions.

The partitioned strategy was working flawlessly. The application used an insert-only pattern on the active daily partition, and old partitions were dropped entirely rather than vacuumed. This meant VACUUM bloat was completely ruled out.

Step 4: Finding the Real Culprit via Wait States

With MVCC bloat off the table, we pivoted to the APM wait state drill-down for the intensive INSERT query during the exact minutes of the CPU spike:

ClientRead (54%): The database engine spent more than half its time completely idle, waiting for the application client to send the next query over the network wire.
Active (24.7%): Time spent physically executing the raw insert statements.
XactSync (17.9%): The engine waiting for the Write-Ahead Log (WAL) to be flushed and synchronized to durable storage upon a COMMIT.
AuroraStorageLogAllocate (2.4%): Storage block layer allocation.

Two metrics tell the final story here:

XactSync at 17.9%: This wait state occurs when an explicit COMMIT forces PostgreSQL to flush WAL records. At a peak volume of 825 inserts per second, the application was issuing 825 individual commits per second. This meant 825 distinct sync operations forcing the storage layer to acknowledge writes sequentially.
ClientRead at 54%: This is the classic signature of single-row, one-at-a-time application database calls. The application loop was executing: Send row $\rightarrow$ Execute $\rightarrow$ Commit $\rightarrow$ Wait for network Ack $\rightarrow$ Send next row.

The shape of the CPU spike closely mirrored a backlog flush pattern. Messages would pile up briefly in the upstream Kafka topic, and then the consumer would drain them all at once in a massive burst of single-row, high-frequency synchronous commits.

Root Cause Summary

Hypothesis	Verdict	Technical Proof
Dead Tuple Accumulation	❌ Ruled Out	`pg_stat_user_tables` showed a 0% dead tuple ratio across all database partitions.
Zombie Connections Blocking Vacuum	❌ Ruled Out	`backend_xmin` was explicitly `NULL` for all zombie replication sessions.
Burst Insert Flush + Per-Row Commits	Root Cause	High `XactSync` (17.9%) paired with a massive `ClientRead` (54%) network wait pattern.

The Action Plan

Fix 1: Terminate and Block the Zombie Connections

Even though they didn't block vacuum in this specific instance, leaving 35 abandoned sessions active for 133 days is risky. They leak connection resources and backend memory.

First, we safely terminated the dangling backends:

SELECT pg_terminate_backend(pid), pid, now() - xact_start AS xact_age
FROM pg_stat_activity
WHERE usename = 'repl_agent' 
  AND state = 'idle in transaction';

Next, we implemented an automated defense line. We updated the cluster configuration parameters to enforce a strict timeout globally:

idle_in_transaction_session_timeout = 600000 # 10 minutes in milliseconds

If any session sits idle inside an uncommitted transaction block for more than 10 minutes, Postgres will now automatically drop the connection and free up system resources.

Fix 2: Implement Micro-Batching in the Consumer Pipeline

The true fix for the CPU spike required changing how the application interacted with the storage engine. Instead of committing every single row individually, we modified the JDBC producer configuration to use a batch size of 500.

-- Old Pattern (825 WAL Syncs/sec):
INSERT row 1 → COMMIT → WAL fsync → Wait
INSERT row 2 → COMMIT → WAL fsync → Wait

-- New Pattern (1.7 WAL Syncs/sec):
INSERT row 1, INSERT row 2 ... INSERT row 500 → COMMIT → 1 WAL fsync

Metric	Before Batching	After Batching (Est.)
Commits/sec (Burst)	~825	~1.7
WAL Fsyncs/sec	~825	~1.7
`XactSync` Wait Time	17.9%	< 1.0%
Transient CPU Spikes	Frequent	Eliminated

Essential Checklist Queries for Triage

Add these queries to your incident response runbook for analyzing database load:

1. Identify Long-Running Snapshot Blockers

SELECT pid, usename, backend_xmin,
  age(backend_xmin) AS xmin_age_transactions,
  now() - xact_start AS session_age
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC 
LIMIT 5;

2. Spot Dangerous `Idle in Transaction` Sessions

SELECT pid, usename, client_addr,
  now() - xact_start AS transaction_age,
  now() - state_change AS idle_duration,
  LEFT(query, 80) AS last_query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - xact_start > interval '5 minutes'
ORDER BY xact_start ASC;

3. Check Table Churn & Dead Tuple Ratios (Including Partitions)

SELECT schemaname, relname, n_live_tup, n_dead_tup,
  ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio_pct,
  last_autovacuum
FROM pg_stat_user_tables
WHERE relname LIKE 'your_table_prefix%'  -- Handles partitioned child tables
ORDER BY n_dead_tup DESC;

💡 Tip: A dead_ratio_pct > 10% indicates a strong need for space recovery; > 20% means the table is critically bloated and query execution performance will likely degrade.

What's Next?

If you found this breakdown helpful, keep an eye out for our next post. We will be diving into the opposite side of database optimization: When Autovacuum goes wrong. We will walk through a separate production incident where a high-churn table choked on over 124 million dead tuples (a brutal 16.6% dead ratio) due to misconfigured autovacuum cost thresholds. Stay tuned!

What It Actually Takes to Audit Aurora PostgreSQL on AWS

Pranay Ravi — Thu, 21 May 2026 23:28:33 +0000

Most operational infrastructure starts this way: a requirement appears before the architecture does.

One day the team needed a database audit solution. Not in a planning doc — someone asked, and I had to build something. I'd owned the Oracle audit pipeline already, so I knew what the destination looked like. The question was what it would take to get there on Aurora PostgreSQL and AWS.

The short answer: more than you'd expect. The longer answer is this article.

What We're Actually Auditing — and Why It Matters

The scope here is specific: individual human users making direct DML changes (SELECT, INSERT, UPDATE, DELETE) to application tables.

Application service accounts are expected to modify data — that's their function. The risk surface is direct human access. A developer connected via psql. A support engineer running an ad-hoc update. A credential that shouldn't have had access to begin with. Those are the actions that need an audit trail in a regulated environment.

This distinction shapes every architectural decision: we enable pgAudit per individual user account, not cluster-wide. Application accounts are excluded entirely.

The Oracle Baseline

Before getting into the build, it's worth framing the comparison.

On Oracle, Unified Auditing (an Enterprise-tier feature) handles this with a single policy definition at the intersection of user, action, and object:

CREATE AUDIT POLICY user_dml_activity
  ACTIONS SELECT, INSERT, UPDATE, DELETE ON app_schema.orders
  WHEN 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') != ''APP_SVC_ACCOUNT'''
  EVALUATE PER SESSION;

AUDIT POLICY user_dml_activity;

Records land in UNIFIED_AUDIT_TRAIL — a structured, SQL-queryable audit view. From there, the data can be exported on a schedule to any downstream analytics platform such as Elasticsearch, Splunk, OpenSearch, or a dedicated audit store. Scheduled queries or alerting rules can then detect patterns like bulk deletes, after-hours access, or unexpected DDL changes and trigger notifications through PagerDuty, Opsgenie, or an observability platform via HTTP webhook. Once the audit policy is defined, the downstream detection and alerting pipeline is relatively small because the audit data is already structured and queryable.

One important operational caveat with Oracle's approach: if the audit tablespace fills up, Oracle halts the database rather than silently dropping audit records — it guarantees completeness at the cost of availability. Tablespace monitoring becomes a hard operational requirement. pgAudit writing to the log stream instead of a table sidesteps this entirely: if log delivery has issues, the database keeps running.

pgAudit is different. There's no policy-based object/action targeting. You set a log class (read, write, ddl, all) per user. Records go to the PostgreSQL log stream — on Aurora, that means CloudWatch Logs. There's no queryable view. You're working with text:

AUDIT: SESSION,1,1,WRITE,INSERT,TABLE,public.orders,
"INSERT INTO orders (customer_id, amount) VALUES ($1, $2)"

It works. But extracting structured, alertable signal from a log stream requires deliberate engineering.

The Architecture

Aurora PostgreSQL  (pgAudit per-user)
        │
        ▼
CloudWatch Logs
        │
EventBridge  rate(5 min)
        │
        ▼
Lambda  → runs Log Insights query → filters noise → publishes count metric
        │
        ▼
CloudWatch Alarm  (count > 0)
        │
        ▼
SNS → incident platform + email

The Lambda exists because CloudWatch Alarms cannot evaluate Log Insights query results directly — there's no native bridge. Lambda runs the query, counts filtered results, and publishes a standard CloudWatch metric. The alarm evaluates that metric. Lambda invocation logs also give you an independent timestamped record of every detection event — useful when compliance asks "when was this first noticed?"

Full code, IAM policies, SNS configurations, and Terraform module: github.com/pcraavi/PostgreSQL-Audit

Gotcha 1: The Silent pgAudit Setup Failure

Enable pgaudit in shared_preload_libraries, reboot the cluster. Simple.

Except — shared_preload_libraries only loads the binary into shared memory. You also need:

CREATE EXTENSION pgaudit;

Without it: zero audit records. Zero error messages. Nothing in CloudWatch. The cluster had been rebooted, the parameter was confirmed, logs were being exported — and there was nothing to show for it.

The diagnostic:

SELECT * FROM pg_extension WHERE extname = 'pgaudit';
-- 0 rows returned

That was the entire problem. One missing statement.

Then enable per-user logging:

ALTER USER john_doe SET pgaudit.log TO 'all';

-- Verify
SELECT usename, useconfig FROM pg_user WHERE usename = 'john_doe';

Gotcha 2: The Noise Problem

The first time you look at raw audit logs from a production cluster, the signal-to-noise ratio is genuinely bad:

AUDIT: SESSION,...,READ,SELECT,,,"SELECT version()"
AUDIT: SESSION,...,READ,SELECT,,,"SELECT * FROM pg_shdescription..."
AUDIT: SESSION,...,READ,SELECT,,,"SET application_name='DBeaver 23.2.0'"
AUDIT: SESSION,...,READ,SELECT,,,"SELECT oid, typarray FROM pg_type WHERE typname=$1"

Every database tool (DBeaver, DataGrip, pgAdmin) fires a catalog query sequence on connect. Every JDBC driver runs initialization SELECTs. Every connection pool runs health checks. Alert on this raw stream and you're alerting on noise constantly — which means you stop paying attention, which defeats the audit entirely.

The filter query was built incrementally by watching actual production traffic:

fields @timestamp, @message, @logStream, @log
| filter @message like /AUDIT:/
| filter (
    @message like /SELECT/ or @message like /INSERT/ or
    @message like /UPDATE/ or @message like /DELETE/
  )
| filter @message not like /SELECT version()/
| filter @message not like /pg_shdescription/
| filter @message not like /pg_catalog/
| filter @message not like /information_schema/
| filter @message not like /SET application_name/
| filter @message not like /DBeaver/
| filter @message not like /PostgreSQL JDBC Driver/
| filter @message not like /datname = \$1/
| sort @timestamp desc

Your exclusion list will grow. Every application stack generates its own connection init patterns.

Gotcha 3: The KMS + CloudWatch Alarms Incompatibility

Encrypt the SNS topic at rest. Straightforward — use alias/aws/sns, the AWS-managed SNS key. Done.

Except the alarm stopped delivering.

CloudWatch Alarms does not have authorization to access the SNS topic encryption key

AWS-managed keys have immutable key policies. You cannot grant CloudWatch kms:GenerateDataKey. The fix is a customer-managed KMS key with an explicit CloudWatch service grant in the key policy. The managed key works fine if Lambda is publishing directly to SNS — the limitation is specific to the CloudWatch Alarms → SNS delivery path.

Key policy and full SNS security configuration (HTTPS enforcement, cross-account lockdown): github.com/pcraavi/PostgreSQL-Audit/tree/main/sns

Gotcha 4: The Incident Platform Deduplication Problem

First alert: fired correctly.
Second alert, third, fourth: nothing.

The incident platform logs showed requests arriving and the "Create Alert" action starting — but no alert created. The issue: platforms like Opsgenie, PagerDuty, and VictorOps deduplicate by alarm name (used as the alert alias). If that alert is already open or acknowledged, subsequent triggers are suppressed.

Correct behavior for most alarms. For an audit alert that should fire every detection window, it needs handling.

Fix options:

Timestamp the alarm name at deploy time ($(date +%s) suffix) — unique alias per deployment
Configure auto-close when the alarm returns to OK — fresh alert on each ALARM transition
Override the alias template in the integration to include a timestamp from the alert payload

Why Not Database Activity Streams?

Experienced AWS engineers will immediately ask this. DAS provides near-real-time activity streaming to Kinesis with structured output. It's a real solution.

The reason we didn't use it: it introduces Kinesis as a required dependency, adds per-event cost, and requires a consumer layer for decryption and processing. For teams without an existing Kinesis pipeline, that's meaningful operational surface area.

pgAudit + CloudWatch uses infrastructure Aurora already depends on. Lambda and SNS are general-purpose services. There's no proprietary tooling, no specialized operational knowledge required. Any engineer with standard AWS experience can maintain it. That portability and low learning curve matters when you're deploying across multiple accounts.

GuardDuty RDS Protection is complementary — it handles threat detection, not structured audit trails. OpenSearch subscription filters would work but introduce an OpenSearch cluster as a dependency, which adds cost and operational overhead.

A Note on pgAudit Overhead

Overhead is minimal at this audit scope. Individual human users in a regulated production environment are not expected to generate high transaction volumes — the audit target is ad-hoc access, not application traffic. The per-session overhead of writing to the PostgreSQL log is negligible when audit scope is limited to non-application users.

For high-volume clusters where audit is a compliance requirement: account for log overhead in instance sizing. Set CloudWatch log retention to match your compliance window rather than keeping logs indefinitely — use aws logs put-retention-policy to enforce it.

What I'd Do Differently

Ship filtered audit records to a structured store. CloudWatch Log Insights is a query tool, not a data store. Extending the Lambda to write each filtered record to DynamoDB or an RDS audit table gives you the closest equivalent to Oracle's UNIFIED_AUDIT_TRAIL: indexed, queryable, long-term audit history. Kinesis Firehose → S3 → Athena is another path for columnar query performance over large windows. The Lambda architecture makes this extension natural — the query and filtering logic is already there.

Terraform from day one. The full stack — Lambda, EventBridge rule, SNS topic, IAM role, CloudWatch alarm, KMS key — fits in a single reusable module. Deploying to additional accounts should be terraform apply with environment variables, not a re-run of CLI commands.

Tag every resource. Environment, ClusterName, Owner. Untagged audit infrastructure across multiple accounts becomes unauditable infrastructure.

Final Thought

Each component here has a clean interface and behaves predictably in isolation. The engineering is in the integration contracts: Log Insights results don't flow to alarms without mediation; AWS-managed KMS keys don't work with CloudWatch delivery; incident platform deduplication suppresses repeated alarm transitions. None of these are documented prominently — they surface when components are wired together under production conditions.

Start with the Log Insights filter query. Validate the signal before building anything around it. The filtering logic is the foundation. The rest is plumbing.

Full implementation: Lambda code, IAM policies, SNS topic policy, KMS key policy, CloudWatch alarm, Terraform module → github.com/pcraavi/PostgreSQL-Audit

Drop a comment if you've hit different noise patterns in your environment, or if you've implemented the Lambda → structured audit table extension — curious how others have approached long-term audit retention on Aurora.

Automated 25 Minutes of My Morning With a Prompt (Not a Script)

Pranay Ravi — Thu, 21 May 2026 03:07:21 +0000

Every serious engineering org I've worked in has the same split personality.

One side: A modern observability stack. AppDynamics, Datadog, whatever the current favorite is. Real-time metrics, distributed traces, beautiful dashboards, alert routing. Years of investment. It works.

The other side: A long tail of legacy monitoring tools that predate the current stack by a decade. Tools that don't speak OpenTelemetry. Tools that can't push to a webhook. Tools whose output format is, and will remain, a 73-row HTML table emailed at 11:15 UTC every morning.

These two sides don't talk to each other. And in most orgs, someone fills that gap manually for 25–30 minutes every morning.

That someone was me.

The Gap Nobody Draws on the Architecture Diagram

The box that should exist on every enterprise architecture diagram but never does:

"Dave reads his email for 25 minutes"

This is what I call an observability seam — the boundary between your modern monitoring stack and legacy outputs it can't ingest. These seams exist because:

Legacy tools were built before modern observability conventions
Integration work is permanently lower priority than feature work
The cost is diffuse (distributed across many humans' mornings) rather than concentrated, so it never hits "fix it" priority

The insight I kept coming back to: this isn't a technology problem. It's an architectural surface area nobody drew. Once you see it that way, the solution becomes tractable.

What the Legacy Tail Actually Looked Like

My specific environment had four data sources, none of which fed the observability stack:

Source	Format	Frequency
Replication latency alerts	Email (body text)	Multiple times daily
DB backup status report	Email (73-row HTML table)	Daily at 11:15 UTC
Infrastructure/AWS notifications	Email (subject-line pattern)	20–50 per day
Deployment Review	Confluence page backed by Jira macros	Checked manually

None of them have APIs. None of them feed my SIEM or APM. The only integration surface available was: it arrives as email, or it lives at a known URL.

That constraint is also the opportunity.

The Architecture: Agent as Integration Layer

The core decision was to treat an LLM agent not as a productivity tool, but as a seam-closing integration layer — something that sits between heterogeneous, unstructured legacy outputs and a human who needs a consistent, actionable daily summary.

The agent does four things each morning:

1. Replication Latency — Signal extraction from noise

Find the latest alert, read the body, and report whether the body is actually populated.

This sounds trivial. It isn't. Replication alerts sometimes fire with empty bodies — the email arrived, but the content didn't. Manually, this is easy to miss because you see the email and assume it's fine. The agent makes "body is empty" an explicit flagged state. That's catching a monitoring failure, not just monitoring an alert.

2. Backup Report — Structured extraction from semi-structured HTML

Parse the 73-row HTML table, tally the BACKUP_STATUS column, surface only the rows that didn't complete.

Output: "69 completed, 0 failed, 3 with no backup — db01, db02, db05."

Reading time: three seconds. A human eyeballing 73 rows is slower and occasionally misses things.

3. Infrastructure Notifications — Classification at scale

Twenty to fifty emails daily, each following the pattern STATUS - Alert (Server Name : X and DB Name : Y).

The agent classifies every subject line, counts by status, and surfaces only the non-SUCCESS entries with a direct link to each email.

Output: "21 SUCCESS, 1 WARNING on SERVER04U / DB09."

That one line is the entire actionable output of fifty emails.

4. Deployment Review — Live data from a known URL

Read the Confluence page, identify the Jira macro structure, query the underlying filters directly, and report live issue counts. Bridges the gap between "someone updated a Confluence page" and "here's the actual current state of production deployments."

Why an LLM Instead of a Python Script?

A reasonable engineer will ask: couldn't this be IMAP + regex + cron?

Yes — and in many cases that's the right answer. Large enterprises already run Airflow, Power Automate, Splunk SOAR, Logic Apps. This isn't automation entering a vacuum.

The honest comparison:

Approach	Strength	Breaks when…
Python + regex + cron	Deterministic, auditable, fast	Email format changes, new alert pattern appears
ETL / SOAR pipeline	Scalable, governed, integrated	Requires schema agreement upstream
LLM agent	Tolerates format variance, low setup cost	Output is nondeterministic, harder to audit

What the LLM reduces is integration friction — not the need for integration.

The backup report column order can shift. The AWS notification subject-line pattern can vary. A new data source can be added in a sentence of English rather than a week of schema work.

That flexibility has a cost: you trade determinism for adaptability. For a morning triage digest where a missed edge case surfaces in the next run, that tradeoff is acceptable. For a system that triggers automated remediation, it isn't.

Right mental model: LLM agents lower the cost of closing observability seams. They don't replace deterministic pipelines where correctness guarantees matter.

Design Principles Worth Keeping

Independent section isolation

Each source is handled independently. A connector outage in one section doesn't abort the others — the agent renders an "unavailable" note and continues. A partial report is vastly more valuable than a silent failure.

Absence as a signal

Each section includes a sanity check that fires if no emails are found for that source.

"No replication alerts received today" is bolded as a warning, not silently skipped.

This paid off in month two, when the backup report email silently stopped arriving after a mail routing change. The sanity check flagged it immediately. Manual triage would have assumed a clean run.

Delta orientation

The report answers "what changed or failed since yesterday", not "what is the current state of everything."

Confirming sameness has no information value but costs the same attention as confirming change. A delta-oriented report routes attention only where it's needed.

Runtime date resolution

The prompt never hardcodes a date. "Today" is resolved against the local clock at execution time. Small thing. Prevents a class of subtle bugs where a stale prompt runs with yesterday's scope.

The Prompt (Sanitized)

The prompt itself is the deliverable worth preserving:

You are producing the daily 9 AM operations report. Run silently — this prompt is self-contained.

REQUIRED CONNECTORS
- Microsoft 365 / Outlook (email search + read_resource on mail:// URIs)
- Atlassian (getConfluencePage; optionally searchJiraIssuesUsingJql)

If any connector is missing or errors out, render that section with a clear
"⚠ connector unavailable" note and continue. Never abort the whole report.

SCOPE OF "TODAY"
"Today" = the calendar day the task fires, in local time.
Always pass afterDateTime: "today" to outlook_email_search.

====
SECTION 1 — Replication Latency Monitoring (latest alert today)
====
1. Search for latest alert today.
2. read_resource on its URI for the full body.
3. Report: count of alerts today, receivedDateTime, sender, and either the
   body text or "Body is empty" if it's only whitespace.

====
SECTION 2 — DB Backup Report (failure check)
====
1. Find today's report email.
2. Parse rows; tally BACKUP_STATUS column.
3. State explicitly: "X completed, Y failed, Z with no backup."
   **Bold** the failure count if > 0.

====
SECTION 3 — Infra notifications today
====
1. Fetch alerts from sender, limit 50.
2. Parse subject pattern: "STATUS - Alert (Server : X and DB : Y)"
3. Summarize counts by status. **Bold** anything not SUCCESS.
4. List non-SUCCESS items with a webLink to each.

====
SECTION 4 — Deployment Review (Confluence)
====
1. getConfluencePage, contentFormat="html".
2. If JIRA tool available, run each filter and include live issue counts.

====
OUTPUT FORMAT
====
- Plain markdown. One H1 with today's date. Four H2 sections in order.
- **Bold** anything requiring attention.
- End with "Sources:" — no duplicate links.
- Tone: factual. No filler. No apology lines.

====
SANITY CHECKS
====
- Section 1 zero results: **"No replication alerts today — monitoring may be down."**
- Section 2 zero results: **"Backup report email not received yet."**
- Section 3 zero results: **"No infra notifications today — verify alerting pipeline."**
- Section 4 failure: include the direct Confluence URL for manual access.

Three things worth noting:

Section independence is explicit. Each section is instructed to fail gracefully and continue. Fault isolation encoded directly in the prompt.

Sanity checks are the highest-value lines. Flagging "no email received today" catches the failure mode where the upstream monitoring system has broken silently — the exact failure mode that checklist-style human review tends to miss.

The prompt is a versioned artifact. As the environment changes — new email formats, new Confluence pages, connector upgrades — the prompt evolves. Treat it as a living document.

Honest Tradeoffs

This wouldn't be a useful post without naming the downsides.

LLM output is nondeterministic. Identical inputs can produce slightly different summaries across runs. I spot-check the raw email count against the agent's tally once a week.

Token truncation is real. A 73-row HTML table pushed through a context window can get silently trimmed. Defensive prompting mitigates but doesn't eliminate this.

The desktop-up dependency. In my setup, scheduled tasks fire when the desktop app is open. A laptop asleep at 9 AM means the report runs when you open it. Acceptable for a daily digest; not for time-critical monitoring.

Trust takes time to calibrate. The first week I ran this, I verified everything manually anyway. By week four, I'd stopped double-checking, and I had enough incident data to know the report was reliable. That calibration period is part of the deployment.

The Pattern Generalizes

Once you see the shape — agent reads heterogeneous sources at a known cadence, filters to deltas, surfaces only what requires human attention — it appears everywhere:

On-call handoff summaries (PagerDuty + Slack + Jira + APM, joined and summarized)
Sprint health reports (blocked tickets, aging tickets, no-owner tickets — weekly, automated)
Security alert triage (SOC mailers often emit 200 events to find 3 that matter)
Compliance evidence collection (SOC 2 renewals are largely a gathering problem, not an analysis problem)

The domain changes. The architecture doesn't.

The Mental Model Shift

The efficiency gain is real — roughly 25 minutes a day × 250 working days ≈ 100 hours annually. But that's not the most interesting outcome.

From push to pull. Most monitoring tools send you everything and you decide what matters. The cost of sending is zero for the system and enormous for the human. An agent flips that: I'll ask each morning for the delta.

From dashboards to digests. A dashboard is only valuable if someone looks at it. A daily digest that synthesizes five sources and filters to a single page is more operationally useful than five perfect dashboards, because it solves the actual constraint — attention — rather than the assumed one — visualization.

From checklists to deltas. The morning health check used to be: open these five places, confirm each is green. That's cognitive load spent confirming sameness. The right version is only tell me what changed.

What This Is, and Isn't

This is not an argument for replacing your observability infrastructure with LLM prompts. Your APM is doing things this agent will never do: real-time anomaly detection, distributed trace correlation, infrastructure topology mapping. The agent doesn't compete with that.

What the agent does is address the seam — the gap between the modern observability stack and the legacy outputs it can't ingest.

Making the seam visible is the first step. Closing it is the second. The agent is the bridge.

If your morning starts with "let me just check a few things in my email" — you have an observability seam. The tooling to close it is available now, and the implementation cost is a well-written prompt.

What observability seams do you have in your environment? Curious what data sources people are working around. Drop a comment.

Can AI Agents Replace Enterprise Workflow Orchestration? A Real-World Test — OpenClaw. n8n. Claude Dispatch. A side-by-side comparison..

Pranay Ravi — Sun, 17 May 2026 21:58:49 +0000

"A database administrator's honest investigation into whether the new wave of AI automation tools can handle enterprise-grade workflows — or whether the boring answer is still the right one."
tags: n8n, automation, database, devops

A database administrator's honest investigation into whether the new wave of AI automation tools can handle enterprise-grade workflows — or whether the boring answer is still the right one.

The workflow that started the question — and ended up answering it

Everyone was talking about these tools. I got curious.

I work as a database administrator. I support hundreds of databases across multiple environments — dev, staging, non-prod, production — in a HIPAA-regulated organization. Access management is a constant, grinding operational burden. Developers need access. Analysts need access. Application owners need access. And every single request needs to be approved, documented, and auditable.

For a long time, the process looked like this: someone would message the DBA, the DBA would manually create a Jira ticket and a Word document, chase down approvals from a manager, a database manager, and the security team, manually create the account, and email the credentials back. Days could pass. Follow-ups stacked up. The security team had to trust that the paperwork was right.

Then I started hearing about Claude Dispatch. And OpenClaw. Both were being described as AI tools that could receive a message and take action — automate tasks, call APIs, connect to services. The demos looked impressive. The communities were excited.

And I thought: wait. Could one of these actually solve the problem I have been living with for years?

I had already built something with n8n — a workflow automation tool. But I genuinely wanted to know whether I had picked the right tool, or whether something newer and smarter had passed it by. So I did what any engineer would do: I used my actual problem as the test.

The problem I needed to solve

Before comparing any tools, let me describe the workflow precisely, because the details are what make the comparison meaningful.

A developer — or a data analyst, or a product manager — needs access to a specific database. The request needs to:

Go to their direct manager for approval
Then to the database manager for approval
Then to the security team for final approval
Create a full audit trail at every step
Call a pre-existing API to provision the account with the correct access level
Deliver the credentials back to the requester
Spin up a Jira ticket for the network team to open the firewall port

Every approval is sequential — no step fires unless the previous one passes. If anyone says no, the chain stops and the requester is notified. If security does not approve, no account gets created. Full stop.

This is not a hypothetical. It runs in a regulated environment where access to production data is governed by HIPAA. The audit trail is not nice-to-have. It is required.

The access levels themselves are structured — not free text. Read only. Read/write. Dev owner. Application owner. DBA. Each maps to a specific API endpoint. Each produces a deterministic result. The central database inventory that drives all of this is a relational database populated automatically when infrastructure is created through Terraform.

Nobody should be able to bypass it.

Figure 1: The complete approval chain — from Webex message to provisioned credentials and dual Jira audit trail

First candidate: Claude Dispatch

Claude Dispatch is Anthropic's answer to the question: what if you could delegate tasks to your AI from your phone and come back to find them done? It lives inside Claude Cowork — a desktop agent product — and creates a persistent connection between your mobile app and the Claude Desktop app running on your computer.

The pitch is genuinely compelling. Send a message from your phone, Claude acts on your desktop: reads files, calls APIs, summarizes documents, delivers results. For personal productivity this is interesting. For ad-hoc delegation it is actually useful.

So I asked the obvious question: could Dispatch receive a Webex message, run an approval chain, call my database API, and write to Jira?

Here is where the investigation got honest quickly.

Dispatch requires the Claude Desktop app to be running on your computer. The moment the laptop sleeps, it stops.
There is no server. There is no always-on process. There is no execution log.
The workflow is driven by an LLM reasoning about what to do — not a deterministic set of rules. The same input could produce a different output on a different day.
There is no concept of a sequential approval gate. Claude does not wait for a human to respond before deciding the next step.
There is no audit trail. No timestamps. No record of who approved what and when.

A workflow that depends on a laptop staying awake is not an enterprise workflow. It is a personal convenience. There is nothing wrong with that — but it is a different category of tool entirely.

Cost-wise, Dispatch is bundled with Claude Pro at $20 per month or Max at $100 per month. Accessible pricing. But the architecture disqualifies it for this use case before the price even matters.

Second candidate: OpenClaw

OpenClaw is a different kind of tool. It is open-source, self-hostable, and designed as a personal AI assistant that runs on your own infrastructure. You can connect it to Webex, Telegram, Slack, WhatsApp — it listens on those channels and uses an LLM to decide what action to take in response to a message.

The self-hosted angle immediately made it more interesting for regulated environments. If you run it on a VPS rather than your laptop, it can operate 24/7. And because it is open-source under an MIT license, the software itself costs nothing. Your real costs are the VPS — roughly $5 to $15 per month — and the API tokens from whichever LLM provider you connect.

So OpenClaw gets past the first disqualification that knocked out Dispatch. It can stay on. Good start.

But then I pushed further:

OpenClaw has no concept of a deterministic approval chain. It reasons about what to do. If I ask it to get manager approval before proceeding, it will try — but there is no guarantee it handles every edge case the same way every time.
There is no built-in error handling or retry logic. If an API call fails, the agent may or may not handle it gracefully.
There are no execution logs in any structured, auditable format. The LLM's reasoning is not a HIPAA audit trail.
There is no native Jira integration. You can make API calls, but you are building that logic yourself in an environment with no visual workflow editor.
Setup requires real DevOps experience — Docker, VPS configuration, model routing. Not a weekend project for someone who just needs automation.

OpenClaw is genuinely impressive for what it is: a powerful, flexible personal AI assistant for technical users who want to automate their own workflows. It is not what I needed.

The core tension is this: OpenClaw lets an AI decide what to do. In a regulated environment, I need a system that does exactly what it is configured to do — every single time.

The one I already had: n8n

n8n is not the newest tool in this comparison. It is not the most talked-about. It does not have a viral GitHub repository or a growing community of people sharing AI agent demos. It is a workflow automation platform — visual, node-based, deterministic.

I had already built the database access workflow in n8n before I started this investigation. What the investigation forced me to do was articulate why it works where the others do not.

n8n runs on a server. Always on. No desktop dependency.
Every workflow execution is logged — step by step, with timestamps. If something fails, you know exactly where and why.
The approval chain is explicit: manager node fires, waits for a webhook response, branches on yes or no. Database manager node fires. Security node fires. No LLM is deciding the order. The order is the configuration.
Jira, Webex, and REST API integrations are native — no custom code required to connect them.
When security approves, a Jira ticket is created automatically with every approval documented: who approved, their role, the timestamp. A second ticket fires for the network team. The requester receives credentials via Webex.

The entire chain — from chat message to provisioned account — is deterministic, auditable, and server-side. It does not matter whether my laptop is on. It does not matter whether the LLM is having a creative day. The workflow does what it is built to do.

Putting them side by side

Here is what the investigation produced — not as opinion, but as a structured comparison against the actual requirements of the problem.

![Figure 2: Tool comparison — enterprise workflow criteria]

Figure 2: How the three tools compare across the criteria that actually matter for enterprise workflows

The green cells are not a coincidence. n8n was built for exactly this category of problem — structured, multi-step, multi-system, always-on automation with governance requirements. Dispatch and OpenClaw were built for a different problem: intelligent, flexible, personal task delegation.

Neither of those things is wrong. They are just different categories.

But is n8n actually accessible? Or is it just the enterprise answer no one wants to hear?

This is the question I kept coming back to. Because n8n has a reputation for requiring technical knowledge. Webhooks, JSON payloads, API authentication, conditional branching. It is not a no-code tool in the purest sense.

And yet — compared to OpenClaw, which requires DevOps expertise to host and configure, and compared to Dispatch, which requires you to trust an LLM to handle regulated processes — n8n is actually the most accessible path to a production-grade solution.

The visual builder is genuinely good. The template library covers most common patterns. The community is large. And the self-hosted Community Edition is free — you pay only for the server, which can be as little as $4 a month.

What n8n asks for is clarity of thought. You need to understand your process before you can automate it. That is not a technical barrier. That is just good engineering.

In 2008, I had my first exposure to BPM tools — business process management software. Back then, automating a multi-step approval workflow required consultants, enterprise licenses, and six-month implementation projects. n8n in 2026 is that same capability, accessible to a single engineer on a modest budget, in a weekend.

The AI-native tools will get there. The direction is right. But for workflows where consistency and auditability are non-negotiable, deterministic automation still wins. Not because AI is not impressive — it is. But because a HIPAA auditor does not accept "the AI usually does it right" as an answer.

What the real world added that no tool comparison captures

There is one thing I did not discover until the workflow was running: some users have managers on paper who are not actually the owners or decision-makers for the systems being requested.

Organizational charts say one thing. Actual accountability sits somewhere else. When the approval request went to the wrong person, the workflow stalled — not because the automation failed, but because the data it depended on was wrong.

No tool comparison surfaces this. You only find it when you run the thing on real people in a real organization.

This is one of the most useful things a well-designed workflow can do: make your organizational data gaps visible. The automation did not hide the problem. It exposed it. And that forced the fix.

So — can the new AI tools replace n8n for this?

No. Not yet. Not for this class of problem.

Claude Dispatch is a remote control for your desktop. It is well-designed and genuinely useful for personal delegation. But it has no server, no audit trail, and no deterministic logic — three things that are non-negotiable in a regulated environment.

OpenClaw is a powerful personal AI assistant that technical users can self-host and extend. It can call APIs and respond to messages. But it has no structured approval chain, no execution logging, and no enterprise governance features.

n8n is not the flashiest answer. It did not go viral. It does not use an LLM to decide what to do next. But it runs reliably, it logs everything, it integrates natively with Jira and Webex, and it does exactly what you configure it to do — every single time.

The right tool is not the newest tool. It is the tool that matches the shape of your problem. And some problems have a shape that requires determinism, not intelligence.

The question I started with — can these AI tools solve what n8n solves — turned out to be the wrong question. The better question is: what kind of automation are you building? If the answer involves regulated data, sequential human approvals, and a legal requirement to prove who did what and when, the answer is still n8n. If the answer involves personal productivity, intelligent delegation, and flexible task handling, Dispatch and OpenClaw are worth a serious look.

Both of those things can be true at the same time.

Principal Engineer | 16+ years in databases, cloud & observability | Oracle · PostgreSQL · Kafka · AWS · Splunk · AppD | Platform engineering & AI delivery

How I Built a Zero-Subscription Local AI Stack — Inspired by a 60-Second YouTube Short

Pranay Ravi — Sun, 17 May 2026 02:33:21 +0000

How I Built a Completely Free Local AI Stack — Inspired by a 60-Second YouTube Short

By Pranaychandra Ravi

It started with a YouTube Short. Someone on my feed casually demonstrated connecting a local AI model to Claude Code and I stopped mid-scroll. No API key. No subscription. No code leaving their machine. I had to know how it worked.

What followed was a deep dive into local AI — Ollama, Gemma4, Docker, Open WebUI, vector databases, context windows, and a Python script that made my local model generate an ASCII diagram of the Earth and Moon. This post documents everything I learned, every question I asked, and every mistake I made along the way. If you're curious about running AI entirely on your own hardware, this one is for you.

First Question: Wait, Is This Actually Free?

My first instinct was skepticism. Claude Code is Anthropic's product. Surely using it requires a Claude subscription?

The short answer is no — not when you pair it with Ollama and a local model.

Here's what I learned: Claude Code is the agent — the tool that reads your files, runs commands, edits code, and manages multi-step tasks in your terminal. By default it calls Anthropic's API, which costs money. But Claude Code exposes environment variables that let you redirect those API calls anywhere you want — including a local Ollama server running on your own machine.

Ollama added official support for Anthropic's Messages API format, meaning Claude Code can talk to it natively. No hacks, no middleware, no subscription. The only cost is your own electricity and hardware.

Claude Code  →  talks to  →  Ollama (local server)  →  runs  →  Your model
                              (no Anthropic servers involved)

So What Exactly Is Ollama?

Before I could set anything up I needed to understand what Ollama actually is, because "install Ollama" doesn't tell you much.

Think of Ollama as two things in one:

1. A model manager — it downloads, stores, and organizes AI models on your machine. Like a package manager but for AI brains.

2. A local API server — once running, it exposes an endpoint at http://localhost:11434 that any application can call. Your code, Claude Code, Open WebUI, VS Code extensions — anything that speaks the Anthropic or OpenAI API format can connect to it.

This is the key insight I kept coming back to: Ollama itself has no intelligence. It's an empty engine. You have to download a model — a large file containing all the AI's weights and knowledge — before anything useful happens.

Without a model:   Ollama = empty server, useless
With a model:      Ollama = fully local AI, free forever

Downloading Your First Model — Which One?

This is where hardware matters. I have:

32GB RAM
NVIDIA GPU with ~11GB VRAM
Core i9 processor

With an NVIDIA card, Ollama automatically uses CUDA — no setup needed. Your GPU handles inference and it's dramatically faster than CPU-only.

The key concept here is VRAM vs RAM:

Model fits in VRAM  →  GPU handles everything  →  Very fast ✅
Model too big for VRAM  →  spills into system RAM  →  Slower ⚠️

With 11GB VRAM I can fit most 7B–13B parameter models entirely in GPU memory, which means fast, snappy responses.

After thinking through my use cases — coding help, image analysis, document review — I landed on Gemma4 (Google's multimodal model, ~12GB). Here's why it beat out alternatives like Qwen3.6 (28GB):

	Gemma4	Qwen3.6
Size	~12GB	~28GB
Fits in 11GB VRAM	Nearly (tiny RAM overflow)	Partial (big RAM spill)
Image understanding	✅ Yes (multimodal)	❌ No
Coding quality	Good	Better
Speed on my hardware	Fast	Slower

My use cases included image-to-text extraction and converting images to coloring pages — Qwen3.6 can't do either because it's text-only. Gemma4 won.

ollama pull gemma4

One command. It downloads, verifies, and stores the model. You can see progress in the terminal.

The Architecture in Plain English

Before going further, I want to share the mental model that made everything click for me:

┌─────────────────────────────────────────────────────┐
│                    YOUR COMPUTER                    │
│                                                     │
│  ┌─────────────┐    ┌──────────────┐               │
│  │ Claude Code │───▶│    Ollama    │               │
│  │  (terminal) │    │ :11434 (API) │               │
│  └─────────────┘    └──────┬───────┘               │
│                            │                        │
│  ┌─────────────┐    ┌──────▼───────┐               │
│  │  Open WebUI │───▶│   Gemma4    │               │
│  │  (browser)  │    │  (the brain) │               │
│  └─────────────┘    └─────────────┘               │
│                                                     │
│  ┌─────────────┐                                   │
│  │  Python API │───▶ http://localhost:11434        │
│  │   scripts   │                                   │
│  └─────────────┘                                   │
└─────────────────────────────────────────────────────┘
              Zero data leaves your machine

Three different interfaces. One local model. Everything private.

Context Windows — What Are They and Why Do They Matter?

One of the most important concepts I clarified was the context window — the model's working memory. It's the maximum amount of text a model can "see" at once in a conversation. Exceed it and it starts forgetting the beginning.

Here's the reality check comparison:

	Claude Sonnet 4.5	Gemma4 (local)
Context window	200,000 tokens	~8,000–32,000 tokens
Approximate pages	~150,000 words	~6,000–24,000 words
6 years of tax docs	Handles comfortably	Would overflow

Your VRAM directly affects how large a context window your local model can hold. More VRAM = more of the model loaded = bigger context available.

You can manually increase it:

ollama run gemma4 --ctx-size 32768

For single documents, images, or focused coding tasks — perfectly fine. For analyzing six years of tax filings all at once? That's where Claude's 200k context is a genuine advantage local models can't match yet.

Can Local Models Search the Internet?

Short answer: No, not by default.

Local models are frozen at their training date. They have no internet connection during your conversation. This was an important distinction to understand.

Claude (this chat)  →  Has web search tool  →  Knows current events ✅
Gemma4 (local)     →  No internet          →  Knowledge frozen at training ❌

This raised an interesting follow-up question though. When I used Gemini to analyze my tax filing and it spotted mistakes — was it searching the internet to find them?

No. And this was a real misconception I had.

Gemini found tax errors because tax law, IRS rules, and common filing mistakes were baked into the model during training. It learned from millions of tax documents, accounting textbooks, and IRS publications. During your session it's not googling anything — it's applying trained knowledge to your specific document.

Think of it like a tax accountant. They studied tax law for years. When reviewing your return they're not searching Google — they're applying what they already know to what you show them.

Local models work the same way. The difference is:

Gemini/Claude: More recent training data, larger knowledge base, up-to-date tax law changes
Gemma4 local: Good foundational knowledge, may be slightly behind on very recent rule changes, but your documents never leave your machine

For sensitive financial documents, that privacy trade-off is significant.

Connecting Claude Code to Gemma4

This was surprisingly simple. Claude Code reads three environment variables:

export ANTHROPIC_AUTH_TOKEN=ollama
export ANTHROPIC_API_KEY=""
export ANTHROPIC_BASE_URL=http://localhost:11434

Or using Ollama's built-in launcher:

ollama launch claude

When Claude Code started up I saw this at the bottom of the welcome screen:

gemma4 · API Usage Billing · pranayraavi@gmail.com's Organization

That confirms it's using Gemma4 through Ollama. No Anthropic billing. No subscription.

What you get with this setup:

✅ File reading and editing across your project
✅ Terminal command execution
✅ Multi-step agentic coding tasks
✅ Git operations
✅ MCP connectors and plugins
✅ Project context awareness
⚠️ Intelligence capped at Gemma4's capability (weaker than Claude Sonnet/Opus)

The Python API Test

Before setting up a GUI I wanted to confirm the raw API worked. Here's the script I wrote:

import requests

def chat(prompt):
    response = requests.post(
        "http://localhost:11434/api/generate",
        json={
            "model": "gemma4",
            "prompt": prompt,
            "stream": False
        }
    )
    return response.json()["response"]

print(chat("Write a hello world in ascii diagram of moon and earth"))

Output:

          (           )
         /              \
  ----(---O---)    (------)  <-- Orbit Path
 /  /   \    /  /   \
|   |     | | |     |   |

Gemma4, running entirely on my machine, responding to a Python script. No API key. No internet. Completely local. This was the moment it really clicked.

Setting Up Open WebUI — The ChatGPT-Like Interface

For a proper GUI I went with Open WebUI — a beautiful, feature-rich interface that runs locally and connects to Ollama.

First attempt using pip failed because I had Python 3.13 and Open WebUI requires Python 3.11 or 3.12:

ERROR: Could not find a version that satisfies the requirement open-webui

So I went the Docker route instead.

Installing Docker Desktop

Docker Desktop is free for personal use. Download from docker.com/products/docker-desktop. During install, WSL 2 backend gets configured automatically on Windows.

Running Open WebUI

docker run -d `
  -p 127.0.0.1:3000:8080 `
  --name open-webui `
  -v open-webui:/app/backend/data `
  --add-host=host.docker.internal:host-gateway `
  ghcr.io/open-webui/open-webui:main

I initially tried -p 3000:80 which caused a port conflict (another process was using port 3000 on my machine). Switching to -p 127.0.0.1:3000:8080 fixed it.

Confirmed it was running:

netstat -ano | findstr :3000
# TCP  0.0.0.0:3000  LISTENING  ← Docker up and running

curl http://localhost:3000
# StatusCode: 200 OK  ← Server responding

Then opened http://localhost:3000 in Chrome and saw the Open WebUI interface with Gemma4 auto-detected.

First Real Test — Image to Text Extraction

One of the reasons I picked Gemma4 over Qwen3.6 was its multimodal capability — it can actually see images. I put this to the test immediately.

I had a photo of handwritten chess notes and uploaded it directly into the Open WebUI chat. The prompt was simple: "convert this image to text".

Gemma4 thought for 11 seconds and returned:

FORK/DOUBLE ATTACK

When we attack two or more pieces at the same time then it is known
as fork or double attack

Note- Knights are good at making fork.

That's a perfect transcription of handwritten text — extracted entirely locally, no cloud OCR service, no API key, nothing leaving my machine. It even generated a relevant follow-up suggestion: "Are there other kinds of tactical attacks besides forks, like pins or skewers?"

This is the multimodal capability in action:

✅ Handwritten text extracted accurately
✅ Context understood (chess notes)
✅ Intelligent follow-up suggested
✅ 100% local — image never left my PC
✅ Free

For anyone with scanned documents, handwritten notes, receipts, or any image containing text — this works out of the box with Gemma4 in Open WebUI.

Document Upload and RAG — How It Actually Works

One of the most powerful features of Open WebUI is document upload with RAG (Retrieval Augmented Generation). This is how you can upload your AWS docs, tax returns, or any PDFs and chat with them.

Here's what happens under the hood:

You upload PDF
      ↓
Open WebUI splits it into chunks
      ↓
Converts chunks to embeddings (mathematical vectors)
      ↓
Stores in ChromaDB (local vector database)
      ↓
You ask a question
      ↓
ChromaDB finds the most relevant chunks
      ↓
Sends chunks to Gemma4 as context
      ↓
Gemma4 answers based on YOUR document

Everything is stored locally at:

C:\Users\lavan\AppData\Roaming\open-webui\data\
  📁 vector_db    ← document embeddings (ChromaDB)
  📁 uploads      ← original files
  📄 webui.db     ← chat history (SQLite)

Your documents never leave your machine. ChromaDB is completely free and open source.

One important limitation: RAG finds relevant chunks, not the entire document. If an answer spans many sections of a large document, it might miss some context. The workaround is to upload smaller, focused documents rather than one giant PDF.

The Full Stack — What I Now Have Running

✅ Ollama          — model manager and local API server
✅ Gemma4          — the AI model (multimodal, ~12GB)
✅ Claude Code     — agentic coding with local model
✅ Open WebUI      — browser-based chat interface with document upload
✅ Python API      — scripts calling the model directly

Total monthly cost: $0

When to Use What

After going through all of this, here's the practical split I settled on:

Task	Use
Coding with file editing	Claude Code + Gemma4
Image analysis / image to text	Open WebUI + Gemma4
Document Q&A (private)	Open WebUI + RAG + Gemma4
Web research / current events	Claude.ai or Perplexity
Complex reasoning / large context	Claude.ai (paid)
Tax doc analysis (all years)	Claude.ai or NotebookLM
Quick Python scripts calling AI	Direct Ollama API

Honest Reflections

What surprised me: How straightforward the setup actually was once I understood the mental model. Ollama is the server, the model is the brain, everything else just connects to it.

What I underestimated: The quality gap between local models and Claude Sonnet/Opus is real. For simple tasks Gemma4 is impressive. For complex multi-step reasoning, Claude's frontier models are noticeably stronger.

What I'd tell myself at the start: Local AI is not a replacement for cloud AI — it's a complement. Use local for private, repetitive, or experimental tasks. Use cloud AI for research, complex reasoning, and anything that benefits from a larger context window.

The privacy win is real: For sensitive documents — financial records, personal data, proprietary code — local AI is genuinely better from a privacy standpoint. Your data does not leave your machine. Full stop.

Resources

Ollama: ollama.com
Open WebUI: openwebui.com
Claude Code: claude.ai/code
Ollama + Claude Code docs: docs.ollama.com/integrations/claude-code
Docker Desktop (free): docker.com/products/docker-desktop

All of this runs on a Windows machine with 32GB RAM, an NVIDIA GPU with ~11GB VRAM, and a Core i9 processor. If you have similar hardware you can replicate this entire stack in an afternoon.

DEV Community: Pranay Ravi

What to Keep in Mind for a Cloud Migration

1. Scope: decide what changes and what waits

2. Discovery: get the complete picture

3. People and calendar: one scrum, one plan

4. Data: the part that decides the cutover

5. Network and edges: where it usually breaks

6. Cutover: rehearse it the same way every time

7. Monitoring, retention and cost: decide early

My take

Auditing Aurora PostgreSQL Without Drowning in Logs (or Leaking PII Into Them)

What It Actually Takes to Audit Aurora PostgreSQL on AWS

The architecture at a glance

Setup, briefly

Opt-in or baseline? We tested both

Four parameters that changed our design

pgaudit.log_parameter: the audit log becomes a data store

pgaudit.log_relation: answering "who touched this table?"

log_statement: everything logged twice

log_connections and log_disconnections: cluster level only

What a baseline means for alerting

pcraavi / PostgreSQL-Audit

Setup PostgreSQL Audit in AWS and get alerted based on Cloudwatch ,Lambda and SNS

Aurora PostgreSQL User Audit Monitor

Repository Structure

Protecting the data and keeping the trail

Mask at query time with pg_columnmask

Close the insider gap with Database Activity Streams

Keep the trail queryable, and scan it

The short version

Not Every Slow Query Needs a New Index

The setup

Lesson 1: A function on an indexed column throws the index away

Lesson 2: What is fine in QA can bite in production

Lesson 3: Carry every filter you know into the subquery

Lesson 4: Ask what a query is for before you tune it

Lesson 5: A test that returns zero rows proves nothing

Lesson 6: "Good enough" is a valid result. Index only what doesn't scale

Lesson 7: A new index can make a query slower

Building the indexes online

A review checklist

Closing: tuning is mostly a conversation

References

Aurora PostgreSQL Backups at Scale, Part 1: Designing AWS Backup Across Multiple Accounts

1. The five nouns of AWS Backup

2. Account-level vs organization-level settings

Management account (governs)

Member accounts (hold)

Policy inheritance

3. Vaults: standard vs logically air-gapped

Standard vault

Logically air-gapped vault

4. Continuous (PITR) vs snapshot backups

The handover nobody tells you about

5. Several policies and respective jobs

Why split it this way

Watch out when you split

6. Backing up a headless/serverless DR cluster

7. The traps that show up years later

🕰️ Trap 1: Your yearly backup will outlive its PostgreSQL version

🧊 Trap 2: Cold storage settings that silently do nothing

🔑 Trap 3: The default KMS key blocks your exit doors

🏚️ Trap 4: Missing vaults fail silently

⏱️ Trap 5: "Last runtime" is not success

What's next

Further reading

15-Minute RPO to 7-Year Retention: Elasticsearch Backups on S3 with SLM (No Scripts, No Keys)

Three questions every backup should answer

The architecture

Step 1: IAM, the keyless way

Step 2: Repositories

Step 3: SLM policies

Five things that bit me (so they don't bite you)

Restoring (the part that actually matters)

What I'd add next

Stop Running psql Commands by Hand — Build a REST API for PostgreSQL User Management

What I Built

The Problem It Solves

Architecture

The Endpoints

`pgaudit.log_parameter`: the audit log becomes a data store

`pgaudit.log_relation`: answering "who touched this table?"

`log_statement`: everything logged twice

`log_connections` and `log_disconnections`: cluster level only

Mask at query time with `pg_columnmask`

2. Spot Dangerous `Idle in Transaction` Sessions