Postgres

For ordinary buffered relation I/O, PostgreSQL’s shared buffers sit above the operating system’s file cache. A shared-buffer hit avoids another read through that lower layer; a shared-buffer miss can still be served from memory by the operating system. The same file data can occupy both caches, so this is not a promise that PostgreSQL and the kernel share one physical copy. The mmap shared-memory settings concern shared regions, not a claim that ordinary table files are directly memory-mapped. ...

PostgreSQL hash indexes narrow an equality search using a hash code, then verify the original value in the table. A hash match is a candidate, not proof of equality. Two different values can have the same code, and different codes can also map to the same bucket. Neither collisions nor shared buckets imply incorrect query results when the normal recheck is performed. ...

LATERAL lets a FROM item refer to columns from preceding items. It is useful when each parent needs a small, differently parameterized result, such as its two newest orders. The query remains one SQL statement and one database round trip, while the lateral expression states which outer values determine each result. It does not promise one particular physical execution strategy. ...

pg_dump exports one PostgreSQL database using a consistent view of transactional data, even while other sessions commit changes. This prevents the ordinary table-by-table copy problem in which related tables reflect different committed states. The snapshot is only part of the backup contract: the archive must also be complete for its intended scope, readable by compatible tools, and successfully restored into a usable database. ...

PostgreSQL offers several meanings of “current time.” A transaction timestamp identifies the beginning of a transaction, a statement timestamp identifies a command’s arrival, and a wall-clock reading samples the current clock during evaluation. Choosing among them changes the meaning of audit fields, expiration checks, and observations made during long-running work. ...

A checkpoint establishes a starting point for PostgreSQL crash recovery. It synchronizes the data-file changes required for that checkpoint and records the corresponding recovery information. WAL must reach durable storage before dependent data pages are written. This ordering allows committed work to survive without forcing every modified table page to disk at each commit, while recovery can replay later changes when necessary. ...

After a large deletion, a PostgreSQL table may retain a large file even when only a small fraction of its rows remain. Ordinary vacuum makes obsolete space reusable; VACUUM FULL rewrites the surviving contents into a new file and rebuilds indexes. That stronger reclamation has a different lock and capacity budget, so it belongs to planned maintenance rather than routine reaction to a large size number. ...

Declarative partitioning lets PostgreSQL expose one logical table while storing its rows in separate partitions. The parent routes writes and provides a common query target; the physical partitions hold the data. Its strongest benefits often concern lifecycle management and selective access, but the choice also affects keys, query shape, and operational locking. ...