Start here

Life of a SELECT

A SELECT is parsed and planned, takes a snapshot and a light table lock, finds the page in shared buffers or reads it from disk, checks which row versions it may see, and streams the rows back.

In plain words

When you run a SELECT, your session's backend process first works out what you asked for (parsing) and how to fetch it fastest (planning), for example by using an index.

It then takes a snapshot: a note of which transactions had finished at that moment. That snapshot decides which version of each row you are allowed to see, so you never see half-finished changes from other sessions.

The rows live in 8 kB pages. If the page is already in shared buffers (PostgreSQL's memory cache) it is read straight from memory. If not, it is read from disk into the cache first, which is much slower. Finally the matching rows are sent back to you.

A plain read does not change your data and does not wait for writers: readers and writers do not block each other in PostgreSQL.

For DBAs and operators

Step by step

Protocol: with the simple query protocol the client sends one Query message; drivers using prepared statements send Parse/Bind/Execute/Sync instead (see wire protocol).
Parse, analyze, rewrite, plan: names are resolved through the system catalogs and search_path, views are expanded by the rewriter, and the planner uses table statistics and the cost settings to choose a plan (see query lifecycle).
Snapshot: built from the proc array. In READ COMMITTED (the default) each statement gets a fresh snapshot; in REPEATABLE READ and SERIALIZABLE the whole transaction uses the first one.
Lock: the table (and any index used) holds an AccessShareLock, taken already while the statement is parsed and planned. It conflicts only with AccessExclusiveLock (for example many ALTER TABLE forms, DROP, TRUNCATE, VACUUM FULL), so a SELECT waits only for those.
Buffer lookup: the executor asks the buffer manager for each page it needs. A hit pins the buffer in shared memory. A miss takes a free buffer if there is one, otherwise chooses a victim with the clock-sweep algorithm (writing it out first if it is dirty), then reads the 8 kB page. The read may still be served by the operating system page cache rather than the disk. In PostgreSQL 18, sequential scans, bitmap heap scans and VACUUM can issue these reads through the new asynchronous I/O subsystem (io_method).
Visibility: for each tuple the backend compares xmin/xmax with the snapshot. If the tuple's hint bits do not yet say whether those transactions committed, it looks the answer up in pg_xact (CLOG) and sets the hint bits on the page.
Results: rows go back as DataRow messages, followed by CommandComplete (SELECT 1) and ReadyForQuery.
End: in autocommit mode the transaction ends right away; locks and the snapshot are released.

Can a SELECT write?

Yes, a little. Setting hint bits dirties the page, and with data checksums enabled (the default for clusters created by initdb in PostgreSQL 18) or wal_log_hints = on, the first such change after a checkpoint writes a full-page image to WAL, shown by pg_waldump as FPI_FOR_HINT. A SELECT can also prune dead row versions from a page it visits (PRUNE_ON_ACCESS). Reads on a hot standby never generate WAL.

See it yourself

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM acc WHERE id = 3;
--  Buffers: shared hit=2          ← found in shared buffers
--  Buffers: shared read=2         ← had to be read in

SELECT locktype, relation::regclass, mode
  FROM pg_locks WHERE pid = pg_backend_pid();

For developers: inside the source

Call flow

exec_simple_query()                        tcop/postgres.c
  pg_parse_query() → raw_parser()             parser/gram.y
  parse_analyze_fixedparams()                 parser/analyze.c
    addRangeTableEntry() → parserOpenTable(…, AccessShareLock)
  QueryRewrite()                              rewrite/rewriteHandler.c
  pg_plan_query() → planner() → standard_planner()
    get_relation_info() opens the indexes (same lock mode)
  PortalStart(): PushActiveSnapshot(GetTransactionSnapshot())
                → ExecutorStart()
  PortalRun() → ExecutorRun() → ExecIndexScan() → index_getnext_slot()
    heapam_index_fetch_tuple() → ReleaseAndReadBuffer()
      → heap_hot_search_buffer() → HeapTupleSatisfiesVisibility()
  printtup() sends DataRow; EndCommand(); ReadyForQuery()

Key pieces

Snapshot: snapmgr.c GetTransactionSnapshot() calls GetSnapshotData(), which reads the proc array under ProcArrayLock (shared) and fills xmin, xmax and the xip[] array of in-progress XIDs. Analysis and planning use their own snapshot; the one the executor uses is taken in PortalStart().
Locks: relation locks go through LockRelationOid() → LockAcquire() in lock.c. Weak modes such as AccessShareLock normally use the per-backend fast path (FastPathGrantRelationLock()) and never touch the shared lock table.
Buffer lookup: BufferAlloc() hashes the BufferTag, takes the buffer-mapping partition LWLock in shared mode and calls BufTableLookup(). A hit is PinBuffer(); a miss goes to GetVictimBuffer() → StrategyGetBuffer() (clock sweep via ClockSweepTick()). Reads are issued through StartReadBuffer()/WaitReadBuffers(), and in 18 scans can batch them with read_stream.c.
Visibility: HeapTupleSatisfiesMVCC() checks xmin/xmax with XidInMVCCSnapshot() and TransactionIdDidCommit() (pg_xact), then SetHintBits() → MarkBufferDirtyHint(), which calls XLogSaveBufferForHint() (the FPI_FOR_HINT record) when checksums or wal_log_hints require it.
Pruning on read: heap_page_prune_opt() is called when a page is read and only acts if pd_prune_xid is old enough and the page is nearly full.

Observe and hook

pageinspect (heap_page_items() shows t_infomask hint bits), pg_buffercache, EXPLAIN (ANALYZE, BUFFERS), trace_locks (only in builds with LOCK_DEBUG).
READMEs: storage/buffer/README, storage/aio/README.md, parser/README.
Hooks along this path: post_parse_analyze_hook, planner_hook, ExecutorStart_hook, ExecutorRun_hook, ExecutorFinish_hook, ExecutorEnd_hook (see auto_explain and pg_stat_statements).

Parameters that shape this

shared_buffers default_transaction_isolation statement_timeout effective_cache_size random_page_cost io_method track_io_timing