I work professionally on backend systems for payment platforms, and there's a category of question that production work keeps you too busy to answer properly: what actually happens inside a database when a payment is retried? What makes a simple UPDATE dangerous at scale? And if someone runs DELETE FROM transactions without a WHERE clause at 2am, what does recovery actually look like — not in theory, but command by command?
As an AWS Certified Solutions Architect, I'm used to thinking about infrastructure trade-offs at the cloud and operations layer. This project was a chance to go one level deeper — into the database engine itself, at a depth that day-to-day work rarely demands but that shapes every architectural decision underneath.
The only honest way to answer those questions was to build a system that forced me to confront them. So I built a minimal payments ledger on Django and PostgreSQL, then methodically stress-tested every decision — wrong partition keys, stale indexes, bloated tables, a deliberately deleted dataset — until the system either held up or broke in ways I could measure and explain.
The result is fintech-ledger-platform: a double-entry ledger with monthly partitioning, idempotent payment APIs, connection pooling, autovacuum tuning, indexed query optimization, and a tested point-in-time recovery runbook. The code is public and comes with six Architecture Decision Records that explain not just what was built but why each choice was made and what was rejected.
This post walks through the most interesting decisions and discoveries. It isn't a tutorial — I'm assuming you've used Postgres before and want to understand what's actually happening under the hood.
The Ledger Design: Why Two Rows Per Payment
The first question was how to model the data. The obvious approach is a single transfers row: source account, destination account, amount, status. Simple to write, simple to read.
I didn't use it, for a reason that becomes obvious once you've seen a payment system at any real volume: UPDATE is expensive in Postgres in ways that aren't visible until the table is large.
Postgres uses Multi-Version Concurrency Control. When you UPDATE a row, Postgres doesn't overwrite the old version — it writes a new row version alongside the old one and marks the old one as deleted. The old version is called a dead tuple. It occupies storage, contributes to heap fragmentation, and must be reclaimed by VACUUM. A payment that moves from PENDING to PROCESSING to SETTLED in three status updates creates three dead tuples per payment. At 1 million payments a day, that's 3 million dead tuples a day on your hottest table, and autovacuum has to run fast enough to keep up.
Instead, every payment creates exactly two rows: a DEBIT on the source account and a CREDIT on the destination. Both share a correlation_id UUID that groups them as one logical payment. Neither row is ever updated after creation.
# ledger/models.py
class Transaction(models.Model):
class EntryType(models.TextChoices):
DEBIT = 'DEBIT', 'Debit'
CREDIT = 'CREDIT', 'Credit'
class Status(models.TextChoices):
PENDING = 'PENDING'
SETTLED = 'SETTLED'
FAILED = 'FAILED'
REVERSED = 'REVERSED' # a new row pair — not an update
correlation_id = models.UUIDField(db_index=True)
account = models.ForeignKey(Account, on_delete=models.PROTECT)
entry_type = models.CharField(max_length=6, choices=EntryType.choices)
amount = models.DecimalField(max_digits=19, decimal_places=4)
# ... created_at, currency, status, metadata
The accounting invariant this creates is immediate: the sum of all debits must equal the sum of all credits, always. Any discrepancy is data corruption, not a business logic question. I added a reconciliation query to the project:
SELECT
correlation_id,
SUM(CASE WHEN entry_type = 'DEBIT' THEN amount ELSE -amount END) AS imbalance
FROM transactions
GROUP BY correlation_id
HAVING SUM(CASE WHEN entry_type = 'DEBIT' THEN amount ELSE -amount END) != 0;
An empty result means the ledger is consistent. Any returned row is a payment that was half-written — a bug in the service layer that wrote one leg without the other. Running this on a schedule and alerting on non-empty results is free reconciliation.
There's a subtlety worth stating explicitly: REVERSED is a status value, but it doesn't mean an existing row's status was changed. A reversal is a new DEBIT+CREDIT pair that undoes the original, with status = 'REVERSED' set at insert time. The original payment row is untouched. This is the append-only contract — once written, a ledger entry is immutable.
The payoff isn't just accounting correctness. Because nothing is ever updated on the transactions table, autovacuum has nothing to do there. Dead tuples stay at zero indefinitely regardless of write volume. I'll come back to why that matters concretely when I get to the bloat experiment.
Partitioning From Day One
A single unpartitioned transaction table works fine at thousands of rows. At hundreds of millions, it becomes a maintenance problem: slow date-range queries, expensive archival, DDL changes that require multi-hour locks. Adding partitioning to a live table at that scale is a multi-day migration event. Starting partitioned costs almost nothing.
The partition key is created_at, monthly. The choice matters: partitioning by account ID (hash partitioning) gives fast point-lookups but destroys date-range pruning entirely — a monthly billing report would scatter-gather across every partition. Monthly calendar partitions align with how operations teams actually query the data, and archival becomes a metadata operation:
ALTER TABLE transactions DETACH PARTITION transactions_2025_01;
That command takes milliseconds and moves an entire month of data to a standalone table with no row movement.
The migration that sets this up is raw SQL — Django's ORM has no concept of partitioned tables, and pretending otherwise would be misleading:
-- ledger/migrations/0002_partition_transactions.py
CREATE TABLE transactions (
id UUID NOT NULL DEFAULT gen_random_uuid(),
correlation_id UUID NOT NULL,
account_id UUID NOT NULL REFERENCES accounts(id),
entry_type VARCHAR(6) NOT NULL,
amount NUMERIC(19, 4) NOT NULL,
-- ...
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY (id, created_at) -- partition key must be part of PK
) PARTITION BY RANGE (created_at);
-- DEFAULT partition catches inserts outside the pre-created range
CREATE TABLE transactions_default PARTITION OF transactions DEFAULT;
-- Pre-create partitions for current and next month
CREATE TABLE transactions_2026_06 PARTITION OF transactions
FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
CREATE TABLE transactions_2026_07 PARTITION OF transactions
FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
The DEFAULT partition is not optional. Without it, any insert with a created_at outside the pre-created ranges fails immediately with ERROR: no partition of relation "transactions" found for row. If a management command that pre-creates next month's partition is forgotten or delayed, you'd have an outage starting at midnight on the first of the month. The DEFAULT partition catches those rows safely — they can be moved once the correct partition is created.
The composite PRIMARY KEY (id, created_at) is a Postgres requirement for partitioned tables — the partition key must appear in the primary key. This means any external service storing a transaction_id and trying to look it up later must either know the created_at (unrealistic) or accept a cross-partition index scan. Worth being aware of before committing to this schema.
Idempotency: The SELECT FOR UPDATE Wasn't Enough
Payment APIs have a fundamental problem: the client cannot know whether a timed-out request was processed. The server might have received and committed the payment, with only the response lost in transit. Or the request might have never arrived. Retrying is the safe choice for the client — but without something preventing it, it's a double-charge.
The standard solution is idempotency keys: the client generates a UUID per payment attempt and sends it as a header. The server stores that key and returns the same response for any subsequent request with the same key, without re-executing the payment.
The implementation I built follows the same pattern Stripe documents: lock or create the key row before doing anything, execute the transfer only if the key is new, store and return the cached response on replay.
# ledger/services.py (simplified)
def create_payment(*, idempotency_key, ...):
with db_transaction.atomic():
idem_key, created = IdempotencyKey.objects.select_for_update().get_or_create(
key=idempotency_key,
defaults={'state': 'PROCESSING', 'request_fingerprint': fingerprint},
)
if not created:
if idem_key.request_fingerprint != fingerprint:
raise IdempotencyConflictError("Key reused with different request body")
if idem_key.state == 'COMPLETE':
return {'cached': True, **idem_key.response_body}
raise IdempotencyConflictError("Request still in flight")
result = _execute_transfer(...)
idem_key.state = 'COMPLETE'
idem_key.response_body = result
idem_key.save()
return result
I thought this was correct. The tests passed. Then I wrote a concurrent test: five threads, all with the same new key, firing simultaneously.
The race condition was subtle. SELECT FOR UPDATE can only lock a row that already exists. If two requests arrive simultaneously and neither finds the row, both fall through to the INSERT in get_or_create. The first insert wins; the second hits a duplicate primary key and raises an IntegrityError. Django's get_or_create propagates that error rather than retrying the get — so the second thread sees an exception that has nothing to do with a payment conflict. It's a database-level uniqueness violation masquerading as an application error.
The fix is to catch IntegrityError explicitly and re-fetch the row the winning thread created:
from django.db import IntegrityError
try:
idem_key, created = IdempotencyKey.objects.select_for_update().get_or_create(
key=idempotency_key,
defaults={...},
)
except IntegrityError:
# Another concurrent request won the INSERT race.
# Re-fetch the row it created and follow the "already exists" path.
idem_key = IdempotencyKey.objects.select_for_update().get(key=idempotency_key)
created = False
Now the losing thread gets the row created by the winner and follows the PROCESSING path cleanly. No phantom exception, no dropped request. The concurrent test — five threads, same new key — consistently produces one executed payment and four cached responses.
This is not a theoretical race. It's a real one that appears in any system under load — in production payment systems, this class of bug typically surfaces during traffic spikes, not in routine testing, because that's the first time concurrent first-requests for the same key arrive within milliseconds of each other. The pattern of SELECT FOR UPDATE → get_or_create → catch IntegrityError → re-fetch is the correct sequence and it's worth knowing cold.
Query Performance: From 4,200ms to 45ms
After seeding the database with 1 million transaction entries across 100 accounts — using a hot-account distribution where the top 20 accounts receive 60% of volume — I captured EXPLAIN (ANALYZE, BUFFERS) on the history query at each indexing stage.
The target query is the transaction history endpoint:
SELECT id, correlation_id, entry_type, amount, currency, status, created_at
FROM transactions
WHERE account_id = $1
AND created_at >= NOW() - INTERVAL '30 days'
ORDER BY amount DESC
LIMIT 50;
Baseline (no indexes beyond PK): ~4,200ms
Seq Scan on transactions_2026_06
Filter: ((account_id = $1) AND (created_at >= ...))
Rows Removed by Filter: 813,447
Buffers: shared read=4,821
Execution Time: 4,218ms
The planner reads the entire partition — every page — discards 99.9% of the rows, and returns 50. The Buffers: shared read=4,821 means most of those pages came from disk, not cache. At this rate, concurrent history requests would saturate I/O on any reasonable hardware.
Stage 2: Composite index (account_id, created_at DESC): ~180ms
CREATE INDEX idx_txn_account_created_at
ON transactions (account_id, created_at DESC);
Column order matters here in a non-obvious way. Putting created_at first would require a full index scan for the account_id predicate — the index would be ordered by date across all accounts, not by account within a date range. With account_id first (equality), the B-tree lookup narrows to a narrow subtree; created_at DESC then scans forward within that subtree for the range predicate. Reversed column order costs roughly the same as no index at all for this query pattern.
Index Scan using idx_txn_account_created_at on transactions_2026_06
Index Cond: ((account_id = $1) AND (created_at >= ...))
Rows Removed by Filter: 0
Buffers: shared hit=23 read=4
Execution Time: 178ms
24× improvement. Buffer hits dominate — the index and matching heap pages fit in shared_buffers.
Stage 4: Covering index with INCLUDE: ~45ms
CREATE INDEX idx_txn_account_created_covering
ON transactions (account_id, created_at DESC)
INCLUDE (entry_type, amount, currency, status);
The INCLUDE columns are stored in the B-tree leaf pages but aren't part of the key. Because the SELECT list is exactly {account_id, created_at, entry_type, amount, currency, status} — all now present in the index — Postgres can answer the query without touching the heap at all:
Index Only Scan using idx_txn_account_created_covering on transactions_2026_06
Index Cond: ((account_id = $1) AND (created_at >= ...))
Heap Fetches: 0
Buffers: shared hit=7
Execution Time: 45ms
Heap Fetches: 0 is the number to watch. It means the visibility map confirmed all matching rows are visible without checking the heap. Zero disk I/O beyond the index pages themselves. 93× faster than the baseline, and the query volume the replica can handle scales proportionally — Index Only Scans use almost no I/O per query.
Why the Planner Chose What It Did
The index choices above are only as good as the planner's statistics. Postgres maintains column statistics in pg_stats — histograms, most-common values, distinct counts — that it uses to estimate how many rows each plan node will return. When those statistics are stale, the planner makes wrong choices.
The most dangerous form of stale statistics: a query predicate matches an account that has grown from 50 rows to 50,000 since the last ANALYZE. The planner estimates 50 rows, chooses an index scan (correct for 50 rows). At 50,000 rows, the index scan makes 50,000 random heap page accesses — slower than a sequential scan would have been. The plan looks right in EXPLAIN; it's only EXPLAIN ANALYZE that reveals rows=50 (estimated), Actual Rows: 49,847.
For high-cardinality columns like account_id, increasing the statistics target gives the planner finer-grained histograms:
ALTER TABLE transactions ALTER COLUMN account_id SET STATISTICS 500;
ANALYZE transactions;
And on SSD storage, the planner's default random_page_cost = 4.0 (treating random I/O as 4× more expensive than sequential) is wrong — random reads from SSD are roughly equal to sequential. Setting random_page_cost = 1.1 makes the planner far more willing to use index scans, which is usually the right call for OLTP workloads.
The Bloat Experiment: Deliberately Breaking a Table
I wanted to understand autovacuum failure modes concretely, not just conceptually. So I introduced a deliberately mutable table — order_status, tracking payment orders through a CREATED → PROCESSING → RISK_CHECK → SETTLED state machine — and ran update cycles against it while measuring dead tuple accumulation.
The setup: 100,000 rows inserted, five full update cycles applied to every row.
After INSERT (0 updates):
Live tuples: 100,000 | Dead tuples: 0 | Table size: 11 MB
After UPDATE cycle 1 (→ PROCESSING):
Live tuples: 100,000 | Dead tuples: 100,000 | Table size: 21 MB
After UPDATE cycle 3 (→ RISK_CHECK):
Live tuples: 100,000 | Dead tuples: 300,000 | Table size: 42 MB
After VACUUM:
Live tuples: 100,000 | Dead tuples: 0 | Table size: 11 MB
Three update cycles. Dead tuple count reaches 3× the live row count. Table size quadruples. VACUUM recovers all of it — but only if it runs fast enough. With default autovacuum settings (autovacuum_vacuum_scale_factor = 0.20), autovacuum fires when dead tuples exceed 20% of live rows — that's 20,000 dead tuples on this table before autovacuum starts. On a higher-churn table, that delay compounds.
The per-table tuning that fixes this:
ALTER TABLE order_status SET (
autovacuum_vacuum_scale_factor = 0.01, -- fire at 1% (was 20%)
autovacuum_analyze_scale_factor = 0.005,
autovacuum_vacuum_cost_delay = 2 -- less I/O throttling (was 20ms)
);
This is targeted — the transactions table, being append-only, gets default settings because autovacuum genuinely has nothing to do there. Global tuning would waste autovacuum workers on no-ops.
The experiment also demonstrated the long-running transaction problem. I opened a transaction and held it open while running more updates. The held transaction has a transaction ID that prevents VACUUM from reclaiming any row version that was visible at the time the transaction started. Dead tuples accumulate unbounded. The detection query for this in production:
SELECT pid, now() - xact_start AS duration, state, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
AND (now() - xact_start) > interval '5 minutes'
ORDER BY duration DESC;
A monitoring system running this every 60 seconds with an alert threshold of 5 minutes would catch idle transactions before they cause bloat incidents. In practice, the most common source is not a rogue query but an application that opened a transaction and then hit an exception handler that didn't call COMMIT or ROLLBACK — leaving the connection in idle in transaction state indefinitely.
One more thing to get right: VACUUM (no FULL) reclaims dead tuple space and makes it reusable, but does not return pages to the operating system — the table file stays the same size on disk. VACUUM FULL rewrites the entire table and returns pages to the OS, but holds an exclusive lock for the entire duration. On a 50GB transaction table, that's a write outage measured in hours. VACUUM FULL is never appropriate on a live payments table — standard VACUUM is the right tool.
Disaster Recovery: I Deleted My Own Data On Purpose
The PITR section started with a question I had never actually tested: can I recover to a specific point in time, reliably, from a cold start with only the backup and WAL archive available?
The infrastructure for this was set up in the Docker Compose configuration:
command: postgres
-c wal_level=replica
-c archive_mode=on
-c archive_command='cp %p /wal_archive/%f'
-c max_wal_senders=5
Every WAL segment is archived when it completes. Combined with a nightly pg_basebackup, this gives sub-minute recovery granularity: take a base backup, apply WAL segments up to any point after it.
The experiment: seed the database with accounts and transactions, record a timestamp, run DELETE FROM transactions WHERE entry_type = 'DEBIT' — a catastrophic half-deletion that breaks the double-entry invariant — and then recover to one second before the delete.
The recovery procedure:
# 1. Stop the running Postgres instance
pg_ctl stop -D $PGDATA
# 2. Extract the base backup
tar -xzf backup.tar.gz -C $RESTORE_DATA_DIR
# 3. Write the recovery target into postgresql.conf
cat >> $RESTORE_DATA_DIR/postgresql.conf << EOF
restore_command = 'cp /wal_archive/%f %p'
recovery_target_time = '2026-06-13 14:23:00+00'
recovery_target_action = 'promote'
EOF
# 4. Create the recovery signal file (Postgres 12+)
touch $RESTORE_DATA_DIR/recovery.signal
# 5. Start the restored instance
pg_ctl start -D $RESTORE_DATA_DIR
After Postgres replays WAL up to the target timestamp, it promotes to a normal read-write instance. The verification query:
SELECT COUNT(*) FROM transactions;
-- Expected: pre-deletion count, split evenly between DEBIT and CREDIT
SELECT SUM(CASE WHEN entry_type = 'DEBIT' THEN amount ELSE -amount END) AS imbalance
FROM transactions GROUP BY correlation_id
HAVING SUM(CASE WHEN entry_type = 'DEBIT' THEN amount ELSE -amount END) != 0;
-- Expected: empty result set — double-entry invariant holds
Both queries returned expected results. The recovery worked. More importantly, writing down every step in a runbook (docs/runbooks/pitr-recovery.md) before attempting it revealed three things I hadn't considered: the recovery.signal file that Postgres 12+ requires (vs. the old recovery.conf), the recovery_target_action = 'promote' setting that prevents the instance from staying in read-only recovery mode indefinitely, and the need to verify the WAL archive is complete before starting recovery (missing segments cause the replay to stall silently).
An untested runbook is a hypothesis, not a recovery procedure. In my professional experience, the teams that recover quickly from data incidents are the ones that have run through the procedure at least once in a non-emergency context — not because they're smarter, but because they've already hit the unexpected steps and know which assumptions were wrong. The difference only becomes apparent at 2am during an actual incident.
The RPO/RTO math: WAL archiving is continuous, so in theory RPO approaches the WAL segment completion time (typically a few seconds under load, or 60 seconds at the archive_timeout if writes are sparse). Practically, I'd target 5 minutes as the stated RPO commitment. RTO depends on WAL volume to replay — at roughly 2GB of WAL per million transactions, a 24-hour recovery means replaying up to 60GB on a local setup, which took about 25 minutes. Within a 30-minute RTO target.
One thing I'd do differently in production: archive to S3 rather than a local directory. The archive_command would become aws s3 cp %p s3://backups/wal/%f. Disk full on the WAL archive volume silently breaks archiving — the archive_command returns exit code 1, Postgres stops archiving, and you discover the problem at recovery time. S3 doesn't fill up.
The Scaling Framework: When to Reach for Each Tool
After building and stressing all of these components, the most useful output wasn't any individual piece — it was a clear sense of the order in which to apply solutions as load grows. The temptation in engineering is to reach for the most powerful-sounding tool first. The right instinct is to exhaust the simpler one.
The framework I documented in the project:
Step 1 — Vertical scale and postgresql.conf tuning.
A single well-tuned Postgres instance handles 50,000+ transactions/hour for most workloads. shared_buffers at 25% of RAM, checkpoint_completion_target = 0.9 to spread I/O, max_connections kept deliberately low (let PgBouncer handle the pool). Cost: a config file change and a restart. Complexity introduced: zero.
Step 2 — Read replica routing (already built). History and reporting queries go to the replica; writes and balance-sensitive reads stay on the primary. The routing rule: if the user might need to read back what they just wrote, use the primary.
Step 3 — Async transaction settlement.
The synchronous HTTP path — lock idempotency key, lock accounts, write ledger, update balances, commit — is the correct architecture for low concurrency. At high concurrency on a popular account (a large merchant receiving thousands of payments per minute), the SELECT FOR UPDATE queue depth becomes the bottleneck. The async solution decouples the HTTP response from the database write: the API writes to a message queue (Kafka, SQS) and returns 202 Accepted immediately; a worker executes the DB transaction. The idempotency key built into this project works unchanged with the async path — the exactly-once guarantee transfers to the worker.
Steps 4-6 — OLAP separation, horizontal sharding, Citus. Each step introduces meaningful operational complexity. Sharding, specifically, is the most expensive architectural decision in payments engineering. Teams that reach for it before exhausting Steps 1-3 spend 6-12 months stabilizing distributed transaction handling and cross-shard saga patterns. A well-tuned single Postgres node handles more than most fintechs process for years.
The ordering matters because each step is a prerequisite for the next. Async settlement requires idempotency keys (built in the ledger design). OLAP separation via logical replication requires WAL archiving (built for PITR). Citus distribution uses the same partition infrastructure already in place. The decisions compound — they weren't independent choices.
What I'd Do Differently, and What's Still Open
The most surprising discovery was how much production behavior is invisible in a local development setup. The replica lag circuit-breaker, the autovacuum behavior under load, the WAL archiving failure modes — all of these require specific conditions that don't appear in a clean test run. Building the simulation scripts (simulate_bloat, seed_transactions, explain_query) was necessary to observe anything real.
There are genuine open questions I'm still thinking about:
Event publishing. The service layer currently writes an AuditLog row when a payment settles. That's internal state — other services (a hypothetical notification service, a fraud scorer) can't subscribe to it. The correct pattern is a transactional outbox: write an outbox_events table in the same transaction as the ledger entries, with a separate publisher reading from the outbox and publishing to Kafka. Without this, the ledger is a data silo. Adding it is the next meaningful architectural step.
Reversal linkage. The model represents reversals correctly — a REVERSED payment is a new row pair, never an update — but there's no foreign key linking a reversal back to the payment it reverses. That means there's no database-level guarantee preventing double-reversals (two concurrent refunds for the same payment). The fix is a unique partial index on (correlation_id) WHERE status = 'REVERSED', or an explicit reversal_of_id FK. Neither is in the schema today.
UUID fragmentation. The id field uses random UUIDs (uuid.uuid4()). Random UUIDs cause B-tree index fragmentation at high insert rates — inserts land at random positions in the index, causing frequent page splits. At a million inserts a day, this is measurable. UUIDv7 (time-ordered UUID) eliminates fragmentation and is now available in most languages and in Postgres 17's gen_random_uuid_v7(). Switching would require a schema migration but no application logic changes.
Idempotency key retention. The idempotency_keys table grows by one row per payment, indefinitely. The schema includes a created_at column for exactly this reason, but there's no cleanup job. The PROTECT foreign key from transactions means you can't delete idempotency keys while transactions reference them — which is forever. The correct resolution is either changing the FK to SET NULL and handling null references in queries, or accepting that idempotency keys are permanent records alongside the ledger entries they governed.
The full codebase, migrations, management commands, ADRs, and runbooks are at github.com/kuldeepstechwork/fintech-ledger-platform. The ADRs in docs/adr/ are the most useful part — each one documents what was chosen, what was rejected, and what the trade-offs are. Reading them alongside the code gives a clearer picture than either alone.
Building this from the ground up gave me a much clearer appreciation for decisions that, in production systems, often get made early and then live with you for years — partition keys, idempotency design, backup strategy and runbooks. In professional work, those decisions happen under time pressure, with imperfect information, and then compound into everything that comes after. Understanding the internals at this depth changes how I reason about them when they come up again: not just what to choose, but why the alternatives were wrong and what conditions would make them right.
The questions this started with — what happens when a payment is retried, when does an UPDATE become dangerous, what does recovery actually look like — have answers now. Most of them involve Postgres doing something more sophisticated than I initially expected, in ways that only become visible when you deliberately set up conditions where they matter.