PostgreSQL : Where is index kept until check point occures ?

Question

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 ?

Answer 1

Your explanation here is not quite right:

When checkpoint reached or WAL buffer becomes full, these WAL files are written to data files.

What happens when a CHECKPOINT hits is that dirty buffers held in shared_buffers are 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:

My question is where does the database keep indexes before writing to data files.

is answered here, in particular this bit:

Checkpoints are points in the sequence of transactions at which it is guaranteed that the heap and index data files have been updated with all information written before that checkpoint.

(emphasis added). Also, your explanation of:

then in this time window [before a CHECKPOINT] all the data is in WAL files and when user request for data, database read WAL files and provide data.

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.