All quizzesHard
MVCC, WAL & pgvector — Series 3
Preview — 3 of 10 questions
Beyond HNSW, pgvector also supports an IVFFlat index type. What's the trade-off between the two?
AIVFFlat clusters vectors into a fixed number of lists (lists parameter) at index build time and, at query time, searches only the nearest cluster(s) — faster to build and smaller than HNSW, but generally lower recall for a given speed, and its clustering is essentially frozen at build time (it doesn't adapt well to a table that keeps growing without periodically rebuilding). HNSW builds a multi-layer navigable graph — slower and more memory-hungry to build, but typically better recall/speed trade-offs at query time and no equivalent "stale clustering" degradation as data grows
BThey're functionally identical; IVFFlat is simply an older, deprecated name for the same algorithm as HNSW
CIVFFlat only works with 2-dimensional vectors; HNSW supports any dimensionality
DIVFFlat guarantees exact nearest-neighbor results; HNSW is always approximate
What does the full_page_writes setting (on by default) protect against, and why does it specifically matter right after a CHECKPOINT?
AIt compresses WAL records to save disk space, unrelated to crash safety
BIt only applies to index pages, never to table (heap) pages
CDisk writes happen in units (often 8KB PostgreSQL pages, but the underlying storage's actual atomic write unit can be smaller) — a crash mid-write can leave a "torn" page, partially old and partially new data. full_page_writes protects against this by writing the entire page image to WAL the first time that page is modified after each checkpoint, so recovery can restore a known-good full copy of the page before replaying finer-grained changes on top, rather than trying to replay incremental changes against a potentially torn page
DIt's purely a performance optimization with no relationship to crash recovery
Can a PostgreSQL standby stream WAL from another standby instead of directly from the primary?
AYes — this is called cascading replication: a standby can itself act as a source of WAL for further downstream standbys, reducing the number of connections (and network/replication overhead) the primary has to serve directly, particularly useful for standbys in a geographically distant region that would otherwise each independently pull WAL across a long-distance link from the primary
BNo — every standby must connect directly to the primary; PostgreSQL has no support for chained replication topologies
CCascading is only possible with logical replication, never with physical streaming replication
DA cascading standby can only serve read queries but cannot forward WAL to anything else
Sign up free to play
Answer all 10 questions (7 more), see explanations for every answer, and track your score.