Reading the Internals of PostgreSQL: Database Cluster, Databases, and Tables
6 hours ago
- #TOAST Mechanism
- #PostgreSQL Internals
- #Database Storage
- PostgreSQL defines a database cluster as a group of databases managed by a single PostgreSQL instance, not multiple servers.
- Logical structure involves OIDs (object identifiers) for databases and objects, stored in system catalogs like pg_database and pg_class.
- Physical storage uses a data directory (PGDATA) with subdirectories for each database (named by OID), and tables/indexes are stored as files.
- Tables and indexes have relfilenode identifiers for physical files, which can change after operations like VACUUM FULL.
- Tablespaces allow storing database files in custom directories, with symlinks in pg_tblspc/ for mapping.
- Heap tables store data in 8KB pages with headers, line pointers, and tuples; pageinspect extension helps examine page internals.
- TOAST (The Oversized-Attribute Storage Technique) handles large values by storing them externally in TOAST tables, with pointers in the main table.
- Writing tuples involves updating page headers and line pointers, with alignment considerations; reading uses sequential scans or index scans (e.g., B-tree).