fsync
By the end of this page you will know what fsync = off turns off, what a crash then costs, and why a session can read the setting but never change it.
fsync is PostgreSQL's setting and PostgreSQL's context: the server's own (sighup), set in the config file when the server starts. With it on, the default, ScramDB asks the operating system to write to disk before it acknowledges: the write-ahead log at commit, and the data and index files when it writes them. With it off, those requests are skipped and the operating system decides when the bytes reach the disk.
Examples on this page use this table:
CREATE TABLE readings (sensor INTEGER, value DOUBLE PRECISION);
Setting it
[storage]
fsync = false
| Key | Type | Default | Effect |
|---|---|---|---|
fsync | bool | true | false skips every flush to disk the storage layer would run. |
Turn it off only for data you can rebuild, such as a bulk load you would rerun or a throwaway test database. It never changes what a query returns: a server with fsync = false answers the same rows, and its data is there after a clean shutdown and restart.
Reading it
SHOW fsync;
SELECT current_setting('fsync');
SELECT setting, context, vartype FROM pg_settings WHERE name = 'fsync';
The answer is the server's value (on unless the config file turned it off), its context is sighup and its type is bool. It is not stored with the data: a server reopened over the same directory takes the value from its own config.
Why a session cannot change it
A session changes a setting for itself, and a flush is the whole server's. So SET, SET LOCAL, RESET and set_config all refuse it, the way PostgreSQL does, with 55P02 before the value is even read:
ERROR: parameter "fsync" cannot be changed now
RESET ALL and DISCARD ALL leave it as it is, and a connection that sets it while starting (in its options as -c fsync=off, or as a startup parameter) is refused and closed with the same 55P02. The value stays the server's.
What it skips, and what it never does
- Skipped: the write-ahead log's flush at commit, and the flushes of the data files (staged ones included), of the directories that hold newly created data files, and of the vector index files.
- Never skipped: a file replaced whole, because a half-written one stops the server or its group from starting: the catalog, the cold tier's files, a backup, a restore, a cluster member's consensus log and the commit protocol's files. They are written on schema changes and administration, never once per commit.
- The cost of a crash: if the machine loses power with
fsyncoff, the commits it acknowledged may be gone, and files it was writing may be torn. Recovery still replays what reached the disk, but it cannot rebuild what never did. A crash of the ScramDB process alone loses nothing the operating system had already accepted.
Every skipped flush is counted on /metrics as scramdb_fsync_skipped_total, so a run that lost nothing to a crash can still show how much it risked. A server with fsync on never increments it. See Observability.
INSERT INTO readings VALUES (1, 20.5), (1, 20.7), (2, 18.1);
SELECT sensor, count(*), avg(value) FROM readings GROUP BY sensor ORDER BY sensor;
Related
- Synchronous Commit for acknowledging a commit early without skipping the flush.
- Storage for what makes a commit durable.