more postgres content
· 1 year ago
8fab5d346d7cb2f0b55a4bbe22cb7efbada88076
Parent:
206b4ba75
1 file changed +1132 −524
- postgresql.html +1132 −524
Diff
--- a/postgresql.html +++ b/postgresql.html @@ -6,22 +6,22 @@ <title>PostgreSQL Power User Cheatsheet - For DBAs & Developers</title> <!-- SEO Meta Description --> - <meta name="description" content="A comprehensive PostgreSQL cheatsheet for DBAs and Developers covering architecture, unique features, performance tuning, indexing, extensibility, replication, security, and key terminology/quirks."> + <meta name="description" content="A comprehensive PostgreSQL cheatsheet for DBAs and Developers covering architecture, unique features, performance tuning, indexing, extensibility, replication, security, partitioning, and key terminology/quirks."> <!-- Canonical URL (Update if hosted) --> - <link rel="canonical" href="https://cheatsheets.davidveksler.com/postgresql.html"> + <link rel="canonical" href="https://cheatsheets.davidveksler.com/postgresql.html"> <!-- EXAMPLE URL --> <!-- Social Media Metadata (Add URLs if needed) --> <meta property="og:title" content="PostgreSQL Power User Cheatsheet"> - <meta property="og:description" content="Comprehensive guide for DBAs & Developers on PostgreSQL features, performance, architecture, and quirks."> + <meta property="og:description" content="Comprehensive guide for DBAs & Developers on PostgreSQL features, performance, architecture, partitioning, security, and quirks."> <meta property="og:type" content="article"> - <meta property="og:url" content="https://cheatsheets.davidveksler.com/postgresql.html"> + <meta property="og:url" content="https://cheatsheets.davidveksler.com/postgresql.html"> <!-- EXAMPLE URL --> <!-- <meta property="og:image" content="https://cheatsheets.davidveksler.com/images/postgres-cheatsheet.png"> --> <!-- <meta property="og:image:alt" content="PostgreSQL logo with sections on performance, indexing, and extensions."> --> <meta name="twitter:card" content="summary_large_image"> <meta name="twitter:title" content="PostgreSQL Power User Cheatsheet"> - <meta name="twitter:description" content="Comprehensive guide for DBAs & Developers on PostgreSQL features, performance, architecture, and quirks."> + <meta name="twitter:description" content="Comprehensive guide for DBAs & Developers on PostgreSQL features, performance, architecture, partitioning, security, and quirks."> <!-- <meta name="twitter:image" content="https://cheatsheets.davidveksler.com/images/postgres-cheatsheet.png"> --> <!-- <meta name="twitter:image:alt" content="PostgreSQL logo with sections on performance, indexing, and extensions."> --> @@ -48,6 +48,7 @@ --pg-color-datatype: #00838f; /* Teal */ --pg-color-indexing: #3f7cac; /* Medium Blue */ --pg-color-sql: #1565c0; /* Stronger Blue */ + --pg-color-partitioning: #0d47a1; /* Darker Blue for Partitioning */ --pg-color-concurrency: #4682b4; /* Steel Blue */ --pg-color-performance: #d32f2f; /* RED - Warning/Action Needed */ --pg-color-extensions: #7b1fa2; /* Purple */ @@ -160,7 +161,7 @@ opacity: 1; } - /* --- Dimming Logic (Copied from previous examples) --- */ + /* --- Dimming Logic --- */ #main-container.is-dimmed .schema-container:not(.is-highlighted-section) .info-card { opacity: 0.4; } @@ -279,6 +280,7 @@ .cat-datatype { --pg-category-color: var(--pg-color-datatype); } .cat-indexing { --pg-category-color: var(--pg-color-indexing); } .cat-sql { --pg-category-color: var(--pg-color-sql); } + .cat-partitioning { --pg-category-color: var(--pg-color-partitioning); } .cat-concurrency { --pg-category-color: var(--pg-color-concurrency); } .cat-performance { --pg-category-color: var(--pg-color-performance); } .cat-extensions { --pg-category-color: var(--pg-color-extensions); } @@ -289,29 +291,28 @@ .cat-quirks { --pg-category-color: var(--pg-color-quirks); } /* Ensure specific types inherit section color if not overridden */ - .type-process, .type-memory, .type-storage, .type-wal, .type-mvcc, .type-txid, .type-terminology { --pg-category-color: var(--pg-color-concept); } + .type-process, .type-memory, .type-storage, .type-wal, .type-mvcc, .type-txid, .type-terminology, .type-catalogs { --pg-category-color: var(--pg-color-concept); } .type-jsonb, .type-array, .type-range, .type-enum, .type-misc-types { --pg-category-color: var(--pg-color-datatype); } - .type-btree, .type-hash, .type-gist, .type-gin, .type-brin, .type-index-features { --pg-category-color: var(--pg-color-indexing); } - .type-cte, .type-window, .type-lateral, .type-distinct-on, .type-upsert, .type-dml { --pg-category-color: var(--pg-color-sql); } + .type-btree, .type-hash, .type-gist, .type-gin, .type-brin, .type-index-features, .type-reindex { --pg-category-color: var(--pg-color-indexing); } + .type-cte, .type-window, .type-lateral, .type-distinct-on, .type-upsert, .type-dml, .type-aggregate-filter { --pg-category-color: var(--pg-color-sql); } + .type-partitioning { --pg-category-color: var(--pg-color-partitioning); } .type-isolation, .type-locking { --pg-category-color: var(--pg-color-concurrency); } - .type-vacuum, .type-analyze, .type-explain, .type-stats, .type-config { --pg-category-color: var(--pg-color-performance); } - .type-pl, .type-extensions, .type-fdw { --pg-category-color: var(--pg-color-extensions); } + .type-vacuum, .type-analyze, .type-explain, .type-stats, .type-config, .type-bloat, .type-os-tuning, .type-anti-patterns { --pg-category-color: var(--pg-color-performance); } + .type-pl, .type-extensions, .type-fdw, .type-triggers { --pg-category-color: var(--pg-color-extensions); } .type-streaming-rep, .type-logical-rep, .type-ha-tools { --pg-category-color: var(--pg-color-replication); } - .type-pgdump, .type-physical-backup, .type-pitr { --pg-category-color: var(--pg-color-backup); } - .type-auth, .type-permissions, .type-ssl { --pg-category-color: var(--pg-color-security); } - .type-psql, .type-gui { --pg-category-color: var(--pg-color-tools); } + .type-pgdump, .type-physical-backup, .type-pitr, .type-backup-tools { --pg-category-color: var(--pg-color-backup); } + .type-auth, .type-permissions, .type-ssl, .type-secdef, .type-enc-at-rest, .type-audit { --pg-category-color: var(--pg-color-security); } + .type-psql, .type-gui, .type-admin-cli { --pg-category-color: var(--pg-color-tools); } .type-case, .type-public, .type-searchpath, .type-pooling, .type-role { --pg-category-color: var(--pg-color-quirks); } /* Override specific types for emphasis */ .type-quirk { --pg-category-color: var(--pg-color-quirks) !important; } /* Ensure quirk color overrides */ .type-perf { --pg-category-color: var(--pg-color-performance) !important; } /* Ensure perf color overrides */ - </style> </head> <body> <header class="page-header"> - <!-- Icon representing PostgreSQL - could use a custom SVG/image if preferred --> <h1><i class="bi bi-database-gear"></i> PostgreSQL Power User Cheatsheet</h1> <p class="lead">A practical guide for Developers and DBAs working with PostgreSQL.</p> </header> @@ -329,10 +330,10 @@ <div class="collapse collapse-content" id="collapseProcess"> <h6>Details</h6> <ul> - <li><strong>Master Process (`postgres`):</strong> Listens for connections, manages shared memory, starts background utility processes (e.g., checkpointer, WAL writer, autovacuum launcher).</li> - <li><strong>Backend Process (`postgres`):</strong> One per connection. Handles parsing, planning, execution, communication with the client. Isolates crashes to a single connection.</li> - <li><strong>Pros:</strong> Robustness, stability (crash in one backend doesn't take down DB).</li> - <li><strong>Cons/Quirk:</strong> Higher connection overhead (memory/CPU per process). Requires external <span class="term"><a href="https://wiki.postgresql.org/wiki/Pooling" target="_blank">connection poolers</a></span> (e.g., <a href="https://www.pgbouncer.org/" target="_blank">PgBouncer</a>, <a href="https://www.pgpool.net/mediawiki/index.php/Main_Page" target="_blank">Pgpool-II</a>) for applications with many short-lived connections (like typical web apps) to avoid resource exhaustion.</li> + <li>Master Process (`postgres`): Listens for connections, manages shared memory, starts background utility processes (e.g., checkpointer, WAL writer, autovacuum launcher).</li> + <li>Backend Process (`postgres`): One per connection. Handles parsing, planning, execution, communication with the client. Isolates crashes to a single connection.</li> + <li>Pros: Robustness, stability (crash in one backend doesn't take down DB).</li> + <li>Cons/Quirk: Higher connection overhead (memory/CPU per process). Requires external <span class="term"><a href="https://wiki.postgresql.org/wiki/Pooling" target="_blank">connection poolers</a></span> (e.g., <a href="https://www.pgbouncer.org/" target="_blank">PgBouncer</a>, <a href="https://www.pgpool.net/mediawiki/index.php/Main_Page" target="_blank">Pgpool-II</a>) for applications with many short-lived connections (like typical web apps) to avoid resource exhaustion.</li> </ul> </div> </div> @@ -345,19 +346,19 @@ <div class="collapse collapse-content" id="collapseMemory"> <h6>Shared Memory (Server-wide)</h6> <ul> - <li><strong>`shared_buffers`</strong>: PostgreSQL's primary data cache. Stores frequently accessed table/index blocks. Typically set to ~25% of system RAM (up to a point, depends on workload/OS). <a href="https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-SHARED-BUFFERS" target="_blank">[docs]</a></li> - <li><strong>WAL Buffers</strong>: Buffers Write-Ahead Log records before writing to disk. Usually auto-tuned (`-1`), small relative to `shared_buffers`. <a href="https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-WAL-BUFFERS" target="_blank">[docs]</a></li> + <li>`shared_buffers`: PostgreSQL's primary data cache. Stores frequently accessed table/index blocks. Typically set to ~25% of system RAM (up to a point, depends on workload/OS). <a href="https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-SHARED-BUFFERS" target="_blank">[docs]</a></li> + <li>WAL Buffers: Buffers Write-Ahead Log records before writing to disk. Usually auto-tuned (`-1`), small relative to `shared_buffers`. <a href="https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-WAL-BUFFERS" target="_blank">[docs]</a></li> </ul> <h6>Per-Backend/Operation Memory</h6> <ul> - <li><strong>`work_mem`</strong>: Memory used by *each* sort operation (ORDER BY, DISTINCT), hash join, hash aggregation. Multiple operations within one query can each use `work_mem`. Set carefully to avoid OOM errors; too low causes disk spills. <a href="https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM" target="_blank">[docs]</a></li> - <li><strong>`maintenance_work_mem`</strong>: Memory used for maintenance tasks like `VACUUM`, `CREATE INDEX`, `ALTER TABLE ADD FOREIGN KEY`. Can be set much higher than `work_mem` as fewer such operations run concurrently. <a href="https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM" target="_blank">[docs]</a></li> - <li><strong>`temp_buffers`</strong>: Caches temporary tables. <a href="https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-TEMP-BUFFERS" target="_blank">[docs]</a></li> + <li>`work_mem`: Memory used by *each* sort operation (ORDER BY, DISTINCT), hash join, hash aggregation. Multiple operations within one query can each use `work_mem`. Set carefully to avoid OOM errors; too low causes disk spills. <a href="https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM" target="_blank">[docs]</a></li> + <li>`maintenance_work_mem`: Memory used for maintenance tasks like `VACUUM`, `CREATE INDEX`, `ALTER TABLE ADD FOREIGN KEY`. Can be set much higher than `work_mem` as fewer such operations run concurrently. <a href="https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM" target="_blank">[docs]</a></li> + <li>`temp_buffers`: Caches temporary tables. <a href="https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-TEMP-BUFFERS" target="_blank">[docs]</a></li> </ul> <h6>Other</h6> <ul> - <li><strong>Commit Log (CLOG) Buffers</strong>: Caches transaction status.</li> - <li><strong>OS Cache</strong>: PostgreSQL relies heavily on the operating system's file cache. <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-EFFECTIVE-CACHE-SIZE" target="_blank">`effective_cache_size`</a></span> tells the planner how much memory is likely available in OS + PG cache.</li> + <li>Commit Log (CLOG/pg_xact) Buffers: Caches transaction status.</li> + <li>OS Cache: PostgreSQL relies heavily on the operating system's file cache. <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-EFFECTIVE-CACHE-SIZE" target="_blank">`effective_cache_size`</a></span> tells the planner how much memory is likely available in OS + PG cache.</li> </ul> </div> </div> @@ -370,8 +371,8 @@ <div class="collapse collapse-content" id="collapseStorage"> <h6>File Layout</h6> <ul> - <li>Data directory (<a href="https://www.postgresql.org/docs/current/storage-file-layout.html" target="_blank">`PGDATA`</a>) contains global config, WAL, per-database directories.</li> - <li>Each database has its own directory (named by its OID).</li> + <li>Data directory (<a href="https://www.postgresql.org/docs/current/storage-file-layout.html" target="_blank">`PGDATA`</a>) contains global config, WAL (`pg_wal`), subtransaction status (`pg_subtrans`), transaction status (`pg_xact`), per-database directories, etc.</li> + <li>Each database has its own directory (named by its OID) within `PGDATA/base/`.</li> <li>Each table and index (<span class="term">relation</span>) is stored in one or more files (named by its filenode OID), typically segmented into 1GB files (`relname.1`, `relname.2`...).</li> </ul> <h6>Tablespaces</h6> @@ -385,7 +386,7 @@ <ul> <li>Handles storage of large column values that don't fit within a standard data block (~8KB). <a href="https://www.postgresql.org/docs/current/storage-toast.html" target="_blank">[docs]</a></li> <li>Automatically splits large values into chunks stored in a separate TOAST table associated with the main table.</li> - <li>Can apply compression to TOASTed values.</li> + <li>Can apply compression (default `pglz`, or `lz4` since v14) to TOASTed values.</li> <li>Transparent to the user but affects performance for very large fields. Contributes to bloat.</li> <li>Control via `ALTER TABLE ... SET STORAGE {PLAIN|EXTERNAL|EXTENDED|MAIN}`.</li> </ul> @@ -395,7 +396,7 @@ <div class="col-lg-4 col-md-6"> <div class="info-card type-wal" id="card-wal"> <div class="card-body"><h5><i class="bi bi-journal-text"></i> Write-Ahead Logging (WAL)</h5> - <div class="card-content-wrapper"><p class="summary">Ensures durability by logging changes *before* they are written to data files. Essential for recovery and replication. <a href="https://www.postgresql.org/docs/current/wal.html" target="_blank">[docs]</a></p> + <div class="card-content-wrapper"><p class="summary">Ensures durability by logging changes *before* they are written to data files. Essential for recovery, replication, and PITR. <a href="https://www.postgresql.org/docs/current/wal.html" target="_blank">[docs]</a></p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseWAL" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseWAL"> <h6>Mechanism</h6> @@ -405,16 +406,16 @@ </ul> <h6>Purpose</h6> <ul> - <li><strong>Crash Recovery:</strong> On startup after a crash, Postgres replays WAL records since the last checkpoint to restore the database to a consistent state.</li> - <li><strong>Durability Guarantee:</strong> A transaction commit is confirmed only after its WAL records are flushed to disk.</li> - <li><strong>Replication:</strong> Standby servers continuously stream and apply WAL records from the primary.</li> - <li><strong>Point-in-Time Recovery (PITR):</strong> Requires continuous archiving of WAL segment files. <a href="https://www.postgresql.org/docs/current/continuous-archiving.html" target="_blank">[docs]</a></li> + <li>Crash Recovery: On startup after a crash, Postgres replays WAL records since the last checkpoint to restore the database to a consistent state.</li> + <li>Durability Guarantee: A transaction commit is confirmed only after its WAL records are flushed to disk (controlled by <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT" target="_blank">`synchronous_commit`</a></span>).</li> + <li>Replication: Standby servers continuously stream and apply WAL records from the primary (Streaming Rep) or decode WAL for logical changes (Logical Rep).</li> + <li>Point-in-Time Recovery (PITR): Requires continuous archiving of WAL segment files. <a href="https://www.postgresql.org/docs/current/continuous-archiving.html" target="_blank">[docs]</a></li> </ul> <h6>Key Concepts</h6> <ul> - <li><strong>WAL Segments:</strong> Files (default 16MB) containing WAL records. Sequentially numbered.</li> - <li><strong><a href="https://www.postgresql.org/docs/current/wal-configuration.html" target="_blank">Checkpoints</a>:</strong> Points in the WAL stream where all data file changes prior to the checkpoint are guaranteed to have been flushed to disk. Limits recovery time. Triggered by time or amount of WAL generated.</li> - <li><strong><a href="https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-ARCHIVING-WAL" target="_blank">WAL Archiving</a>:</strong> Process of copying completed WAL segment files to a safe location (<span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-ARCHIVE-COMMAND" target="_blank">`archive_command`</a></span>). Essential for PITR and some replication setups.</li> + <li>WAL Segments: Files (default 16MB) containing WAL records. Sequentially numbered. Located in `pg_wal`.</li> + <li><a href="https://www.postgresql.org/docs/current/wal-configuration.html" target="_blank">Checkpoints</a>: Points in the WAL stream where all data file changes prior to the checkpoint are guaranteed to have been flushed to disk. Limits recovery time. Triggered by time (<span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-CHECKPOINT-TIMEOUT" target="_blank">`checkpoint_timeout`</a></span>), amount of WAL generated (<span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-MAX-WAL-SIZE" target="_blank">`max_wal_size`</a></span>), or manually (`CHECKPOINT` command). Managed by the <span class="term">Checkpointer</span> process. <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-CHECKPOINT-COMPLETION-TARGET" target="_blank">`checkpoint_completion_target`</a></span> spreads I/O.</li> + <li><a href="https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-ARCHIVING-WAL" target="_blank">WAL Archiving</a>: Process of copying completed WAL segment files to a safe location (<span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-ARCHIVE-COMMAND" target="_blank">`archive_command`</a></span>). Essential for PITR and some replication setups. Requires <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-ARCHIVE-MODE" target="_blank">`archive_mode = on`</a></span>.</li> </ul> </div> </div> @@ -422,52 +423,63 @@ <div class="col-lg-4 col-md-6"> <div class="info-card type-mvcc" id="card-mvcc"> <div class="card-body"><h5><i class="bi bi-layers"></i> MVCC</h5> - <div class="card-content-wrapper"><p class="summary">Multi-Version Concurrency Control. Updates create new row versions instead of overwriting. Readers don't block writers. <a href="https://www.postgresql.org/docs/current/mvcc.html" target="_blank">[docs]</a></p> + <div class="card-content-wrapper"><p class="summary">Multi-Version Concurrency Control. Updates create new row versions instead of overwriting. Readers don't block writers. Leads to <span class="term">bloat</span>. <a href="https://www.postgresql.org/docs/current/mvcc.html" target="_blank">[docs]</a></p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseMVCC" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseMVCC"> <h6>How it Works</h6> <ul> - <li>When a row is updated or deleted, Postgres marks the old version as "expired" by setting transaction metadata (`xmax`) and inserts a new version (for UPDATE) with its own transaction metadata (`xmin`).</li> - <li>Each transaction gets a snapshot of the database when it starts (in `READ COMMITTED`) or at its first query (in `REPEATABLE READ`/`SERIALIZABLE`).</li> - <li>A transaction can only "see" row versions that were committed *before* its snapshot began and are not expired *relative to its snapshot*.</li> + <li>Each row version has system columns: `xmin` (inserting transaction ID), `xmax` (deleting/locking transaction ID), `cmin`/`cmax` (command ID within tx), `ctid` (physical location).</li> + <li>When a row is updated or deleted, Postgres marks the old version as "expired" by setting `xmax` and inserts a new version (for UPDATE) with its own `xmin`.</li> + <li>Each transaction gets a snapshot of the database (list of visible transaction IDs) when it starts (in `READ COMMITTED` for each statement, `REPEATABLE READ`/`SERIALIZABLE` for the transaction). <a href="https://www.postgresql.org/docs/current/mvcc-intro.html#MVCC-VISIBILITY-RULES" target="_blank">[visibility]</a></li> + <li>A transaction can only "see" row versions where `xmin` is committed and visible to its snapshot, AND (`xmax` is not set OR `xmax` is aborted OR `xmax` is not visible to its snapshot).</li> <li>Expired row versions (<span class="term">dead tuples</span>) physically remain until cleaned up by <span class="term"><a href="https://www.postgresql.org/docs/current/sql-vacuum.html" target="_blank">`VACUUM`</a></span>.</li> </ul> <h6>Benefits</h6> <ul> <li>High concurrency: Readers don't block writers, and writers don't block readers (for data access).</li> - <li>Read consistency: Queries see a consistent snapshot of the data.</li> + <li>Read consistency: Queries see a consistent snapshot of the data as defined by the isolation level.</li> </ul> <h6>Consequences (Quirks)</h6> <ul> - <li><strong>Bloat:</strong> Dead tuples accumulate, consuming disk space and potentially slowing down scans. Requires regular <span class="term"><a href="https://www.postgresql.org/docs/current/routine-vacuuming.html" target="_blank">`VACUUM`</a></span>.</li> - <li><strong>UPDATEs are expensive:</strong> An UPDATE is internally like a DELETE + INSERT, requiring new tuple creation and index entries.</li> - <li><strong><a href="https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND" target="_blank">Transaction ID Wraparound</a>:</strong> Requires freezing via `VACUUM` (see Quirks section).</li> + <li>Bloat: Dead tuples accumulate, consuming disk space and potentially slowing down scans. Requires regular <span class="term"><a href="https://www.postgresql.org/docs/current/routine-vacuuming.html" target="_blank">`VACUUM`</a></span>.</li> + <li>UPDATEs are expensive: An UPDATE is internally like a DELETE + INSERT, requiring new tuple creation and index entries for all indexes on the table. (<span class="term"><a href="https://www.postgresql.org/docs/current/storage-hot.html" target="_blank">HOT updates</a></span> can mitigate index churn if no indexed columns are changed and space exists on the same page).</li> + <li><a href="https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND" target="_blank">Transaction ID Wraparound</a>: Requires freezing via `VACUUM` (see Quirks section).</li> </ul> </div> </div> </div> <div class="col-lg-4 col-md-6"> - <div class="info-card type-terminology" id="card-terminology"> - <div class="card-body"><h5><i class="bi bi-translate"></i> PG Terminology</h5> - <div class="card-content-wrapper"><p class="summary">Key terms: <span class="term"><a href="https://www.postgresql.org/docs/current/glossary.html#GLOSSARY-RELATION" target="_blank">Relation</a></span> (Table/Index), <span class="term"><a href="https://www.postgresql.org/docs/current/glossary.html#GLOSSARY-SCHEMA" target="_blank">Schema</a></span> (Namespace), <span class="term"><a href="https://www.postgresql.org/docs/current/glossary.html#GLOSSARY-ROLE" target="_blank">Role</a></span> (User/Group), <span class="term"><a href="https://www.postgresql.org/docs/current/glossary.html#GLOSSARY-HEAP" target="_blank">Heap</a></span> (Table Data), <span class="term"><a href="https://www.postgresql.org/docs/current/glossary.html#GLOSSARY-TUPLE" target="_blank">Tuple</a></span> (Row).</p> - <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseTerms" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> - <div class="collapse collapse-content" id="collapseTerms"> - <ul> - <li><strong>Relation:</strong> General term for any table-like object stored on disk. Most commonly refers to Tables and Indexes. Views are also sometimes called relations but are stored differently (as rules/queries). <a href="https://www.postgresql.org/docs/current/glossary.html#GLOSSARY-RELATION" target="_blank">[glossary]</a></li> - <li><strong>Schema:</strong> A namespace within a database containing objects like tables, views, functions, etc. Allows organizing objects and preventing naming conflicts. Distinct from the concept of "table schema" (column definitions). Default is `public`. <a href="https://www.postgresql.org/docs/current/ddl-schemas.html" target="_blank">[docs]</a></li> - <li><strong>Role:</strong> Unified concept encompassing both Users and Groups. A role can log in (if `LOGIN` privilege is granted), own objects, and be a member of other roles (inheritance). Use `CREATE ROLE`. <a href="https://www.postgresql.org/docs/current/database-roles.html" target="_blank">[docs]</a></li> - <li><strong>Heap:</strong> The main storage structure for a table's data (rows/tuples). <a href="https://www.postgresql.org/docs/current/glossary.html#GLOSSARY-HEAP" target="_blank">[glossary]</a></li> - <li><strong>Tuple:</strong> A physical row version stored on disk within a heap block. <a href="https://www.postgresql.org/docs/current/glossary.html#GLOSSARY-TUPLE" target="_blank">[glossary]</a></li> - <li><strong>OID (Object Identifier):</strong> Internal unique number assigned to most database objects (databases, tables, rows - optional, schemas, etc.). Often used in system catalogs. <a href="https://www.postgresql.org/docs/current/datatype-oid.html" target="_blank">[docs]</a></li> - <li><strong>WAL:</strong> Write-Ahead Log. <a href="https://www.postgresql.org/docs/current/wal.html" target="_blank">[docs]</a></li> - <li><strong>MVCC:</strong> Multi-Version Concurrency Control. <a href="https://www.postgresql.org/docs/current/mvcc.html" target="_blank">[docs]</a></li> - <li><strong>TOAST:</strong> The Oversized Attribute Storage Technique. <a href="https://www.postgresql.org/docs/current/storage-toast.html" target="_blank">[docs]</a></li> - <li><strong>Planner/Optimizer:</strong> Component that determines the most efficient way to execute a query. <a href="https://www.postgresql.org/docs/current/query-path-planning.html" target="_blank">[docs]</a></li> - <li><strong>Executor:</strong> Component that runs the plan generated by the planner. <a href="https://www.postgresql.org/docs/current/executor.html" target="_blank">[docs]</a></li> - </ul> - </div> - </div> - </div> + <div class="info-card type-catalogs" id="card-catalogs"> + <div class="card-body"><h5><i class="bi bi-book"></i> System Catalogs</h5> + <div class="card-content-wrapper"><p class="summary">Internal tables in the <span class="term">`pg_catalog`</span> schema storing metadata about database objects. Query them for introspection. <a href="https://www.postgresql.org/docs/current/catalogs.html" target="_blank">[docs]</a></p> + <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseCatalogs" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> + <div class="collapse collapse-content" id="collapseCatalogs"> + <h6>Purpose</h6> + <p>Hold the schema definitions, configuration, and status information for the database itself. Everything `psql`'s `\d` commands show comes from here.</p> + <h6>Key Catalogs (Examples)</h6> + <ul> + <li><a href="https://www.postgresql.org/docs/current/catalog-pg-class.html" target="_blank">`pg_class`</a>: Information about tables, indexes, sequences, views (relations). Columns: `relname`, `relnamespace` (schema OID), `relkind` (type), `reltuples` (approx rows), `relpages` (approx pages).</li> + <li><a href="https://www.postgresql.org/docs/current/catalog-pg-attribute.html" target="_blank">`pg_attribute`</a>: Information about table columns. Columns: `attrelid` (table OID), `attname`, `atttypid` (type OID), `attnum` (column number), `attnotnull`.</li> + <li><a href="https://www.postgresql.org/docs/current/catalog-pg-namespace.html" target="_blank">`pg_namespace`</a>: Information about schemas. Columns: `nspname`, `nspowner`.</li> + <li><a href="https://www.postgresql.org/docs/current/catalog-pg-index.html" target="_blank">`pg_index`</a>: Information about indexes. Columns: `indexrelid` (index OID), `indrelid` (table OID), `indkey` (indexed column numbers).</li> + <li><a href="https://www.postgresql.org/docs/current/catalog-pg-proc.html" target="_blank">`pg_proc`</a>: Information about functions and procedures.</li> + <li><a href="https://www.postgresql.org/docs/current/catalog-pg-type.html" target="_blank">`pg_type`</a>: Information about data types.</li> + <li><a href="https://www.postgresql.org/docs/current/view-pg-settings.html" target="_blank">`pg_settings`</a>: Current server configuration parameters.</li> + <li><a href="https://www.postgresql.org/docs/current/view-pg-locks.html" target="_blank">`pg_locks`</a>: Information about current locks.</li> + <li><a href="https://www.postgresql.org/docs/current/view-pg-roles.html" target="_blank">`pg_roles`</a>: Information about roles.</li> + </ul> + <h6>Querying</h6> + <p>Join catalogs using OID (Object Identifier) columns. Often complex, consider using higher-level <span class="term"><a href="https://www.postgresql.org/docs/current/information-schema.html" target="_blank">Information Schema</a></span> views (`information_schema.tables`, `information_schema.columns`) for standard metadata, though catalogs offer more detail.</p> + <pre><code>-- Find columns of a table +SELECT a.attname, format_type(a.atttypid, a.atttypmod) AS type +FROM pg_catalog.pg_attribute a +WHERE a.attrelid = 'myschema.mytable'::regclass -- Use regclass cast +AND a.attnum > 0 +AND NOT a.attisdropped +ORDER BY a.attnum;</code></pre> + </div> + </div> + </div> </div> <!-- /.row --> </div> <!-- /.schema-container --> @@ -484,8 +496,8 @@ <div class="collapse collapse-content" id="collapseJsonb"> <h6>JSON vs JSONB</h6> <ul> - <li><strong>`JSON`</strong>: Stores an exact textual copy. Preserves whitespace, key order, duplicate keys. Slower processing.</li> - <li><strong>`JSONB`</strong>: Stores decomposed binary format. More efficient storage (usually), much faster processing. Removes duplicate keys, doesn't preserve whitespace/key order. **Generally preferred.** <a href="https://www.postgresql.org/docs/current/datatype-json.html" target="_blank">[docs]</a></li> + <li>`JSON`: Stores an exact textual copy. Preserves whitespace, key order, duplicate keys. Slower processing.</li> + <li>`JSONB`: Stores decomposed binary format. More efficient storage (usually), much faster processing. Removes duplicate keys, doesn't preserve whitespace/key order. **Generally preferred.** <a href="https://www.postgresql.org/docs/current/datatype-json.html" target="_blank">[docs]</a></li> </ul> <h6>Key Operators</h6> <ul> @@ -584,6 +596,7 @@ <h6>Exclusion Constraints</h6> <ul> <li>Prevent overlapping ranges within a table, often used for scheduling or booking systems. <a href="https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION" target="_blank">[docs]</a></li> + <li>Requires GiST index support for the overlap operator (`&&`).</li> <li><code>ALTER TABLE events ADD CONSTRAINT no_overlapping_times EXCLUDE USING GIST (room_id WITH =, event_times WITH &&);</code></li> </ul> </div> @@ -602,10 +615,10 @@ </ul> <h6>Benefits</h6> <ul> - <li><strong>Type Safety:</strong> Ensures only defined values can be stored.</li> - <li><strong>Readability:</strong> More descriptive than storing magic numbers or strings.</li> - <li><strong>Storage Efficiency:</strong> Stored efficiently internally (typically 4 bytes).</li> - <li><strong>Ordering:</strong> Enum values have an implicit sort order based on definition order.</li> + <li>Type Safety: Ensures only defined values can be stored.</li> + <li>Readability: More descriptive than storing magic numbers or strings.</li> + <li>Storage Efficiency: Stored efficiently internally (typically 4 bytes).</li> + <li>Ordering: Enum values have an implicit sort order based on definition order.</li> </ul> <h6>Usage</h6> <ul> @@ -615,24 +628,28 @@ <h6>Modification</h6> <ul> <li>Add values: `ALTER TYPE primary_color ADD VALUE 'yellow' AFTER 'blue';` (Cannot easily remove or reorder values). <a href="https://www.postgresql.org/docs/current/sql-altertype.html" target="_blank">[docs]</a></li> + <li>Renaming values is possible: `ALTER TYPE ... RENAME VALUE ... TO ...`.</li> </ul> <h6>Comparison to Check Constraints</h6> - <p>Enums are generally preferred over `CHECK (color IN ('red', 'green', 'blue'))` because they are more type-safe, potentially faster, and easier to manage centrally.</p> + <p>Enums are generally preferred over `CHECK (color IN ('red', 'green', 'blue'))` because they are more type-safe, potentially faster, easier to manage centrally, and self-documenting.</p> </div> </div> </div> <div class="col-lg-4 col-md-6"> <div class="info-card type-misc-types" id="card-misc-types"> <div class="card-body"><h5><i class="bi bi-box-seam"></i> Other Notable Types</h5> - <div class="card-content-wrapper"><p class="summary">Includes native <span class="term"><a href="https://www.postgresql.org/docs/current/datatype-uuid.html" target="_blank">UUID</a></span>, <a href="https://www.postgresql.org/docs/current/datatype-geometric.html" target="_blank">Geometric types</a> (<a href="https://postgis.net/" target="_blank">PostGIS</a> foundation), Network types (<span class="term"><a href="https://www.postgresql.org/docs/current/datatype-net-types.html" target="_blank">`inet`</a></span>), and <span class="term"><a href="https://www.postgresql.org/docs/current/hstore.html" target="_blank">Hstore</a></span> (key/value).</p> + <div class="card-content-wrapper"><p class="summary">Includes native <span class="term">UUID</span>, <span class="term">Geometric</span>, <span class="term">Network (`inet`)</span>, <span class="term">`hstore`</span>, <span class="term">`tsvector`/`tsquery`</span>, <span class="term">`DOMAIN`</span>, and <span class="term">Composite Types</span>.</p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseMiscTypes" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseMiscTypes"> <ul> - <li><strong>`UUID`</strong>: Stores Universally Unique Identifiers. Preferred over storing as `text` for efficiency and semantics. Generate using <span class="term"><a href="https://www.postgresql.org/docs/current/functions-uuid.html" target="_blank">`gen_random_uuid()`</a></span> (requires <span class="term"><a href="https://www.postgresql.org/docs/current/pgcrypto.html" target="_blank">`pgcrypto`</a></span> extension) or client-side. <a href="https://www.postgresql.org/docs/current/datatype-uuid.html" target="_blank">[docs]</a></li> - <li><strong>Geometric Types (`point`, `line`, `lseg`, `box`, `path`, `polygon`, `circle`)</strong>: Basic 2D geometric types. Indexed using GiST. Foundation for the powerful <a href="https://postgis.net/" target="_blank">PostGIS</a> extension. <a href="https://www.postgresql.org/docs/current/datatype-geometric.html" target="_blank">[docs]</a></li> - <li><strong>Network Address Types (`cidr`, `inet`, `macaddr`, `macaddr8`)</strong>: Store and query IP addresses/networks and MAC addresses. Supports subnet containment operators (`>>`, `<<`). Indexable. <a href="https://www.postgresql.org/docs/current/datatype-net-types.html" target="_blank">[docs]</a></li> - <li><strong><a href="https://www.postgresql.org/docs/current/hstore.html" target="_blank">`Hstore`</a> Extension (`CREATE EXTENSION hstore`)</strong>: Simple key-value store within a column. Keys/values are strings. Indexed using GIN or GiST. Often superseded by JSONB now, but still useful for simpler cases or legacy systems. Operators: `->`, `?`, `@>`.</li> - <li><strong><a href="https://www.postgresql.org/docs/current/datatype-datetime.html" target="_blank">`TIMESTAMP WITH TIME ZONE` (`timestamptz`)</a> vs `TIMESTAMP WITHOUT TIME ZONE` (`timestamp`)</strong>: + <li>`UUID`: Stores Universally Unique Identifiers. Preferred over storing as `text` for efficiency and semantics. Generate using <span class="term"><a href="https://www.postgresql.org/docs/current/functions-uuid.html" target="_blank">`gen_random_uuid()`</a></span> (requires <span class="term"><a href="https://www.postgresql.org/docs/current/pgcrypto.html" target="_blank">`pgcrypto`</a></span> extension) or client-side. <a href="https://www.postgresql.org/docs/current/datatype-uuid.html" target="_blank">[docs]</a></li> + <li>Geometric Types (`point`, `line`, `lseg`, `box`, `path`, `polygon`, `circle`): Basic 2D geometric types. Indexed using GiST. Foundation for the powerful <a href="https://postgis.net/" target="_blank">PostGIS</a> extension. <a href="https://www.postgresql.org/docs/current/datatype-geometric.html" target="_blank">[docs]</a></li> + <li>Network Address Types (`cidr`, `inet`, `macaddr`, `macaddr8`): Store and query IP addresses/networks and MAC addresses. Supports subnet containment operators (`>>`, `<<`). Indexable (B-Tree/GiST). <a href="https://www.postgresql.org/docs/current/datatype-net-types.html" target="_blank">[docs]</a></li> + <li><a href="https://www.postgresql.org/docs/current/hstore.html" target="_blank">`Hstore`</a> Extension (`CREATE EXTENSION hstore`): Simple key-value store within a column. Keys/values are strings. Indexed using GIN or GiST. Often superseded by JSONB now, but still useful for simpler cases or legacy systems. Operators: `->`, `?`, `@>`.</li> + <li>Full-Text Search Types (`tsvector`, `tsquery`): Used for storing preprocessed documents (`tsvector`) and search queries (`tsquery`). Used with FTS functions (`to_tsvector`, `to_tsquery`, `ts_rank`) and operators (`@@`). Indexable with GIN or GiST. <a href="https://www.postgresql.org/docs/current/datatype-textsearch.html" target="_blank">[docs]</a></li> + <li><a href="https://www.postgresql.org/docs/current/sql-createdomain.html" target="_blank">`DOMAIN`</a> Types: Create user-defined types based on existing types but with added constraints (`CHECK`, `NOT NULL`). Useful for enforcing data rules centrally. E.g., `CREATE DOMAIN email_address AS text CHECK (value ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');`</li> + <li><a href="https://www.postgresql.org/docs/current/rowtypes.html" target="_blank">Composite Types</a> (`CREATE TYPE name AS (...)`): User-defined types representing the structure of a row or record (a list of field names and their types). Can be used for function arguments, return values, or table columns. E.g., `CREATE TYPE address AS (street text, city text, zip text);`</li> + <li><a href="https://www.postgresql.org/docs/current/datatype-datetime.html" target="_blank">`TIMESTAMP WITH TIME ZONE` (`timestamptz`)</a> vs `TIMESTAMP WITHOUT TIME ZONE` (`timestamp`): <ul><li>`timestamptz`: **Strongly recommended**. Stores UTC timestamp. Converts input to UTC based on session timezone, converts back to session timezone on output. Unambiguous point in time.</li> <li>`timestamp`: Stores literal date/time provided, ignoring timezone. Ambiguous. Avoid unless you have a very specific reason.</li></ul> </li> @@ -665,29 +682,29 @@ <li><code>CREATE INDEX idx_orders_created ON orders (created_at DESC);</code> (For `WHERE created_at > '...' ORDER BY created_at DESC`)</li> </ul> <h6>Considerations</h6> - <p>Effective for high-cardinality columns. Can become large. Index maintenance (bloat) is a factor.</p> + <p>Effective for high-cardinality columns. Can become large. Index maintenance (bloat, see <span class="term">VACUUM</span> and <span class="term">REINDEX</span>) is a factor.</p> </div> </div> </div> <div class="col-lg-4 col-md-6"> <div class="info-card type-gin" id="card-gin"> <div class="card-body"><h5><i class="bi bi-diagram-2"></i> GIN Index</h5> - <div class="card-content-wrapper"><p class="summary">Generalized Inverted Index. Optimized for composite types like <span class="term"><a href="https://www.postgresql.org/docs/current/datatype-json.html" target="_blank">JSONB</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/arrays.html" target="_blank">Arrays</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/hstore.html" target="_blank">Hstore</a></span>, <a href="https://www.postgresql.org/docs/current/textsearch.html" target="_blank">Full-Text Search</a>. Indexes elements *within* values. <a href="https://www.postgresql.org/docs/current/gin.html" target="_blank">[docs]</a></p> + <div class="card-content-wrapper"><p class="summary">Generalized Inverted Index. Optimized for composite types like <span class="term">JSONB</span>, <span class="term">Arrays</span>, <span class="term">Hstore</span>, <span class="term">Full-Text Search</span>. Indexes elements *within* values. <a href="https://www.postgresql.org/docs/current/gin.html" target="_blank">[docs]</a></p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseGin" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseGin"> <h6>Core Concept</h6> <p>Creates an index entry for each unique element (key, array item, lexeme) pointing to the rows (heap TIDs) containing that element. Ideal for "contains" or "exists" style queries. <a href="https://www.postgresql.org/docs/current/gin-intro.html" target="_blank">[intro]</a></p> <h6>Use Cases & Supported Operators</h6> <ul> - <li><strong>Arrays:</strong> Supports `@>` (contains), `<@` (contained by), `&&` (overlaps), `=` (equality). <a href="https://www.postgresql.org/docs/current/gin-builtin-opclasses.html#GIN-BUILTIN-OPCLASSES-ARRAY" target="_blank">[opclasses]</a></li> - <li><strong>JSONB:</strong> Supports `@>`, `<@`, `?` (key exists), `?|` (any key exists), `?&` (all keys exist). Use <span class="term"><a href="https://www.postgresql.org/docs/current/gin-builtin-opclasses.html#GIN-BUILTIN-OPCLASSES-JSONB" target="_blank">`jsonb_ops`</a></span> (default) or <span class="term"><a href="https://www.postgresql.org/docs/current/gin-builtin-opclasses.html#GIN-BUILTIN-OPCLASSES-JSONB" target="_blank">`jsonb_path_ops`</a></span> (optimized for `@>`).</li> - <li><strong>Hstore:</strong> Supports `@>`, `?`, `?|`, `?&`. <a href="https://www.postgresql.org/docs/current/hstore.html#HSTORE-INDEXING" target="_blank">[docs]</a></li> - <li><strong>Full-Text Search (`tsvector`):</strong> Supports `@@` (match) operator. <a href="https://www.postgresql.org/docs/current/textsearch-indexes.html" target="_blank">[docs]</a></li> + <li>Arrays: Supports `@>` (contains), `<@` (contained by), `&&` (overlaps), `=` (equality). <a href="https://www.postgresql.org/docs/current/gin-builtin-opclasses.html#GIN-BUILTIN-OPCLASSES-ARRAY" target="_blank">[opclasses]</a></li> + <li>JSONB: Supports `@>`, `<@`, `?` (key exists), `?|` (any key exists), `?&` (all keys exist). Use <span class="term"><a href="https://www.postgresql.org/docs/current/gin-builtin-opclasses.html#GIN-BUILTIN-OPCLASSES-JSONB" target="_blank">`jsonb_ops`</a></span> (default) or <span class="term"><a href="https://www.postgresql.org/docs/current/gin-builtin-opclasses.html#GIN-BUILTIN-OPCLASSES-JSONB" target="_blank">`jsonb_path_ops`</a></span> (optimized for `@>`).</li> + <li>Hstore: Supports `@>`, `?`, `?|`, `?&`. <a href="https://www.postgresql.org/docs/current/hstore.html#HSTORE-INDEXING" target="_blank">[docs]</a></li> + <li>Full-Text Search (`tsvector`): Supports `@@` (match) operator. <a href="https://www.postgresql.org/docs/current/textsearch-indexes.html" target="_blank">[docs]</a></li> </ul> <h6>Performance</h6> <ul> <li>Very fast lookups for containment/existence queries.</li> - <li>Can be significantly slower to build/update than B-Tree or GiST, as inserting/updating one row might require updating many index entries if the value contains many unique elements. <a href="https://www.postgresql.org/docs/current/gin-implementation.html#GIN-FAST-UPDATE" target="_blank">[fast update]</a></li> + <li>Can be significantly slower to build/update than B-Tree or GiST, as inserting/updating one row might require updating many index entries if the value contains many unique elements. Tuning <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-GIN-PENDING-LIST-LIMIT" target="_blank">`gin_pending_list_limit`</a></span> can help. <a href="https://www.postgresql.org/docs/current/gin-implementation.html#GIN-FAST-UPDATE" target="_blank">[fast update]</a></li> <li>Index size can be large, but often smaller than GiST for the same data.</li> </ul> <h6>Example</h6> @@ -699,17 +716,17 @@ <div class="col-lg-4 col-md-6"> <div class="info-card type-gist" id="card-gist"> <div class="card-body"><h5><i class="bi bi-grid-3x3-gap"></i> GiST Index</h5> - <div class="card-content-wrapper"><p class="summary">Generalized Search Tree. Framework index for complex types: <a href="https://www.postgresql.org/docs/current/datatype-geometric.html" target="_blank">Geometric</a>, <a href="https://www.postgresql.org/docs/current/textsearch.html" target="_blank">Full-Text Search</a>, <a href="https://www.postgresql.org/docs/current/rangetypes.html" target="_blank">Ranges</a>. Handles overlap/containment, nearest-neighbor. <a href="https://www.postgresql.org/docs/current/gist.html" target="_blank">[docs]</a></p> + <div class="card-content-wrapper"><p class="summary">Generalized Search Tree. Framework index for complex types: <span class="term">Geometric</span>, <span class="term">Full-Text Search</span>, <span class="term">Ranges</span>. Handles overlap/containment, nearest-neighbor. <a href="https://www.postgresql.org/docs/current/gist.html" target="_blank">[docs]</a></p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseGist" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseGist"> <h6>Core Concept</h6> <p>A height-balanced tree structure like B-Tree, but generalized to handle complex data types and query operators beyond simple comparison. Uses "lossy" indexing in some cases, meaning the index might return some false positives that need rechecking against the heap. <a href="https://www.postgresql.org/docs/current/gist-intro.html" target="_blank">[intro]</a></p> <h6>Use Cases & Supported Operators</h6> <ul> - <li><strong>Geometric Types (<a href="https://postgis.net/" target="_blank">PostGIS</a>):</strong> Supports spatial operators like intersection (`&&`), containment (`@`, `~`), distance (`<->` for KNN). <a href="https://www.postgresql.org/docs/current/gist-builtin-opclasses.html#GIST-BUILTIN-OPCLASSES-GEOMETRIC" target="_blank">[opclasses]</a></li> - <li><strong>Range Types:</strong> Supports overlap (`&&`), containment (`@>`, `<@`), adjacency (`-|-`), etc. <a href="https://www.postgresql.org/docs/current/gist-builtin-opclasses.html#GIST-BUILTIN-OPCLASSES-RANGE" target="_blank">[opclasses]</a></li> - <li><strong>Full-Text Search (`tsvector`):</strong> Supports `@@` (match) operator. Often faster updates but slower searches than GIN for FTS. <a href="https://www.postgresql.org/docs/current/textsearch-indexes.html" target="_blank">[docs]</a></li> - <li><strong>Other extensions (`hstore`, <span class="term"><a href="https://www.postgresql.org/docs/current/pgtrgm.html" target="_blank">`pg_trgm`</a></span> for fuzzy search):</strong> Can provide GiST support.</li> + <li>Geometric Types (<a href="https://postgis.net/" target="_blank">PostGIS</a>): Supports spatial operators like intersection (`&&`), containment (`@`, `~`), distance (`<->` for KNN). <a href="https://www.postgresql.org/docs/current/gist-builtin-opclasses.html#GIST-BUILTIN-OPCLASSES-GEOMETRIC" target="_blank">[opclasses]</a></li> + <li>Range Types: Supports overlap (`&&`), containment (`@>`, `<@`), adjacency (`-|-`), etc. <a href="https://www.postgresql.org/docs/current/gist-builtin-opclasses.html#GIST-BUILTIN-OPCLASSES-RANGE" target="_blank">[opclasses]</a></li> + <li>Full-Text Search (`tsvector`): Supports `@@` (match) operator. Often faster updates but slower searches than GIN for FTS. <a href="https://www.postgresql.org/docs/current/textsearch-indexes.html" target="_blank">[docs]</a></li> + <li>Other extensions (`hstore`, <span class="term"><a href="https://www.postgresql.org/docs/current/pgtrgm.html" target="_blank">`pg_trgm`</a></span> for fuzzy search): Can provide GiST support.</li> </ul> <h6>Performance</h6> <ul> @@ -730,11 +747,12 @@ <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseBrin" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseBrin"> <h6>Core Concept</h6> - <p>Stores summary information (min/max values) for ranges of physical table blocks (`pages_per_range` setting). During a query, checks the summary to see if blocks *might* contain relevant values, skipping blocks where values are guaranteed to be outside the query range. <a href="https://www.postgresql.org/docs/current/brin-intro.html" target="_blank">[intro]</a></p> + <p>Stores summary information (min/max values, potentially other info depending on opclass) for ranges of physical table blocks (`pages_per_range` setting). During a query, checks the summary to see if blocks *might* contain relevant values, skipping blocks where values are guaranteed to be outside the query range. <a href="https://www.postgresql.org/docs/current/brin-intro.html" target="_blank">[intro]</a></p> <h6>Ideal Scenario</h6> <ul> <li>Very large tables (many GBs/TBs).</li> <li>Column values have a strong linear correlation with their physical storage location. This often happens naturally for append-only tables with a timestamp or sequence column (e.g., log data, IoT measurements).</li> + <li>Queries select relatively large ranges.</li> </ul> <h6>Benefits</h6> <ul> @@ -743,12 +761,12 @@ </ul> <h6>Limitations</h6> <ul> - <li>Only effective if the physical correlation exists. Randomly distributed data or frequently updated tables will not benefit.</li> - <li>Less efficient than B-Tree for highly selective queries.</li> + <li>Only effective if the physical correlation exists. Randomly distributed data or frequently updated tables (causing rows to move physically) will not benefit.</li> + <li>Less efficient than B-Tree for highly selective queries (finding single rows).</li> <li>Index results are "lossy" - it identifies candidate blocks, the query still needs to scan those blocks.</li> </ul> <h6>Example</h6> - <p><code>CREATE INDEX idx_logs_ts_brin ON logs USING BRIN (log_timestamp);</code></p> + <p><code>CREATE INDEX idx_logs_ts_brin ON logs USING BRIN (log_timestamp) WITH (pages_per_range = 32);</code></p> </div> </div> </div> @@ -771,7 +789,7 @@ <ul> <li>Potentially smaller and slightly faster than B-Tree *only* for equality lookups on certain data patterns (e.g., long strings where only equality is needed).</li> <li>In most cases, B-Tree performs comparably or better even for equality and offers much more flexibility (range queries, sorting).</li> - <li>B-Tree is usually the better default choice even if only equality is needed.</li> + <li>B-Tree is usually the better default choice even if only equality is needed. Use Hash only if benchmarks show a significant benefit for a specific `=`-only workload.</li> </ul> <h6>Example</h6> <p><code>CREATE INDEX idx_data_hash ON large_objects USING HASH (data_blob_hash);</code></p> @@ -781,32 +799,62 @@ <div class="col-lg-4 col-md-6"> <div class="info-card type-index-features" id="card-index-features"> <div class="card-body"><h5><i class="bi bi-stars"></i> Special Index Features</h5> - <div class="card-content-wrapper"><p class="summary">Includes <span class="term"><a href="https://www.postgresql.org/docs/current/indexes-partial.html" target="_blank">Partial</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/indexes-expressional.html" target="_blank">Expression</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-INCLUDE" target="_blank">Covering (INCLUDE)</a></span>, and <span class="term"><a href="https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY" target="_blank">Concurrent</a></span> indexes for optimization.</p> + <div class="card-content-wrapper"><p class="summary">Includes <span class="term">Partial</span>, <span class="term">Expression</span>, <span class="term">Covering (INCLUDE)</span>, and <span class="term">Concurrent</span> indexes for optimization and maintenance.</p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseIdxFeatures" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseIdxFeatures"> <ul> - <li><strong><a href="https://www.postgresql.org/docs/current/indexes-partial.html" target="_blank">Partial Indexes</a>:</strong> Index only a subset of rows defined by a `WHERE` clause. + <li><a href="https://www.postgresql.org/docs/current/indexes-partial.html" target="_blank">Partial Indexes</a>: Index only a subset of rows defined by a `WHERE` clause. <ul><li>Reduces index size and maintenance overhead if queries frequently filter on the same condition.</li> - <li>Example: `CREATE INDEX idx_orders_pending ON orders (order_id) WHERE status = 'pending';`</li></ul> + <li>Example: `CREATE INDEX idx_orders_pending ON orders (order_id) WHERE status = 'pending';` (Only useful for queries also containing `WHERE status = 'pending'`).</li></ul> </li> - <li><strong><a href="https://www.postgresql.org/docs/current/indexes-expressional.html" target="_blank">Expression Indexes</a> (Functional Indexes):</strong> Index the result of a function or expression applied to one or more columns. - <ul><li>Useful for queries filtering/sorting on functions (e.g., `lower()`, date extraction).</li> + <li><a href="https://www.postgresql.org/docs/current/indexes-expressional.html" target="_blank">Expression Indexes</a> (Functional Indexes): Index the result of a function or expression applied to one or more columns. + <ul><li>Useful for queries filtering/sorting on functions (e.g., `lower()`, date extraction). The query must use the *exact* same expression.</li> <li>Example: `CREATE INDEX idx_users_email_lower ON users (lower(email));` (for `WHERE lower(email) = '...'`)</li></ul> </li> - <li><strong><a href="https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-INCLUDE" target="_blank">Covering Indexes (INCLUDE clause)</a>:</strong> Store additional, non-key columns directly in the index leaf pages. - <ul><li>Allows <span class="term"><a href="https://www.postgresql.org/docs/current/indexes-index-only-scans.html" target="_blank">Index-Only Scans</a></span> where the query can be satisfied entirely from the index without visiting the table heap.</li> + <li><a href="https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-INCLUDE" target="_blank">Covering Indexes (INCLUDE clause)</a>: Store additional, non-key columns directly in the index leaf pages. + <ul><li>Allows <span class="term"><a href="https://www.postgresql.org/docs/current/indexes-index-only-scans.html" target="_blank">Index-Only Scans</a></span> where the query can be satisfied entirely from the index without visiting the table heap (requires table visibility map to be up-to-date via VACUUM).</li> <li>Example: `CREATE INDEX idx_items_name ON items (name) INCLUDE (price, category);` (for `SELECT price FROM items WHERE name = '...'`)</li></ul> </li> - <li><strong><a href="https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY" target="_blank">Concurrent Index Builds</a>:</strong> Create indexes without blocking writes (`INSERT`/`UPDATE`/`DELETE`) on the table. - <ul><li>Takes longer and uses more resources than a standard `CREATE INDEX`. Requires multiple passes.</li> + <li><a href="https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY" target="_blank">Concurrent Index Builds</a>: Create indexes without blocking writes (`INSERT`/`UPDATE`/`DELETE`) on the table. + <ul><li>Takes longer and uses more resources than a standard `CREATE INDEX`. Requires multiple passes and can fail (leaving an invalid index that needs dropping).</li> <li>Syntax: `CREATE INDEX CONCURRENTLY ...;`</li> - <li>Essential for adding indexes to busy production tables with minimal downtime.</li> - <li>Similar `REINDEX CONCURRENTLY` exists. <a href="https://www.postgresql.org/docs/current/sql-reindex.html#SQL-REINDEX-CONCURRENTLY" target="_blank">[reindex]</a></li></ul> + <li>Essential for adding indexes to busy production tables with minimal downtime. Cannot run inside a transaction block.</li> </li> </ul> </div> </div> </div> + <div class="col-lg-4 col-md-6"> + <div class="info-card type-reindex" id="card-reindex"> + <div class="card-body"><h5><i class="bi bi-arrow-repeat"></i> REINDEX</h5> + <div class="card-content-wrapper"><p class="summary">Rebuilds an index, useful for shrinking bloated indexes or repairing corruption. Supports <span class="term">`CONCURRENTLY`</span> option. <a href="https://www.postgresql.org/docs/current/sql-reindex.html" target="_blank">[docs]</a></p> + <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseReindex" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> + <div class="collapse collapse-content" id="collapseReindex"> + <h6>Purpose</h6> + <ul> + <li>Reduce Bloat: Rebuilds the index from scratch using current table data, removing dead space (similar effect to `VACUUM FULL` but just for the index).</li> + <li>Repair Corruption: Can fix certain types of index corruption.</li> + <li>Update Index Definition: Historically needed for changes in collation versions (less common now).</li> + </ul> + <h6>Commands</h6> + <ul> + <li>`REINDEX INDEX index_name;`: Rebuilds a specific index. Takes `ACCESS EXCLUSIVE` lock on the index (blocks reads/writes using the index).</li> + <li>`REINDEX TABLE table_name;`: Rebuilds all indexes on a specific table. Locks the table.</li> + <li>`REINDEX SCHEMA schema_name;`: Rebuilds all indexes in a schema.</li> + <li>`REINDEX DATABASE database_name;`: Rebuilds all indexes in a database (requires caution).</li> + <li>`REINDEX SYSTEM database_name;`: Rebuilds system catalog indexes (requires caution).</li> + </ul> + <h6>Concurrent Reindex</h6> + <ul> + <li><span class="term"><a href="https://www.postgresql.org/docs/current/sql-reindex.html#SQL-REINDEX-CONCURRENTLY" target="_blank">`REINDEX INDEX index_name CONCURRENTLY;`</a></span> (Postgres 12+): Rebuilds the index without taking heavy locks that block reads/writes.</li> + <li>Similar mechanism to `CREATE INDEX CONCURRENTLY`: builds a new index in the background, then replaces the old one in a short lock window.</li> + <li>Takes longer, uses more resources, cannot run in a transaction block. Preferred method for large/busy tables.</li> + </ul> + <h6>When to Use</h6> + <p>Consider `REINDEX` (preferably `CONCURRENTLY`) when index bloat is significant (detected via monitoring) and affecting performance, or if index corruption is suspected.</p> + </div> + </div> + </div> </div> <!-- /.row --> </div> <!-- /.schema-container --> @@ -825,20 +873,20 @@ <h6>Syntax</h6> <p><code>function_name() OVER ( [PARTITION BY expr_list] [ORDER BY expr_list] [frame_clause] )</code> <a href="https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS" target="_blank">[syntax]</a></p> <ul> - <li><strong>`PARTITION BY`</strong>: Divides rows into partitions (groups). Window function is applied independently to each partition. (Optional)</li> - <li><strong>`ORDER BY`</strong>: Defines the order of rows within each partition. Required for ranking and frame-dependent functions. (Optional for some functions)</li> - <li><strong>`frame_clause`</strong>: Defines the subset of rows within the partition relative to the current row (e.g., `ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING`). (Optional)</li> + <li>`PARTITION BY`: Divides rows into partitions (groups). Window function is applied independently to each partition. (Optional)</li> + <li>`ORDER BY`: Defines the order of rows within each partition. Required for ranking and frame-dependent functions. (Optional for some functions)</li> + <li>`frame_clause`: Defines the subset of rows within the partition relative to the current row (e.g., `ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING`). (Optional) <a href="https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS-FRAME-CLAUSE" target="_blank">[frame]</a></li> </ul> <h6>Common Functions</h6> <ul> - <li><strong>Ranking:</strong> <span class="term"><a href="https://www.postgresql.org/docs/current/functions-window.html#FUNCTIONS-WINDOW-RANKING" target="_blank">`ROW_NUMBER()`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/functions-window.html#FUNCTIONS-WINDOW-RANKING" target="_blank">`RANK()`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/functions-window.html#FUNCTIONS-WINDOW-RANKING" target="_blank">`DENSE_RANK()`</a></span>, `NTILE(n)`.</li> - <li><strong>Aggregate Windows:</strong> `SUM()`, `AVG()`, `COUNT()`, `MAX()`, `MIN()` applied over the window frame.</li> - <li><strong>Value Fetching:</strong> <span class="term"><a href="https://www.postgresql.org/docs/current/functions-window.html#FUNCTIONS-WINDOW-ACCESS" target="_blank">`LAG()`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/functions-window.html#FUNCTIONS-WINDOW-ACCESS" target="_blank">`LEAD()`</a></span>, `FIRST_VALUE()`, `LAST_VALUE()`, `NTH_VALUE()`.</li> + <li>Ranking: <span class="term"><a href="https://www.postgresql.org/docs/current/functions-window.html#FUNCTIONS-WINDOW-RANKING" target="_blank">`ROW_NUMBER()`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/functions-window.html#FUNCTIONS-WINDOW-RANKING" target="_blank">`RANK()`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/functions-window.html#FUNCTIONS-WINDOW-RANKING" target="_blank">`DENSE_RANK()`</a></span>, `NTILE(n)`.</li> + <li>Aggregate Windows: `SUM()`, `AVG()`, `COUNT()`, `MAX()`, `MIN()` applied over the window frame.</li> + <li>Value Fetching: <span class="term"><a href="https://www.postgresql.org/docs/current/functions-window.html#FUNCTIONS-WINDOW-ACCESS" target="_blank">`LAG()`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/functions-window.html#FUNCTIONS-WINDOW-ACCESS" target="_blank">`LEAD()`</a></span>, `FIRST_VALUE()`, `LAST_VALUE()`, `NTH_VALUE()`.</li> </ul> <h6>Example Use Cases</h6> <p>Calculating running totals, ranking results within categories, finding previous/next values, computing moving averages.</p> - <p><code>SELECT order_id, order_date, amount, SUM(amount) OVER (ORDER BY order_date) as running_total FROM orders;</code></p> - <p><code>SELECT product, category, sales, RANK() OVER (PARTITION BY category ORDER BY sales DESC) as category_rank FROM product_sales;</code></p> + <pre><code>SELECT order_id, order_date, amount, SUM(amount) OVER (ORDER BY order_date) as running_total FROM orders;</code></pre> + <pre><code>SELECT product, category, sales, RANK() OVER (PARTITION BY category ORDER BY sales DESC) as category_rank FROM product_sales;</code></pre> </div> </div> </div> @@ -876,8 +924,9 @@ UNION ALL SELECT * FROM subordinates;</code></pre> <h6>Materialization (Quirk)</h6> <ul> - <li>By default, CTEs might be "inlined" by the planner.</li> - <li>Use `WITH cte AS <span class="term"><a href="https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-CTEMATERIALIZATION" target="_blank">MATERIALIZED</a></span> (...)` (Postgres 12+) to force the CTE result to be computed once and stored temporarily. Can help performance if the CTE is referenced multiple times, but can hurt if it's large and only used partially.</li> + <li>By default, CTEs might be "inlined" by the planner (re-evaluated each time referenced).</li> + <li>Use `WITH cte AS <span class="term"><a href="https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-CTEMATERIALIZATION" target="_blank">MATERIALIZED</a></span> (...)` (Postgres 12+) to force the CTE result to be computed once and stored temporarily (like a temp table). Can help performance if the CTE is complex and referenced multiple times, but can hurt if it's large and only used partially or if inlining would allow better overall optimization.</li> + <li>Use `WITH cte AS NOT MATERIALIZED (...)` (PG12+) to hint against materialization.</li> </ul> <h6>Data Modifying CTEs</h6> <p>You can have `INSERT`, `UPDATE`, or `DELETE` statements within a CTE, using their `RETURNING` clause to pass data to subsequent parts of the query. <a href="https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING" target="_blank">[docs]</a></p> @@ -887,7 +936,7 @@ SELECT * FROM subordinates;</code></pre> <div class="col-lg-4 col-md-6"> <div class="info-card type-distinct-on" id="card-distinct-on"> <div class="card-body"><h5><i class="bi bi-filter-circle"></i> DISTINCT ON</h5> - <div class="card-content-wrapper"><p class="summary">Postgres-specific extension. Selects the *first* row for each unique combination of expressions in the <span class="term"><a href="https://www.postgresql.org/docs/current/sql-select.html#SQL-DISTINCT" target="_blank">`DISTINCT ON (...)`</a></span> clause.</p> + <div class="card-content-wrapper"><p class="summary">Postgres-specific extension. Selects the *first* row for each unique combination of expressions in the <span class="term"><a href="https://www.postgresql.org/docs/current/sql-select.html#SQL-DISTINCT" target="_blank">`DISTINCT ON (...)`</a></span> clause, based on `ORDER BY`.</p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseDistinctOn" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseDistinctOn"> <h6>Syntax</h6> @@ -896,9 +945,9 @@ SELECT * FROM subordinates;</code></pre> <ul> <li>The query result is ordered according to the *entire* `ORDER BY` clause.</li> <li>For each group of rows that are identical according to the `DISTINCT ON` expressions, only the *first* row according to the `ORDER BY` clause is kept.</li> - <li>**Crucial:** The `ORDER BY` clause *must* start with the same expressions as `DISTINCT ON` to get predictable results. Additional `ORDER BY` expressions determine which row is chosen within each distinct group. <a href="https://www.postgresql.org/docs/current/sql-select.html#SQL-DISTINCT" target="_blank">[docs]</a></li> + <li>**Crucial:** The `ORDER BY` clause *must* start with the same expressions as `DISTINCT ON` (order matters!) to get predictable results. Additional `ORDER BY` expressions determine which row is chosen within each distinct group. <a href="https://www.postgresql.org/docs/current/sql-select.html#SQL-DISTINCT" target="_blank">[docs]</a></li> </ul> - <h6>Use Case: "Latest/Greatest per Group"</h6> + <h6>Use Case: "Greatest/Latest N per Group" (N=1)</h6> <p>Very common for finding the most recent entry for each item.</p> <pre><code>-- Get the latest event for each device_id SELECT DISTINCT ON (device_id) @@ -906,7 +955,7 @@ SELECT DISTINCT ON (device_id) FROM device_events ORDER BY device_id, event_timestamp DESC;</code></pre> <h6>Comparison to Alternatives</h6> - <p>Often more concise and performant than using window functions (`ROW_NUMBER() OVER (...) ... WHERE rn = 1`) or correlated subqueries for the same "latest-per-group" task, especially if indexed appropriately.</p> + <p>Often more concise and performant than using window functions (`ROW_NUMBER() OVER (...) ... WHERE rn = 1`) or correlated subqueries for the same "latest-per-group" task, especially if indexed appropriately on the `DISTINCT ON` and `ORDER BY` columns.</p> </div> </div> </div> @@ -921,17 +970,17 @@ ORDER BY device_id, event_timestamp DESC;</code></pre> VALUES (value_list) ON CONFLICT (conflict_target) DO conflict_action;</code></pre> <ul> - <li><strong>`conflict_target`</strong>: Specifies the constraint that triggers the conflict handling. Can be: + <li>`conflict_target`: Specifies the constraint that triggers the conflict handling. Can be: <ul> - <li>`(column_name [, ...])`: A list of columns with a unique constraint.</li> + <li>`(column_name [, ...])`: A list of columns with a unique constraint/index.</li> <li>`ON CONSTRAINT constraint_name`: The name of a unique or exclusion constraint.</li> - <li>`WHERE predicate`: (Optional) Only handle conflict if predicate is true.</li> + <li>`WHERE predicate`: (Optional) Handle conflict only if inference predicate is true.</li> </ul> </li> - <li><strong>`conflict_action`</strong>: Can be: + <li>`conflict_action`: Can be: <ul> <li>`DO NOTHING`: Silently ignore the row if a conflict occurs.</li> - <li>`DO UPDATE SET col1 = value1 [, ...] [WHERE condition]`: Update the existing row that caused the conflict. Can reference the existing row using the special `EXCLUDED` table (`SET count = mytable.count + EXCLUDED.count`).</li> + <li>`DO UPDATE SET col1 = value1 [, ...] [WHERE condition]`: Update the existing row that caused the conflict. Can reference the existing row using the table name (e.g., `counters.value`) and the proposed insertion row using the special `EXCLUDED` table (`SET count = mytable.count + EXCLUDED.count`). The optional `WHERE` condition refers to the existing row.</li> </ul> </li> </ul> @@ -952,7 +1001,7 @@ SET value = counters.value + EXCLUDED.value;</code></pre> <div class="col-lg-4 col-md-6"> <div class="info-card type-dml" id="card-dml"> <div class="card-body"><h5><i class="bi bi-pencil-square"></i> Advanced DML</h5> - <div class="card-content-wrapper"><p class="summary">Supports <span class="term"><a href="https://www.postgresql.org/docs/current/sql-update.html" target="_blank">`UPDATE ... FROM`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/sql-delete.html" target="_blank">`DELETE ... USING`</a></span> for joining in modifications. <span class="term"><a href="https://www.postgresql.org/docs/current/dml-returning.html" target="_blank">`RETURNING`</a></span> clause gets back modified rows.</p> + <div class="card-content-wrapper"><p class="summary">Supports <span class="term">`UPDATE ... FROM`</span>, <span class="term">`DELETE ... USING`</span> for joining. <span class="term">`RETURNING`</span> clause gets back modified rows. <span class="term">`TABLESAMPLE`</span> for subsets.</p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseDml" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseDml"> <h6>UPDATE FROM / DELETE USING</h6> @@ -984,6 +1033,13 @@ RETURNING name, stock; -- Delete old logs and return the IDs deleted DELETE FROM logs WHERE log_date < '2023-01-01' RETURNING log_id;</code></pre> + <h6>TABLESAMPLE Clause</h6> + <p>Selects a random subset of rows from a table, typically much faster than `ORDER BY random() LIMIT n` for large tables. <a href="https://www.postgresql.org/docs/current/sql-select.html#SQL-TABLESAMPLE" target="_blank">[docs]</a></p> + <ul> + <li>Methods: `BERNOULLI` (scans whole table, includes each row with specified probability), `SYSTEM` (faster, selects random blocks, probability is approximate).</li> + <li>Example: `SELECT * FROM large_log_table TABLESAMPLE SYSTEM (1);` (Select ~1% of blocks)</li> + <li>Use `REPEATABLE (seed)` for reproducible samples.</li> + </ul> </div> </div> </div> @@ -1026,31 +1082,170 @@ LEFT JOIN LATERAL ( </div> </div> </div> + <div class="col-lg-4 col-md-6"> + <div class="info-card type-aggregate-filter" id="card-aggregate-filter"> + <div class="card-body"><h5><i class="bi bi-funnel"></i> Aggregate `FILTER` Clause</h5> + <div class="card-content-wrapper"><p class="summary">SQL standard way to apply a condition *before* aggregation using <span class="term"><a href="https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-AGGREGATES" target="_blank">`FILTER (WHERE ...)`</a></span>. Cleaner than `CASE` statements.</p> + <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseAggFilter" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> + <div class="collapse collapse-content" id="collapseAggFilter"> + <h6>Syntax</h6> + <p><code>aggregate_function(expression) FILTER (WHERE condition)</code></p> + <h6>Purpose</h6> + <p>Provides a standard and often more readable way to perform conditional aggregation compared to using `CASE` inside the aggregate function.</p> + <h6>Example: Count paid vs unpaid orders</h6> + <pre><code>-- Traditional CASE method +SELECT + SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders, + SUM(CASE WHEN status = 'unpaid' THEN 1 ELSE 0 END) AS unpaid_orders +FROM orders; + +-- Using FILTER clause +SELECT + COUNT(*) FILTER (WHERE status = 'paid') AS paid_orders, + COUNT(*) FILTER (WHERE status = 'unpaid') AS unpaid_orders +FROM orders; + +-- Can be combined with GROUP BY +SELECT customer_id, + COUNT(*) FILTER (WHERE amount > 100) as large_orders, + COUNT(*) FILTER (WHERE amount <= 100) as small_orders +FROM orders +GROUP BY customer_id;</code></pre> + <h6>Benefits</h6> + <ul> + <li>Improved readability, especially for complex conditions or multiple conditional aggregates.</li> + <li>SQL standard syntax.</li> + <li>Potentially allows for better optimization by the planner in some cases.</li> + </ul> + </div> + </div> + </div> + </div> <!-- /.row --> + </div> <!-- /.schema-container --> + + <!-- 5. NATIVE PARTITIONING --> + <div class="schema-container cat-partitioning" data-section-id="section-partitioning"> + <h2 class="section-title" id="title-partitioning">Native Partitioning</h2> + <div class="row"> + <div class="col-lg-6 col-md-6"> + <div class="info-card type-partitioning" id="card-declarative-partitioning"> + <div class="card-body"><h5><i class="bi bi-layout-split"></i> Declarative Partitioning</h5> + <div class="card-content-wrapper"><p class="summary">Split large tables into smaller, manageable pieces (partitions) based on <span class="term">LIST</span>, <span class="term">RANGE</span>, or <span class="term">HASH</span> of a key. Improves performance and manageability. <a href="https://www.postgresql.org/docs/current/ddl-partitioning.html" target="_blank">[docs]</a></p> + <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapsePartitioning" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> + <div class="collapse collapse-content" id="collapsePartitioning"> + <h6>Concept</h6> + <p>A partitioned table is a logical "parent" table that is physically composed of multiple child tables (partitions). Rows inserted into the parent are automatically routed to the correct partition based on the partition key and method.</p> + <h6>Partitioning Methods</h6> + <ul> + <li>RANGE: Partitions based on ranges of the partition key (e.g., date ranges, numeric ranges). `PARTITION BY RANGE (created_at)` <a href="https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-RANGE" target="_blank">[docs]</a></li> + <li>LIST: Partitions based on specific values of the partition key (e.g., country codes, status codes). `PARTITION BY LIST (country_code)` <a href="https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-LIST" target="_blank">[docs]</a></li> + <li>HASH: Partitions based on a hash of the partition key, distributing rows roughly evenly. `PARTITION BY HASH (user_id)` <a href="https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-HASH" target="_blank">[docs]</a></li> + </ul> + <h6>Creating Partitions</h6> + <pre><code>-- Parent Table Definition +CREATE TABLE measurements ( + city_id int not null, + logdate date not null, + peaktemp int, + unitsales int +) <span class="term">PARTITION BY RANGE (logdate)</span>; + +-- Creating Partitions +CREATE TABLE measurements_y2023m01 <span class="term">PARTITION OF</span> measurements + <span class="term">FOR VALUES FROM ('2023-01-01') TO ('2023-02-01')</span>; + +CREATE TABLE measurements_y2023m02 PARTITION OF measurements + FOR VALUES FROM ('2023-02-01') TO ('2023-03-01'); + +-- List Partition Example +CREATE TABLE cities ( ... ) PARTITION BY LIST (country); +CREATE TABLE cities_us PARTITION OF cities FOR VALUES IN ('US'); +CREATE TABLE cities_jp PARTITION OF cities FOR VALUES IN ('JP'); +CREATE TABLE cities_other PARTITION OF cities DEFAULT; -- Optional default</code></pre> + <h6>Benefits</h6> + <ul> + <li>Performance (Partition Pruning): Queries filtering by the partition key can skip scanning irrelevant partitions entirely.</li> + <li>Manageability: Easier bulk data loading/deletion by manipulating partitions (`ATTACH`/`DETACH PARTITION`, `DROP TABLE partition_name`). Efficient for time-series data retention (drop old partitions).</li> + <li>Maintenance: Operations like `VACUUM` or `REINDEX` can often be performed on individual partitions.</li> + </ul> + <h6>Considerations</h6> + <ul> + <li>Indexes and constraints usually need to be created on each partition individually (though primary keys/unique constraints must include the partition key). Use `CREATE INDEX ON parent_table ...` to automatically create on all partitions (and future ones).</li> + <li>Partition key cannot easily be changed after creation.</li> + <li>Too many partitions can increase planning time and resource usage.</li> + <li>Use extensions like <a href="https://github.com/pgpartman/pg_partman" target="_blank">`pg_partman`</a> to automate partition creation and maintenance.</li> + </ul> + </div> + </div> + </div> + <div class="col-lg-6 col-md-6"> + <div class="info-card type-partitioning" id="card-partition-maintenance"> + <div class="card-body"><h5><i class="bi bi-wrench-adjustable"></i> Partition Management</h5> + <div class="card-content-wrapper"><p class="summary">Add new partitions, remove old ones using <span class="term">`CREATE TABLE ... PARTITION OF`</span>, <span class="term">`ALTER TABLE ... ATTACH PARTITION`</span>, <span class="term">`ALTER TABLE ... DETACH PARTITION`</span>, `DROP TABLE`. Automate with tools.</p> + <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapsePartMaint" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> + <div class="collapse collapse-content" id="collapsePartMaint"> + <h6>Adding Partitions</h6> + <ul> + <li>Direct Creation: Create a new table that is immediately a partition. + <pre><code>CREATE TABLE measurements_y2023m03 PARTITION OF measurements + FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');</code></pre> + </li> + <li>Attaching Existing Table: Attach a regular table (matching the parent's structure) as a new partition. Useful for bulk loading data into a separate table first. <a href="https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-ATTACH-PARTITION" target="_blank">[docs]</a> + <pre><code>CREATE TABLE measurements_staging (...); -- Load data here +ALTER TABLE measurements <span class="term">ATTACH PARTITION</span> measurements_staging + FOR VALUES FROM ('2023-04-01') TO ('2023-05-01');</code></pre> + This operation takes a brief `ACCESS EXCLUSIVE` lock on the parent table. + </li> + </ul> + <h6>Removing Partitions</h6> + <ul> + <li>Detaching Partition: Convert a partition back into a standalone regular table. The data remains but is no longer part of the partitioned table. Takes a brief `ACCESS EXCLUSIVE` lock. <a href="https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-DETACH-PARTITION" target="_blank">[docs]</a> + <pre><code>ALTER TABLE measurements <span class="term">DETACH PARTITION</span> measurements_y2023m01; +-- Now measurements_y2023m01 is a regular table. +-- Can archive or drop it later.</code></pre> + </li> + <li>Dropping Partition: Directly drop the partition table. This permanently deletes the data in that partition. Fast operation. + <pre><code>DROP TABLE measurements_y2023m01;</code></pre> + </li> + </ul> + <h6>Automation</h6> + <ul> + <li>Manual creation/dropping of range partitions (e.g., monthly) is tedious and error-prone.</li> + <li>Use extensions like <a href="https://github.com/pgpartman/pg_partman" target="_blank">`pg_partman`</a> which provide functions to automatically create new partitions ahead of time and drop old ones based on retention policies. Often scheduled via <span class="term">`pg_cron`</span> or external schedulers.</li> + </ul> + <h6>Indexes & Constraints</h6> + <ul> + <li>Remember to create indexes on new partitions. Using `CREATE INDEX ON parent_table ...` handles this automatically for future partitions.</li> + <li>Foreign keys referencing partitioned tables are possible but have limitations. <a href="https://www.postgresql.org/docs/current/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE-LIMITATIONS" target="_blank">[limitations]</a></li> + </ul> + </div> + </div> + </div> </div> <!-- /.row --> </div> <!-- /.schema-container --> - <!-- 5. CONCURRENCY & LOCKING --> + <!-- 6. CONCURRENCY & LOCKING --> <div class="schema-container cat-concurrency" data-section-id="section-concurrency"> <h2 class="section-title" id="title-concurrency">Concurrency & Locking</h2> <div class="row"> - <div class="col-lg-4 col-md-6"> + <div class="col-lg-6 col-md-6"> <!-- Adjusted to lg-6 --> <div class="info-card type-isolation" id="card-isolation"> <div class="card-body"><h5><i class="bi bi-shield-lock"></i> Isolation Levels</h5> - <div class="card-content-wrapper"><p class="summary"><span class="term"><a href="https://www.postgresql.org/docs/current/transaction-iso.html#XACT-READ-COMMITTED" target="_blank">`READ COMMITTED`</a></span> (default), <span class="term"><a href="https://www.postgresql.org/docs/current/transaction-iso.html#XACT-REPEATABLE-READ" target="_blank">`REPEATABLE READ`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE" target="_blank">`SERIALIZABLE`</a></span>. Control transaction visibility. `RR` can cause serialization failures. <a href="https://www.postgresql.org/docs/current/transaction-iso.html" target="_blank">[docs]</a></p> + <div class="card-content-wrapper"><p class="summary"><span class="term">`READ COMMITTED`</span> (default, statement-level snapshot), <span class="term">`REPEATABLE READ`</span> (transaction snapshot, can fail), <span class="term">`SERIALIZABLE`</span> (full serializability, more failures). <a href="https://www.postgresql.org/docs/current/transaction-iso.html" target="_blank">[docs]</a></p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseIsolation" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseIsolation"> <h6>Levels & Guarantees</h6> <ul> - <li><strong>`READ UNCOMMITTED`</strong> (Not implemented in PG, behaves like `READ COMMITTED`): Allows dirty reads.</li> - <li><strong>`READ COMMITTED`</strong> (Default): Guarantees no dirty reads. Each statement sees a snapshot of data committed *before that statement began*. Can experience non-repeatable reads and phantom reads. <a href="https://www.postgresql.org/docs/current/transaction-iso.html#XACT-READ-COMMITTED" target="_blank">[docs]</a></li> - <li><strong>`REPEATABLE READ`</strong>: Guarantees no dirty reads or non-repeatable reads. Entire transaction sees a snapshot of data committed *before the transaction began*. Can experience phantom reads (though PG often prevents them via predicate locking). **Quirk:** May fail with a <span class="term"><a href="https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZATION-FAILURE" target="_blank">serialization failure</a></span> (error 40001) if it detects a potential anomaly that would violate serializability. Requires application retry logic. <a href="https://www.postgresql.org/docs/current/transaction-iso.html#XACT-REPEATABLE-READ" target="_blank">[docs]</a></li> - <li><strong>`SERIALIZABLE`</strong>: Strongest level. Guarantees transactions behave as if they executed one after another sequentially. Prevents all anomalies (dirty, non-repeatable, phantom). **Quirk:** More likely to experience serialization failures than `REPEATABLE READ`. Requires application retry logic. High performance overhead due to extensive locking/tracking. <a href="https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE" target="_blank">[docs]</a></li> + <li>`READ UNCOMMITTED` (Not implemented in PG, behaves like `READ COMMITTED`): Allows dirty reads.</li> + <li>`READ COMMITTED` (Default): Guarantees no dirty reads. Each statement sees a snapshot of data committed *before that statement began*. Can experience non-repeatable reads (same query gets different results later in tx) and phantom reads (new rows appear later in tx). **Important Quirk:** Often sufficient, but be aware of its statement-level snapshot behavior. <a href="https://www.postgresql.org/docs/current/transaction-iso.html#XACT-READ-COMMITTED" target="_blank">[docs]</a></li> + <li>`REPEATABLE READ`: Guarantees no dirty reads or non-repeatable reads. Entire transaction sees a snapshot of data committed *before the transaction's first query began*. Can experience phantom reads (though PG often prevents them via predicate locking). **Quirk:** May fail with a <span class="term"><a href="https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZATION-FAILURE" target="_blank">serialization failure</a></span> (error 40001) if it detects a potential anomaly (write skew) that would violate serializability. Requires application retry logic. <a href="https://www.postgresql.org/docs/current/transaction-iso.html#XACT-REPEATABLE-READ" target="_blank">[docs]</a></li> + <li>`SERIALIZABLE`: Strongest level. Guarantees transactions behave as if they executed one after another sequentially. Prevents all anomalies (dirty, non-repeatable, phantom). **Quirk:** More likely to experience serialization failures than `REPEATABLE READ` due to stricter checking. Requires application retry logic. Higher performance overhead. <a href="https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE" target="_blank">[docs]</a></li> </ul> <h6>Choosing a Level</h6> <ul> - <li>Stick with `READ COMMITTED` unless specific guarantees are needed.</li> - <li>Use `REPEATABLE READ` or `SERIALIZABLE` for multi-statement transactions requiring a consistent view or complex read-modify-write operations where race conditions are possible. Be prepared to handle serialization failures by retrying the transaction.</li> + <li>Stick with `READ COMMITTED` unless specific read consistency across multiple statements within a transaction is required.</li> + <li>Use `REPEATABLE READ` or `SERIALIZABLE` for multi-statement transactions requiring a consistent view or complex read-modify-write operations where race conditions or anomalies are possible. Be prepared to handle serialization failures by retrying the transaction.</li> </ul> <h6>Setting Level</h6> <p><code><a href="https://www.postgresql.org/docs/current/sql-set-transaction.html" target="_blank">SET TRANSACTION ISOLATION LEVEL</a> REPEATABLE READ;</code><br/> @@ -1058,38 +1253,39 @@ LEFT JOIN LATERAL ( </div> </div> </div> - <div class="col-lg-4 col-md-6"> + <div class="col-lg-6 col-md-6"> <!-- Adjusted to lg-6 --> <div class="info-card type-locking" id="card-locking"> <div class="card-body"><h5><i class="bi bi-lock"></i> Locking Mechanisms</h5> - <div class="card-content-wrapper"><p class="summary">Implicit row-level locks via MVCC/DML. Explicit <span class="term"><a href="https://www.postgresql.org/docs/current/sql-lock.html" target="_blank">`LOCK TABLE`</a></span>. Application-level <span class="term"><a href="https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS" target="_blank">Advisory Locks</a></span>.</p> + <div class="card-content-wrapper"><p class="summary">Implicit row-level via MVCC/DML. Explicit <span class="term">`SELECT FOR UPDATE/SHARE`</span>, <span class="term">`LOCK TABLE`</span>. Application-level <span class="term">Advisory Locks</span>. Monitor with <span class="term">`pg_locks`</span>.</p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseLocking" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseLocking"> - <h6>Row-Level Locks (Implicit)</h6> + <h6>Row-Level Locks (Implicit & Explicit)</h6> <ul> - <li>Managed automatically by MVCC and DML commands.</li> - <li><span class="term"><a href="https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-ROWS" target="_blank">`SELECT ... FOR UPDATE`</a></span>/`FOR NO KEY UPDATE`/`FOR SHARE`/`FOR KEY SHARE`: Explicitly lock rows returned by a `SELECT` statement, preventing other transactions from modifying or locking them incompatibly until the current transaction ends. Used to prevent race conditions in read-modify-write cycles.</li> + <li>Managed automatically by MVCC and DML commands (e.g., `UPDATE` takes `ROW EXCLUSIVE` lock on updated rows).</li> + <li><span class="term"><a href="https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-ROWS" target="_blank">`SELECT ... FOR UPDATE`</a></span>/`FOR NO KEY UPDATE`/`FOR SHARE`/`FOR KEY SHARE`: Explicitly lock rows returned by a `SELECT` statement, preventing other transactions from modifying or locking them incompatibly until the current transaction ends. Used to prevent race conditions in read-modify-write cycles. Options `NOWAIT` and `SKIP LOCKED`.</li> </ul> <h6>Table-Level Locks (Explicit)</h6> <ul> <li><span class="term"><a href="https://www.postgresql.org/docs/current/sql-lock.html" target="_blank">`LOCK TABLE table_name [IN lock_mode]`</a></span>: Explicitly lock an entire table.</li> - <li>Modes (increasing strength): `ACCESS SHARE`, `ROW SHARE`, `ROW EXCLUSIVE`, `SHARE UPDATE EXCLUSIVE`, `SHARE`, `SHARE ROW EXCLUSIVE`, `EXCLUSIVE`, `ACCESS EXCLUSIVE`. <a href="https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES" target="_blank">[modes]</a></li> + <li>Modes (increasing strength): `ACCESS SHARE`, `ROW SHARE`, `ROW EXCLUSIVE`, `SHARE UPDATE EXCLUSIVE`, `SHARE`, `SHARE ROW EXCLUSIVE`, `EXCLUSIVE`, `ACCESS EXCLUSIVE`. See <a href="https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-CONFLICTS" target="_blank">Lock Compatibility Matrix</a>.</li> <li>Use with caution! Can severely impact concurrency. Needed for some DDL operations or specific application logic.</li> </ul> <h6>Advisory Locks</h6> <ul> <li>Application-defined locks managed by function calls (e.g., <span class="term"><a href="https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS" target="_blank">`pg_advisory_lock(key)`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS" target="_blank">`pg_advisory_xact_lock(key)`</a></span>). <a href="https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS" target="_blank">[docs]</a></li> <li>Based on arbitrary numbers (e.g., derived from an application entity ID). Not tied to specific tables or rows.</li> - <li>Useful for coordinating application-level processes or locking concepts not directly represented by a single table row.</li> - <li>Transaction-scoped (`pg_advisory_xact_lock`) or session-scoped (`pg_advisory_lock`).</li> + <li>Useful for coordinating application-level processes or locking concepts not directly represented by a single table row (e.g., limiting concurrent processing of a job type).</li> + <li>Transaction-scoped (`pg_advisory_xact_lock`) or session-scoped (`pg_advisory_lock`). Remember to unlock session locks!</li> </ul> <h6>Deadlocks</h6> <ul> <li>Occur when two (or more) transactions wait for locks held by each other.</li> <li>Postgres automatically detects deadlocks and aborts one of the transactions (raising an error). <a href="https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-DEADLOCKS" target="_blank">[docs]</a></li> - <li>Prevent by acquiring locks in a consistent, globally defined order.</li> + <li>**Diagnosis:** Check PostgreSQL logs (if `log_lock_waits` is enabled). Query `pg_locks` and `pg_stat_activity` during contention.</li> + <li>**Prevention:** Acquire locks on multiple objects in a consistent, globally defined order across all application code. Use coarser locks (e.g., table lock) if fine-grained locking proves too complex. Reduce transaction duration. Use `SELECT ... FOR UPDATE SKIP LOCKED` for queue processing patterns.</li> </ul> <h6>Monitoring</h6> - <p>Use the <span class="term"><a href="https://www.postgresql.org/docs/current/view-pg-locks.html" target="_blank">`pg_locks`</a></span> view to inspect currently held locks.</p> + <p>Use the <span class="term"><a href="https://www.postgresql.org/docs/current/view-pg-locks.html" target="_blank">`pg_locks`</a></span> view to inspect currently held locks and waiting transactions. Query <span class="term"><a href="https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW" target="_blank">`pg_stat_activity`</a></span> to see `wait_event_type` and `wait_event` (e.g., `Lock`, `LWLock`).</p> </div> </div> </div> @@ -1097,34 +1293,34 @@ LEFT JOIN LATERAL ( </div> <!-- /.schema-container --> - <!-- 6. PERFORMANCE & MAINTENANCE --> + <!-- 7. PERFORMANCE & MAINTENANCE --> <div class="schema-container cat-performance" data-section-id="section-performance"> <h2 class="section-title" id="title-performance">Performance & Maintenance</h2> <div class="row"> <div class="col-lg-4 col-md-6"> <div class="info-card type-perf type-vacuum" id="card-vacuum"> <div class="card-body"><h5><i class="bi bi-recycle"></i> VACUUM</h5> - <div class="card-content-wrapper"><p class="summary">Essential maintenance! Reclaims space from dead rows (<span class="term"><a href="https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-SPACE-RECOVERY" target="_blank">bloat</a></span>), updates visibility map, prevents <span class="term"><a href="https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND" target="_blank">TXID Wraparound</a></span>. <span class="term"><a href="https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM" target="_blank">Autovacuum</a></span> is critical. <a href="https://www.postgresql.org/docs/current/sql-vacuum.html" target="_blank">[docs]</a></p> + <div class="card-content-wrapper"><p class="summary">Essential maintenance! Reclaims space from dead rows (<span class="term">bloat</span>), updates visibility map, prevents <span class="term">TXID Wraparound</span>. <span class="term">Autovacuum</span> is critical but needs monitoring/tuning. <a href="https://www.postgresql.org/docs/current/sql-vacuum.html" target="_blank">[docs]</a></p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseVacuum" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseVacuum"> <h6>Why VACUUM is Needed (MVCC)</h6> <p>Because MVCC creates new row versions for UPDATEs/DELETEs, the old ("dead") versions remain physically until `VACUUM` runs. Without it, tables grow indefinitely (<span class="term">bloat</span>) and performance degrades. <a href="https://www.postgresql.org/docs/current/routine-vacuuming.html" target="_blank">[routine vacuuming]</a></p> <h6>What VACUUM Does</h6> <ul> - <li><strong>Reclaims Space:</strong> Marks space occupied by dead tuples as reusable for future INSERTs/UPDATEs within the same table (standard `VACUUM` doesn't typically return space to the OS).</li> - <li><strong>Updates <a href="https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-VISIBILITY-MAP" target="_blank">Visibility Map (VM)</a>:</strong> Marks blocks containing only visible-to-all tuples, enabling <a href="https://www.postgresql.org/docs/current/indexes-index-only-scans.html" target="_blank">Index-Only Scans</a>.</li> - <li><strong>Updates Statistics:</strong> Optionally runs `ANALYZE` (`VACUUM ANALYZE`).</li> - <li><strong>Prevents <a href="https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND" target="_blank">TXID Wraparound</a>:</strong> "Freezes" old tuple transaction IDs to prevent comparison issues when the TXID counter wraps around (every ~4 billion transactions). Critical for database operation.</li> + <li>Reclaims Space: Marks space occupied by dead tuples as reusable for future INSERTs/UPDATEs within the same table (standard `VACUUM` doesn't typically return space to the OS).</li> + <li>Updates <a href="https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-VISIBILITY-MAP" target="_blank">Visibility Map (VM)</a>: Marks blocks containing only visible-to-all tuples, enabling <a href="https://www.postgresql.org/docs/current/indexes-index-only-scans.html" target="_blank">Index-Only Scans</a>.</li> + <li>Updates Statistics: Optionally runs `ANALYZE` (`VACUUM ANALYZE`).</li> + <li>Prevents <a href="https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND" target="_blank">TXID Wraparound</a>: "Freezes" old tuple transaction IDs to prevent comparison issues when the TXID counter wraps around (every ~4 billion transactions). Critical for database operation.</li> </ul> <h6>VACUUM vs VACUUM FULL</h6> <ul> - <li><strong>`VACUUM [tablename]`</strong>: Standard vacuum. Runs concurrently with reads/writes (minimal locking). Reclaims space within the table. **Run regularly.**</li> - <li><strong><a href="https://www.postgresql.org/docs/current/sql-vacuum.html#SQL-VACUUM-FULL" target="_blank">`VACUUM FULL [tablename]`</a></strong>: Rewrites the entire table to a new file, reclaiming maximum space (including returning to OS). Requires `ACCESS EXCLUSIVE` lock (blocks all access). Use sparingly, only when severe bloat needs aggressive reclaiming. Consider <a href="https://github.com/reorg/pg_repack" target="_blank">`pg_repack`</a> extension as an online alternative.</li> + <li>`VACUUM [tablename]`: Standard vacuum. Runs concurrently with reads/writes (minimal locking). Reclaims space within the table. **Run regularly.**</li> + <li><a href="https://www.postgresql.org/docs/current/sql-vacuum.html#SQL-VACUUM-FULL" target="_blank">`VACUUM FULL [tablename]`</a>: Rewrites the entire table to a new file, reclaiming maximum space (including returning to OS). Requires `ACCESS EXCLUSIVE` lock (blocks all access). Use sparingly, only when severe bloat needs aggressive reclaiming. Consider <a href="https://github.com/reorg/pg_repack" target="_blank">`pg_repack`</a> extension as an online alternative.</li> </ul> <h6>Autovacuum</h6> <ul> <li>Background process that automatically runs `VACUUM` and `ANALYZE` on tables based on thresholds (number of dead tuples, inserts/updates/deletes). <a href="https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM" target="_blank">[docs]</a></li> - <li>**Essential for health.** Default settings are often too conservative for busy databases. Requires tuning (<span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-MAX-WORKERS" target="_blank">`autovacuum_max_workers`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-SCALE-FACTOR" target="_blank">`autovacuum_vacuum_scale_factor`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-ANALYZE-THRESHOLD" target="_blank">`autovacuum_analyze_threshold`</a></span>, etc. <a href="https://www.postgresql.org/docs/current/runtime-config-autovacuum.html" target="_blank">[config]</a>). Monitor its activity!</li> + <li>**Essential for health.** Default settings are often too conservative for busy databases. Requires tuning (<span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-MAX-WORKERS" target="_blank">`autovacuum_max_workers`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-SCALE-FACTOR" target="_blank">`autovacuum_vacuum_scale_factor`</a></span> / `_threshold`, <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-ANALYZE-THRESHOLD" target="_blank">`autovacuum_analyze_threshold`</a></span> / `_scale_factor`, etc. <a href="https://www.postgresql.org/docs/current/runtime-config-autovacuum.html" target="_blank">[config]</a>). Monitor its activity!</li> </ul> </div> </div> @@ -1132,18 +1328,18 @@ LEFT JOIN LATERAL ( <div class="col-lg-4 col-md-6"> <div class="info-card type-perf type-analyze" id="card-analyze"> <div class="card-body"><h5><i class="bi bi-graph-up"></i> ANALYZE</h5> - <div class="card-content-wrapper"><p class="summary">Collects statistics about table data distribution (histograms, distinct values) used by the query planner to choose optimal execution plans. <a href="https://www.postgresql.org/docs/current/sql-analyze.html" target="_blank">[docs]</a></p> + <div class="card-content-wrapper"><p class="summary">Collects statistics about table data distribution (histograms, distinct values) used by the query planner to choose optimal execution plans. Run automatically by Autovacuum. <a href="https://www.postgresql.org/docs/current/sql-analyze.html" target="_blank">[docs]</a></p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseAnalyze" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseAnalyze"> <h6>Why ANALYZE is Needed</h6> - <p>The query planner needs accurate information about the data in tables to make good decisions (e.g., estimate how many rows a `WHERE` clause will return, decide between index scan vs sequential scan, choose join methods). <a href="https://www.postgresql.org/docs/current/planner-stats.html" target="_blank">[planner stats]</a></p> + <p>The query planner needs accurate information about the data in tables to make good decisions (e.g., estimate how many rows a `WHERE` clause will return, decide between index scan vs sequential scan, choose join methods). Outdated stats lead to bad plans. <a href="https://www.postgresql.org/docs/current/planner-stats.html" target="_blank">[planner stats]</a></p> <h6>What ANALYZE Collects</h6> <ul> - <li>Total number of rows.</li> - <li>Number of distinct values per column.</li> + <li>Total number of rows (`reltuples` in `pg_class`).</li> + <li>Number of distinct values per column (`n_distinct`).</li> <li>Most common values (MCVs) and their frequencies.</li> <li>Histograms representing data distribution for range comparisons.</li> - <li>Correlation between physical row order and column values (for BRIN indexes).</li> + <li>Correlation between physical row order and column values (for BRIN indexes and plan costing).</li> <li>Stored in system catalog <span class="term"><a href="https://www.postgresql.org/docs/current/catalog-pg-statistic.html" target="_blank">`pg_statistic`</a></span>.</li> </ul> <h6>When to Run</h6> @@ -1154,214 +1350,391 @@ LEFT JOIN LATERAL ( <li>Manually run `ANALYZE tablename;` or `ANALYZE VERBOSE tablename;` if query plans seem poor or after large data modifications before autovacuum kicks in.</li> </ul> <h6>Autovacuum & ANALYZE</h6> - <p>Autovacuum typically triggers `ANALYZE` based on a percentage of rows changed (<span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-ANALYZE-SCALE-FACTOR" target="_blank">`autovacuum_analyze_scale_factor`</a></span> + <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-ANALYZE-THRESHOLD" target="_blank">`autovacuum_analyze_threshold`</a></span>). Tuning may be required. <a href="https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM" target="_blank">[docs]</a></p> + <p>Autovacuum typically triggers `ANALYZE` based on a percentage of rows changed (<span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-ANALYZE-SCALE-FACTOR" target="_blank">`autovacuum_analyze_scale_factor`</a></span> + <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-ANALYZE-THRESHOLD" target="_blank">`autovacuum_analyze_threshold`</a></span>). Tuning may be required, especially for large tables where the default % is too high. <a href="https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM" target="_blank">[docs]</a></p> <h6>Statistics Target</h6> - <p>Control the detail level of statistics (number of histogram buckets, MCVs) using `ALTER TABLE ... ALTER COLUMN ... SET STATISTICS <number>;` (Default 100). Increase for columns with skewed data used in important `WHERE` clauses. <a href="https://www.postgresql.org/docs/current/planner-stats.html#PLANNER-STATS-TARGET" target="_blank">[docs]</a></p> + <p>Control the detail level of statistics (number of histogram buckets, MCVs) using `ALTER TABLE ... ALTER COLUMN ... SET STATISTICS <number>;` (Default 100). Increase for columns with skewed data used in important `WHERE` clauses or `JOIN` conditions. Requires another `ANALYZE`. <a href="https://www.postgresql.org/docs/current/planner-stats.html#PLANNER-STATS-TARGET" target="_blank">[docs]</a></p> </div> </div> </div> <div class="col-lg-4 col-md-6"> <div class="info-card type-perf type-explain" id="card-explain"> <div class="card-body"><h5><i class="bi bi-search"></i> EXPLAIN</h5> - <div class="card-content-wrapper"><p class="summary">Analyze query execution plans. <span class="term"><a href="https://www.postgresql.org/docs/current/sql-explain.html" target="_blank">`EXPLAIN ANALYZE`</a></span> runs the query and shows actual times and row counts. Use <span class="term"><a href="https://www.postgresql.org/docs/current/sql-explain.html#SQL-EXPLAIN-ANALYZE-BUFFERS" target="_blank">`BUFFERS`</a></span> for cache info.</p> + <div class="card-content-wrapper"><p class="summary">Analyze query execution plans. <span class="term">`EXPLAIN ANALYZE`</span> runs the query and shows actual times/rows. Use <span class="term">`BUFFERS`</span> for cache/IO info. Essential diagnostic tool. <a href="https://www.postgresql.org/docs/current/sql-explain.html" target="_blank">[docs]</a></p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseExplain" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseExplain"> <h6>Purpose</h6> <p>Understand how PostgreSQL intends to execute (or actually executed) a query. Essential for diagnosing performance issues and validating index usage. <a href="https://www.postgresql.org/docs/current/using-explain.html" target="_blank">[using explain]</a></p> <h6>Basic Usage</h6> <ul> - <li><strong>`EXPLAIN SELECT ...`</strong>: Shows the planner's *estimated* execution plan, costs, and row counts without running the query. Safe to use anytime.</li> - <li><strong>`EXPLAIN ANALYZE SELECT ...`</strong>: **Runs the query** and shows the actual execution plan, including actual times per node, actual row counts, loops, etc. Use cautiously on production for long-running or modifying queries.</li> + <li>`EXPLAIN SELECT ...`: Shows the planner's *estimated* execution plan, costs, and row counts without running the query. Safe to use anytime.</li> + <li>`EXPLAIN ANALYZE SELECT ...`: **Runs the query** and shows the actual execution plan, including actual times per node, actual row counts, loops, etc. Use cautiously on production for long-running or modifying queries.</li> </ul> <h6>Key Options</h6> <ul> - <li><strong>`BUFFERS`</strong>: With `ANALYZE`, shows buffer usage (cache hits, blocks read from disk) per node. Crucial for identifying I/O bottlenecks. <code>EXPLAIN (ANALYZE, BUFFERS) ...</code> <a href="https://www.postgresql.org/docs/current/sql-explain.html#SQL-EXPLAIN-ANALYZE-BUFFERS" target="_blank">[docs]</a></li> - <li><strong>`COSTS`</strong>: Enable/disable display of estimated costs (default true). `EXPLAIN (COSTS false) ...`</li> - <li><strong>`TIMING`</strong>: Enable/disable display of actual timing per node with `ANALYZE` (default true). `EXPLAIN (ANALYZE, TIMING false) ...`</li> - <li><strong>`VERBOSE`</strong>: Shows more details, like output column lists per node.</li> - <li><strong>`FORMAT {TEXT|XML|JSON|YAML}`</strong>: Choose output format (default TEXT). JSON is useful for programmatic analysis.</li> + <li>`BUFFERS`: With `ANALYZE`, shows buffer usage (shared hits, reads, dirtied, written; temp reads/written) per node. Crucial for identifying I/O bottlenecks and cache efficiency. <code>EXPLAIN (ANALYZE, BUFFERS) ...</code> <a href="https://www.postgresql.org/docs/current/sql-explain.html#SQL-EXPLAIN-ANALYZE-BUFFERS" target="_blank">[docs]</a></li> + <li>`WAL`: With `ANALYZE`, shows WAL record generation counts and sizes.</li> + <li>`SETTINGS`: Shows non-default configuration settings affecting the plan.</li> + <li>`COSTS`: Enable/disable display of estimated costs (default true). `EXPLAIN (COSTS false) ...`</li> + <li>`TIMING`: Enable/disable display of actual timing per node with `ANALYZE` (default true). `EXPLAIN (ANALYZE, TIMING false) ...`</li> + <li>`VERBOSE`: Shows more details, like output column lists per node, schema-qualified names.</li> + <li>`SUMMARY`: Enable/disable summary info (planning/execution time) with `ANALYZE` (default true).</li> + <li>`FORMAT {TEXT|XML|JSON|YAML}`: Choose output format (default TEXT). JSON is useful for programmatic analysis or plan visualizers.</li> </ul> <h6>Interpreting Output</h6> <ul> - <li>Read plans from bottom-up (most indented is executed first).</li> - <li>Look for high estimated costs vs. low actual rows (planner misestimation).</li> - <li>Identify slow nodes (high actual time).</li> - <li>Check for Sequential Scans on large tables where Index Scans were expected.</li> - <li>Analyze buffer hits/reads to understand caching effectiveness.</li> - <li>Compare estimated rows vs actual rows (indicates potentially outdated statistics).</li> + <li>Read plans from inside-out / bottom-up (most indented node executes first).</li> + <li>Compare estimated rows vs actual rows (large discrepancies suggest outdated statistics or planner limitations).</li> + <li>Identify slow nodes (high `actual time`).</li> + <li>Check for Sequential Scans on large tables where Index Scans or Bitmap Heap Scans were expected.</li> + <li>Analyze buffer hits/reads (`Buffers:` line) to understand caching effectiveness (high reads indicate poor caching or cold cache).</li> + <li>Look for expensive Sorts or Hash operations (check `work_mem`).</li> <li><a href="https://www.postgresql.org/docs/current/using-explain.html#USING-EXPLAIN-ANALYZE" target="_blank">[Interpreting ANALYZE]</a></li> </ul> <h6>Tools</h6> - <p>Visualizers like <a href="https://explain.depesz.com/" target="_blank">explain.depesz.com</a>, <a href="https://explain.dalibo.com/" target="_blank">explain.dalibo.com</a>, <a href="https://pev.dalibo.com/" target="_blank">PEV</a> make complex plans easier to read.</p> + <p>Visualizers like <a href="https://explain.depesz.com/" target="_blank">explain.depesz.com</a>, <a href="https://explain.dalibo.com/" target="_blank">explain.dalibo.com</a>, <a href="https://pev.dalibo.com/" target="_blank">PEV</a> make complex plans easier to read (use `FORMAT JSON`).</p> </div> </div> </div> <div class="col-lg-4 col-md-6"> <div class="info-card type-perf type-stats" id="card-stats"> - <div class="card-body"><h5><i class="bi bi-bar-chart-line"></i> Statistics Views</h5> - <div class="card-content-wrapper"><p class="summary">Monitor activity: <span class="term"><a href="https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW" target="_blank">`pg_stat_activity`</a></span> (connections), <span class="term"><a href="https://www.postgresql.org/docs/current/pgstatstatements.html" target="_blank">`pg_stat_statements`</a></span> (query stats), <span class="term"><a href="https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-USER-TABLES-VIEW" target="_blank">`pg_stat_user_tables`</a></span> (table usage), <span class="term"><a href="https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-LOCKS-VIEW" target="_blank">`pg_locks`</a></span>.</p> + <div class="card-body"><h5><i class="bi bi-bar-chart-line"></i> Statistics Views & Extensions</h5> + <div class="card-content-wrapper"><p class="summary">Monitor activity: <span class="term">`pg_stat_activity`</span>, <span class="term">`pg_stat_statements`</span>, <span class="term">`pg_stat_*_tables/indexes`</span>, <span class="term">`pg_locks`</span>. Use <span class="term">`pg_buffercache`</span> for cache inspection.</p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseStats" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseStats"> <h6>Key Views for Monitoring</h6> <ul> - <li><strong><a href="https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW" target="_blank">`pg_stat_activity`</a></strong>: Shows information about currently active backend processes/connections. + <li><a href="https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW" target="_blank">`pg_stat_activity`</a>: Shows information about currently active backend processes/connections. <ul><li>Columns: `pid`, `datname`, `usename`, `client_addr`, `backend_start`, `query_start`, `state` (active, idle, idle in transaction), `wait_event_type`/`wait_event`, `query`, `backend_type`.</li> - <li>Essential for seeing long-running queries, idle transactions, waiting connections.</li></ul> + <li>Essential for seeing long-running queries, idle transactions, waiting connections (lock contention).</li></ul> </li> - <li><strong><a href="https://www.postgresql.org/docs/current/pgstatstatements.html" target="_blank">`pg_stat_statements`</a></strong> (`CREATE EXTENSION pg_stat_statements;`): Tracks execution statistics for all normalized queries executed. **Crucial for identifying expensive queries.** - <ul><li>Requires setting `shared_preload_libraries = 'pg_stat_statements'` in `postgresql.conf` and a server restart.</li> - <li>Columns: `queryid`, `query`, `calls`, `total_exec_time`, `mean_exec_time`, `rows`, `shared_blks_hit`/`read`/`dirtied`/`written`, `temp_blks_read`/`written`.</li> + <li><a href="https://www.postgresql.org/docs/current/pgstatstatements.html" target="_blank">`pg_stat_statements`</a> (`CREATE EXTENSION pg_stat_statements;`): Tracks execution statistics for all normalized queries executed. **Crucial for identifying expensive queries.** + <ul><li>Requires setting `shared_preload_libraries = 'pg_stat_statements'` in `postgresql.conf` and a server restart. Tune settings like `pg_stat_statements.max`.</li> + <li>Columns: `queryid`, `query`, `calls`, `total_exec_time`, `mean_exec_time`, `rows`, `shared_blks_hit`/`read`/`dirtied`/`written`, `temp_blks_read`/`written`, `wal_records`, `wal_bytes`.</li> <li>Use `pg_stat_statements_reset()` to clear stats.</li></ul> </li> - <li><strong><a href="https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW" target="_blank">`pg_stat_user_tables` / `pg_stat_all_tables`</a></strong>: Shows table access statistics. + <li><a href="https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW" target="_blank">`pg_stat_user_tables` / `pg_stat_all_tables`</a>: Shows table access statistics. <ul><li>Columns: `seq_scan`, `seq_tup_read`, `idx_scan`, `idx_tup_fetch`, `n_tup_ins`/`upd`/`del`/`hot_upd`, `n_live_tup`, `n_dead_tup`, `last_vacuum`/`autovacuum`, `last_analyze`/`autoanalyze`.</li> <li>Useful for monitoring table bloat (`n_dead_tup`), scan types, vacuum/analyze activity.</li></ul> </li> - <li><strong><a href="https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STATIO-ALL-TABLES-VIEW" target="_blank">`pg_statio_user_tables` / `pg_statio_all_tables`</a></strong>: Shows table I/O statistics. - <ul><li>Columns: `heap_blks_read`/`hit`, `idx_blks_read`/`hit`, `toast_blks_read`/`hit`.</li> - <li>Helps identify tables causing heavy I/O or poor caching.</li></ul> + <li><a href="https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STATIO-ALL-TABLES-VIEW" target="_blank">`pg_statio_user_tables` / `pg_statio_all_tables`</a>: Shows table I/O statistics (buffer cache). + <ul><li>Columns: `heap_blks_read`/`hit`, `idx_blks_read`/`hit`, `toast_blks_read`/`hit`, `tidx_blks_read`/`hit`.</li> + <li>Helps identify tables causing heavy I/O or poor caching (`*_hit` vs `*_read` ratio).</li></ul> </li> - <li><strong><a href="https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-LOCKS-VIEW" target="_blank">`pg_locks`</a></strong>: Shows currently held locks. Useful for diagnosing locking contention and deadlocks.</li> - <li><strong><a href="https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW" target="_blank">`pg_stat_replication`</a></strong>: Monitor streaming replication status on the primary.</li> - <li><strong><a href="https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-WAL-RECEIVER-VIEW" target="_blank">`pg_stat_wal_receiver`</a></strong>: Monitor streaming replication status on the standby.</li> + <li><a href="https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-LOCKS-VIEW" target="_blank">`pg_locks`</a>: Shows currently held locks and waiting transactions. Useful for diagnosing locking contention and deadlocks.</li> + <li><a href="https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-REPLICATION-VIEW" target="_blank">`pg_stat_replication`</a>: Monitor streaming replication status and lag on the primary.</li> + <li><a href="https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-WAL-RECEIVER-VIEW" target="_blank">`pg_stat_wal_receiver`</a>: Monitor streaming replication status on the standby.</li> + <li><a href="https://www.postgresql.org/docs/current/pgbuffercache.html" target="_blank">`pg_buffercache`</a> Extension (`CREATE EXTENSION pg_buffercache;`): Provides a detailed view of blocks currently held in `shared_buffers`. Useful for advanced cache analysis. Requires superuser privileges.</li> </ul> </div> </div> </div> <div class="col-lg-4 col-md-6"> <div class="info-card type-perf type-config" id="card-config"> - <div class="card-body"><h5><i class="bi bi-sliders"></i> Configuration Tuning</h5> - <div class="card-content-wrapper"><p class="summary">Key settings in <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config.html" target="_blank">`postgresql.conf`</a></span>: memory (<span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-SHARED-BUFFERS" target="_blank">`shared_buffers`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM" target="_blank">`work_mem`</a></span>), WAL (<span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-MAX-WAL-SIZE" target="_blank">`max_wal_size`</a></span>), Checkpoints, Autovacuum. Use <a href="https://pgtune.leopard.in.ua/" target="_blank">`pgtune`</a>.</p> + <div class="card-body"><h5><i class="bi bi-sliders"></i> Configuration Tuning (`postgresql.conf`)</h5> + <div class="card-content-wrapper"><p class="summary">Key settings: memory (<span class="term">`shared_buffers`</span>, <span class="term">`work_mem`</span>), WAL (<span class="term">`max_wal_size`</span>), Checkpoints, Autovacuum. Use <a href="https://pgtune.leopard.in.ua/" target="_blank">`pgtune`</a> as starting point. Reload/Restart needed.</p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseConfig" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseConfig"> <p>Configuration is primarily done via <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config.html" target="_blank">`postgresql.conf`</a></span> (requires reload or restart for many settings) and <span class="term"><a href="https://www.postgresql.org/docs/current/auth-pg-hba-conf.html" target="_blank">`pg_hba.conf`</a></span> (authentication, requires reload). Use <span class="term"><a href="https://www.postgresql.org/docs/current/sql-altersystem.html" target="_blank">`ALTER SYSTEM SET ...`</a></span> to modify `postgresql.conf` via SQL (writes to `postgresql.auto.conf`, overrides main file).</p> <h6>Key Parameter Groups</h6> <ul> - <li><strong>Memory:</strong> (See Memory card) <a href="https://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY" target="_blank">`shared_buffers`</a>, `work_mem`, `maintenance_work_mem`, `effective_cache_size`. Critical for performance.</li> - <li><strong>WAL:</strong> <a href="https://www.postgresql.org/docs/current/runtime-config-wal.html#RUNTIME-CONFIG-WAL-SETTINGS" target="_blank">`wal_level`</a> (`replica` is default, needed for replication/PITR), `max_wal_size` & `min_wal_size` (control WAL disk usage before checkpoint), `wal_buffers`, `commit_delay`, `commit_siblings`. Affects durability and write performance.</li> - <li><strong>Checkpoints:</strong> <a href="https://www.postgresql.org/docs/current/runtime-config-wal.html#RUNTIME-CONFIG-WAL-CHECKPOINTS" target="_blank">`checkpoint_timeout`</a> (max time between checkpoints), `checkpoint_completion_target` (spread checkpoint I/O over time, e.g., 0.9). Controls recovery time vs. I/O spikes.</li> - <li><strong>Autovacuum:</strong> (See VACUUM card) <a href="https://www.postgresql.org/docs/current/runtime-config-autovacuum.html" target="_blank">`autovacuum`</a>, `autovacuum_max_workers`, `autovacuum_naptime`, `autovacuum_vacuum_threshold`, `autovacuum_vacuum_scale_factor`, `autovacuum_analyze_threshold`, `autovacuum_analyze_scale_factor`, `log_autovacuum_min_duration`. **Requires careful tuning for busy systems.**</li> - <li><strong>Planner:</strong> <a href="https://www.postgresql.org/docs/current/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS" target="_blank">`random_page_cost`</a>, `seq_page_cost`, `effective_cache_size`, <a href="https://www.postgresql.org/docs/current/runtime-config-query.html#RUNTIME-CONFIG-QUERY-ENABLE" target="_blank">`enable_*` flags</a> (e.g., `enable_bitmapscan`). Influences query plan choices.</li> - <li><strong>Connections:</strong> <a href="https://www.postgresql.org/docs/current/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SETTINGS" target="_blank">`max_connections`</a> (set high enough for clients + pooler), `superuser_reserved_connections`.</li> - <li><strong>Logging:</strong> <a href="https://www.postgresql.org/docs/current/runtime-config-logging.html" target="_blank">`log_destination`</a>, `logging_collector`, `log_directory`, `log_filename`, `log_statement`, `log_min_duration_statement`, `log_checkpoints`, `log_lock_waits`, `log_temp_files`, `log_autovacuum_min_duration`. Essential for troubleshooting.</li> + <li>Memory: (See Memory card) <a href="https://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY" target="_blank">`shared_buffers`</a>, `work_mem`, `maintenance_work_mem`, `effective_cache_size`. Critical for performance.</li> + <li>WAL: <a href="https://www.postgresql.org/docs/current/runtime-config-wal.html#RUNTIME-CONFIG-WAL-SETTINGS" target="_blank">`wal_level`</a> (`replica` is default, needed for replication/PITR), `max_wal_size` & `min_wal_size` (control WAL disk usage before checkpoint), `wal_buffers`, `commit_delay`, `commit_siblings`, `synchronous_commit`. Affects durability and write performance.</li> + <li>Checkpoints: <a href="https://www.postgresql.org/docs/current/runtime-config-wal.html#RUNTIME-CONFIG-WAL-CHECKPOINTS" target="_blank">`checkpoint_timeout`</a> (max time between checkpoints), `checkpoint_completion_target` (spread checkpoint I/O over time, e.g., 0.9). Controls recovery time vs. I/O spikes.</li> + <li>Autovacuum: (See VACUUM card) <a href="https://www.postgresql.org/docs/current/runtime-config-autovacuum.html" target="_blank">`autovacuum`</a>, `autovacuum_max_workers`, `autovacuum_naptime`, `autovacuum_vacuum_threshold`, `autovacuum_vacuum_scale_factor`, `autovacuum_analyze_threshold`, `autovacuum_analyze_scale_factor`, `autovacuum_vacuum_cost_delay`, `autovacuum_vacuum_cost_limit`, `log_autovacuum_min_duration`. **Requires careful tuning for busy systems.**</li> + <li>Planner: <a href="https://www.postgresql.org/docs/current/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS" target="_blank">`random_page_cost`</a> (tune based on storage type, lower for SSDs, e.g., 1.1), `seq_page_cost` (usually 1.0), `effective_cache_size`, `cpu_tuple_cost`, `cpu_index_tuple_cost`, `cpu_operator_cost`, <a href="https://www.postgresql.org/docs/current/runtime-config-query.html#RUNTIME-CONFIG-QUERY-ENABLE" target="_blank">`enable_*` flags</a> (e.g., `enable_bitmapscan`). Influences query plan choices.</li> + <li>Connections: <a href="https://www.postgresql.org/docs/current/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SETTINGS" target="_blank">`max_connections`</a> (set high enough for clients + pooler, mindful of memory), `superuser_reserved_connections`.</li> + <li>Logging: <a href="https://www.postgresql.org/docs/current/runtime-config-logging.html" target="_blank">`log_destination`</a>, `logging_collector`, `log_directory`, `log_filename`, `log_statement` (use carefully, e.g. `ddl` or `mod`), `log_min_duration_statement` (crucial for finding slow queries), `log_checkpoints`, `log_lock_waits`, `log_temp_files`, `log_autovacuum_min_duration`. Essential for troubleshooting.</li> </ul> <h6>Tools & Approach</h6> <ul> <li>Start with defaults or recommendations from <a href="https://pgtune.leopard.in.ua/" target="_blank">`pgtune`</a> (online tool or script) based on system resources.</li> <li>Monitor performance (using stats views, OS tools) and adjust parameters iteratively based on workload.</li> - <li>Understand the impact of each parameter before changing it. Read the documentation!</li> + <li>Understand the impact of each parameter before changing it. Read the documentation! Check the `context` in `pg_settings` (needs reload/restart?).</li> <li>Use `SHOW parameter_name;` or query <span class="term"><a href="https://www.postgresql.org/docs/current/view-pg-settings.html" target="_blank">`pg_settings`</a></span> view to see current values.</li> - <li>Use <span class="term"><a href="https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL-TABLE" target="_blank">`SELECT pg_reload_conf();`</a></span> to apply changes that don't require a restart.</li> + <li>Use <span class="term"><a href="https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL-TABLE" target="_blank">`SELECT pg_reload_conf();`</a></span> or `pg_ctl reload` to apply changes that don't require a restart. Use `pg_ctl restart` for others.</li> </ul> </div> </div> </div> + <div class="col-lg-4 col-md-6"> + <div class="info-card type-perf type-bloat" id="card-bloat"> + <div class="card-body"><h5><i class="bi bi-balloon"></i> Bloat Monitoring & Remediation</h5> + <div class="card-content-wrapper"><p class="summary">MVCC leads to dead tuples (<span class="term">bloat</span>) in tables/indexes. Monitor using queries or extensions. Remediate with <span class="term">VACUUM</span>, <span class="term">REINDEX</span>, or <span class="term">`pg_repack`</span>.</p> + <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseBloat" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> + <div class="collapse collapse-content" id="collapseBloat"> + <h6>What is Bloat?</h6> + <p>Unused space in table and index files resulting from accumulated dead tuples (from `UPDATE`/`DELETE`) that haven't been fully reclaimed by `VACUUM`. Reduces performance and wastes disk space.</p> + <h6>Detecting Bloat</h6> + <ul> + <li>Queries:** Use queries comparing the physical size (`pg_total_relation_size`) with estimated live data size. Many community scripts exist (search for "postgres bloat query"). These are estimates. Example snippet: + <pre><code>SELECT ..., pg_size_pretty(bloat_size) AS bloat, + pg_size_pretty(table_size - bloat_size) AS live_data +FROM ( ... complex query using pg_class, pg_statistic ... ) ...</code></pre> + </li> + <li><a href="https://www.postgresql.org/docs/current/pgstattuple.html" target="_blank">`pgstattuple`</a> Extension:** (`CREATE EXTENSION pgstattuple;`) Provides functions (`pgstattuple`, `pgstatindex`) to scan relations and give precise statistics on dead tuples, free space, etc. Can be I/O intensive.</li> + <li>Monitoring `n_dead_tup`:** Track `n_dead_tup` in `pg_stat_user_tables`. High numbers indicate potential for bloat if `VACUUM` isn't keeping up.</li> + </ul> + <h6>Remediation</h6> + <ul> + <li>Regular `VACUUM` / Autovacuum Tuning:** The primary preventative measure. Ensure autovacuum is running frequently and effectively enough for your workload.</li> + <li>Manual `VACUUM [tablename]`:** Can help clear out dead tuples if autovacuum is lagging. Does not typically shrink files significantly.</li> + <li>`REINDEX INDEX index_name [CONCURRENTLY]`:** Rebuilds a specific index, removing bloat from the index. Concurrent option recommended.</li> + <li>`VACUUM FULL tablename;`:** Rewrites the entire table and its indexes, removing all bloat and shrinking files. Requires `ACCESS EXCLUSIVE` lock (downtime). Use sparingly.</li> + <li><a href="https://github.com/reorg/pg_repack" target="_blank">`pg_repack`</a> Extension:** Popular tool to perform an online `VACUUM FULL`-like operation with minimal locking (creates a new table copy, applies changes via triggers, then swaps). Requires installing the extension and running the command-line tool.</li> + </ul> + </div> + </div> + </div> +<div class="col-lg-4 col-md-6"> + <div class="info-card type-perf type-os-tuning" id="card-os-tuning"> + <div class="card-body"><h5><i class="bi bi-ubuntu"></i> OS-Level Tuning Hints</h5> + <div class="card-content-wrapper"><p class="summary">OS configuration impacts DB performance. Consider filesystem, huge pages, swappiness, read-ahead, CPU governor.</p> + <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseOSTuning" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> + <div class="collapse collapse-content" id="collapseOSTuning"> + <h6>Key Areas (Linux Focused Examples)</h6> + <ul> + <li>Filesystem:** + <ul><li>Prefer `XFS` or `ext4` for data directories.</li> + <li>Mount options: `noatime`, `nodiratime` can reduce minor I/O overhead. Ensure barriers are enabled (default).</li></ul> + </li> + <li>Transparent Huge Pages (THP):** + <ul><li>Often recommended to be **disabled** (`never`) for database workloads, especially older kernels. Can cause latency spikes and memory fragmentation issues. <a href="https://www.postgresql.org/docs/current/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT" target="_blank">[docs reference]</a></li> + <li>Check: `cat /sys/kernel/mm/transparent_hugepage/enabled`</li> + <li>Disable: Add `transparent_hugepage=never` to kernel boot parameters or via `systemd` service unit.</li></ul> + </li> + <li>Swappiness (`vm.swappiness`):** + <ul><li>Controls how aggressively the kernel swaps memory. Default is often 60.</li> + <li>For dedicated DB servers with ample RAM, lowering this (e.g., 1, 10) discourages swapping of database cache, preventing performance cliffs. Don't set to 0 unless you fully understand implications.</li> + <li>Set via `sysctl vm.swappiness=10` (temporary) or in `/etc/sysctl.conf` (persistent).</li></ul> + </li> + <li>Virtual Memory Overcommit:** + <ul><li>`vm.overcommit_memory`: Often set to `2` (don't overcommit) along with configuring `vm.overcommit_ratio` appropriately to prevent OOM killer issues if memory usage spikes unexpectedly. Needs careful calculation based on RAM + Swap. <a href="https://www.postgresql.org/docs/current/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT" target="_blank">[docs]</a></li></ul> + </li> + <li>Block Device Read-Ahead:** + <ul><li>`blockdev --setra <N> /dev/sdX`. Setting appropriate read-ahead (e.g., 256 or 512 sectors = 128K/256K) for the data disk can sometimes improve large sequential scan performance. Depends heavily on storage type and workload. Benchmark changes.</li></ul> + </li> + <li>CPU Governor:** + <ul><li>Ensure the CPU frequency scaling governor is set to `performance` rather than `ondemand` or `powersave` for consistent database performance on dedicated servers.</li> + <li>Check: `cat /sys/devices/system/cpu/cpu*/cpufreq/scaling_governor`</li> + <li>Set via tools like `cpupower` or kernel parameters.</li></ul> + </li> + </ul> + <h6>General Advice</h6> + <p>These are starting points. Always monitor system performance (CPU, memory, I/O with tools like `vmstat`, `iostat`, `top`/`htop`) and benchmark changes carefully. Defaults are often not optimal for heavy database workloads.</p> + </div> + </div> + </div> + <div class="col-lg-4 col-md-6"> + <div class="info-card type-perf type-anti-patterns" id="card-anti-patterns"> + <div class="card-body"><h5><i class="bi bi-x-octagon"></i> Common Query Anti-Patterns</h5> + <div class="card-content-wrapper"><p class="summary">Avoid common pitfalls: non-SARGable predicates, `SELECT *`, N+1 queries, large `IN` lists, function calls in `WHERE` without indexes.</p> + <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseAntiPatterns" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> + <div class="collapse collapse-content" id="collapseAntiPatterns"> + <ul> + <li>Non-SARGable Predicates:** Conditions in `WHERE` clauses that prevent the planner from using an index effectively ("SARGable" = Search ARGument Able). + <ul><li>Applying functions to indexed columns: `WHERE lower(email) = '...'` (Fix: use expression index or store lowercase). `WHERE date_trunc('day', created_at) = '...'` (Fix: use range query `WHERE created_at >= '...' AND created_at < '...'`).</li> + <li>Wildcard prefix in `LIKE`: `WHERE name LIKE '%Smith'` (Fix: consider full-text search or reverse index if suffix search needed).</li> + <li>Numeric/Date operations on column: `WHERE amount * 1.1 > 100` (Fix: rewrite as `WHERE amount > 100 / 1.1`).</li> + </ul> + </li> + <li>`SELECT *`:** Selecting all columns when only a few are needed. + <ul><li>Wastes network bandwidth and server/client memory.</li> + <li>Prevents Index-Only Scans.</li> + <li>Makes code brittle if table structure changes.</li> + <li>Fix: Explicitly list required columns (`SELECT id, name, email FROM users ...`).</li></ul> + </li> + <li>N+1 Query Problem:** Common in ORMs or application loops. Fetching a list of parent items (1 query), then looping through them to fetch related child items one by one (N queries). + <ul><li>Causes excessive database round trips and load.</li> + <li>Fix: Use `JOIN` in the initial query, use `IN` clauses with fetched IDs, or use dataloader patterns.</li></ul> + </li> + <li>Large `IN` Lists / Many `OR` Conditions:** `WHERE id IN (1, 2, ..., 10000)` or `WHERE col='a' OR col='b' OR ...`. + <ul><li>Can lead to poor plan choices or excessive planning time.</li> + <li>Fix: Use `JOIN` against a temporary table or `VALUES` list containing the IDs/values. For `OR`, consider `UNION ALL` or rewriting logic.</li></ul> + </li> + <li>Implicit Type Casting:** Relying on Postgres to cast types in comparisons (e.g., comparing `integer` column to a string). + <ul><li>Can sometimes prevent index usage.</li> + <li>Fix: Ensure application sends parameters with matching types or use explicit `CAST`.</li></ul> + </li> + <li>Overuse of `OFFSET` for Pagination:** `LIMIT x OFFSET y` becomes increasingly slow as `y` grows, because the server still needs to compute and discard `y` rows. + <ul><li>Fix: Use keyset/seek pagination (based on remembering the last seen value of an ordered column: `WHERE indexed_col > last_value ORDER BY indexed_col LIMIT x`).</li></ul> + </li> + </ul> + </div> + </div> + </div> </div> <!-- /.row --> </div> <!-- /.schema-container --> - <!-- 7. EXTENSIBILITY --> + + <!-- 8. EXTENSIBILITY --> <div class="schema-container cat-extensions" data-section-id="section-extensions"> <h2 class="section-title" id="title-extensions">Extensibility (Postgres Superpower)</h2> <div class="row"> <div class="col-lg-4 col-md-6"> <div class="info-card type-pl" id="card-pl"> <div class="card-body"><h5><i class="bi bi-filetype-sql"></i> Stored Procedures/Functions</h5> - <div class="card-content-wrapper"><p class="summary">Write server-side logic in various languages. Default is <span class="term"><a href="https://www.postgresql.org/docs/current/plpgsql.html" target="_blank">`PL/pgSQL`</a></span>. Others include <span class="term"><a href="https://www.postgresql.org/docs/current/plpython.html" target="_blank">`PL/Python`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/plperl.html" target="_blank">`PL/Perl`</a></span>, <span class="term"><a href="https://github.com/plv8/plv8" target="_blank">`PL/v8`</a></span>. <a href="https://www.postgresql.org/docs/current/server-programming.html" target="_blank">[docs]</a></p> + <div class="card-content-wrapper"><p class="summary">Write server-side logic. Default is <span class="term">`PL/pgSQL`</span>. Others include <span class="term">`PL/Python`</span>, <span class="term">`PL/v8`</span>, <span class="term">`SQL`</span>, <span class="term">`C`</span>. <a href="https://www.postgresql.org/docs/current/server-programming.html" target="_blank">[docs]</a></p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapsePL" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapsePL"> <h6>Purpose</h6> - <p>Encapsulate business logic, perform complex operations atomically within the database, reduce network round trips.</p> + <p>Encapsulate business logic, perform complex operations atomically within the database, reduce network round trips, enforce complex constraints or security logic.</p> <h6>Languages</h6> <ul> - <li><strong>`PL/pgSQL`</strong>: Default, procedural language similar to Oracle's PL/SQL. Block-structured, variables, control flow (IF, LOOP), exception handling. Widely used and robust. <a href="https://www.postgresql.org/docs/current/plpgsql.html" target="_blank">[docs]</a></li> - <li><strong>`PL/Python` (`plpython3u`)</strong>: Write functions in Python. Access to Python libraries. Untrusted (`u`) means no filesystem access restrictions by default (use with care). Useful for data analysis, complex string manipulation, external API calls (via libraries). <a href="https://www.postgresql.org/docs/current/plpython.html" target="_blank">[docs]</a></li> - <li><strong>`PL/Perl` (`plperl`)</strong>: Write functions in Perl. <a href="https://www.postgresql.org/docs/current/plperl.html" target="_blank">[docs]</a></li> - <li><strong>`PL/v8` (`plv8`)</strong>: Write functions in JavaScript (using the V8 engine). Useful for JSON processing, web-related logic. <a href="https://github.com/plv8/plv8" target="_blank">[repo]</a></li> - <li><strong>`SQL`</strong>: Simple functions written purely in SQL. Can be inlined by the planner. <a href="https://www.postgresql.org/docs/current/sql-createfunction.html#SQL-CREATEFUNCTION-LANGUAGE-SQL" target="_blank">[docs]</a></li> - <li><strong>`C`</strong>: Highest performance, allows low-level access, but more complex development and deployment. <a href="https://www.postgresql.org/docs/current/xfunc-c.html" target="_blank">[docs]</a></li> + <li>`PL/pgSQL`: Default, procedural language similar to Oracle's PL/SQL. Block-structured, variables, control flow (IF, LOOP), exception handling. Widely used and robust. <a href="https://www.postgresql.org/docs/current/plpgsql.html" target="_blank">[docs]</a></li> + <li>`PL/Python` (`plpython3u`): Write functions in Python. Access to Python libraries. Untrusted (`u`) means fewer security restrictions (use with care, consider trusted variant `plpython3` if available/sufficient). Useful for data analysis, complex string manipulation, external API calls (via libraries). <a href="https://www.postgresql.org/docs/current/plpython.html" target="_blank">[docs]</a></li> + <li>`PL/v8` (`plv8`): Write functions in JavaScript (using the V8 engine). Useful for JSON processing, web-related logic. Needs separate installation. <a href="https://github.com/plv8/plv8" target="_blank">[repo]</a></li> + <li>`SQL`: Simple functions written purely in SQL. Can often be inlined by the planner for better performance. Good for simple wrappers or calculations. <a href="https://www.postgresql.org/docs/current/sql-createfunction.html#SQL-CREATEFUNCTION-LANGUAGE-SQL" target="_blank">[docs]</a></li> + <li>`C`: Highest performance, allows low-level access, but more complex development, compilation, and deployment. <a href="https://www.postgresql.org/docs/current/xfunc-c.html" target="_blank">[docs]</a></li> + <li>Others: `PL/Perl`, `PL/Tcl`, `PL/Java`, etc.</li> </ul> <h6>Creating Functions</h6> <pre><code><a href="https://www.postgresql.org/docs/current/sql-createfunction.html" target="_blank">CREATE OR REPLACE FUNCTION</a> get_user_count() -RETURNS integer AS $$ +RETURNS integer AS $$ -- Dollar Quoting DECLARE user_count integer; BEGIN SELECT count(*) INTO user_count FROM users; RETURN user_count; END; -$$ LANGUAGE plpgsql;</code></pre> +$$ LANGUAGE plpgsql VOLATILE SECURITY INVOKER; -- Defaults</code></pre> <h6>Procedures (Postgres 11+)</h6> - <p>Use <span class="term"><a href="https://www.postgresql.org/docs/current/sql-createprocedure.html" target="_blank">`CREATE PROCEDURE ... LANGUAGE ... AS $$ ... $$;`</a></span>. Unlike functions, procedures do not return a value directly and can commit/rollback transactions within their body (using specific commands depending on the language).</p> - <h6>Triggers</h6> - <p>Functions can be called by triggers (<span class="term"><a href="https://www.postgresql.org/docs/current/sql-createtrigger.html" target="_blank">`CREATE TRIGGER ... EXECUTE FUNCTION my_trigger_func();`</a></span>) to execute automatically on DML events (BEFORE/AFTER INSERT/UPDATE/DELETE). <a href="https://www.postgresql.org/docs/current/triggers.html" target="_blank">[docs]</a></p> + <p>Use <span class="term"><a href="https://www.postgresql.org/docs/current/sql-createprocedure.html" target="_blank">`CREATE PROCEDURE ... LANGUAGE ... AS $$ ... $$;`</a></span>. Unlike functions, procedures do not return a value directly and *can* control transactions within their body (`COMMIT`, `ROLLBACK` - only at top level, not within nested blocks usually). Called using `CALL my_proc();`. <a href="https://www.postgresql.org/docs/current/plpgsql-transactions.html" target="_blank">[Tx Ctrl]</a></p> + <h6>Function Volatility & Security</h6> + <ul> + <li>Volatility (`VOLATILE`, `STABLE`, `IMMUTABLE`): Hints to the planner about function side effects and result consistency. Crucial for performance and correct use in indexes. <a href="https://www.postgresql.org/docs/current/sql-createfunction.html#SQL-CREATEFUNCTION-VOLATILITY" target="_blank">[docs]</a></li> + <li>Security (`SECURITY INVOKER` vs `SECURITY DEFINER`): Determines if the function runs with the privileges of the *calling user* (INVOKER, default) or the *user who defined the function* (DEFINER). See Security section card. <a href="https://www.postgresql.org/docs/current/sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY" target="_blank">[docs]</a></li> + </ul> </div> </div> </div> + <div class="col-lg-4 col-md-6"> + <div class="info-card type-triggers" id="card-triggers"> + <div class="card-body"><h5><i class="bi bi-bell"></i> Triggers</h5> + <div class="card-content-wrapper"><p class="summary">Execute a function automatically on DML events (`INSERT`, `UPDATE`, `DELETE`). Use `BEFORE`/`AFTER`, Row/Statement level. Complex logic, use carefully. <a href="https://www.postgresql.org/docs/current/triggers.html" target="_blank">[docs]</a></p> + <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseTriggers" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> + <div class="collapse collapse-content" id="collapseTriggers"> + <h6>Concept</h6> + <p>Triggers define that a specific function (trigger function, must return type `trigger`) should be executed automatically when a certain DML event occurs on a specific table.</p> + <h6>Creating Triggers</h6> + <ol> + <li>Create Trigger Function: Usually in PL/pgSQL. Receives context via special variables (`TG_OP`, `NEW`, `OLD`). Must return `NEW` (for `BEFORE INSERT/UPDATE`), `OLD` (for `BEFORE DELETE`), or `NULL`. + <pre><code>CREATE OR REPLACE FUNCTION update_changetimestamp_func() +RETURNS trigger AS $$ +BEGIN + NEW.changed_at = clock_timestamp(); -- Update column + RETURN NEW; -- Return row to be inserted/updated +END; +$$ LANGUAGE plpgsql;</code></pre> + </li> + <li>Create Trigger Definition: Attach the function to the table and event. <a href="https://www.postgresql.org/docs/current/sql-createtrigger.html" target="_blank">[docs]</a> + <pre><code>CREATE TRIGGER set_changetimestamp +<span class="term">BEFORE UPDATE</span> ON my_table -- Timing (BEFORE/AFTER/INSTEAD OF) +<span class="term">FOR EACH ROW</span> -- Level (ROW/STATEMENT) +EXECUTE FUNCTION update_changetimestamp_func();</code></pre> + </li> + </ol> + <h6>Key Options</h6> + <ul> + <li>Timing (`BEFORE`, `AFTER`): Execute function before or after the row operation. `BEFORE` triggers can modify the `NEW` row or skip the operation (`RETURN NULL`). `AFTER` triggers see the final result but cannot change it directly (often used for auditing or cascading actions).</li> + <li>Timing (`INSTEAD OF`): For Views only. Execute function *instead of* the DML operation on the view, allowing updates to non-updatable views.</li> + <li>Level (`FOR EACH ROW`, `FOR EACH STATEMENT`): Row-level triggers fire once per affected row. Statement-level triggers fire once per SQL statement, regardless of rows affected.</li> + <li>Events (`INSERT`, `UPDATE [OF column, ...]`, `DELETE`, `TRUNCATE`): Specify which DML operations activate the trigger.</li> + <li>Constraints:** Trigger behavior can interact with constraints (`DEFERRABLE INITIALLY DEFERRED`).</li> + </ul> + <h6>Use Cases</h6> + <ul> + <li>Auditing changes.</li> + <li>Maintaining derived/denormalized data.</li> + <li>Enforcing complex business rules or constraints not possible with standard checks/foreign keys.</li> + <li>Updating modification timestamps.</li> + </ul> + <h6>Pitfalls</h6> + <ul> + <li>Can make data modification logic complex and hard to debug ("hidden" logic).</li> + <li>Can significantly impact performance if trigger functions are slow or cause cascading effects.</li> + <li>Risk of infinite recursion if triggers cause actions that fire the same trigger again.</li> + <li>Consider alternatives (application logic, rules, procedures) before resorting to complex triggers.</li> + </ul> + </div> + </div> + </div> <div class="col-lg-4 col-md-6"> <div class="info-card type-extensions" id="card-extensions"> <div class="card-body"><h5><i class="bi bi-plug"></i> Extensions</h5> - <div class="card-content-wrapper"><p class="summary">Package new types, functions, operators via <span class="term"><a href="https://www.postgresql.org/docs/current/sql-createextension.html" target="_blank">`CREATE EXTENSION`</a></span>. Huge ecosystem (<a href="https://postgis.net/" target="_blank">PostGIS</a>, <a href="https://www.timescale.com/" target="_blank">TimescaleDB</a>, <a href="https://www.citusdata.com/" target="_blank">Citus</a>, <span class="term"><a href="https://www.postgresql.org/docs/current/pgstatstatements.html" target="_blank">`pg_stat_statements`</a></span>). <a href="https://www.postgresql.org/docs/current/extend-extensions.html" target="_blank">[docs]</a></p> + <div class="card-content-wrapper"><p class="summary">Package new types, functions, operators via <span class="term">`CREATE EXTENSION`</span>. Huge ecosystem (<a href="https://postgis.net/" target="_blank">PostGIS</a>, <a href="https://www.timescale.com/" target="_blank">TimescaleDB</a>, <span class="term">`pg_stat_statements`</span>, <span class="term">`pg_cron`</span>, <span class="term">`pgaudit`</span>). <a href="https://www.postgresql.org/docs/current/extend-extensions.html" target="_blank">[docs]</a></p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseExtensions" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseExtensions"> <h6>Concept</h6> - <p>Extensions bundle related SQL objects (types, functions, operators, index methods, etc.) into a single package that can be easily installed (`CREATE EXTENSION`) and removed (`DROP EXTENSION`) from a database. <a href="https://www.postgresql.org/docs/current/extend-extensions.html" target="_blank">[docs]</a></p> + <p>Extensions bundle related SQL objects (types, functions, operators, index methods, etc.) into a single package that can be easily installed (`CREATE EXTENSION`) and removed (`DROP EXTENSION`) from a database. Simplifies deployment and version management of add-on features. <a href="https://www.postgresql.org/docs/current/extend-extensions.html" target="_blank">[docs]</a></p> <h6>Managing Extensions</h6> <ul> - <li><span class="term"><a href="https://www.postgresql.org/docs/current/sql-createextension.html" target="_blank">`CREATE EXTENSION extension_name [SCHEMA schema_name];`</a></span>: Installs the extension. Requires extension files to be present on the server.</li> - <li><span class="term"><a href="https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMANDS-DX" target="_blank">`\dx`</a></span> (in psql): List installed extensions.</li> - <li><span class="term"><a href="https://www.postgresql.org/docs/current/sql-alterextension.html" target="_blank">`ALTER EXTENSION extension_name UPDATE [TO 'new_version'];`</a></span>: Upgrades an extension.</li> - <li><span class="term"><a href="https://www.postgresql.org/docs/current/sql-dropextension.html" target="_blank">`DROP EXTENSION extension_name;`</a></span>: Removes the extension and its objects.</li> + <li><span class="term"><a href="https://www.postgresql.org/docs/current/sql-createextension.html" target="_blank">`CREATE EXTENSION extension_name [SCHEMA schema_name] [VERSION version] [CASCADE];`</a></span>: Installs the extension. Requires extension control/script files to be present on the server filesystem (usually via OS package manager or compilation).</li> + <li><span class="term"><a href="https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMANDS-DX" target="_blank">`\dx`</a></span> (in psql): List installed extensions. `SELECT * FROM pg_extension;`</li> + <li>`SELECT * FROM pg_available_extensions;`: List extensions available to be installed.</li> + <li><span class="term"><a href="https://www.postgresql.org/docs/current/sql-alterextension.html" target="_blank">`ALTER EXTENSION extension_name UPDATE [TO 'new_version'];`</a></span>: Upgrades an extension to a newer installed version.</li> + <li><span class="term"><a href="https://www.postgresql.org/docs/current/sql-dropextension.html" target="_blank">`DROP EXTENSION extension_name [CASCADE|RESTRICT];`</a></span>: Removes the extension and its objects. `CASCADE` also drops dependent objects.</li> </ul> <h6>Notable Extensions (Examples)</h6> <ul> - <li><strong><a href="https://postgis.net/" target="_blank">PostGIS</a>:</strong> Adds comprehensive support for geographic objects and spatial queries. Industry standard for geospatial data.</li> - <li><strong><a href="https://www.timescale.com/" target="_blank">TimescaleDB</a>:</strong> Turns PostgreSQL into a powerful time-series database with automatic partitioning (hypertables), specialized functions, and compression.</li> - <li><strong><a href="https://www.citusdata.com/" target="_blank">Citus</a>:</strong> Distributes PostgreSQL horizontally for sharding and parallel query processing.</li> - <li><strong><a href="https://www.postgresql.org/docs/current/pgstatstatements.html" target="_blank">`pg_stat_statements`</a></strong>: Tracks query execution statistics (essential for performance tuning).</li> - <li><strong><a href="https://www.postgresql.org/docs/current/pgcrypto.html" target="_blank">`pgcrypto`</a></strong>: Provides cryptographic functions (hashing, encryption).</li> - <li><strong><a href="https://www.postgresql.org/docs/current/uuid-ossp.html" target="_blank">`uuid-ossp`</a></strong> / <strong>`pgcrypto`</strong>: Functions to generate UUIDs.</li> - <li><strong><a href="https://www.postgresql.org/docs/current/hstore.html" target="_blank">`hstore`</a></strong>: Key-value data type.</li> - <li><strong><a href="https://www.postgresql.org/docs/current/pgtrgm.html" target="_blank">`pg_trgm`</a></strong>: Trigram matching for fuzzy string search.</li> - <li><strong><a href="https://github.com/citusdata/pg_cron" target="_blank">`pg_cron`</a></strong>: In-database job scheduler (like cron).</li> - <li><strong><a href="https://github.com/reorg/pg_repack" target="_blank">`pg_repack`</a></strong>: Online table reorganization (alternative to `VACUUM FULL`).</li> + <li><a href="https://postgis.net/" target="_blank">PostGIS</a>: Adds comprehensive support for geographic objects and spatial queries.</li> + <li><a href="https://www.timescale.com/" target="_blank">TimescaleDB</a>: Turns PostgreSQL into a powerful time-series database with hypertables and specialized functions.</li> + <li><a href="https://www.citusdata.com/" target="_blank">Citus</a>: Distributes PostgreSQL horizontally for sharding and parallel query processing.</li> + <li><a href="https://www.postgresql.org/docs/current/pgstatstatements.html" target="_blank">`pg_stat_statements`</a>: Tracks query execution statistics (essential for performance tuning).</li> + <li><a href="https://www.postgresql.org/docs/current/pgcrypto.html" target="_blank">`pgcrypto`</a>: Provides cryptographic functions (hashing, encryption).</li> + <li><a href="https://www.postgresql.org/docs/current/uuid-ossp.html" target="_blank">`uuid-ossp`</a> / `pgcrypto`: Functions to generate UUIDs.</li> + <li><a href="https://www.postgresql.org/docs/current/hstore.html" target="_blank">`hstore`</a>: Key-value data type.</li> + <li><a href="https://www.postgresql.org/docs/current/pgtrgm.html" target="_blank">`pg_trgm`</a>: Trigram matching for fuzzy string search (e.g., `LIKE '%word%'` alternative using GiST/GIN indexes).</li> + <li><a href="https://github.com/citusdata/pg_cron" target="_blank">`pg_cron`</a>: In-database job scheduler (like OS cron).</li> + <li><a href="https://github.com/reorg/pg_repack" target="_blank">`pg_repack`</a>: Online table reorganization (alternative to `VACUUM FULL`).</li> + <li><a href="https://github.com/pgpartman/pg_partman" target="_blank">`pg_partman`</a>: Automates time-based and serial partitioning management.</li> + <li><a href="https://www.pgaudit.org/" target="_blank">`pgaudit`</a>: Provides detailed session and object audit logging.</li> + <li><a href="https://github.com/ankane/pgvector" target="_blank">`pgvector`</a>: Vector similarity search for AI/ML applications.</li> + <li><a href="https://www.postgresql.org/docs/current/bloom.html" target="_blank">`bloom`</a>: Bloom filter index access method.</li> + <li><a href="https://www.postgresql.org/docs/current/pgbuffercache.html" target="_blank">`pg_buffercache`</a>: Inspect shared buffer content.</li> </ul> <h6>Finding Extensions</h6> - <p><a href="https://pgxn.org/" target="_blank">PostgreSQL Extension Network (PGXN)</a>, GitHub, vendor websites.</p> + <p><a href="https://pgxn.org/" target="_blank">PostgreSQL Extension Network (PGXN)</a>, GitHub, vendor websites, cloud provider offerings.</p> </div> </div> </div> <div class="col-lg-4 col-md-6"> <div class="info-card type-fdw" id="card-fdw"> <div class="card-body"><h5><i class="bi bi-database-down"></i> Foreign Data Wrappers (FDWs)</h5> - <div class="card-content-wrapper"><p class="summary">Access external data sources as if they were local tables using <span class="term"><a href="https://www.postgresql.org/docs/current/sql-createforeigntable.html" target="_blank">`CREATE FOREIGN TABLE`</a></span>. Connect to other PG dbs, MySQL, files, etc. <a href="https://www.postgresql.org/docs/current/ddl-foreign-data.html" target="_blank">[docs]</a></p> + <div class="card-content-wrapper"><p class="summary">Access external data sources as if they were local tables using <span class="term">`CREATE FOREIGN TABLE`</span>. Connect to other PG dbs, MySQL, files, etc. <a href="https://www.postgresql.org/docs/current/ddl-foreign-data.html" target="_blank">[docs]</a></p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseFdw" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseFdw"> <h6>Concept</h6> - <p>FDWs provide a standard SQL interface to query data residing outside the current PostgreSQL database, potentially even on different database systems or flat files. <a href="https://www.postgresql.org/docs/current/ddl-foreign-data.html" target="_blank">[docs]</a></p> + <p>FDWs provide a standard SQL interface to query data residing outside the current PostgreSQL database, potentially even on different database systems or flat files. <a href="https://wiki.postgresql.org/wiki/Foreign_data_wrappers" target="_blank">[PG Wiki FDW List]</a></p> <h6>Setup Steps</h6> <ol> - <li><strong>Install FDW Extension:</strong> <span class="term"><a href="https://www.postgresql.org/docs/current/sql-createextension.html" target="_blank">`CREATE EXTENSION postgres_fdw;`</a></span> (for connecting to other PG dbs <a href="https://www.postgresql.org/docs/current/postgres-fdw.html" target="_blank">[postgres_fdw]</a>) or `oracle_fdw`, `mysql_fdw`, `file_fdw`, etc.</li> - <li><strong>Create Server:</strong> <span class="term"><a href="https://www.postgresql.org/docs/current/sql-createserver.html" target="_blank">`CREATE SERVER remote_pg_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'other_host', dbname 'other_db', port '5432');`</a></span></li> - <li><strong>Create User Mapping:</strong> <span class="term"><a href="https://www.postgresql.org/docs/current/sql-createusermapping.html" target="_blank">`CREATE USER MAPPING FOR current_user SERVER remote_pg_server OPTIONS (user 'remote_user', password 'remote_pass');`</a></span> (maps local user to remote credentials)</li> - <li><strong>Create Foreign Table:</strong> Define the structure of the remote table locally. - <pre><code><a href="https://www.postgresql.org/docs/current/sql-createforeigntable.html" target="_blank">CREATE FOREIGN TABLE</a> remote_users ( + <li>Install FDW Extension: <span class="term"><a href="https://www.postgresql.org/docs/current/sql-createextension.html" target="_blank">`CREATE EXTENSION postgres_fdw;`</a></span> (for connecting to other PG dbs <a href="https://www.postgresql.org/docs/current/postgres-fdw.html" target="_blank">[postgres_fdw]</a>) or `oracle_fdw`, `mysql_fdw`, `file_fdw`, `odbc_fdw`, `mongo_fdw`, etc.</li> + <li>Create Server: <span class="term"><a href="https://www.postgresql.org/docs/current/sql-createserver.html" target="_blank">`CREATE SERVER remote_pg_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'other_host', dbname 'other_db', port '5432');`</a></span></li> + <li>Create User Mapping: <span class="term"><a href="https://www.postgresql.org/docs/current/sql-createusermapping.html" target="_blank">`CREATE USER MAPPING FOR current_user SERVER remote_pg_server OPTIONS (user 'remote_user', password 'remote_pass');`</a></span> (maps local user to remote credentials)</li> + <li>Create Foreign Table(s): Define the structure of the remote table locally, or import schema definitions. + <pre><code>-- Manual Definition +<a href="https://www.postgresql.org/docs/current/sql-createforeigntable.html" target="_blank">CREATE FOREIGN TABLE</a> remote_users ( id integer OPTIONS (column_name 'user_id'), -- Map column name if needed name text, email text -) SERVER remote_pg_server OPTIONS (schema_name 'public', table_name 'users');</code></pre> - (Or use <span class="term"><a href="https://www.postgresql.org/docs/current/sql-importforeignschema.html" target="_blank">`IMPORT FOREIGN SCHEMA ...`</a></span> to import definitions automatically). +) SERVER remote_pg_server OPTIONS (schema_name 'public', table_name 'users'); + +-- Import Schema (Easier) +<a href="https://www.postgresql.org/docs/current/sql-importforeignschema.html" target="_blank">IMPORT FOREIGN SCHEMA</a> remote_public_schema +LIMIT TO (users, orders) -- Optional: limit tables +FROM SERVER remote_pg_server INTO local_schema;</code></pre> </li> </ol> <h6>Usage</h6> - <p>Query the foreign table (`SELECT * FROM remote_users;`) just like a local table. Postgres translates the query and pushes down operations (filtering, joins if supported by the FDW) to the remote source where possible. <a href="https://www.postgresql.org/docs/current/ddl-foreign-data.html#DDL-FOREIGN-DATA-QUERYING" target="_blank">[querying]</a></p> + <p>Query the foreign table (`SELECT * FROM local_schema.remote_users;`) just like a local table. Postgres translates the query and pushes down operations (filtering, joins, aggregates if supported by the FDW) to the remote source where possible (check `EXPLAIN`). <a href="https://www.postgresql.org/docs/current/ddl-foreign-data.html#DDL-FOREIGN-DATA-QUERYING" target="_blank">[querying]</a></p> <h6>Benefits</h6> <ul> <li>Unified view of distributed data.</li> <li>Leverage PostgreSQL's SQL and features to query diverse sources.</li> - <li>Can simplify data integration tasks.</li> + <li>Can simplify data integration tasks without complex ETL.</li> + <li>Some FDWs support writes (`INSERT`/`UPDATE`/`DELETE`) back to the foreign source.</li> </ul> <h6>Considerations</h6> - <p>Performance depends heavily on the specific FDW, network latency, and how much processing can be pushed down to the remote server. Not all FDWs support write operations.</p> + <p>Performance depends heavily on the specific FDW, network latency, and how much processing can be pushed down to the remote server. Authentication and security need careful management. Transaction handling across systems can be complex (consider two-phase commit extensions if needed).</p> </div> </div> </div> @@ -1369,38 +1742,44 @@ $$ LANGUAGE plpgsql;</code></pre> </div> <!-- /.schema-container --> - <!-- 8. REPLICATION & HA --> + <!-- 9. REPLICATION & HA --> <div class="schema-container cat-replication" data-section-id="section-replication"> <h2 class="section-title" id="title-replication">Replication & High Availability</h2> <div class="row"> <div class="col-lg-4 col-md-6"> <div class="info-card type-streaming-rep" id="card-streaming-rep"> <div class="card-body"><h5><i class="bi bi-broadcast"></i> Streaming Replication</h5> - <div class="card-content-wrapper"><p class="summary">Built-in physical (binary) replication. Creates read-only standbys (<span class="term"><a href="https://www.postgresql.org/docs/current/hot-standby.html" target="_blank">Hot Standby</a></span>). Async or <a href="https://www.postgresql.org/docs/current/warm-standby.html#SYNCHRONOUS-REPLICATION" target="_blank">Sync</a> commit. Uses <span class="term"><a href="https://www.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLICATION-SLOTS" target="_blank">Slots</a></span>. <a href="https://www.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLICATION" target="_blank">[docs]</a></p> + <div class="card-content-wrapper"><p class="summary">Built-in physical (binary) replication. Creates read-only standbys (<span class="term">Hot Standby</span>). Async or <a href="https://www.postgresql.org/docs/current/warm-standby.html#SYNCHRONOUS-REPLICATION" target="_blank">Sync</a> commit. Use <span class="term">Replication Slots</span>. <a href="https://www.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLICATION" target="_blank">[docs]</a></p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseStreamingRep" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseStreamingRep"> <h6>Mechanism</h6> <ul> - <li>Standby server connects to the primary and streams WAL records as they are generated.</li> + <li>Standby server connects to the primary (using credentials defined in `primary_conninfo`) and streams WAL records via replication protocol (`walsender` on primary, `walreceiver` on standby).</li> <li>Standby continuously replays these WAL records to keep its data files nearly identical to the primary's. <a href="https://www.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLICATION" target="_blank">[docs]</a></li> </ul> <h6>Hot Standby (Read Replicas)</h6> <ul> <li>If <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-HOT-STANDBY" target="_blank">`hot_standby = on`</a></span> (default) on the standby, it can serve read-only queries while continuously applying WALs. <a href="https://www.postgresql.org/docs/current/hot-standby.html" target="_blank">[docs]</a></li> - <li>Read queries on the standby see a slightly delayed view of the data. Can configure <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-STANDBY-STREAMING-DELAY" target="_blank">`max_standby_streaming_delay`</a></span> / <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-STANDBY-ARCHIVE-DELAY" target="_blank">`max_standby_archive_delay`</a></span> to cancel queries conflicting with WAL application.</li> + <li>Read queries on the standby see a slightly delayed view of the data. Can configure <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-HOT-STANDBY-FEEDBACK" target="_blank">`hot_standby_feedback`</a></span> to prevent VACUUM on primary from removing rows needed by long standby queries. Configure <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-STANDBY-STREAMING-DELAY" target="_blank">`max_standby_streaming_delay`</a></span> / `archive_delay` to cancel standby queries conflicting with WAL application.</li> </ul> <h6>Synchronous vs Asynchronous</h6> <ul> - <li><strong>Asynchronous (Default):</strong> Primary commits transaction once WAL is written locally. Fastest, but potential for small data loss on primary crash if WAL hasn't reached standby.</li> - <li><strong><a href="https://www.postgresql.org/docs/current/warm-standby.html#SYNCHRONOUS-REPLICATION" target="_blank">Synchronous</a>:</strong> Primary waits for confirmation from one or more synchronous standbys (<span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-SYNCHRONOUS-STANDBY-NAMES" target="_blank">`synchronous_standby_names`</a></span>) that they have received (and potentially written/applied) the WAL before confirming commit to client. Slower commits, but guarantees zero data loss if synchronous standby takes over.</li> + <li>Asynchronous (Default): Primary commits transaction once WAL is written locally (<span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT" target="_blank">`synchronous_commit = on`</a></span> or `local`). Fastest, but potential for small data loss on primary crash if WAL hasn't reached standby.</li> + <li><a href="https://www.postgresql.org/docs/current/warm-standby.html#SYNCHRONOUS-REPLICATION" target="_blank">Synchronous</a>: Primary waits for confirmation from one or more synchronous standbys (listed in <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-SYNCHRONOUS-STANDBY-NAMES" target="_blank">`synchronous_standby_names`</a></span>) that they have received/written/applied WAL (based on `synchronous_commit` level: `remote_write`, `on` (default sync=remote_flush), `remote_apply`) before confirming commit to client. Slower commits, but guarantees less/zero data loss if synchronous standby takes over.</li> + <li>Quorum Commit:** Configure `synchronous_standby_names` like `ANY N (stby1, stby2, ...)` to wait for N standbys.</li> </ul> <h6>Replication Slots</h6> <ul> - <li><span class="term"><a href="https://www.postgresql.org/docs/current/sql-createreplicationslot.html" target="_blank">`CREATE_REPLICATION_SLOT`</a></span> / `DROP_REPLICATION_SLOT`.</li> - <li>Ensures the primary retains WAL segments needed by a specific standby, even if the standby disconnects temporarily. Prevents premature WAL deletion that would break replication. **Essential for robust replication.** <a href="https://www.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLICATION-SLOTS" target="_blank">[docs]</a></li> + <li>Created on primary via <span class="term"><a href="https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-REPLICATION" target="_blank">`pg_create_physical_replication_slot()`</a></span>. Dropped via <span class="term"><a href="https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-REPLICATION" target="_blank">`pg_drop_replication_slot()`</a></span>. View with `pg_replication_slots`.</li> + <li>Ensures the primary retains WAL segments needed by a specific standby, even if the standby disconnects temporarily. Prevents premature WAL deletion that would break replication. **Essential for robust replication.** <a href="https://www.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLICATION-SLOTS" target="_blank">[docs]</a> Monitor disk usage on primary (`pg_wal`) if slots become inactive.</li> + </ul> + <h6>Other Concepts</h6> + <ul> + <li>Cascading Replication:** A standby can be configured to replicate from another standby, reducing load on the primary.</li> + <li>Monitoring:** Use `pg_stat_replication` on primary, `pg_stat_wal_receiver` on standby to check lag (`write_lag`, `flush_lag`, `replay_lag`).</li> </ul> <h6>Use Cases</h6> - <p>Read scaling, High Availability (HA) failover, near real-time data warehousing feed.</p> + <p>Read scaling, High Availability (HA) failover, disaster recovery standby, near real-time data warehousing feed.</p> </div> </div> </div> @@ -1412,32 +1791,34 @@ $$ LANGUAGE plpgsql;</code></pre> <div class="collapse collapse-content" id="collapseLogicalRep"> <h6>Mechanism</h6> <ul> - <li>Decodes WAL records into a logical representation of data changes (INSERT/UPDATE/DELETE row values). <a href="https://www.postgresql.org/docs/current/logicaldecoding.html" target="_blank">[decoding]</a></li> - <li>Transmits these logical changes to subscribers.</li> - <li>Requires <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-WAL-LEVEL" target="_blank">`wal_level = logical`</a></span> on the publisher.</li> + <li>Uses logical decoding (<span class="term">`pgoutput`</span> plugin is default) to convert WAL records into a stream of logical changes (BEGIN, COMMIT, INSERT, UPDATE, DELETE with row data). <a href="https://www.postgresql.org/docs/current/logicaldecoding.html" target="_blank">[decoding]</a></li> + <li>Transmits these logical changes to subscribers which apply them as DML.</li> + <li>Requires <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-WAL-LEVEL" target="_blank">`wal_level = logical`</a></span> on the publisher. Also needs sufficient <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-WAL-SENDERS" target="_blank">`max_wal_senders`</a></span> and <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-REPLICATION-SLOTS" target="_blank">`max_replication_slots`</a></span>.</li> </ul> <h6>Publisher/Subscriber Model</h6> <ul> - <li><strong>Publisher:</strong> The source database. Define a <span class="term"><a href="https://www.postgresql.org/docs/current/sql-createpublication.html" target="_blank">`PUBLICATION`</a></span> specifying which tables (or all tables) to publish changes for. `CREATE PUBLICATION mypub FOR TABLE users, orders;` <a href="https://www.postgresql.org/docs/current/logical-replication-publication.html" target="_blank">[docs]</a></li> - <li><strong>Subscriber:</strong> The target database. Define a <span class="term"><a href="https://www.postgresql.org/docs/current/sql-createsubscription.html" target="_blank">`SUBSCRIPTION`</a></span> connecting to the publisher and mapping to the publication. `CREATE SUBSCRIPTION mysub CONNECTION '...' PUBLICATION mypub;` <a href="https://www.postgresql.org/docs/current/logical-replication-subscription.html" target="_blank">[docs]</a></li> - <li>Target tables must exist on the subscriber and have compatible schemas (usually identical).</li> + <li>Publisher: The source database. Define a <span class="term"><a href="https://www.postgresql.org/docs/current/sql-createpublication.html" target="_blank">`PUBLICATION`</a></span> specifying which tables (or `ALL TABLES`) and optionally which DML operations (`publish = 'insert, update'`) to publish. `CREATE PUBLICATION mypub FOR TABLE users, orders;` <a href="https://www.postgresql.org/docs/current/logical-replication-publication.html" target="_blank">[docs]</a></li> + <li>Subscriber: The target database. Define a <span class="term"><a href="https://www.postgresql.org/docs/current/sql-createsubscription.html" target="_blank">`SUBSCRIPTION`</a></span> connecting to the publisher and mapping to one or more publications. `CREATE SUBSCRIPTION mysub CONNECTION '...' PUBLICATION mypub WITH (copy_data = false, create_slot = true);` <a href="https://www.postgresql.org/docs/current/logical-replication-subscription.html" target="_blank">[docs]</a></li> + <li>Target tables must exist on the subscriber and have compatible schemas (usually identical names and data types, primary keys needed for UPDATE/DELETE).</li> + <li>Uses replication slots on publisher automatically (`create_slot=true`).</li> </ul> <h6>Advantages over Streaming Replication</h6> <ul> - <li><strong>Selective Replication:</strong> Replicate only specific tables or filter rows (using publication options).</li> - <li><strong>Cross-Version Replication:</strong> Replicate between different major PostgreSQL versions.</li> - <li><strong>Consolidate Changes:</strong> Multiple publishers can replicate to a single subscriber.</li> - <li><strong>Format Flexibility:</strong> Changes are logical, potentially allowing replication to non-PostgreSQL systems via custom consumers.</li> + <li>Selective Replication: Replicate only specific tables or filter rows (using `WHERE` clause in publication, PG15+).</li> + <li>Cross-Version/Platform Replication: Replicate between different major PostgreSQL versions or potentially to other systems.</li> + <li>Consolidate/Distribute Changes: Multiple publishers can replicate to a single subscriber, or one publisher to many subscribers.</li> + <li>Writeable Subscriber:** Subscriber tables can be written to independently (can cause conflicts if not managed carefully).</li> </ul> <h6>Limitations</h6> <ul> - <li>Does not replicate DDL changes (schema changes must be applied manually on both sides). <a href="https://www.postgresql.org/docs/current/logical-replication-restrictions.html" target="_blank">[restrictions]</a></li> + <li>Does not replicate DDL changes (schema changes must be applied manually on both sides, carefully). <a href="https://www.postgresql.org/docs/current/logical-replication-restrictions.html" target="_blank">[restrictions]</a></li> <li>Does not replicate sequence changes or large objects directly.</li> - <li>Can have higher performance overhead than streaming replication.</li> - <li>Initial data synchronization often requires separate step (e.g., `pg_dump` or `COPY WITH (SNAPSHOT ...)` in subscription options).</li> + <li>Can have higher performance overhead and replication lag than streaming replication.</li> + <li>Initial data synchronization often requires separate step (`copy_data=true` option during `CREATE SUBSCRIPTION`, or manual `pg_dump`).</li> + <li>Conflicts can occur if subscriber data is modified independently.</li> </ul> <h6>Use Cases</h6> - <p>Selective data aggregation, feeding data warehouses, replicating between major versions during upgrades, distributing workload subset.</p> + <p>Selective data aggregation/distribution, feeding data warehouses, replicating between major versions during upgrades, zero-downtime upgrades (complex setup), data sharing between microservices.</p> </div> </div> </div> @@ -1450,19 +1831,19 @@ $$ LANGUAGE plpgsql;</code></pre> <p>While PostgreSQL provides the core replication mechanisms, automating the detection of primary failure and promotion of a standby requires external tooling. <a href="https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling" target="_blank">[PG Wiki Overview]</a></p> <h6>Common Tools</h6> <ul> - <li><strong><a href="https://patroni.readthedocs.io/" target="_blank">Patroni</a>:</strong> Popular Python-based template for building HA clusters. Uses a Distributed Configuration Store (DCS) like etcd, Consul, or Zookeeper for leader election and cluster state management. Manages PostgreSQL configuration and handles automated failover. Highly flexible and widely used.</li> - <li><strong><a href="https://repmgr.org/" target="_blank">repmgr</a>:</strong> Open-source tool suite focused specifically on managing PostgreSQL replication and failover. Provides monitoring, node management, and failover capabilities. Simpler than Patroni in some aspects, less reliant on external DCS.</li> - <li><strong><a href="https://www.pgpool.net/mediawiki/index.php/Main_Page" target="_blank">Pgpool-II</a>:</strong> Acts as a middleware proxy providing connection pooling, load balancing for read replicas, and automated failover capabilities (though often considered complex to configure correctly for HA). Can also parallelize queries.</li> - <li><strong>Cloud Provider Solutions:</strong> Managed services like AWS RDS, Google Cloud SQL, Azure Database for PostgreSQL often have built-in HA features that handle failover automatically behind the scenes.</li> + <li><a href="https://patroni.readthedocs.io/" target="_blank">Patroni</a>: Popular Python-based template for building HA clusters. Uses a Distributed Configuration Store (DCS) like etcd, Consul, or Zookeeper for leader election and cluster state management. Manages PostgreSQL configuration and handles automated failover/switchover. Highly flexible and widely used, often in Kubernetes environments.</li> + <li><a href="https://repmgr.org/" target="_blank">repmgr</a>: Open-source tool suite focused specifically on managing PostgreSQL streaming replication and failover. Provides monitoring, node management (`repmgrd` daemon), and failover/switchover commands. Can be simpler than Patroni for basic setups, less reliant on external DCS (but can integrate).</li> + <li><a href="https://www.pgpool.net/mediawiki/index.php/Main_Page" target="_blank">Pgpool-II</a>: Acts as a middleware proxy providing connection pooling, load balancing for read replicas, and automated failover capabilities (watchdog process). Can also parallelize queries. Often considered more complex to configure correctly for HA compared to dedicated failover managers.</li> + <li>Cloud Provider Solutions: Managed services like AWS RDS, Google Cloud SQL, Azure Database for PostgreSQL often have built-in HA features that handle failover automatically behind the scenes using their own control planes.</li> </ul> <h6>Key HA Concepts Managed by Tools</h6> <ul> - <li><strong>Health Monitoring:</strong> Regularly checking the status of primary and standby nodes.</li> - <li><strong>Failure Detection:</strong> Identifying when the primary server is unresponsive.</li> - <li><strong>Leader Election/Standby Promotion:</strong> Choosing the best standby to promote to the new primary.</li> - <li><strong>Topology Updates:</strong> Reconfiguring remaining standbys to follow the newly promoted primary.</li> - <li><strong>Client Redirection:</strong> Ensuring applications connect to the new primary (often via DNS update, virtual IP, or proxy layer like PgBouncer/HAProxy configured by the HA tool).</li> - <li><strong>Split-Brain Prevention:</strong> Mechanisms to ensure only one node acts as the primary at any time.</li> + <li>Health Monitoring: Regularly checking the status of primary and standby nodes via SQL connections or other means.</li> + <li>Failure Detection: Identifying when the primary server is unresponsive based on monitoring checks and timeouts.</li> + <li>Leader Election/Standby Promotion: Choosing the best standby (e.g., least lag, priority setting) to promote to the new primary using `pg_promote()` (or `pg_ctl promote`).</li> + <li>Topology Updates: Reconfiguring remaining standbys to follow the newly promoted primary (updating `primary_conninfo`).</li> + <li>Client Redirection: Ensuring applications connect to the new primary (often via DNS update, virtual IP failover managed by tools like Keepalived, or interaction with a load balancer/proxy layer like PgBouncer/HAProxy configured by the HA tool).</li> + <li>Split-Brain Prevention: Mechanisms (usually via the DCS in Patroni, or fencing scripts) to ensure only one node acts as the primary at any time, even during network partitions.</li> </ul> </div> </div> @@ -1470,27 +1851,33 @@ $$ LANGUAGE plpgsql;</code></pre> </div> <!-- /.row --> </div> <!-- /.schema-container --> - <!-- 9. BACKUP & RECOVERY --> + <!-- 10. BACKUP & RECOVERY --> <div class="schema-container cat-backup" data-section-id="section-backup"> <h2 class="section-title" id="title-backup">Backup & Recovery</h2> <div class="row"> <div class="col-lg-4 col-md-6"> <div class="info-card type-pgdump" id="card-pgdump"> <div class="card-body"><h5><i class="bi bi-file-earmark-zip"></i> Logical Backups (`pg_dump`)</h5> - <div class="card-content-wrapper"><p class="summary">Creates logical backups (SQL commands). Flexible, portable across versions/architectures. Slower restore for large DBs. <a href="https://www.postgresql.org/docs/current/app-pgdump.html" target="_blank">[docs]</a></p> + <div class="card-content-wrapper"><p class="summary">Creates logical backups (SQL commands). Flexible, portable. Slower restore for large DBs. Not for PITR. Use <span class="term">`pg_dumpall`</span> for globals. <a href="https://www.postgresql.org/docs/current/app-pgdump.html" target="_blank">[docs]</a></p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapsePgdump" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapsePgdump"> <h6>Tool & Purpose</h6> - <p><span class="term"><a href="https://www.postgresql.org/docs/current/app-pgdump.html" target="_blank">`pg_dump`</a></span> utility creates a script file containing SQL commands (`CREATE TABLE`, `COPY` data, `CREATE INDEX`, etc.) needed to recreate the database.</p> + <p><span class="term"><a href="https://www.postgresql.org/docs/current/app-pgdump.html" target="_blank">`pg_dump`</a></span> utility connects to a database and generates a script file containing SQL commands (`CREATE TABLE`, `COPY` data, `CREATE INDEX`, etc.) needed to recreate the database objects and data.</p> <h6>Key Features & Options</h6> <ul> - <li><strong>Formats:</strong> Plain text (`.sql`), Custom (`.dump`, compressed, requires `pg_restore`), Directory (parallel dump/restore), Tar. Custom/Directory formats are generally preferred for flexibility and parallel restore.</li> - <li><strong>Consistency:</strong> Takes a consistent snapshot using `REPEATABLE READ` or by acquiring brief locks.</li> - <li><strong>Granularity:</strong> Can dump entire DB, specific schemas (`-n schema`), specific tables (`-t table`). Can dump data-only (`-a`), schema-only (`-s`).</li> - <li><strong>Portability:</strong> Backups are generally portable across different machine architectures and PostgreSQL major versions (within reason).</li> - <li><strong>Restoration:</strong> Plain text restored via `psql < dump.sql`. Custom/Directory/Tar formats restored using <span class="term"><a href="https://www.postgresql.org/docs/current/app-pgrestore.html" target="_blank">`pg_restore`</a></span>.</li> - </ul> - <h6>Pros</h6> + <li>Formats: Plain text (`.sql`, `-Fp`), Custom (`.dump`, compressed, requires `pg_restore`, `-Fc`), Directory (`dir/`, parallel dump/restore, requires `pg_restore`, `-Fd`), Tar (`.tar`, requires `pg_restore`, `-Ft`). Custom/Directory formats are generally preferred for flexibility (parallel restore, object selection during restore).</li> + <li>Consistency: Takes a consistent snapshot using `REPEATABLE READ` transaction or by acquiring brief `ACCESS SHARE` locks (`--lock-wait-timeout`).</li> + <li>Granularity: Can dump entire DB, specific schemas (`-n schema`), specific tables (`-t table`). Exclude schemas/tables (`-N`, `-T`). Dump data-only (`-a`), schema-only (`-s`).</li> + <li>Portability: Backups are generally portable across different machine architectures and PostgreSQL major versions (though compatibility issues can arise with very old/new versions).</li> + <li>Restoration: Plain text restored via `psql < dump.sql`. Custom/Directory/Tar formats restored using <span class="term"><a href="https://www.postgresql.org/docs/current/app-pgrestore.html" target="_blank">`pg_restore`</a></span> (allows parallel restore with `-j`).</li> + </ul> + <h6>`pg_dumpall`</h6> + <ul> + <li><span class="term"><a href="https://www.postgresql.org/docs/current/app-pgdumpall.html" target="_blank">`pg_dumpall`</a></span> utility dumps *all* databases in a cluster plus global objects (roles, tablespaces). Output is plain SQL format.</li> + <li>Use `-g` for globals only, `-r` for roles only, `-t` for tablespaces only.</li> + <li>Essential for backing up the complete cluster state including users/permissions.</li> + </ul> + <h6>Pros</h6> <ul> <li>Very flexible for migrating data, restoring specific objects, upgrading major versions.</li> <li>Human-readable format (plain text).</li> @@ -1499,124 +1886,175 @@ $$ LANGUAGE plpgsql;</code></pre> <ul> <li>Can be slow to restore large databases because it replays all SQL commands and rebuilds indexes from scratch.</li> <li>Not suitable for Point-in-Time Recovery (PITR).</li> + <li>`pg_dump` (not `pg_dumpall`) doesn't include global objects.</li> </ul> <h6>Example</h6> - <p><code>pg_dump -U postgres -Fc -f my_database.dump my_database</code> (Custom format)<br/> - <code>pg_restore -U postgres -d target_database my_database.dump</code></p> - <p><span class="term"><a href="https://www.postgresql.org/docs/current/app-pgdumpall.html" target="_blank">`pg_dumpall`</a></span> dumps all databases plus global objects (roles, tablespaces).</p> + <p><code>pg_dump -U postgres -Fc -f my_database.dump my_database</code><br/> + <code>pg_dumpall -U postgres -f globals.sql --globals-only</code><br/> + <code>pg_restore -U postgres -d target_database -j 4 my_database.dump</code></p> </div> </div> </div> <div class="col-lg-4 col-md-6"> <div class="info-card type-physical-backup" id="card-physical-backup"> <div class="card-body"><h5><i class="bi bi-hdd-stack"></i> Physical Backups (`pg_basebackup`)</h5> - <div class="card-content-wrapper"><p class="summary">Creates a binary copy of the database cluster files. Faster restore than `pg_dump`. Foundation for PITR. <a href="https://www.postgresql.org/docs/current/app-pgbasebackup.html" target="_blank">[docs]</a></p> + <div class="card-content-wrapper"><p class="summary">Creates a binary copy of the database cluster files. Faster restore than `pg_dump`. Foundation for PITR & replication setup. <a href="https://www.postgresql.org/docs/current/app-pgbasebackup.html" target="_blank">[docs]</a></p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapsePhysical" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapsePhysical"> <h6>Tool & Purpose</h6> - <p><span class="term"><a href="https://www.postgresql.org/docs/current/app-pgbasebackup.html" target="_blank">`pg_basebackup`</a></span> connects to a running server (like a standby) and copies the entire data directory (`PGDATA`) file by file.</p> + <p><span class="term"><a href="https://www.postgresql.org/docs/current/app-pgbasebackup.html" target="_blank">`pg_basebackup`</a></span> connects to a running primary or standby server using the replication protocol and copies the entire data directory (`PGDATA`) file by file.</p> <h6>Key Features & Options</h6> <ul> - <li><strong>Binary Copy:</strong> Creates a filesystem-level copy of the database files.</li> - <li><strong>Consistency:</strong> Ensures a consistent snapshot by coordinating with the server via replication protocol.</li> - <li><strong>WAL Inclusion:</strong> Can optionally include required WAL files in the backup (`-X stream` or `-X fetch`) for a self-contained recoverable backup.</li> - <li><strong>Format:</strong> Plain (copy of `PGDATA`), Tar (`-Ft`).</li> + <li>Binary Copy: Creates a filesystem-level copy of the database files at a specific point in the WAL stream.</li> + <li>Consistency: Ensures a consistent snapshot by coordinating with the server (`pg_backup_start`/`stop`).</li> + <li>WAL Inclusion (`-X`/`--wal-method`): + <ul><li>`none`: Don't include WAL (requires separate WAL archiving for recovery).</li> + <li>`fetch`: Include WAL files needed to make backup consistent (fetched after files copied).</li> + <li>`stream`: Stream WAL files alongside data files during backup (preferred method, ensures needed WAL is captured).</li></ul> + </li> + <li>Format (`-F`/`--format`): `plain` (copy of `PGDATA`), `tar` (`-Ft`).</li> + <li>Progress (`-P`/`--progress`). Checkpoint (`-c`/`--checkpoint=fast|spread`). Rate Limit (`--max-rate`).</li> </ul> <h6>Pros</h6> <ul> - <li>Much faster restoration for large databases compared to `pg_dump`, as no SQL needs re-execution or index rebuilding (just WAL replay).</li> + <li>Much faster restoration for large databases compared to `pg_dump`, as no SQL needs re-execution or index rebuilding (just WAL replay from the backup checkpoint onwards).</li> <li>The necessary starting point for Point-in-Time Recovery (PITR).</li> + <li>Can be used to easily set up new streaming replication standbys.</li> </ul> <h6>Cons</h6> <ul> - <li>Less flexible: Restores the entire database cluster. Not easy to restore single tables.</li> - <li>Backup size is the full size of the data directory.</li> + <li>Less flexible: Restores the entire database cluster. Not easy to restore single tables (requires restoring cluster elsewhere and extracting).</li> + <li>Backup size is the full size of the data directory (unless using external tools with compression/deduplication).</li> <li>Generally not portable across major PostgreSQL versions or different machine architectures (endianness, block size).</li> </ul> <h6>Example</h6> - <p><code>pg_basebackup -U replicator -h primary_host -D /path/to/backup/dir -Ft -X stream -P</code> (Tar format, stream WALs, show progress)</p> + <p><code>pg_basebackup -h source_host -U replicator -D /path/to/backup/dir -Ft -X stream -P -R</code><br/> + (Creates backup in tar format, streams WAL, shows progress, and writes basic recovery settings to output dir)</p> </div> </div> </div> <div class="col-lg-4 col-md-6"> <div class="info-card type-pitr" id="card-pitr"> <div class="card-body"><h5><i class="bi bi-clock-history"></i> Point-in-Time Recovery (PITR)</h5> - <div class="card-content-wrapper"><p class="summary">Restore a database to any specific moment using a physical base backup and continuous <span class="term"><a href="https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-ARCHIVING-WAL" target="_blank">WAL Archiving</a></span>. <a href="https://www.postgresql.org/docs/current/continuous-archiving.html" target="_blank">[docs]</a></p> + <div class="card-content-wrapper"><p class="summary">Restore database to any specific moment using a physical base backup and continuous <span class="term">WAL Archiving</span>. Requires careful setup and testing. <a href="https://www.postgresql.org/docs/current/continuous-archiving.html" target="_blank">[docs]</a></p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapsePITR" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapsePITR"> <h6>Concept</h6> - <p>Combines a physical base backup with archived WAL segment files to replay database changes up to a specific target point (timestamp, transaction ID, named restore point). <a href="https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-PITR-RECOVERY" target="_blank">[recovery]</a></p> + <p>Combines a physical base backup with archived WAL segment files to replay database changes *beyond* the end of the base backup, stopping at a specific target point (timestamp, transaction ID, named restore point). <a href="https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-PITR-RECOVERY" target="_blank">[recovery]</a></p> <h6>Requirements</h6> <ol> - <li><strong>Physical Base Backup:</strong> Taken using `pg_basebackup` or similar method. <a href="https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-MAKING-BASE-BACKUP" target="_blank">[base backup]</a></li> - <li><strong>Continuous WAL Archiving:</strong> The primary server must be configured (<span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-WAL-LEVEL" target="_blank">`wal_level = replica`</a></span> or higher, <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-ARCHIVE-MODE" target="_blank">`archive_mode = on`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-ARCHIVE-COMMAND" target="_blank">`archive_command = '...'`</a></span>) to continuously copy completed WAL segments to a separate, safe archive location. <a href="https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-ARCHIVING-WAL" target="_blank">[archiving]</a></li> + <li>Physical Base Backup: Taken using `pg_basebackup` or similar method (e.g., `pgBackRest`, `Barman`). <a href="https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-MAKING-BASE-BACKUP" target="_blank">[base backup]</a></li> + <li>Continuous WAL Archiving: The primary server must be configured (<span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-WAL-LEVEL" target="_blank">`wal_level = replica`</a></span> or higher, <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-ARCHIVE-MODE" target="_blank">`archive_mode = on`</a></span> or `always`, <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-ARCHIVE-COMMAND" target="_blank">`archive_command`</a></span>) to continuously copy completed WAL segments to a separate, safe archive location (e.g., NFS, S3 via helper scripts). <a href="https://www.postgresql.org/docs/current/continuous-archiving.html#BACKUP-ARCHIVING-WAL" target="_blank">[archiving]</a></li> </ol> - <h6>Recovery Process</h6> + <h6>Recovery Process (High Level)</h6> <ol> <li>Restore the chosen physical base backup to a new data directory.</li> - <li>Create a <span class="term"><a href="https://www.postgresql.org/docs/current/recovery-config.html#RECOVERY-SIGNAL-FILE" target="_blank">`recovery.signal`</a></span> file (Postgres 12+) or `recovery.conf` file (older versions) in the restored data directory.</li> - <li>Configure <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-RESTORE-COMMAND" target="_blank">`restore_command`</a></span> in `postgresql.conf` (or `recovery.conf`) to tell Postgres how to fetch WAL files from the archive location (e.g., `restore_command = 'cp /path/to/archive/%f %p'`).</li> - <li>Specify the recovery target using parameters like <span class="term"><a href="https://www.postgresql.org/docs/current/recovery-target-settings.html#RECOVERY-TARGET-TIME" target="_blank">`recovery_target_time`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/recovery-target-settings.html#RECOVERY-TARGET-XID" target="_blank">`recovery_target_xid`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/recovery-target-settings.html#RECOVERY-TARGET-NAME" target="_blank">`recovery_target_name`</a></span>, or `recovery_target = 'immediate'`. Use <span class="term"><a href="https://www.postgresql.org/docs/current/recovery-target-settings.html#RECOVERY-TARGET-ACTION" target="_blank">`recovery_target_action = 'promote'`</a></span> (PG12+) or `standby_mode = 'off'` (older) to bring the server up after reaching the target. <a href="https://www.postgresql.org/docs/current/recovery-target-settings.html" target="_blank">[target settings]</a></li> - <li>Start PostgreSQL on the restored directory. It will enter recovery mode, replay WALs from the archive until the target is reached, then become operational.</li> + <li>Ensure the WAL archive is accessible from the recovery location.</li> + <li>Configure recovery settings in `postgresql.conf` (PG12+) or create `recovery.conf` (pre-PG12). + <ul><li><a href="https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-RESTORE-COMMAND" target="_blank">`restore_command`</a>: Command to fetch WAL files from archive (e.g., `restore_command = 'cp /path/to/archive/%f %p'` or script using `aws s3 cp`, etc.). `%f`=filename, `%p`=path to copy to.</li> + <li><a href="https://www.postgresql.org/docs/current/recovery-target-settings.html" target="_blank">Recovery Target</a>: Specify *when* to stop recovery (e.g., <span class="term">`recovery_target_time`</span>, `_xid`, `_lsn`, `_name`). Or `recovery_target = 'immediate'` to recover just past backup end.</li> + <li><a href="https://www.postgresql.org/docs/current/recovery-target-settings.html#RECOVERY-TARGET-ACTION" target="_blank">`recovery_target_action`</a>: `pause` (default), `promote` (finish recovery and become primary), `shutdown`.</li> + </ul> + </li> + <li>Create a <span class="term"><a href="https://www.postgresql.org/docs/current/recovery-config.html#RECOVERY-SIGNAL-FILE" target="_blank">`recovery.signal`</a></span> file (PG12+) in the data directory to trigger recovery mode on startup. (Remove `standby.signal` if present).</li> + <li>Start PostgreSQL on the restored directory. It will enter recovery mode, replay WALs using `restore_command` until the target is reached, then perform the `recovery_target_action`.</li> </ol> <h6>Benefits</h6> <ul> - <li>Recover from data corruption, accidental deletes/updates by restoring to a point *before* the incident occurred.</li> - <li>Provides fine-grained disaster recovery capability.</li> + <li>Recover from logical errors (accidental deletes/updates) by restoring to a point *before* the incident occurred.</li> + <li>Provides fine-grained disaster recovery capability, minimizing data loss.</li> </ul> - <h6>Tools</h6> - <p>Tools like <a href="https://pgbackrest.org/" target="_blank">`pgBackRest`</a> or <a href="https://pgbarman.org/" target="_blank">`Barman`</a> significantly simplify managing base backups, WAL archiving, and the PITR process, adding features like parallel backup/restore, incremental backups, retention policies, and validation.</p> + <h6>Tools & Verification</h6> + <p>Tools like <a href="https://pgbackrest.org/" target="_blank">`pgBackRest`</a> or <a href="https://pgbarman.org/" target="_blank">`Barman`</a> significantly simplify managing base backups, WAL archiving, retention, and the PITR process. **Regularly test your backups and PITR procedures!**</p> </div> </div> </div> + <div class="col-lg-4 col-md-6"> + <div class="info-card type-backup-tools" id="card-backup-tools"> + <div class="card-body"><h5><i class="bi bi-tools"></i> Backup Management Tools</h5> + <div class="card-content-wrapper"><p class="summary">Tools like <span class="term">pgBackRest</span>, <span class="term">Barman</span>, <span class="term">WAL-G</span> simplify backup/restore/PITR, adding features like parallel ops, incremental/diff backups, compression, validation.</p> + <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseBackupTools" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> + <div class="collapse collapse-content" id="collapseBackupTools"> + <p>While `pg_dump` and `pg_basebackup` provide basic backup capabilities, dedicated tools offer significant advantages for managing backups, especially in production environments.</p> + <h6>Popular Tools</h6> + <ul> + <li><a href="https://pgbackrest.org/" target="_blank">pgBackRest</a>:** Feature-rich, reliable, high-performance tool. + <ul><li>Features: Parallel backup/restore, full/incremental/differential backups, compression, checksums, backup validation, S3/Azure/GCS support, flexible retention policies, PITR automation, tablespace support.</li> + <li>Widely adopted in the community. Actively developed.</li></ul> + </li> + <li><a href="https://pgbarman.org/" target="_blank">Barman (Backup and Recovery Manager)</a>:** Python-based tool, integrates well with standard PG tools. + <ul><li>Features: Manages base backups and WAL archiving, PITR, compression, remote backup/restore, replication slot management, backup catalog.</li> + <li>Mature and stable option.</li></ul> + </li> + <li><a href="https://github.com/wal-g/wal-g" target="_blank">WAL-G</a>:** Successor to WAL-E. Focuses on WAL archiving/fetching and base backups, often to cloud storage. + <ul><li>Features: Parallel WAL push/fetch, compression, encryption, S3/GCS/Azure/Swift support, base backups, PITR support.</li> + <li>Often used for its efficient WAL archiving capabilities.</li></ul> + </li> + </ul> + <h6>Why Use Them?</h6> + <ul> + <li>Automation:** Simplify complex processes like WAL archiving, PITR setup, retention management.</li> + <li>Performance:** Parallel operations significantly speed up backup/restore for large databases.</li> + <li>Efficiency:** Incremental/differential backups save time and storage space compared to repeated full base backups. Compression reduces storage costs.</li> + <li>Reliability:** Features like checksums and validation help ensure backup integrity.</li> + <li>Cloud Integration:** Built-in support for major cloud storage providers.</li> + </ul> + <h6>Recommendation</h6> + <p>For any serious production deployment requiring PITR, using a dedicated backup tool like `pgBackRest` or `Barman` is highly recommended over manual scripting of `pg_basebackup` and `archive_command`.</p> + <h6>Verification</h6> + <p>Regardless of the tool used, **regularly schedule and perform test restores** (ideally PITR tests) to ensure your backups are valid and your recovery procedures work as expected.</p> + </div> + </div> + </div> </div> <!-- /.row --> </div> <!-- /.schema-container --> - <!-- 10. SECURITY --> + <!-- 11. SECURITY --> <div class="schema-container cat-security" data-section-id="section-security"> <h2 class="section-title" id="title-security">Security</h2> <div class="row"> <div class="col-lg-4 col-md-6"> <div class="info-card type-auth" id="card-auth"> <div class="card-body"><h5><i class="bi bi-key"></i> Authentication (`pg_hba.conf`)</h5> - <div class="card-content-wrapper"><p class="summary">Host-Based Authentication controls *who* can connect from *where* using *which* method (e.g., <span class="term"><a href="https://www.postgresql.org/docs/current/auth-methods.html#AUTH-SCRAM-SHA-256" target="_blank">`scram-sha-256`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/auth-methods.html#AUTH-MD5" target="_blank">`md5`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/auth-methods.html#AUTH-PEER" target="_blank">`peer`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/auth-methods.html#AUTH-LDAP" target="_blank">`ldap`</a></span>). <a href="https://www.postgresql.org/docs/current/auth-pg-hba-conf.html" target="_blank">[docs]</a></p> + <div class="card-content-wrapper"><p class="summary">Host-Based Authentication controls *who* can connect from *where* using *which* method (e.g., <span class="term">`scram-sha-256`</span>, <span class="term">`md5`</span>, <span class="term">`peer`</span>, <span class="term">`cert`</span>, <span class="term">`ldap`</span>). Order matters! <a href="https://www.postgresql.org/docs/current/auth-pg-hba-conf.html" target="_blank">[docs]</a></p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseAuth" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseAuth"> - <h6>File Location</h6> + <h6>File Location & Reload</h6> <p>Located in the data directory (`PGDATA`). Requires a server reload (`pg_ctl reload` or `SELECT pg_reload_conf();`) to apply changes.</p> <h6>Format</h6> <p>Each line defines a connection rule: <a href="https://www.postgresql.org/docs/current/auth-pg-hba-conf.html" target="_blank">[docs]</a></p> <p><code>type database user address method [options]</code></p> <ul> - <li><strong>`type`</strong>: Connection type (`local` for Unix sockets, `host` for TCP/IP, `hostssl`, `hostnossl`).</li> - <li><strong>`database`</strong>: Database name (`all`, specific name, comma-separated list, `@file`).</li> - <li><strong>`user`</strong>: Role name (`all`, specific name, comma-separated list, `@file`).</li> - <li><strong>`address`</strong>: Client IP address range (CIDR notation like `192.168.1.0/24`, `samehost`, `samenet`, `all`). Required for `host*` types.</li> - <li><strong>`method`</strong>: Authentication method: <a href="https://www.postgresql.org/docs/current/auth-methods.html" target="_blank">[methods]</a> + <li>`type`: Connection type (`local` for Unix sockets, `host` for non-SSL TCP/IP, `hostssl` for SSL TCP/IP, `hostnossl` for non-SSL TCP/IP).</li> + <li>`database`: Database name (`all`, specific name, comma-separated list, `@file`, `replication`).</li> + <li>`user`: Role name (`all`, specific name, comma-separated list, `@file`).</li> + <li>`address`: Client IP address range (CIDR notation like `192.168.1.0/24`, `samehost`, `samenet`, `all`). Required for `host*` types.</li> + <li>`method`: Authentication method: <a href="https://www.postgresql.org/docs/current/auth-methods.html" target="_blank">[methods]</a> <ul> - <li>`trust`: Allow connection unconditionally (dangerous!).</li> + <li>`trust`: Allow connection unconditionally (Use only for `local` socket peer auth if absolutely necessary, very dangerous otherwise).</li> <li>`reject`: Deny connection unconditionally.</li> - <li>`scram-sha-256`: Salted Challenge Response Authentication (preferred password method).</li> - <li>`md5`: Older challenge-response password method (less secure than SCRAM).</li> - <li>`password`: Send plain text password (avoid unless connection is SSL/TLS secured).</li> + <li>`scram-sha-256`: Salted Challenge Response Authentication (preferred password method since PG10).</li> + <li>`md5`: Older challenge-response password method (less secure than SCRAM, avoid if possible).</li> + <li>`password`: Send plain text password (avoid unless connection is SSL/TLS secured via `hostssl`).</li> <li>`peer`: For `local` connections, authenticate if OS username matches database role name.</li> - <li>`ident`: For TCP/IP, query client's ident server (rarely used).</li> - <li>`ldap`, `gss`, `sspi`, `pam`, `cert`: Integrate with external auth systems.</li> + <li>`ident`: For TCP/IP, query client's ident server (rarely used, often blocked).</li> + <li>`ldap`, `gss`, `sspi`, `pam`, `radius`: Integrate with external auth systems.</li> + <li>`cert`: Authenticate using client SSL certificate (`clientcert=verify-ca` or `verify-full` option).</li> </ul> </li> - <li><strong>`options`</strong>: Method-specific options (e.g., for LDAP, PAM).</li> + <li>`options`: Method-specific options (e.g., `clientcert`, LDAP config, PAM service name).</li> </ul> <h6>Processing Order</h6> - <p>Rules are processed sequentially. The *first* matching rule for the connection type, database, user, and source address is used.</p> + <p>Rules are processed sequentially from top to bottom. The *first* matching rule for the connection type, database, user, and source address is used. Place more specific rules *before* more general ones.</p> <h6>Best Practices</h6> - <p>Be specific. Avoid `all all 0.0.0.0/0 trust`. Use `scram-sha-256` for passwords. Use `hostssl` to enforce SSL/TLS for remote TCP/IP connections.</p> + <p>Be specific. Avoid `all all 0.0.0.0/0 trust`. Use `scram-sha-256` for passwords. Use `hostssl` to enforce SSL/TLS for remote TCP/IP connections. Use `peer` authentication for local superuser access where appropriate. Limit `replication` connections to specific users/addresses.</p> </div> </div> </div> <div class="col-lg-4 col-md-6"> <div class="info-card type-permissions" id="card-permissions"> <div class="card-body"><h5><i class="bi bi-person-check"></i> Roles & Permissions</h5> - <div class="card-content-wrapper"><p class="summary">Unified <span class="term"><a href="https://www.postgresql.org/docs/current/database-roles.html" target="_blank">Role</a></span> concept. Use <span class="term"><a href="https://www.postgresql.org/docs/current/sql-grant.html" target="_blank">`GRANT`</a></span>/<span class="term"><a href="https://www.postgresql.org/docs/current/sql-revoke.html" target="_blank">`REVOKE`</a></span> for privileges. Set <span class="term"><a href="https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html" target="_blank">`DEFAULT PRIVILEGES`</a></span>. <a href="https://www.postgresql.org/docs/current/ddl-rowsecurity.html" target="_blank">Row-Level Security (RLS)</a>.</p> + <div class="card-content-wrapper"><p class="summary">Unified <span class="term">Role</span> concept. Use <span class="term">`GRANT`</span>/<span class="term">`REVOKE`</span> for privileges (incl. column-level). Set <span class="term">`DEFAULT PRIVILEGES`</span>. Use <span class="term">Row-Level Security (RLS)</span> for fine-grained access.</p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapsePermissions" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapsePermissions"> <h6>Roles</h6> @@ -1628,11 +2066,12 @@ $$ LANGUAGE plpgsql;</code></pre> <h6>Privileges (`GRANT`/`REVOKE`)</h6> <ul> <li>Control access to database objects. <a href="https://www.postgresql.org/docs/current/privileges.html" target="_blank">[docs]</a></li> - <li>Syntax: <span class="term"><a href="https://www.postgresql.org/docs/current/sql-grant.html" target="_blank">`GRANT {privilege [, ...]|ALL} ON {object_type|ALL TABLES IN SCHEMA ...} object_name TO {role_name|PUBLIC};`</a></span></li> - <li>Object Types: `TABLE`, `SEQUENCE`, `DATABASE`, `DOMAIN`, `FOREIGN DATA WRAPPER`, `FOREIGN SERVER`, `FUNCTION`, `LANGUAGE`, `SCHEMA`, `TABLESPACE`, `TYPE`.</li> + <li>Syntax: <span class="term"><a href="https://www.postgresql.org/docs/current/sql-grant.html" target="_blank">`GRANT {privilege [, ...]|ALL} [(column [, ...])] ON {object_type|ALL TABLES IN SCHEMA ...} object_name TO {role_name|PUBLIC};`</a></span></li> + <li>Object Types: `TABLE`, `SEQUENCE`, `DATABASE`, `DOMAIN`, `FOREIGN DATA WRAPPER`, `FOREIGN SERVER`, `FUNCTION`, `PROCEDURE`, `LANGUAGE`, `SCHEMA`, `TABLESPACE`, `TYPE`.</li> <li>Privileges: `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, `REFERENCES`, `TRIGGER`, `CREATE`, `CONNECT`, `TEMPORARY`, `EXECUTE`, `USAGE`.</li> - <li><span class="term"><a href="https://www.postgresql.org/docs/current/database-roles.html#DATABASE-ROLES-PUBLIC" target="_blank">`PUBLIC`</a></span> pseudo-role grants to all roles. Use sparingly.</li> - <li>Use <span class="term"><a href="https://www.postgresql.org/docs/current/sql-revoke.html" target="_blank">`REVOKE`</a></span> to remove privileges.</li> + <li>Column-Level:** Can grant `SELECT`, `INSERT`, `UPDATE`, `REFERENCES` on specific columns (`GRANT SELECT (id, name) ON ...`).</li> + <li><span class="term"><a href="https://www.postgresql.org/docs/current/database-roles.html#DATABASE-ROLES-PUBLIC" target="_blank">`PUBLIC`</a></span> pseudo-role grants to all roles. Revoke default public privileges on schemas (esp. `public`) and potentially functions.</li> + <li>Use <span class="term"><a href="https://www.postgresql.org/docs/current/sql-revoke.html" target="_blank">`REVOKE`</a></span> to remove privileges. Use `GRANT ... WITH GRANT OPTION` carefully.</li> </ul> <h6>Default Privileges</h6> <ul> @@ -1642,10 +2081,10 @@ $$ LANGUAGE plpgsql;</code></pre> </ul> <h6>Row-Level Security (RLS)</h6> <ul> - <li>Define policies (<span class="term"><a href="https://www.postgresql.org/docs/current/sql-createpolicy.html" target="_blank">`CREATE POLICY ...`</a></span>) that restrict which *rows* a user can view or modify within a table, based on user characteristics or data values. <a href="https://www.postgresql.org/docs/current/ddl-rowsecurity.html" target="_blank">[docs]</a></li> - <li>Policies are applied *after* standard SQL permissions.</li> - <li>Requires <span class="term"><a href="https://www.postgresql.org/docs/current/sql-altertable.html" target="_blank">`ALTER TABLE ... ENABLE ROW LEVEL SECURITY;`</a></span>.</li> - <li>Roles need <span class="term"><a href="https://www.postgresql.org/docs/current/role-attributes.html" target="_blank">`BYPASSRLS`</a></span> attribute to ignore policies (use with extreme care).</li> + <li>Define policies (<span class="term"><a href="https://www.postgresql.org/docs/current/sql-createpolicy.html" target="_blank">`CREATE POLICY ...`</a></span>) that restrict which *rows* a user can view or modify within a table, based on user characteristics (`current_user`), data values, or security labels. <a href="https://www.postgresql.org/docs/current/ddl-rowsecurity.html" target="_blank">[docs]</a></li> + <li>Policies are applied *after* standard SQL permissions. Define `USING` (for SELECT/read) and `WITH CHECK` (for INSERT/UPDATE/write) expressions.</li> + <li>Requires enabling on the table: <span class="term"><a href="https://www.postgresql.org/docs/current/sql-altertable.html" target="_blank">`ALTER TABLE ... ENABLE ROW LEVEL SECURITY;`</a></span></li> + <li>Roles need <span class="term"><a href="https://www.postgresql.org/docs/current/role-attributes.html" target="_blank">`BYPASSRLS`</a></span> attribute to ignore policies (use with extreme care, usually only for admin/backup roles). Table owners generally bypass RLS too.</li> </ul> </div> </div> @@ -1653,7 +2092,7 @@ $$ LANGUAGE plpgsql;</code></pre> <div class="col-lg-4 col-md-6"> <div class="info-card type-ssl" id="card-ssl"> <div class="card-body"><h5><i class="bi bi-shield-check"></i> SSL/TLS Encryption</h5> - <div class="card-content-wrapper"><p class="summary">Encrypt client-server communication. Configure server (<span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-SSL" target="_blank">`ssl=on`</a></span>, certs) and enforce via <span class="term"><a href="https://www.postgresql.org/docs/current/auth-pg-hba-conf.html" target="_blank">`pg_hba.conf`</a></span> (`hostssl`). <a href="https://www.postgresql.org/docs/current/ssl-tcp.html" target="_blank">[docs]</a></p> + <div class="card-content-wrapper"><p class="summary">Encrypt client-server communication. Configure server (<span class="term">`ssl=on`</span>, certs) and enforce via <span class="term">`pg_hba.conf`</span> (`hostssl`). Verify certs via client <span class="term">`sslmode`</span>. <a href="https://www.postgresql.org/docs/current/ssl-tcp.html" target="_blank">[docs]</a></p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseSSL" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseSSL"> <h6>Purpose</h6> @@ -1661,156 +2100,260 @@ $$ LANGUAGE plpgsql;</code></pre> <h6>Server Configuration (`postgresql.conf`)</h6> <ul> <li><span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-SSL" target="_blank">`ssl = on`</a></span>: Enable SSL/TLS support.</li> - <li><span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-SSL-CERT-FILE" target="_blank">`ssl_cert_file = 'server.crt'`</a></span>: Path to the server's certificate file.</li> - <li><span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-SSL-KEY-FILE" target="_blank">`ssl_key_file = 'server.key'`</a></span>: Path to the server's private key file. Permissions must be restrictive (e.g., `0600`).</li> - <li><span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-SSL-CA-FILE" target="_blank">`ssl_ca_file = 'root.crt'`</a></span>: (Optional) Path to trusted Certificate Authority certs for verifying client certificates.</li> - <li><span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-SSL-CIPHERS" target="_blank">`ssl_ciphers = 'HIGH:!aNULL'`</a></span>: (Optional) Configure allowed cipher suites.</li> - <li><a href="https://www.postgresql.org/docs/current/ssl-tcp.html#SSL-SERVER-FILES" target="_blank">[Server Files]</a></li> + <li><span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-SSL-CERT-FILE" target="_blank">`ssl_cert_file = 'server.crt'`</a></span>: Path to the server's PEM certificate file.</li> + <li><span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-SSL-KEY-FILE" target="_blank">`ssl_key_file = 'server.key'`</a></span>: Path to the server's PEM private key file. Permissions must be restrictive (e.g., `0600`, readable only by postgres user).</li> + <li><span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-SSL-CA-FILE" target="_blank">`ssl_ca_file = 'root.crt'`</a></span>: (Optional) Path to trusted Certificate Authority certs for verifying client certificates (if using `cert` auth).</li> + <li><span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-PASSWORD-ENCRYPTION" target="_blank">`password_encryption`</a></span> should be `scram-sha-256` (or `md5`) even with SSL.</li> + <li><a href="https://www.postgresql.org/docs/current/ssl-tcp.html#SSL-SERVER-FILES" target="_blank">[Server Files]</a> Restart server after changing SSL settings.</li> </ul> <h6>Enforcing SSL/TLS (`pg_hba.conf`)</h6> <ul> - <li>Use connection type `hostssl` instead of `host` for specific rules. This forces clients matching that rule to use an SSL connection. <a href="https://www.postgresql.org/docs/current/auth-pg-hba-conf.html" target="_blank">[docs]</a></li> + <li>Use connection type `hostssl` instead of `host` for specific rules. This forces clients matching that rule to use an SSL connection; non-SSL attempts will be rejected. <a href="https://www.postgresql.org/docs/current/auth-pg-hba-conf.html" target="_blank">[docs]</a></li> <li>Example: `hostssl all myuser 192.168.1.0/24 scram-sha-256` (Requires SSL for `myuser` from that subnet).</li> - <li>Using `host` allows both SSL and non-SSL connections if `ssl=on` is set.</li> + <li>Using `host` allows both SSL and non-SSL connections if `ssl=on` is set. Use `hostnossl` to explicitly allow only non-SSL (rarely needed).</li> </ul> <h6>Client Configuration</h6> <ul> <li>Clients specify connection parameters to control SSL usage. <a href="https://www.postgresql.org/docs/current/libpq-ssl.html" target="_blank">[libpq client docs]</a></li> - <li>Connection String/Libpq: <span class="term"><a href="https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNECT-SSLMODE" target="_blank">`sslmode`</a></span> parameter (`disable`, `allow`, `prefer`, `require`, `verify-ca`, `verify-full`). - <ul><li>`require`: Enforce SSL, don't verify CA.</li> - <li>`verify-ca`: Enforce SSL, verify server cert against trusted CA (`sslrootcert` parameter).</li> - <li>`verify-full`: Enforce SSL, verify CA, and verify server hostname matches certificate CN/SAN. **Most secure.**</li></ul> + <li>Connection String/Libpq: <span class="term"><a href="https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNECT-SSLMODE" target="_blank">`sslmode`</a></span> parameter is crucial: + <ul><li>`disable`: No SSL.</li> + <li>`allow`: Try non-SSL first, then SSL if server requires.</li> + <li>`prefer`: Try SSL first, then non-SSL if server doesn't support. (Default)</li> + <li>`require`: Only try SSL. Fail if server doesn't support. (Doesn't verify cert).</li> + <li>`verify-ca`: Only try SSL, verify server cert against trusted CA (`sslrootcert` parameter). Fail if no match.</li> + <li>`verify-full`: Only try SSL, verify CA, *and* verify server hostname matches certificate CN/SAN. **Most secure.**</li></ul> </li> - <li>Other parameters: `sslcert`, `sslkey`, `sslrootcert`.</li> + <li>Other parameters: `sslcert` (client cert), `sslkey` (client key), `sslrootcert` (path to trusted CA cert(s) on client).</li> </ul> <h6>Client Certificate Authentication</h6> - <p>Can configure server to require clients to present a valid certificate signed by a trusted CA (`clientcert=verify-ca` or `verify-full` in `pg_hba.conf`). <a href="https://www.postgresql.org/docs/current/auth-methods.html#AUTH-CERT" target="_blank">[cert auth]</a></p> + <p>Configure server to require clients to present a valid certificate signed by a trusted CA (via `ssl_ca_file`) using `clientcert=verify-ca` or `verify-full` option in the `pg_hba.conf` rule's `options` field, combined with `auth-method=cert`. <a href="https://www.postgresql.org/docs/current/auth-methods.html#AUTH-CERT" target="_blank">[cert auth]</a></p> + </div> + </div> + </div> + <div class="col-lg-4 col-md-6"> + <div class="info-card type-secdef" id="card-secdef"> + <div class="card-body"><h5><i class="bi bi-incognito"></i> `SECURITY DEFINER` Functions</h5> + <div class="card-content-wrapper"><p class="summary">Functions run with privileges of the *definer* (owner), not the *caller*. Powerful but dangerous. Secure carefully! <a href="https://www.postgresql.org/docs/current/sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY" target="_blank">[docs]</a></p> + <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseSecDef" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> + <div class="collapse collapse-content" id="collapseSecDef"> + <h6>Concept</h6> + <p>By default, functions run with `SECURITY INVOKER` semantics - they execute with the permissions of the user calling the function. Functions defined with `SECURITY DEFINER` execute with the privileges of the user who *owns* the function.</p> + <h6>Use Cases</h6> + <ul> + <li>Allowing less privileged users to perform specific, controlled actions on tables they don't normally have direct access to (e.g., insert into a specific log table, run a specific update).</li> + <li>Abstracting complex operations involving multiple tables with different permissions.</li> + </ul> + <h6>Security Risks & Best Practices (CRITICAL!)</h6> + <p>If not written carefully, `SECURITY DEFINER` functions can be exploited for privilege escalation.</p> + <ul> + <li>Restrict `EXECUTE` Privilege:** `REVOKE EXECUTE ON FUNCTION my_secdef_func(...) FROM PUBLIC;` then `GRANT EXECUTE ON FUNCTION ... TO specific_role;`. Never grant `EXECUTE` to `PUBLIC` unless absolutely necessary and safe.</li> + <li>Set Secure `search_path`:** Inside the function, *always* set a safe `search_path` to prevent hijacking by objects in untrusted schemas: `SET search_path = pg_catalog, public;` (or just `pg_catalog` if `public` isn't needed). Do this at the function definition level: + <pre><code>CREATE FUNCTION ... SECURITY DEFINER +<span class="term">SET search_path = pg_catalog;</span> +AS $$ ... $$ LANGUAGE ...;</code></pre> <a href="https://www.postgresql.org/docs/current/sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY-DEFINER" target="_blank">[secure path]</a></li> + <li>Qualify All Object Names:** Explicitly schema-qualify all tables, functions, types, etc. accessed within the function (e.g., `SELECT * FROM myschema.mytable;`) unless the `search_path` is absolutely trustworthy (rare).</li> + <li>Validate Inputs:** Treat all function arguments as potentially hostile. Sanitize and validate them appropriately.</li> + <li>Principle of Least Privilege:** Ensure the function *owner* has only the minimum privileges necessary for the function's operation. Avoid owning `SECURITY DEFINER` functions as a superuser if possible.</li> + </ul> + <h6>Example (Conceptual)</h6> + <pre><code>-- Owner: admin_role (has INSERT on audit_log) +-- Caller: app_user (NO insert on audit_log) +CREATE FUNCTION log_action(user_id integer, action text) +RETURNS void AS $$ +BEGIN + INSERT INTO audit_schema.audit_log(actor_id, action_desc, log_time) + VALUES (user_id, action, now()); +END; +$$ LANGUAGE plpgsql SECURITY DEFINER + SET search_path = pg_catalog; -- Secure path! + +REVOKE EXECUTE ON FUNCTION log_action(integer, text) FROM PUBLIC; +GRANT EXECUTE ON FUNCTION log_action(integer, text) TO app_user;</code></pre> + </div> + </div> + </div> + <div class="col-lg-4 col-md-6"> + <div class="info-card type-enc-at-rest" id="card-enc-at-rest"> + <div class="card-body"><h5><i class="bi bi-database-lock"></i> Encryption at Rest</h5> + <div class="card-content-wrapper"><p class="summary">Protect data stored on disk. Options: Full Disk Encryption (FDE) at OS/HW level (common), filesystem-level, or column-level via <span class="term">`pgcrypto`</span>.</p> + <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseEncAtRest" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> + <div class="collapse collapse-content" id="collapseEncAtRest"> + <h6>Goal</h6> + <p>Prevent unauthorized access to database files (`PGDATA`, WAL, backups) if physical storage media is compromised.</p> + <h6>Common Approaches</h6> + <ul> + <li>Full Disk Encryption (FDE):** + <ul><li>Implemented at the Operating System level (e.g., LUKS on Linux, BitLocker on Windows, FileVault on macOS) or Hardware level (Self-Encrypting Drives - SEDs).</li> + <li>Encrypts the entire block device containing the database files.</li> + <li>Transparent to PostgreSQL. Protects against offline attacks (stolen drives).</li> + <li>Does *not* protect against threats when the OS is running and the filesystem is mounted (e.g., malicious OS user, SQL injection).</li> + <li>**Generally the recommended baseline approach.** Often required by compliance standards.</li></ul> + </li> + <li>Filesystem-Level Encryption:** + <ul><li>Some filesystems offer native encryption features (e.g., ZFS encryption, ext4 fscrypt).</li> + <li>Encrypts files or directories within a mounted filesystem.</li> + <li>May offer more granularity than FDE. Similar protection profile (primarily offline attacks).</li></ul> + </li> + <li>Column-Level Encryption (using <span class="term"><a href="https://www.postgresql.org/docs/current/pgcrypto.html" target="_blank">`pgcrypto`</a></span>):** + <ul><li>Encrypt specific sensitive columns within tables using functions like `pgp_sym_encrypt()` / `pgp_sym_decrypt()` (symmetric) or `pgp_pub_encrypt()` / `pgp_priv_decrypt()` (asymmetric).</li> + <li>Provides protection even from privileged database users (e.g., DBAs) if they don't have the decryption key.</li> + <li>Requires application logic to handle encryption/decryption. Keys must be managed securely outside the database.</li> + <li>Significantly impacts query performance (cannot index encrypted data directly for searching, except with workarounds like hashing or specialized indexes on unencrypted components if feasible).</li> + <li>Use case: Protecting highly sensitive specific fields (e.g., SSNs, credit card numbers - though PCI DSS has strict rules).</li></ul> + </li> + <li>Transparent Data Encryption (TDE):** + <ul><li>Some commercial forks or extensions of PostgreSQL offer TDE solutions that automatically encrypt/decrypt data files, often with external key management integration.</li> + <li>Not part of standard PostgreSQL core.</li></ul> + </li> + </ul> + <h6>Key Management</h6> + <p>Securely managing encryption keys is crucial for FDE, filesystem, and column-level encryption. Use hardware security modules (HSMs), cloud KMS, or robust key management practices.</p> + </div> + </div> + </div> + <div class="col-lg-4 col-md-6"> + <div class="info-card type-audit" id="card-audit"> + <div class="card-body"><h5><i class="bi bi-clipboard2-data"></i> Auditing</h5> + <div class="card-content-wrapper"><p class="summary">Track database activity. Use standard logging (`log_statement`, `log_connections`, etc.) or dedicated extension <span class="term"><a href="https://www.pgaudit.org/" target="_blank">`pgaudit`</a></span> for detailed, structured logging.</p> + <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseAudit" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> + <div class="collapse collapse-content" id="collapseAudit"> + <h6>Why Audit?</h6> + <p>Security monitoring (detecting suspicious activity), compliance requirements (HIPAA, SOX, GDPR), troubleshooting application behavior.</p> + <h6>Built-in Logging Parameters (`postgresql.conf`)</h6> + <ul> + <li><span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-STATEMENT" target="_blank">`log_statement`</a></span>: Logs executed SQL statements (`none`, `ddl`, `mod`, `all`). `all` is very verbose, use with caution.</li> + <li><span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-MIN-DURATION-STATEMENT" target="_blank">`log_min_duration_statement`</a></span>: Log statements exceeding a duration (ms). Good for performance analysis, less for security audit.</li> + <li><span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-CONNECTIONS" target="_blank">`log_connections`</a></span> / <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-DISCONNECTIONS" target="_blank">`log_disconnections`</a></span>: Log session start/end.</li> + <li><span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-HOSTNAME" target="_blank">`log_hostname`</a></span>: Log client hostname (can add overhead).</li> + <li><span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-LINE-PREFIX" target="_blank">`log_line_prefix`</a></span>: Customize log entry format (include user, db, pid, timestamp, etc. - essential context). Example: `'%m [%p] %q%u@%d/%a '`.</li> + <li>Limitations: Can be hard to parse reliably, may miss some details, performance overhead with `log_statement=all`.</li> + </ul> + <h6>`pgaudit` Extension</h6> + <ul> + <li><a href="https://www.pgaudit.org/" target="_blank">`pgaudit`</a> provides detailed, structured audit logging. Requires `shared_preload_libraries = 'pgaudit'` and restart.</li> + <li>Features:** + <ul><li>Logs specific event types (READ, WRITE, FUNCTION, ROLE, DDL, MISC).</li> + <li>Fine-grained object-level auditing (log access only to specific tables).</li> + <li>Logs statement text *and* parameters separately (optional).</li> + <li>Structured output format (e.g., JSON) for easier parsing by log analysis tools (SIEM).</li> + </ul> + </li> + <li>Configuration:** Via `postgresql.conf` parameters (e.g., `pgaudit.log = 'read, write'`, `pgaudit.log_parameter = on`, `pgaudit.role = 'auditor_role'`).</li> + <li>**Recommendation:** Generally preferred over `log_statement=all` for serious auditing needs due to structure and granularity.</li> + </ul> + <h6>Log Management</h6> + <p>Ensure logs are stored securely, rotated appropriately, and ideally shipped to a central log management system for analysis and alerting.</p> </div> </div> </div> </div> <!-- /.row --> </div> <!-- /.schema-container --> - <!-- 11. ESSENTIAL TOOLS --> + + <!-- 12. ESSENTIAL TOOLS --> <div class="schema-container cat-tools" data-section-id="section-tools"> <h2 class="section-title" id="title-tools">Essential Tools</h2> <div class="row"> - <div class="col-lg-6 col-md-6"> + <div class="col-lg-4 col-md-6"> <!-- Adjusted to lg-4 --> <div class="info-card type-psql" id="card-psql"> - <div class="card-body"><h5><i class="bi bi-terminal"></i> psql Command-Line</h5> - <div class="card-content-wrapper"><p class="summary">The indispensable interactive terminal. Master its meta-commands (<span class="term"><a href="https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMANDS" target="_blank">`\?`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMANDS-D" target="_blank">`\d`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMANDS-L" target="_blank">`\l`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMANDS-TIMING" target="_blank">`\timing`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMANDS-E" target="_blank">`\e`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMANDS-COPY" target="_blank">`\copy`</a></span>, etc.) for efficiency. <a href="https://www.postgresql.org/docs/current/app-psql.html" target="_blank">[docs]</a></p> + <div class="card-body"><h5><i class="bi bi-terminal"></i> `psql` Command-Line</h5> + <div class="card-content-wrapper"><p class="summary">The indispensable interactive terminal. Master its meta-commands (`\?`, `\d`, `\l`, `\timing`, `\x`, `\e`, `\copy`, etc.) for efficiency. <a href="https://www.postgresql.org/docs/current/app-psql.html" target="_blank">[docs]</a></p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapsePsql" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapsePsql"> <h6>Connecting</h6> <p><code>psql -h host -p port -U user -d dbname</code></p> - <h6>Key Meta-Commands (prefixed with `\`) <a href="https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMANDS" target="_blank">[full list]</a></h6> + <h6>Key Meta-Commands (`\`) <a href="https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMANDS" target="_blank">[full list]</a></h6> <ul> - <li><strong>Help & Info:</strong> - <ul> - <li>`\?`: Help on `\` commands.</li> - <li>`\h [SQL Command]`: Help on specific SQL commands (e.g., `\h SELECT`).</li> - <li>`\l` or `\l+`: List databases (with details).</li> - <li>`\c dbname [user]`: Connect to a different database/user.</li> - <li>`\conninfo`: Display current connection info.</li> - <li>`\encoding [enc]`: Show/set client encoding.</li> - <li>`\password [user]`: Change user password securely.</li> - <li>`\q`: Quit psql.</li> - </ul> - </li> - <li><strong>Object Inspection (`\d` family):</strong> <a href="https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMANDS-D" target="_blank">[docs `\d`]</a> - <ul> - <li>`\d[S+] [pattern]`: List tables, views, sequences, indexes (+ means more detail, S includes system objects).</li> - <li>`\dt[S+] [pattern]`: List tables.</li> - <li>`\di[S+] [pattern]`: List indexes.</li> - <li>`\ds[S+] [pattern]`: List sequences.</li> - <li>`\dv[S+] [pattern]`: List views.</li> - <li>`\dm[S+] [pattern]`: List materialized views.</li> - <li>`\df[S+] [pattern]`: List functions.</li> - <li>`\dT[S+] [pattern]`: List data types.</li> - <li>`\dn[S+] [pattern]`: List schemas.</li> - <li>`\du[S+] [pattern]` or `\dg[S+] [pattern]`: List roles.</li> - <li>`\dp [pattern]`: List table/view/sequence access privileges.</li> - <li>`\ddp [pattern]`: List default privileges.</li> - <li>`\dx[+] [pattern]`: List extensions.</li> - </ul> - </li> - <li><strong>Query Execution & Formatting:</strong> - <ul> - <li>`\timing [on|off]`: Toggle display of query execution time.</li> - <li>`\x [on|off|auto]`: Toggle expanded display format (useful for wide rows).</li> - <li>`\H`: Toggle HTML output format.</li> - <li>`\watch [sec]`: Execute current query buffer repeatedly.</li> - <li>`\g`: Execute query buffer.</li> - <li>`\s [file]`: Show history or save to file.</li> - <li>`\i file`: Execute commands from file.</li> - <li>`\e [file]`: Edit current query buffer (or file) in external editor (`$EDITOR`).</li> - <li>`\ef [funcname]`: Edit function definition in external editor.</li> - <li>`\prompt name var`: Prompt user for variable.</li> - <li>`\set name value`: Set internal psql variable.</li> - <li>`\unset name`: Unset variable.</li> - </ul> - </li> - <li><strong>Data Import/Export:</strong> - <ul> - <li><span class="term"><a href="https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMANDS-COPY" target="_blank">`\copy table FROM 'file' [options]`</a></span>: Client-side copy (psql reads file).</li> - <li>`\copy table TO 'file' [options]`: Client-side copy (psql writes file).</li> - <li>(Use SQL <span class="term"><a href="https://www.postgresql.org/docs/current/sql-copy.html" target="_blank">`COPY`</a></span> for server-side file access).</li> - </ul> - </li> + <li>Help & Info: `\?`, `\h [SQL]`, `\l[+]`, `\c[onnect] ...`, `\conninfo`, `\encoding [enc]`, `\password [user]`, `\q`.</li> + <li>Object Inspection (`\d`): <a href="https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMANDS-D" target="_blank">[docs `\d`]</a> `\d[S+] [pat]`, `\dt` (tables), `\di` (indexes), `\ds` (sequences), `\dv` (views), `\dm` (mat views), `\df` (functions), `\dT` (types), `\dn` (schemas), `\du` / `\dg` (roles), `\dp` (privs), `\ddp` (def privs), `\dx` (extensions), `\dy` (event triggers), `\dL` (languages), `\des` (foreign servers), `\dew` (fdws).</li> + <li>Query Exec & Format: `\timing`, `\x [on|off|auto]`, `\H` (HTML), `\watch [sec]`, `\g [file]`, `\s [file]`, `\i file`, `\e [file]`, `\ef [func]`, `\prompt name var`, `\set name val`, `\unset name`, `\echo text`, `\o [file]` (redirect output).</li> + <li>Data I/O: <span class="term"><a href="https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-META-COMMANDS-COPY" target="_blank">`\copy table FROM/TO 'file' [opts]`</a></span> (Client-side).</li> + <li>Transaction:** `\set AUTOCOMMIT off/on`, `BEGIN`, `COMMIT`, `ROLLBACK`.</li> + </ul> + <h6>Variables & Scripting</h6> + <ul> + <li>Use `\set name value` to set variables. Reference with `:'name'`.</li> + <li>Use `-v name=value` on command line.</li> + <li>Conditional execution with `\if expr` ... `\elif expr` ... `\else` ... `\endif`.</li> </ul> <h6>Customization</h6> - <p><span class="term"><a href="https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-FILES" target="_blank">`.psqlrc`</a></span> file in home directory for custom settings, aliases (`\set`), prompts.</p> + <p><span class="term"><a href="https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-FILES" target="_blank">`.psqlrc`</a></span> file in home directory for custom settings (e.g., `\set PROMPT1 '%n@%m:%> '`), aliases (`\set`), startup commands.</p> + <p>Environment variables like `PGHOST`, `PGPORT`, `PGUSER`, `PGDATABASE`, `PGPASSWORD` (use `.pgpass` file instead for password). <a href="https://www.postgresql.org/docs/current/libpq-envars.html" target="_blank">[Env Vars]</a></p> </div> </div> </div> - <div class="col-lg-6 col-md-6"> + <div class="col-lg-4 col-md-6"> <!-- Adjusted to lg-4 --> <div class="info-card type-gui" id="card-gui"> <div class="card-body"><h5><i class="bi bi-window-desktop"></i> GUI Tools</h5> - <div class="card-content-wrapper"><p class="summary">Visual administration and querying tools like <span class="term"><a href="https://www.pgadmin.org/" target="_blank">`pgAdmin`</a></span> (open source), <a href="https://dbeaver.io/" target="_blank">DBeaver</a> (universal), <a href="https://www.jetbrains.com/datagrip/" target="_blank">DataGrip</a> (commercial).</p> + <div class="card-content-wrapper"><p class="summary">Visual administration and querying: <span class="term">`pgAdmin`</span> (official, web/desktop), <a href="https://dbeaver.io/" target="_blank">DBeaver</a> (universal, FOSS), <a href="https://www.jetbrains.com/datagrip/" target="_blank">DataGrip</a> (commercial IDE).</p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseGui" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseGui"> <h6>Common Options</h6> <ul> - <li><strong><a href="https://www.pgadmin.org/" target="_blank">pgAdmin</a>:</strong> - <ul><li>Official open-source graphical administration tool for PostgreSQL.</li> - <li>Features: Server status monitoring, object browser, SQL query tool with syntax highlighting & explain, data editor, backup/restore UI, user management, schema diff.</li> - <li>Runs as a web application (pgAdmin 4) or older desktop app (pgAdmin 3 - legacy).</li> - <li>Can be complex, sometimes perceived as slow or resource-heavy.</li></ul> - </li> - <li><strong><a href="https://dbeaver.io/" target="_blank">DBeaver</a>:</strong> - <ul><li>Free, open-source universal database tool (supports many DBs including Postgres).</li> - <li>Features: Connection management, SQL editor with auto-complete & formatting, visual query builder, data viewer/editor, ER diagrams, data transfer/migration tools.</li> - <li>Popular choice due to versatility and performance. Community & Enterprise editions.</li></ul> + <li><a href="https://www.pgadmin.org/" target="_blank">pgAdmin</a>: + <ul><li>Official open-source graphical tool.</li> + <li>Features: Server status monitoring, object browser/editor, SQL query tool (explain analyze visualization), data editor, backup/restore UI, user management, schema diff.</li> + <li>Runs as a web application (pgAdmin 4). Can feel heavy sometimes.</li></ul> </li> - <li><strong><a href="https://www.jetbrains.com/datagrip/" target="_blank">DataGrip (JetBrains)</a>:</strong> - <ul><li>Commercial IDE for databases from JetBrains (makers of IntelliJ, PyCharm).</li> - <li>Features: Advanced SQL editor (introspection, refactoring, code completion), schema navigation, data editor, import/export, VCS integration.</li> - <li>Powerful, particularly strong editor features. Part of the JetBrains ecosystem. Requires license.</li></ul> + <li><a href="https://dbeaver.io/" target="_blank">DBeaver</a>: + <ul><li>Free, open-source universal database tool (supports many DBs).</li> + <li>Features: Connection management, SQL editor (auto-complete, formatting), visual query builder, data viewer/editor, ER diagrams, data transfer/migration tools, SSH tunneling.</li> + <li>Popular choice due to versatility and performance. Community & Pro editions.</li></ul> </li> - <li><strong>Other Tools:</strong> - <ul><li>psql (already covered - powerful CLI)</li> - <li><a href="https://www.navicat.com/en/products/navicat-for-postgresql" target="_blank">Navicat for PostgreSQL</a> (Commercial)</li> - <li><a href="https://eggerapps.at/postico/" target="_blank">Postico</a> (macOS native, Commercial)</li> - <li><a href="https://tableplus.com/" target="_blank">TablePlus</a> (macOS/Windows/Linux native, Commercial/Free limited)</li> - <li><a href="https://www.oracle.com/database/sqldeveloper/" target="_blank">SQL Developer</a> (Oracle tool, supports Postgres via extension)</li></ul> + <li><a href="https://www.jetbrains.com/datagrip/" target="_blank">DataGrip (JetBrains)</a>: + <ul><li>Commercial database IDE from JetBrains.</li> + <li>Features: Advanced SQL editor (introspection, refactoring, excellent code completion), schema navigation/comparison, data editor, import/export, VCS integration, SSH tunneling.</li> + <li>Powerful, excellent editor. Requires license (free for students/OS projects). Included in IntelliJ Ultimate.</li></ul> </li> + <li>Other Tools: <a href="https://eggerapps.at/postico/" target="_blank">Postico</a> (macOS), <a href="https://tableplus.com/" target="_blank">TablePlus</a> (Native GUI, multi-platform), <a href="https://www.navicat.com/en/products/navicat-for-postgresql" target="_blank">Navicat</a> (Commercial).</li> </ul> <h6>Choosing a Tool</h6> - <p>Depends on preference, budget, operating system, and required features. `psql` is essential for scripting and server access. GUIs are helpful for visual exploration, administration tasks, and complex query development.</p> + <p>Depends on preference, budget, OS, and required features. `psql` is essential for scripting and server access. GUIs are helpful for visual exploration, administration tasks, and complex query development/debugging.</p> + </div> + </div> + </div> + <div class="col-lg-4 col-md-6"> <!-- Adjusted to lg-4 --> + <div class="info-card type-admin-cli" id="card-admin-cli"> + <div class="card-body"><h5><i class="bi bi-gear-wide-connected"></i> Admin Command-Line Tools</h5> + <div class="card-content-wrapper"><p class="summary">Server-side tools: <span class="term">`pg_ctl`</span> (start/stop/reload), <span class="term">`initdb`</span> (create cluster), <span class="term">`pg_controldata`</span> (read control file), <span class="term">`pgbench`</span> (benchmark).</p> + <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseAdminCli" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> + <div class="collapse collapse-content" id="collapseAdminCli"> + <p>These tools are typically found in the PostgreSQL `bin` directory and operate directly on the database cluster or server process.</p> + <ul> + <li><a href="https://www.postgresql.org/docs/current/app-pg-ctl.html" target="_blank">`pg_ctl`</a>:** Utility for initializing, starting, stopping, restarting, reloading configuration, promoting standbys, and getting status of a PostgreSQL server. + <ul><li>Syntax: `pg_ctl -D /path/to/PGDATA <start|stop|restart|reload|promote|status>`</li> + <li>Often used by service management scripts (`systemd`, `init.d`).</li></ul> + </li> + <li><a href="https://www.postgresql.org/docs/current/app-initdb.html" target="_blank">`initdb`</a>:** Creates a new PostgreSQL database cluster (the `PGDATA` directory structure and template databases). Run only once to set up a new instance. + <ul><li>Options: `-D` (data dir), `-U` (superuser name), `-W` (prompt for password), `--locale`, `--encoding`.</li></ul> + </li> + <li><a href="https://www.postgresql.org/docs/current/app-pgcontroldata.html" target="_blank">`pg_controldata`</a>:** Displays control information stored in `PGDATA/global/pg_control`, such as WAL segment info, checkpoint location, cluster state, database OID. Useful for diagnostics. Run while server is stopped. + <ul><li>Syntax: `pg_controldata /path/to/PGDATA`</li></ul> + </li> + <li><a href="https://www.postgresql.org/docs/current/pgresetwal.html" target="_blank">`pg_resetwal`</a> (formerly `pg_resetxlog`):** Emergency recovery tool to reset WAL information. **Extremely dangerous!** Can cause data loss/corruption. Use only as a last resort if the server won't start due to WAL corruption and you understand the consequences. Requires server to be stopped.</li> + <li><a href="https://www.postgresql.org/docs/current/pgbench.html" target="_blank">`pgbench`</a>:** Simple benchmarking tool included with PostgreSQL. + <ul><li>Initialize test tables: `pgbench -i [options] dbname`</li> + <li>Run standard TPC-B like test: `pgbench [options] dbname`</li> + <li>Run custom scripts: `pgbench -f script.sql [options] dbname`</li> + <li>Options: `-c` (clients), `-j` (threads), `-T` (duration), `-P` (progress report interval), `-N` (no vacuum).</li> + <li>Useful for basic performance testing and comparing configuration changes.</li></ul> + </li> + </ul> </div> </div> </div> </div> <!-- /.row --> </div> <!-- /.schema-container --> - <!-- 12. QUIRKS & TERMINOLOGY RECAP --> + + <!-- 13. QUIRKS & TERMINOLOGY RECAP --> <div class="schema-container cat-quirks" data-section-id="section-quirks"> <h2 class="section-title" id="title-quirks">Key Quirks & Terminology Recap</h2> <div class="row"> <div class="col-lg-4 col-md-6"> <div class="info-card type-quirk type-case" id="card-quirk-case"> - <div class="card-body"><h5><i class="bi bi-type-h1"></i> Identifier Case Sensitivity</h5> - <div class="card-content-wrapper"><p class="summary">Unquoted identifiers are folded to <span class="term">lowercase</span>. Use double quotes (`"MyTable"`) to preserve case. Critical migration gotcha! <a href="https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS" target="_blank">[docs]</a></p> + <div class="card-body"><h5><i class="bi bi-type-h1"></i> Identifier Case Sensitivity</h5> + <div class="card-content-wrapper"><p class="summary">Unquoted identifiers are folded to <span class="term">lowercase</span>. Use double quotes (`"MyTable"`) to preserve case (causes hassles!). Max length ~63 chars. <a href="https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS" target="_blank">[docs]</a></p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseQuirkCase" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseQuirkCase"> <h6>The Rule</h6> @@ -1821,92 +2364,97 @@ $$ LANGUAGE plpgsql;</code></pre> </ul> <h6>Migration Impact</h6> <p>Databases like SQL Server or MySQL (on Windows/macOS by default) are often case-insensitive or preserve case differently. Migrating schemas and queries requires careful attention to quoting or consistent use of lowercase identifiers to avoid errors.</p> + <h6>Identifier Length</h6> + <p>Default maximum length for identifiers (tables, columns, functions, etc.) is 63 bytes (`NAMEDATALEN` constant). Can be changed by recompiling PostgreSQL if absolutely necessary (rarely done).</p> <h6>Best Practice</h6> - <p>Avoid quoted identifiers unless absolutely necessary. Use lowercase names with underscores (`my_table`, `user_id`) consistently for maximum portability and minimum confusion.</p> + <p>Avoid quoted identifiers unless absolutely necessary (e.g., using reserved words, requiring special characters). Use lowercase names with underscores (`my_table`, `user_id`) consistently for maximum portability and minimum confusion.</p> </div> </div> </div> <div class="col-lg-4 col-md-6"> <div class="info-card type-quirk type-vacuum" id="card-quirk-vacuum"> - <div class="card-body"><h5><i class="bi bi-recycle"></i> VACUUM Necessity</h5> - <div class="card-content-wrapper"><p class="summary">Due to MVCC, dead tuples accumulate (<span class="term"><a href="https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-SPACE-RECOVERY" target="_blank">bloat</a></span>). Regular <span class="term"><a href="https://www.postgresql.org/docs/current/sql-vacuum.html" target="_blank">`VACUUM`</a></span> (usually via <span class="term"><a href="https://www.postgresql.org/docs/current/routine-vacuuming.html#AUTOVACUUM" target="_blank">Autovacuum</a></span>) is mandatory for performance and <a href="https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND" target="_blank">TXID wraparound</a> prevention.</p> + <div class="card-body"><h5><i class="bi bi-recycle"></i> VACUUM Necessity (MVCC)</h5> + <div class="card-content-wrapper"><p class="summary">MVCC causes <span class="term">bloat</span> (dead rows). Regular <span class="term">`VACUUM`</span> (via <span class="term">Autovacuum</span>) is MANDATORY for performance and to prevent <span class="term">TXID wraparound</span> failure.</p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseQuirkVacuum" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseQuirkVacuum"> <h6>Recap</h6> - <p>MVCC doesn't physically delete or update rows in place. Old row versions remain until cleaned up. <a href="https://www.postgresql.org/docs/current/routine-vacuuming.html" target="_blank">[routine vacuuming]</a></p> + <p>MVCC doesn't physically delete or update rows in place. Old row versions (<span class="term">dead tuples</span>) remain until cleaned up by `VACUUM`. <a href="https://www.postgresql.org/docs/current/routine-vacuuming.html" target="_blank">[routine vacuuming]</a></p> <h6>Consequences of Neglect</h6> <ul> - <li><strong>Bloat:</strong> Tables and indexes grow larger than necessary, wasting disk space.</li> - <li><strong>Performance Degradation:</strong> Sequential scans read more dead tuples; index scans might become less efficient; Index-Only Scans might fail if the Visibility Map isn't updated.</li> - <li><strong><a href="https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND" target="_blank">TXID Wraparound Failure</a>:</strong> If `VACUUM` doesn't run often enough to "freeze" old transaction IDs on very busy systems, the database may eventually be forced to shut down to prevent data corruption when the TXID counter wraps. This is catastrophic.</li> + <li>Bloat: Tables and indexes grow larger than necessary, wasting disk space.</li> + <li>Performance Degradation: Sequential scans read more dead tuples; index scans might become less efficient; Index-Only Scans might fail if the Visibility Map isn't updated.</li> + <li><a href="https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND" target="_blank">TXID Wraparound Failure</a>: Transaction IDs are 32-bit numbers. If `VACUUM` doesn't run often enough to "freeze" very old tuple transaction IDs on extremely busy or long-running systems (where >2 billion transactions occur between vacuums of oldest data), the database may eventually be forced to shut down into single-user mode to prevent data corruption when the TXID counter wraps around. This is catastrophic but preventable with proper vacuuming. Monitor `age(datfrozenxid)` via SQL.</li> </ul> <h6>Action Required</h6> <ul> - <li>Ensure Autovacuum is enabled (<span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM" target="_blank">`autovacuum = on`</a></span>).</li> - <li>**Monitor Autovacuum:** Check logs (<span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-AUTOVACUUM-MIN-DURATION" target="_blank">`log_autovacuum_min_duration`</a></span>), <span class="term"><a href="https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-USER-TABLES-VIEW" target="_blank">`pg_stat_user_tables`</a></span> (last vacuum times, dead tuples).</li> - <li>**Tune Autovacuum:** Adjust settings (<span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-SCALE-FACTOR" target="_blank">`*_scale_factor`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-THRESHOLD" target="_blank">`*_threshold`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-DELAY" target="_blank">`*_cost_delay`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-COST-LIMIT" target="_blank">`*_cost_limit`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-MAX-WORKERS" target="_blank">`max_workers`</a></span>) based on table size, update frequency, and system resources. Defaults are often insufficient for active databases. <a href="https://www.postgresql.org/docs/current/runtime-config-autovacuum.html" target="_blank">[config]</a></li> - <li>Occasionally monitor for excessive bloat using tools or queries (`pgstattuple` extension) and consider manual `VACUUM` or `VACUUM FULL` / `pg_repack` if necessary.</li> + <li>Ensure Autovacuum is enabled (<span class="term">`autovacuum = on`</span>).</li> + <li>**Monitor Autovacuum:** Check logs (<span class="term">`log_autovacuum_min_duration`</span>), <span class="term">`pg_stat_user_tables`</span> (last vacuum times, dead tuples). Monitor TXID age (`SELECT datname, age(datfrozenxid) FROM pg_database;`).</li> + <li>**Tune Autovacuum:** Adjust settings (<span class="term">`*_scale_factor`</span>, <span class="term">`*_threshold`</span>, <span class="term">`*_cost_delay/limit`</span>, <span class="term">`max_workers`</span>, `autovacuum_freeze_max_age`) based on table size, update frequency, and system resources. Defaults are often insufficient for active databases. <a href="https://www.postgresql.org/docs/current/runtime-config-autovacuum.html" target="_blank">[config]</a></li> + <li>Occasionally monitor for excessive bloat (see Bloat Monitoring card) and consider manual `VACUUM`, `REINDEX CONCURRENTLY`, or `pg_repack` if necessary.</li> </ul> </div> </div> </div> <div class="col-lg-4 col-md-6"> <div class="info-card type-quirk type-pooling" id="card-quirk-pooling"> - <div class="card-body"><h5><i class="bi bi-server"></i> Connection Pooling</h5> - <div class="card-content-wrapper"><p class="summary">Postgres's process-per-connection model makes external poolers (<a href="https://www.pgbouncer.org/" target="_blank">PgBouncer</a>, <a href="https://www.pgpool.net/mediawiki/index.php/Main_Page" target="_blank">Pgpool-II</a>) highly recommended for apps with many connections.</p> + <div class="card-body"><h5><i class="bi bi-server"></i> Connection Pooling (External)</h5> + <div class="card-content-wrapper"><p class="summary">Postgres's process-per-connection model makes external poolers (<a href="https://www.pgbouncer.org/" target="_blank">PgBouncer</a>, <a href="https://www.pgpool.net/mediawiki/index.php/Main_Page" target="_blank">Pgpool-II</a>) highly recommended for apps with many frequent, short-lived connections.</p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseQuirkPooling" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseQuirkPooling"> <h6>The Issue</h6> - <p>Each connection to PostgreSQL forks a new OS process (`postgres` backend), consuming significant RAM and CPU resources for process management. Creating and tearing down connections is also relatively expensive. <a href="https://www.postgresql.org/docs/current/server-process.html" target="_blank">[process model]</a></p> - <p>Applications (especially web apps) that open many short-lived connections directly to the database can quickly exhaust server resources and lead to poor performance.</p> + <p>Each connection to PostgreSQL forks a new OS process (`postgres` backend), consuming significant RAM (tens of MBs) and CPU resources for process management. Creating and tearing down connections is also relatively expensive. <a href="https://www.postgresql.org/docs/current/server-process.html" target="_blank">[process model]</a></p> + <p>Applications (especially web/serverless apps) that open many short-lived connections directly to the database can quickly exhaust server resources (`max_connections` and memory) and lead to poor performance.</p> <h6>The Solution: External Connection Poolers</h6> - <p>Middleware that maintains a pool of persistent connections to the PostgreSQL server. Application connects to the pooler instead of directly to Postgres. <a href="https://wiki.postgresql.org/wiki/Pooling" target="_blank">[PG Wiki Pooling]</a></p> + <p>Middleware that maintains a pool of persistent physical connections to the PostgreSQL server. Application connects to the pooler instead of directly to Postgres. <a href="https://wiki.postgresql.org/wiki/Pooling" target="_blank">[PG Wiki Pooling]</a></p> <ul> - <li><strong><a href="https://www.pgbouncer.org/" target="_blank">PgBouncer</a>:</strong> Lightweight, single-purpose connection pooler. Very popular, stable, low overhead. Offers different pooling modes: - <ul><li><em>Session Pooling:</em> Client keeps connection until disconnect (least effective pooling).</li> - <li><em>Transaction Pooling:</em> Connection returned to pool after each transaction. Requires client not to use session-based features (e.g., SET commands, advisory locks) across transactions. **Commonly used.**</li> - <li><em>Statement Pooling:</em> Connection returned after each statement (most aggressive, most restrictions).</li></ul> + <li><a href="https://www.pgbouncer.org/" target="_blank">PgBouncer</a>: Lightweight, single-purpose connection pooler. Very popular, stable, low overhead. Offers different pooling modes: + <ul><li><em>Session Pooling:</em> Client keeps logical connection and physical backend connection until disconnect (least effective pooling, mostly just limits connection count).</li> + <li><em>Transaction Pooling:</em> Physical connection returned to pool after each transaction (`COMMIT`/`ROLLBACK`). Requires client not to use session-based features (e.g., `SET` commands, advisory locks, temp tables) across transactions. **Commonly used.**</li> + <li><em>Statement Pooling:</em> Physical connection returned after each statement (most aggressive, most restrictions, rare).</li></ul> </li> - <li><strong><a href="https://www.pgpool.net/mediawiki/index.php/Main_Page" target="_blank">Pgpool-II</a>:</strong> More feature-rich middleware. Provides connection pooling, load balancing across replicas, replication management, and automated failover (though complex). Higher overhead than PgBouncer.</li> + <li><a href="https://www.pgpool.net/mediawiki/index.php/Main_Page" target="_blank">Pgpool-II</a>: More feature-rich middleware. Provides connection pooling, load balancing across replicas, replication management, and automated failover (watchdog). Higher overhead and complexity than PgBouncer.</li> + <li>Some application frameworks/drivers have built-in pooling, but an external pooler is often still beneficial for managing total server connections across multiple app instances.</li> </ul> <h6>Benefits of Pooling</h6> <ul> - <li>Reduces connection overhead on the PostgreSQL server.</li> + <li>Reduces connection overhead (memory/CPU) on the PostgreSQL server.</li> <li>Allows supporting thousands of application clients with only tens or hundreds of actual database connections.</li> - <li>Improves performance by reusing existing connections.</li> + <li>Improves performance by reusing existing connections, reducing connection latency.</li> </ul> <h6>Recommendation</h6> - <p>For most applications making frequent, short-lived connections, using PgBouncer in transaction pooling mode is highly recommended.</p> + <p>For most applications making frequent connections, using PgBouncer in transaction pooling mode between the application and the database is highly recommended.</p> </div> </div> </div> <div class="col-lg-4 col-md-6"> <div class="info-card type-quirk type-public" id="card-quirk-public"> - <div class="card-body"><h5><i class="bi bi-eye"></i> `public` Schema</h5> - <div class="card-content-wrapper"><p class="summary">Default schema. All roles have `CREATE` and `USAGE` grants initially. Best practice: <span class="term"><a href="https://www.postgresql.org/docs/current/sql-revoke.html" target="_blank">`REVOKE`</a></span> create, use dedicated schemas. <a href="https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PUBLIC" target="_blank">[docs]</a></p> + <div class="card-body"><h5><i class="bi bi-eye"></i> `public` Schema Defaults</h5> + <div class="card-content-wrapper"><p class="summary">Default schema where all roles have `CREATE` and `USAGE` initially. Security risk! Best practice: <span class="term">`REVOKE CREATE`</span>, use dedicated schemas. <a href="https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PUBLIC" target="_blank">[docs]</a></p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseQuirkPublic" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseQuirkPublic"> <h6>Default Behavior</h6> <ul> <li>Every new database contains a schema named `public`. <a href="https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PUBLIC" target="_blank">[docs]</a></li> - <li>By default, the special <span class="term"><a href="https://www.postgresql.org/docs/current/database-roles.html#DATABASE-ROLES-PUBLIC" target="_blank">`PUBLIC`</a></span> pseudo-role (meaning all roles) is granted `CREATE` (permission to create objects like tables) and `USAGE` (permission to access objects) privileges on the `public` schema.</li> + <li>By default, the special <span class="term"><a href="https://www.postgresql.org/docs/current/database-roles.html#DATABASE-ROLES-PUBLIC" target="_blank">`PUBLIC`</a></span> pseudo-role (meaning all roles) is granted `CREATE` (permission to create objects like tables) and `USAGE` (permission to access objects within) privileges on the `public` schema.</li> <li>If no schema is specified when creating an object (e.g., `CREATE TABLE mytable (...)`), it gets created in the first schema listed in the user's <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-SEARCH-PATH" target="_blank">`search_path`</a></span> where they have `CREATE` permission, which is typically `public`.</li> </ul> <h6>Problems</h6> <ul> <li>Can lead to a cluttered namespace with objects from different applications or users mixed together.</li> - <li>Security Risk: Any user can potentially create objects in `public`, potentially leading to naming conflicts or unexpected behavior (e.g., function hijacking if `search_path` is manipulated). <a href="https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATTERNS" target="_blank">[patterns]</a></li> + <li>Security Risk: Any user can potentially create objects (tables, functions) in `public`, potentially leading to naming conflicts or unexpected behavior (e.g., function/operator hijacking if `public` is early in the `search_path`). <a href="https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATTERNS" target="_blank">[patterns]</a></li> </ul> <h6>Recommended Practice</h6> <ol> - <li>**Revoke Default Privileges:** As a superuser, immediately after database creation: - <pre><code><a href="https://www.postgresql.org/docs/current/sql-revoke.html" target="_blank">REVOKE CREATE ON SCHEMA public FROM PUBLIC;</a> --- Optionally revoke USAGE if desired, but often needed --- REVOKE USAGE ON SCHEMA public FROM PUBLIC;</code></pre> + <li>Revoke Default Privileges:** As a superuser, immediately after database creation (or in template database `template1`): + <pre><code>REVOKE CREATE ON SCHEMA public FROM PUBLIC; + -- Optional, but recommended for stricter control: + -- REVOKE USAGE ON SCHEMA public FROM PUBLIC; + -- GRANT USAGE ON SCHEMA public TO specific_roles_that_need_it; -- e.g., for extensions + </code></pre> </li> - <li>**Create Dedicated Schemas:** Use schemas to organize objects logically (e.g., `CREATE SCHEMA app_schema;`, `CREATE SCHEMA reporting;`). <a href="https://www.postgresql.org/docs/current/sql-createschema.html" target="_blank">[create schema]</a></li> - <li>**Grant Permissions Explicitly:** Grant necessary `USAGE` and object-specific privileges (SELECT, INSERT, etc.) on dedicated schemas/tables to specific application roles. <a href="https://www.postgresql.org/docs/current/sql-grant.html" target="_blank">[grant]</a></li> - <li>**Set `search_path`:** Configure the <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-SEARCH-PATH" target="_blank">`search_path`</a></span> appropriately for application roles (e.g., `ALTER ROLE app_user SET search_path = app_schema, public;`) so they don't need to schema-qualify table names frequently, but still prioritize the correct schema.</li> + <li>Create Dedicated Schemas:** Use schemas to organize objects logically (e.g., `CREATE SCHEMA app_schema AUTHORIZATION app_owner;`). <a href="https://www.postgresql.org/docs/current/sql-createschema.html" target="_blank">[create schema]</a></li> + <li>Grant Permissions Explicitly:** Grant necessary `USAGE` on the schema and object-specific privileges (SELECT, INSERT, etc.) on objects within dedicated schemas to specific application roles. <a href="https://www.postgresql.org/docs/current/sql-grant.html" target="_blank">[grant]</a></li> + <li>Set `search_path` Carefully:** Configure the <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-SEARCH-PATH" target="_blank">`search_path`</a></span> appropriately for application roles (e.g., `ALTER ROLE app_user SET search_path = app_schema, public;`) so they don't need to schema-qualify table names frequently, but still prioritize the correct schema. Place `public` last or omit if possible.</li> </ol> </div> </div> @@ -1914,31 +2462,33 @@ $$ LANGUAGE plpgsql;</code></pre> <div class="col-lg-4 col-md-6"> <div class="info-card type-quirk type-searchpath" id="card-quirk-searchpath"> <div class="card-body"><h5><i class="bi bi-signpost"></i> `search_path`</h5> - <div class="card-content-wrapper"><p class="summary">Determines the order schemas are searched for unqualified objects. Impacts name resolution and security. <a href="https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH" target="_blank">[docs]</a></p> + <div class="card-content-wrapper"><p class="summary">Determines the order schemas are searched for unqualified objects (tables, functions). Impacts name resolution and security. Default includes user schema, then `public`. <a href="https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH" target="_blank">[docs]</a></p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseQuirkSearchPath" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseQuirkSearchPath"> <h6>Functionality</h6> - <p>When you reference an object (table, function, type, etc.) without explicitly qualifying it with a schema name (e.g., `SELECT * FROM users` instead of `SELECT * FROM app_schema.users`), PostgreSQL searches the schemas listed in the <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-SEARCH-PATH" target="_blank">`search_path`</a></span> setting in order, using the first matching object found.</p> + <p>When you reference an object (table, function, type, operator, etc.) without explicitly qualifying it with a schema name (e.g., `SELECT * FROM users` instead of `SELECT * FROM app_schema.users`), PostgreSQL searches the schemas listed in the <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-SEARCH-PATH" target="_blank">`search_path`</a></span> setting in order, using the first matching object found.</p> <h6>Default Value</h6> - <p>Typically defaults to `"$user", public`. This means it first looks for a schema with the same name as the current user, then looks in the `public` schema.</p> + <p>Typically defaults to `"$user", public`. This means it first looks for a schema with the same name as the current user role (`CREATE SCHEMA myuser AUTHORIZATION myuser;`), then looks in the `public` schema. If `$user` schema doesn't exist, it's skipped.</p> <h6>Setting the Path</h6> <ul> <li>Session level: `SET search_path TO schema1, schema2, public;`</li> - <li>User level: `ALTER ROLE myuser SET search_path = schema1, public;`</li> - <li>Database level: `ALTER DATABASE mydb SET search_path = schema1, public;`</li> - <li>Server level (`postgresql.conf`): Sets the default for new sessions.</li> + <li>User level (persistent for user): `ALTER ROLE myuser SET search_path = schema1, public;`</li> + <li>Database level (default for db): `ALTER DATABASE mydb SET search_path = schema1, public;`</li> + <li>Server level (`postgresql.conf`): Sets the system default for new sessions.</li> + <li>Function level: `ALTER FUNCTION myfunc(...) SET search_path = ...;` or `SET search_path = ...` in function definition (crucial for `SECURITY DEFINER`).</li> <li><a href="https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH-SETTING" target="_blank">[setting path]</a></li> </ul> - <h6>Security Implications</h6> + <h6>Security Implications (Trojan Horse)</h6> <ul> - <li>If a user can create objects in a schema listed *earlier* in the `search_path` than the intended schema (especially `public` if default permissions aren't revoked), they might be able to create objects (like functions) that hijack calls intended for objects in later schemas. <a href="https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PRIVILEGES" target="_blank">[security]</a></li> - <li>Example: If `search_path = public, app_schema`, and a user creates a function `public.do_something()`, a call to `do_something()` might execute the malicious public version instead of `app_schema.do_something()`.</li> + <li>If a user can create objects in a schema listed *earlier* in the `search_path` than the intended schema (especially `public` if default `CREATE` privileges aren't revoked), they might be able to create objects (like functions or operators) that hijack calls intended for objects in later schemas. <a href="https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PRIVILEGES" target="_blank">[security]</a></li> + <li>Example: If `search_path = public, app_schema`, and a malicious user creates a function `public.important_function()`, a call to `important_function()` (expecting `app_schema.important_function()`) might execute the malicious public version. This is especially dangerous within `SECURITY DEFINER` functions if their `search_path` isn't explicitly secured.</li> </ul> <h6>Best Practice</h6> <ul> <li>Always be aware of the current `search_path` (<span class="term"><a href="https://www.postgresql.org/docs/current/sql-show.html" target="_blank">`SHOW search_path;`</a></span>).</li> - <li>Consider setting a specific, restricted `search_path` for application roles that only includes the necessary application schema(s) and potentially `public` *last* if needed for extensions.</li> - <li>Qualify object names with schemas (`app_schema.users`) in security-sensitive contexts or when ambiguity is possible, especially within function definitions (`SECURITY DEFINER` functions).</li> + <li>Set a specific, minimal, and controlled `search_path` for application roles (e.g., only the necessary application schema(s), maybe `pg_catalog`).</li> + <li>Place `public` last in the path, or remove it entirely if not needed (e.g., for extensions).</li> + <li>Qualify object names with schemas (`app_schema.users`) in security-sensitive contexts (like `SECURITY DEFINER` functions) or when ambiguity is possible.</li> <li>Follow best practices for the `public` schema (revoke default create privileges).</li> </ul> </div> @@ -1947,11 +2497,11 @@ $$ LANGUAGE plpgsql;</code></pre> <div class="col-lg-4 col-md-6"> <div class="info-card type-quirk type-role" id="card-quirk-role"> <div class="card-body"><h5><i class="bi bi-people"></i> Unified Role Concept</h5> - <div class="card-content-wrapper"><p class="summary">PostgreSQL uses <span class="term"><a href="https://www.postgresql.org/docs/current/database-roles.html" target="_blank">Role</a></span> for both users and groups. Roles can inherit privileges from other roles they are members of.</p> + <div class="card-content-wrapper"><p class="summary">PostgreSQL uses <span class="term"><a href="https://www.postgresql.org/docs/current/database-roles.html" target="_blank">Role</a></span> for both users (with `LOGIN`) and groups (with `NOLOGIN`). Roles can inherit privileges from other roles they are members of.</p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseQuirkRole" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseQuirkRole"> <h6>Unified Model</h6> - <p>Unlike some databases with distinct concepts for "User" (can log in) and "Group" (container for privileges), PostgreSQL uses a single `ROLE` construct. <a href="https://www.postgresql.org/docs/current/database-roles.html" target="_blank">[docs]</a></p> + <p>Unlike some databases with distinct concepts for "User" (can log in) and "Group" (container for privileges), PostgreSQL uses a single `ROLE` construct for both. <a href="https://www.postgresql.org/docs/current/database-roles.html" target="_blank">[docs]</a></p> <ul> <li>A role that can log in has the <span class="term"><a href="https://www.postgresql.org/docs/current/role-attributes.html" target="_blank">`LOGIN`</a></span> attribute (`CREATE ROLE myuser WITH LOGIN PASSWORD '...';` is equivalent to `CREATE USER ...`).</li> <li>A role intended as a group typically has `NOLOGIN` (`CREATE ROLE readonly_group WITH NOLOGIN;`).</li> @@ -1959,33 +2509,69 @@ $$ LANGUAGE plpgsql;</code></pre> <h6>Membership and Inheritance</h6> <ul> <li>Roles can be members of other roles: <span class="term"><a href="https://www.postgresql.org/docs/current/sql-grant.html" target="_blank">`GRANT group_role TO user_role;`</a></span>. <a href="https://www.postgresql.org/docs/current/role-membership.html" target="_blank">[membership]</a></li> - <li>By default (<span class="term"><a href="https://www.postgresql.org/docs/current/role-attributes.html" target="_blank">`INHERIT`</a></span> attribute), a role automatically inherits all privileges granted directly to the roles it is a member of.</li> - <li>Example: If `readonly_group` is granted `SELECT` on `mytable`, and `myuser` is a member of `readonly_group` (`GRANT readonly_group TO myuser;`), then `myuser` can also `SELECT` from `mytable`.</li> + <li>By default (<span class="term"><a href="https://www.postgresql.org/docs/current/role-attributes.html" target="_blank">`INHERIT`</a></span> attribute is default), a role automatically gains all privileges granted directly to the roles it is a member of, and privileges of roles *those* roles are members of, recursively.</li> + <li>Example: If `readonly_group` is granted `SELECT` on `mytable`, and `myuser` is granted membership in `readonly_group` (`GRANT readonly_group TO myuser;`), then `myuser` automatically has `SELECT` permission on `mytable`.</li> + <li>Use `NOINHERIT` attribute on a role if its privileges should only be active after explicitly using `SET ROLE group_role;`.</li> + <li>Use `SET ROLE` to temporarily assume the privileges of a different role the current user is a member of.</li> </ul> <h6>Benefits</h6> <ul> - <li>Flexible privilege management through group roles. Grant permissions to the group role once, then manage user access by adding/removing them from the group.</li> + <li>Flexible privilege management through group roles. Grant permissions to the group role once, then manage user access by adding/removing them from the group (`GRANT`/`REVOKE group_role FROM user_role`).</li> <li>Allows complex hierarchical privilege structures if needed.</li> </ul> <h6>Managing Membership</h6> <ul> - <li>`GRANT group_role TO user_role;`</li> - <li><span class="term"><a href="https://www.postgresql.org/docs/current/sql-revoke.html" target="_blank">`REVOKE group_role FROM user_role;`</a></span></li> + <li>`GRANT group_role TO user_role [, ...];`</li> + <li><span class="term"><a href="https://www.postgresql.org/docs/current/sql-revoke.html" target="_blank">`REVOKE group_role FROM user_role [, ...];`</a></span></li> + <li>Check membership: `\du user_role` in psql, or query `pg_auth_members`.</li> </ul> <h6>Consideration</h6> - <p>While powerful, understand the inheritance model (`INHERIT` vs `NOINHERIT`) and use group roles strategically to simplify permission management rather than granting everything directly to login roles.</p> + <p>While powerful, understand the inheritance model (`INHERIT` vs `NOINHERIT`, `SET ROLE`) and use group roles strategically to simplify permission management rather than granting everything directly to individual login roles.</p> </div> </div> </div> </div> <!-- /.row --> </div> <!-- /.schema-container --> + <!-- Terminology Recap Card (Could be merged or kept separate) --> + <div class="schema-container cat-concept" data-section-id="section-terminology-recap"> + <h2 class="section-title" id="title-terminology-recap">Terminology Recap</h2> + <div class="row"> + <div class="col-12"> <!-- Full width for recap --> + <div class="info-card type-terminology" id="card-terminology-recap"> + <div class="card-body"><h5><i class="bi bi-translate"></i> Key PG Terms</h5> + <div class="card-content-wrapper"><p class="summary">Quick reference for core PG vocabulary: <span class="term">Relation</span>, <span class="term">Schema</span>, <span class="term">Role</span>, <span class="term">Heap</span>, <span class="term">Tuple</span>, <span class="term">WAL</span>, <span class="term">LSN</span>, <span class="term">MVCC</span>, <span class="term">TOAST</span>, <span class="term">Planner</span>, <span class="term">Executor</span>, <span class="term">Bloat</span>, <span class="term">Pooler</span>.</p> + <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseTermsRecap" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> + <div class="collapse collapse-content" id="collapseTermsRecap"> + <ul> + <li>Relation: General term for any table-like object storing data. Most commonly Tables and Indexes. Sometimes Views. Stored as files. <a href="https://www.postgresql.org/docs/current/glossary.html#GLOSSARY-RELATION" target="_blank">[glossary]</a></li> + <li>Schema: A namespace within a database containing objects (tables, views, functions, etc.). Used for organization and access control. Default is `public`. Distinct from "table schema" (column definitions). <a href="https://www.postgresql.org/docs/current/ddl-schemas.html" target="_blank">[docs]</a></li> + <li>Role: Unified concept for Users (with `LOGIN`) and Groups (`NOLOGIN`). Owns objects, has privileges, can inherit from other roles. <a href="https://www.postgresql.org/docs/current/database-roles.html" target="_blank">[docs]</a></li> + <li>Heap: The main storage structure for a table's data, typically an unordered collection of pages containing tuples. <a href="https://www.postgresql.org/docs/current/glossary.html#GLOSSARY-HEAP" target="_blank">[glossary]</a></li> + <li>Tuple: A physical row version stored on disk within a heap block. Contains data + header (xmin, xmax, etc.). <a href="https://www.postgresql.org/docs/current/glossary.html#GLOSSARY-TUPLE" target="_blank">[glossary]</a></li> + <li>WAL (Write-Ahead Log): Transaction log ensuring durability and enabling recovery/replication. Changes logged before applied to heap/index. Stored in `pg_wal` directory. <a href="https://www.postgresql.org/docs/current/wal.html" target="_blank">[docs]</a></li> + <li>LSN (Log Sequence Number): Pointer to a specific location in the WAL stream, identifying a unique record position. Used for recovery/replication progress tracking.</li> + <li>MVCC (Multi-Version Concurrency Control): Concurrency model where updates create new tuple versions instead of overwriting. Enables readers not blocking writers. Requires `VACUUM`. <a href="https://www.postgresql.org/docs/current/mvcc.html" target="_blank">[docs]</a></li> + <li>TOAST (The Oversized Attribute Storage Technique): Mechanism for storing large field values out of line in a separate TOAST table. <a href="https://www.postgresql.org/docs/current/storage-toast.html" target="_blank">[docs]</a></li> + <li>Planner/Optimizer: Component that analyzes a SQL query and statistics to determine the most efficient execution plan (e.g., which indexes to use, join methods). <a href="https://www.postgresql.org/docs/current/query-path-planning.html" target="_blank">[docs]</a></li> + <li>Executor: Component that runs the plan generated by the planner, fetching data and performing operations. <a href="https://www.postgresql.org/docs/current/executor.html" target="_blank">[docs]</a></li> + <li>Bloat:** Wasted/unused space in table and index files due to dead tuples not yet vacuumed or fragmentation. Requires `VACUUM` or `REINDEX`/`VACUUM FULL`/`pg_repack` to reclaim.</li> + <li>Connection Pooler:** External middleware (PgBouncer, Pgpool-II) managing persistent DB connections for applications, necessary due to process-per-connection model.</li> + <li>Checkpoint:** Point in WAL stream where prior dirty buffers are guaranteed flushed to disk. Limits recovery time.</li> + <li>Catalog:** Internal tables (`pg_catalog` schema) storing database metadata.</li> + </ul> + </div> + </div> + </div> + </div> <!-- /.row --> + </div> <!-- /.schema-container --> + </div> <!-- /container --> <footer class="container text-center"> <!-- Updated Year --> - <p>© 2025 David Veksler</p> + <p>© 2025 David Veksler</p> <!-- UPDATE YEAR AS NEEDED --> </footer> <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js"></script> @@ -2013,7 +2599,7 @@ document.addEventListener('DOMContentLoaded', () => { const oldSchemaContainer = currentHoverState.card.closest('.schema-container'); if (oldSchemaContainer) { oldSchemaContainer.classList.remove('is-highlighted-section'); - // Ensure title opacity is reset if needed (it wasn't dimmed in this version, but good practice) + // Ensure title opacity is reset if needed const oldTitle = oldSchemaContainer.querySelector('.section-title'); if(oldTitle) oldTitle.style.opacity = ''; } @@ -2036,7 +2622,7 @@ document.addEventListener('DOMContentLoaded', () => { card.classList.add('is-highlighted'); // Highlight the card itself if (schemaContainer) { - schemaContainer.classList.add('is-highlighted-section'); // Highlight section too (optional) + schemaContainer.classList.add('is-highlighted-section'); // Highlight section too // Ensure title is fully visible if section highlighting dims it const title = schemaContainer.querySelector('.section-title'); if (title) title.style.opacity = '1'; @@ -2046,20 +2632,31 @@ document.addEventListener('DOMContentLoaded', () => { // --- Event Listeners using Event Delegation on main container --- mainContainer.addEventListener('mouseover', (event) => { const targetCard = event.target.closest('.info-card'); - if (targetCard) { // && targetCard !== currentHoverState.card) { // Check if it's a new card + if (targetCard) { applyHoverState(targetCard); } }); mainContainer.addEventListener('mouseout', (event) => { - // Check if the mouse is leaving a card and not entering another card immediately - if (event.target.closest('.info-card') && !event.relatedTarget?.closest('.info-card')) { - // Delay slightly to see if we land on another card quickly + // Check if the mouse is leaving a card or its container, and not entering another card immediately + const relatedTargetIsCard = event.relatedTarget?.closest('.info-card'); + const relatedTargetIsContainer = event.relatedTarget?.closest('.schema-container'); + + if (event.target.closest('.info-card') && !relatedTargetIsCard) { + // Delay slightly to handle quick movements between cards or to outside setTimeout(() => { - // Check *again* if we are currently over *any* card after the delay const isStillOverACard = mainContainer.querySelector('.info-card:hover'); - if (!isStillOverACard) { - clearHoverState(false); // Use non-forced clear first + const isStillOverAContainer = mainContainer.querySelector('.schema-container:hover'); + // Clear only if mouse is truly outside *all* cards and *all* containers + if (!isStillOverACard && !isStillOverAContainer) { + clearHoverState(false); + } else if (!isStillOverACard && isStillOverAContainer && currentHoverState.card) { + // If over container but not card, check if it's the *same* container + const currentContainer = currentHoverState.card.closest('.schema-container'); + if(mainContainer.querySelector('.schema-container:hover') !== currentContainer) { + clearHoverState(false); // Clear if moved to different container + } + // Otherwise, stay dimmed/highlighted while over the section } }, 50); // Short delay } else if (!mainContainer.contains(event.relatedTarget)) { @@ -2073,32 +2670,43 @@ document.addEventListener('DOMContentLoaded', () => { const collapseToggles = document.querySelectorAll('.details-toggle'); collapseToggles.forEach(button => { const targetId = button.getAttribute('data-bs-target'); - const targetCollapse = document.querySelector(targetId); - const icon = button.querySelector('.bi'); - - if (targetCollapse && icon) { - // Set initial icon state based on whether the collapse element starts shown - if (targetCollapse.classList.contains('show')) { - icon.classList.remove('bi-chevron-down'); - icon.classList.add('bi-chevron-up'); - } else { - icon.classList.remove('bi-chevron-up'); - icon.classList.add('bi-chevron-down'); + // Ensure targetId starts with '#' for querySelector + const targetSelector = targetId.startsWith('#') ? targetId : `#${targetId}`; + try { + const targetCollapse = document.querySelector(targetSelector); + const icon = button.querySelector('.bi'); + + if (targetCollapse && icon) { + // Set initial icon state based on whether the collapse element starts shown + if (targetCollapse.classList.contains('show')) { + icon.classList.remove('bi-chevron-down'); + icon.classList.add('bi-chevron-up'); + } else { + icon.classList.remove('bi-chevron-up'); + icon.classList.add('bi-chevron-down'); + } + + // Listen to Bootstrap collapse events to toggle icon + targetCollapse.addEventListener('show.bs.collapse', () => { + icon.classList.remove('bi-chevron-down'); + icon.classList.add('bi-chevron-up'); + }); + targetCollapse.addEventListener('hide.bs.collapse', () => { + icon.classList.remove('bi-chevron-up'); + icon.classList.add('bi-chevron-down'); + }); + } else if (!targetCollapse) { + console.warn(`Collapse target not found for selector: ${targetSelector}`); + } else if (!icon) { + console.warn(`Icon not found in button for target: ${targetId}`); } - - // Listen to Bootstrap collapse events to toggle icon - targetCollapse.addEventListener('show.bs.collapse', () => { - icon.classList.remove('bi-chevron-down'); - icon.classList.add('bi-chevron-up'); - }); - targetCollapse.addEventListener('hide.bs.collapse', () => { - icon.classList.remove('bi-chevron-up'); - icon.classList.add('bi-chevron-down'); - }); + } catch (e) { + console.error(`Error processing toggle for target ${targetSelector}: ${e}`); } }); }); </script> + </body> </html> \ No newline at end of file