In PostgreSQL data is first written to WAL files. When commit is done, data is written to WAL files and then return to client (synchronous commit). When checkpoint reached or WAL buffer becomes full, these WAL files are written to data files. My question is where does the database keep indexes before writing to data files. Assume checkpoint occurs in every 15 min , then in this time window all the data is in WAL files and when user request for data, database read WAL files and provide data. In this time period does WAL also keep indexes ?
PostgreSQL – Where is Index Kept Until Checkpoint Occurs?
indexpostgresqlwrite-ahead-logging
Related Question
- Postgresql – Postgres 9.1.16 Hot Standby
- PostgreSQL Backup – Simplest Filesystem Backup of Postgres Database
- PostgreSQL – How Checkpoint Knows Which Point to Recover From
- PostgreSQL – What is the archive_command and When to Use It
- PostgreSQL Checkpoint Log Explained
- Sql-server – Where in the database file is an index rebuild started? Alternative to SHRINKFILE
- Postgresql – What makes it so much faster to write to the “WAL” compared to directly to the “real database”
Best Answer
Your explanation here is not quite right:
What happens when a
CHECKPOINThits is that dirty buffers held inshared_buffersare guaranteed to be written (i.e. fsync'ed) to disk. The WAL files must have already been fsync'ed to disk at COMMIT time, assuming synchronous_commit=on and fsync=on, etc.Your question of:
is answered here, in particular this bit:
(emphasis added). Also, your explanation of:
PostgreSQL reads its (index, heap, etc.) blocks either from shared_buffers, if they are available there, or from disk if not. Postgres does not need to read out of its WAL files other than for crash recovery (similarly, standby) purposes.