What a Database Actually Does When It Says Committed

Start at the returned and you miss it. Start at the decision and you miss how it arrived. The bug lived in the hand-off.

This is the anatomy of the commit, and of the specific property that makes durability a choice rather than a fact: the database writes the log before the data, and the log's flush is the durability boundary, and the flush's timing is a setting, so the commit's meaning depends on the configuration.

This was Postgres 16, and the mechanics are the same for any database with a write-ahead log.

Why the log comes first

The data is the table, and the table is the pages, and the pages are the random writes, and the random is the slow, and the slow is the reason the log exists, because the log is the append, and the append is the sequential, and the sequential is the fast, and the fast is the commit's speed, and the speed is the log's purpose.

The commit writes the log record, and the record is the change, and the change is the description, and the description is the recovery's input, and the recovery is the replay, and the replay is the data's rebuild, and the rebuild is the log's promise, and the promise is the durability, and the durability is the flush, and the flush is the disk, and the disk is the power's survival, and the survival is the guarantee.

The data pages are written later, and the later is the checkpoint, and the checkpoint is the batch, and the batch is the efficiency, and the efficiency is the reason the data lags the log, and the lags is the recovery's work, and the work is the replay, and the replay is the log's job, and the job is the durability's mechanism.

Where the setting decides

The flush is the fsync, and the fsync is the disk's confirmation, and the confirmation is the durability, and the durability is the setting, and the setting is the synchronous_commit, and the commit is the three options.

On. The commit waits for the fsync, and the waits is the durability, and the durability is the zero loss, and the zero is the promise, and the promise is the cost, and the cost is the latency, and the latency is the disk's round trip, and the round trip is the commit's delay.

Off. The commit returns immediately, and the immediately is the fast, and the fast is the loss window, and the window is the unflushed log, and the unflushed is the power's loss, and the loss is the recent commits, and the recent is the seconds, and the seconds is the setting's trade, and the trade is the speed for the durability.

Remote or local. The replication adds the standby's confirmation, and the confirmation is the survival on the primary's death, and the death is the failover, and the failover is the data's presence, and the presence is the setting's guarantee, and the guarantee is the replication's mode.

The setting is the decision, and the decision is the business's, because the trade is the latency against the loss, and the loss is the money or the audit or the nothing, and the nothing is the off, and the money is the on, and the choice is the configuration, and the configuration should be explicit rather than default.

Why the commit is not the data

The client's commit returns, and the returns is the log's written, and the written is the maybe flushed, and the flushed is the durable, and the durable is the setting, so the commit's meaning is the configuration's, and the configuration is the invisible to the client, and the invisible is the assumption, and the assumption is the durable, and the durable is the maybe, and the maybe is the incident when the power goes.

This is the same gap as the Postgres replica that was hours behind at failover, same mechanism, different costume.

What actually fixed it

Set synchronous_commit explicitly. The setting is the decision, and the decision is the documented, and the documented is the review's, and the review is the fix, and the fix is the explicit, because the default is the assumption, and the assumption is the unexamined trade.

Know your storage's fsync behaviour. The disk lies, and the lies is the cached write, and the cached is the not durable, and the not durable is the fsync's falsehood, and the falsehood is the RAID controller's, and the controller's is the battery, and the battery is the guarantee, and the guarantee is the check, and the check is the fix.

Test the recovery. The primary is killed and the data is checked, and the checked is the promise's verification, and the verification is the fix, and the fix is the drill, and the drill is the discipline, because the untested durability is the assumed durability, and the assumed is the incident.

Use the replication mode that matches the promise. The synchronous replication is the zero loss on failover, and the zero is the guarantee, and the guarantee is the cost, and the cost is the latency, and the latency is the trade, and the trade is the decision, and the decision is the configuration.

Log the setting in the runbook. The durability's configuration is the incident's context, and the context is the diagnosis, and the diagnosis is the loss's explanation, and the explanation is the fix, and the fix is the document, and the document is the discipline.

The rule I keep

A commit writes the write-ahead log, and the log's flush is the durability boundary, so the commit's meaning depends on synchronous_commit, the storage's real fsync behaviour and the replication mode. Set the option explicitly, verify the disk does not lie, and test the recovery.

The commit returned and the data was not on the disk yet, because the log was written and the flush was pending, and the pending was a configuration choice. Get that property wrong and every layer above it lies to you politely.

Published on debugly.dev by Rohit Bhadani | Founder of Krova Cloud