add links
· 1 year ago
9a8e8bccbb3f0d4824cdbb55875c573565b4cc3f
Parent:
ba8b13f73
1 file changed +312 −297
- postgresql.html +312 −297
Diff
--- a/postgresql.html +++ b/postgresql.html @@ -250,6 +250,14 @@ border: 1px solid var(--pg-primary-light); font-size: 0.95em; } + .term a { + color: inherit; /* Link inherits color from parent */ + text-decoration: none; /* Remove underline from term link */ + } + .term a:hover { + text-decoration: underline; /* Add underline on hover */ + } + /* Special styling for Quirk cards */ .info-card.type-quirk { border-left-color: var(--pg-color-quirks); } @@ -316,7 +324,7 @@ <div class="col-lg-4 col-md-6"> <div class="info-card type-process" id="card-process"> <div class="card-body"><h5><i class="bi bi-cpu"></i> Process Model</h5> - <div class="card-content-wrapper"><p class="summary">Multi-process architecture. A central listener (<span class="term">postmaster</span>/`postgres`) forks a new backend process for each client connection.</p> + <div class="card-content-wrapper"><p class="summary">Multi-process architecture. A central listener (<span class="term"><a href="https://www.postgresql.org/docs/current/server-start.html" target="_blank">postmaster</a></span>/`postgres`) forks a new backend process for each client connection.</p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseProcess" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseProcess"> <h6>Details</h6> @@ -324,7 +332,7 @@ <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">connection poolers</span> (e.g., PgBouncer, Pgpool-II) for applications with many short-lived connections (like typical web apps) to avoid resource exhaustion.</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> </ul> </div> </div> @@ -332,24 +340,24 @@ <div class="col-lg-4 col-md-6"> <div class="info-card type-memory" id="card-memory"> <div class="card-body"><h5><i class="bi bi-memory"></i> Memory Areas</h5> - <div class="card-content-wrapper"><p class="summary">Key memory settings include <span class="term">shared_buffers</span> (main cache), <span class="term">work_mem</span> (per-operation sort/hash), and <span class="term">maintenance_work_mem</span> (VACUUM, indexes).</p> + <div class="card-content-wrapper"><p class="summary">Key memory settings include <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-SHARED-BUFFERS" target="_blank">shared_buffers</a></span> (main cache), <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-WORK-MEM" target="_blank">work_mem</a></span> (per-operation sort/hash), and <span class="term"><a href="https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM" target="_blank">maintenance_work_mem</a></span> (VACUUM, indexes).</p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseMemory" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <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).</li> - <li><strong>WAL Buffers</strong>: Buffers Write-Ahead Log records before writing to disk. Usually auto-tuned (`-1`), small relative to `shared_buffers`.</li> + <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> </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.</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.</li> - <li><strong>`temp_buffers`</strong>: Caches temporary tables.</li> + <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> </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. `effective_cache_size` tells the planner how much memory is likely available in OS + PG cache.</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> </ul> </div> </div> @@ -357,25 +365,25 @@ <div class="col-lg-4 col-md-6"> <div class="info-card type-storage" id="card-storage"> <div class="card-body"><h5><i class="bi bi-hdd"></i> Storage</h5> - <div class="card-content-wrapper"><p class="summary">Data stored in files based on OIDs. <span class="term">Tablespaces</span> map logical names to disk locations. Large values use <span class="term">TOAST</span>.</p> + <div class="card-content-wrapper"><p class="summary">Data stored in files based on OIDs. <span class="term"><a href="https://www.postgresql.org/docs/current/manage-ag-tablespaces.html" target="_blank">Tablespaces</a></span> map logical names to disk locations. Large values use <span class="term"><a href="https://www.postgresql.org/docs/current/storage-toast.html" target="_blank">TOAST</a></span>.</p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseStorage" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseStorage"> <h6>File Layout</h6> <ul> - <li>Data directory (`PGDATA`) contains global config, WAL, per-database directories.</li> + <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>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> <ul> - <li>Allow defining locations on the filesystem where database objects (tables, indexes, databases) can be stored.</li> + <li>Allow defining locations on the filesystem where database objects (tables, indexes, databases) can be stored. <a href="https://www.postgresql.org/docs/current/manage-ag-tablespaces.html" target="_blank">[docs]</a></li> <li>Useful for separating objects onto different disk types (e.g., fast SSDs for indexes, large HDDs for tables) or managing storage quotas.</li> - <li><code>CREATE TABLESPACE fastspace LOCATION '/mnt/ssd_storage';</code></li> + <li><code><a href="https://www.postgresql.org/docs/current/sql-createtablespace.html" target="_blank">CREATE TABLESPACE</a> fastspace LOCATION '/mnt/ssd_storage';</code></li> <li><code>CREATE TABLE ... TABLESPACE fastspace;</code></li> </ul> <h6>TOAST (The Oversized Attribute Storage Technique)</h6> <ul> - <li>Handles storage of large column values that don't fit within a standard data block (~8KB).</li> + <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>Transparent to the user but affects performance for very large fields. Contributes to bloat.</li> @@ -387,12 +395,12 @@ <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.</p> + <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> <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> <ul> - <li>All changes (INSERT, UPDATE, DELETE, DDL) are first written as log records to WAL buffers, then flushed to WAL segment files on disk (`pg_wal` directory).</li> + <li>All changes (INSERT, UPDATE, DELETE, DDL) are first written as log records to WAL buffers, then flushed to WAL segment files on disk (<span class="term"><a href="https://www.postgresql.org/docs/current/wal-internals.html" target="_blank">`pg_wal` directory</a></span>).</li> <li>Only *after* the WAL record is durably stored on disk is the change allowed to be written to the actual table/index files (heap/index pages) in shared buffers, which are flushed later by the checkpointer or background writer.</li> </ul> <h6>Purpose</h6> @@ -400,13 +408,13 @@ <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.</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> </ul> <h6>Key Concepts</h6> <ul> <li><strong>WAL Segments:</strong> Files (default 16MB) containing WAL records. Sequentially numbered.</li> - <li><strong>Checkpoints:</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>WAL Archiving:</strong> Process of copying completed WAL segment files to a safe location (`archive_command`). Essential for PITR and some replication setups.</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> </ul> </div> </div> @@ -414,7 +422,7 @@ <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.</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. <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> @@ -422,7 +430,7 @@ <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>Expired row versions (<span class="term">dead tuples</span>) physically remain until cleaned up by `VACUUM`.</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> @@ -431,9 +439,9 @@ </ul> <h6>Consequences (Quirks)</h6> <ul> - <li><strong>Bloat:</strong> Dead tuples accumulate, consuming disk space and potentially slowing down scans. Requires regular `VACUUM`.</li> + <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>Transaction ID Wraparound:</strong> Requires freezing via `VACUUM` (see Quirks section).</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> </ul> </div> </div> @@ -441,21 +449,21 @@ <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">Relation</span> (Table/Index), <span class="term">Schema</span> (Namespace), <span class="term">Role</span> (User/Group), <span class="term">Heap</span> (Table Data), <span class="term">Tuple</span> (Row).</p> + <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).</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`.</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`.</li> - <li><strong>Heap:</strong> The main storage structure for a table's data (rows/tuples).</li> - <li><strong>Tuple:</strong> A physical row version stored on disk within a heap block.</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.</li> - <li><strong>WAL:</strong> Write-Ahead Log.</li> - <li><strong>MVCC:</strong> Multi-Version Concurrency Control.</li> - <li><strong>TOAST:</strong> The Oversized Attribute Storage Technique.</li> - <li><strong>Planner/Optimizer:</strong> Component that determines the most efficient way to execute a query.</li> - <li><strong>Executor:</strong> Component that runs the plan generated by the planner.</li> + <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> @@ -471,13 +479,13 @@ <div class="col-lg-4 col-md-6"> <div class="info-card type-jsonb" id="card-jsonb"> <div class="card-body"><h5><i class="bi bi-file-earmark-code"></i> JSON / JSONB</h5> - <div class="card-content-wrapper"><p class="summary"><span class="term">JSONB</span> stores decomposed binary JSON. Supports GIN indexing for fast lookups using operators like `->>`, `@>`, `?`.</p> + <div class="card-content-wrapper"><p class="summary"><span class="term"><a href="https://www.postgresql.org/docs/current/datatype-json.html" target="_blank">JSONB</a></span> stores decomposed binary JSON. Supports <a href="https://www.postgresql.org/docs/current/gin-intro.html" target="_blank">GIN indexing</a> for fast lookups using operators like <span class="term"><a href="https://www.postgresql.org/docs/current/functions-json.html" target="_blank">`->>`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSONB-OP-TABLE" target="_blank">`@>`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSONB-OP-TABLE" target="_blank">`?`</a></span>.</p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseJsonb" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <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.**</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> </ul> <h6>Key Operators</h6> <ul> @@ -490,12 +498,13 @@ <li>`?`: Key exists (string operand). `'{"a":1}'::jsonb ? 'a'` -> true.</li> <li>`?|`: Any key in array exists.</li> <li>`?&`: All keys in array exist.</li> + <li><a href="https://www.postgresql.org/docs/current/functions-json.html" target="_blank">[Full List in Docs]</a></li> </ul> <h6>Indexing (GIN)</h6> <ul> - <li>`CREATE INDEX idx_gin ON mytable USING GIN (jsonb_column);` (Indexes all keys/values).</li> - <li>`CREATE INDEX idx_gin_ops ON mytable USING GIN (jsonb_column jsonb_ops);` (Default opclass).</li> - <li>`CREATE INDEX idx_gin_path_ops ON mytable USING GIN (jsonb_column jsonb_path_ops);` (Optimized for `@>` operator).</li> + <li>`CREATE INDEX idx_gin ON mytable USING GIN (jsonb_column);` (Indexes all keys/values). <a href="https://www.postgresql.org/docs/current/gin-intro.html" target="_blank">[docs]</a></li> + <li>`CREATE INDEX idx_gin_ops ON mytable USING GIN (jsonb_column jsonb_ops);` (Default opclass <a href="https://www.postgresql.org/docs/current/gin-builtin-opclasses.html#GIN-BUILTIN-OPCLASSES-JSONB" target="_blank">[docs]</a>).</li> + <li>`CREATE INDEX idx_gin_path_ops ON mytable USING GIN (jsonb_column jsonb_path_ops);` (Optimized for `@>` operator <a href="https://www.postgresql.org/docs/current/gin-builtin-opclasses.html#GIN-BUILTIN-OPCLASSES-JSONB" target="_blank">[docs]</a>).</li> <li>Supports operators like `@>`, `?`, `?|`, `?&`. Crucial for performance!</li> </ul> <h6>Use Cases</h6> @@ -506,14 +515,14 @@ <div class="col-lg-4 col-md-6"> <div class="info-card type-array" id="card-array"> <div class="card-body"><h5><i class="bi bi-list-ol"></i> Arrays</h5> - <div class="card-content-wrapper"><p class="summary">Native array support for most data types (e.g., `integer[]`, `text[]`). Indexable with GIN using operators like `@>`, `&&`.</p> + <div class="card-content-wrapper"><p class="summary">Native array support for most data types (e.g., `integer[]`, `text[]`). Indexable with GIN using operators like <span class="term"><a href="https://www.postgresql.org/docs/current/functions-array.html" target="_blank">`@>`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/functions-array.html" target="_blank">`&&`</a></span>. <a href="https://www.postgresql.org/docs/current/arrays.html" target="_blank">[docs]</a></p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseArray" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseArray"> <h6>Syntax</h6> <ul> <li>Declaration: `my_ints integer[]`, `my_tags text[]`.</li> <li>Literals: `ARRAY[1, 2, 3]`, `'{apple, banana, cherry}'::text[]`.</li> - <li>Access: `my_array[1]` (1-based indexing!). `my_array[1:2]` (slice).</li> + <li>Access: `my_array[1]` (1-based indexing!). `my_array[1:2]` (slice). <a href="https://www.postgresql.org/docs/current/arrays.html#ARRAYS-ACCESSING" target="_blank">[docs]</a></li> </ul> <h6>Key Operators</h6> <ul> @@ -523,15 +532,16 @@ <li>`&&`: Overlaps (have elements in common). `ARRAY[1,2,3] && ARRAY[3,4,5]` -> true.</li> <li>`ANY(array)` / `SOME(array)`: Check against array elements, e.g. `col = ANY(my_array)`.</li> <li>`ALL(array)`: Check all elements satisfy condition.</li> + <li><a href="https://www.postgresql.org/docs/current/functions-array.html" target="_blank">[Full List in Docs]</a></li> </ul> <h6>Indexing (GIN)</h6> <ul> - <li>`CREATE INDEX idx_array_gin ON mytable USING GIN (array_column);`</li> - <li>Supports `@>`, `<@`, `&&`, `=` (for some types). Essential for searching within arrays efficiently.</li> + <li>`CREATE INDEX idx_array_gin ON mytable USING GIN (array_column);` <a href="https://www.postgresql.org/docs/current/gin-intro.html" target="_blank">[docs]</a></li> + <li>Supports `@>`, `<@`, `&&`, `=` (for some types <a href="https://www.postgresql.org/docs/current/gin-builtin-opclasses.html" target="_blank">[opclasses]</a>). Essential for searching within arrays efficiently.</li> </ul> <h6>Functions</h6> <ul> - <li>`array_append()`, `array_prepend()`, `array_cat()`, `array_dims()`, `cardinality()` (size), `unnest()` (expand array to rows).</li> + <li>`array_append()`, `array_prepend()`, `array_cat()`, `array_dims()`, `cardinality()` (size), <span class="term"><a href="https://www.postgresql.org/docs/current/functions-array.html#FUNCTIONS-ARRAY-UNNEST" target="_blank">`unnest()`</a></span> (expand array to rows).</li> </ul> <h6>Use Cases</h6> <p>Storing tags, simple lists of related IDs, multi-value attributes.</p> @@ -541,18 +551,18 @@ <div class="col-lg-4 col-md-6"> <div class="info-card type-range" id="card-range"> <div class="card-body"><h5><i class="bi bi-calendar-range"></i> Range Types</h5> - <div class="card-content-wrapper"><p class="summary">Represent ranges (`int4range`, `tsrange`, etc.). Support GIST indexing and exclusion constraints to prevent overlaps.</p> + <div class="card-content-wrapper"><p class="summary">Represent ranges (<span class="term"><a href="https://www.postgresql.org/docs/current/rangetypes.html" target="_blank">`int4range`</a></span>, <span class="term"><a href="https://www.postgresql.org/docs/current/rangetypes.html" target="_blank">`tsrange`</a></span>, etc.). Support <a href="https://www.postgresql.org/docs/current/gist-intro.html" target="_blank">GiST indexing</a> and <span class="term"><a href="https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION" target="_blank">exclusion constraints</a></span> to prevent overlaps.</p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseRange" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseRange"> <h6>Built-in Types</h6> <ul> - <li>`int4range`, `int8range`, `numrange`, `tsrange` (timestamp without tz), `tstzrange` (timestamp with tz), `daterange`.</li> - <li>Can create custom range types.</li> + <li>`int4range`, `int8range`, `numrange`, `tsrange` (timestamp without tz), `tstzrange` (timestamp with tz), `daterange`. <a href="https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-BUILTIN" target="_blank">[docs]</a></li> + <li>Can create custom range types. <a href="https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-DEFINING" target="_blank">[docs]</a></li> </ul> <h6>Syntax</h6> <ul> <li>Literals: `[)`, `(]`, `()`, `[]` denote inclusive/exclusive bounds.</li> - <li>Examples: `'[2024-01-01, 2024-01-31)'::daterange`, `'[10, 20]'::int4range`. Empty ranges allowed. Unbounded ranges use omitted bound.</li> + <li>Examples: `'[2024-01-01, 2024-01-31)'::daterange`, `'[10, 20]'::int4range`. Empty ranges allowed. Unbounded ranges use omitted bound. <a href="https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-CONSTANTS" target="_blank">[docs]</a></li> </ul> <h6>Key Operators</h6> <ul> @@ -564,15 +574,16 @@ <li>`&<`: Extends to the right (no overlap).</li> <li>`&>`: Extends to the left (no overlap).</li> <li>`-|-` (adjacent): `tsrange('2024-01-01 10:00', '2024-01-01 11:00') -|- tsrange('2024-01-01 11:00', '2024-01-01 12:00')` -> true.</li> + <li><a href="https://www.postgresql.org/docs/current/functions-range.html" target="_blank">[Full List in Docs]</a></li> </ul> <h6>Indexing (GiST)</h6> <ul> - <li>`CREATE INDEX idx_range_gist ON mytable USING GIST (range_column);`</li> - <li>Supports range operators like `@>`, `&&`, `<@`, etc.</li> + <li>`CREATE INDEX idx_range_gist ON mytable USING GIST (range_column);` <a href="https://www.postgresql.org/docs/current/gist-intro.html" target="_blank">[docs]</a></li> + <li>Supports range operators like `@>`, `&&`, `<@`, etc. <a href="https://www.postgresql.org/docs/current/gist-builtin-opclasses.html" target="_blank">[opclasses]</a></li> </ul> <h6>Exclusion Constraints</h6> <ul> - <li>Prevent overlapping ranges within a table, often used for scheduling or booking systems.</li> + <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><code>ALTER TABLE events ADD CONSTRAINT no_overlapping_times EXCLUDE USING GIST (room_id WITH =, event_times WITH &&);</code></li> </ul> </div> @@ -581,7 +592,7 @@ <div class="col-lg-4 col-md-6"> <div class="info-card type-enum" id="card-enum"> <div class="card-body"><h5><i class="bi bi-signpost-split"></i> Enum Types</h5> - <div class="card-content-wrapper"><p class="summary">User-defined enumerated types (`CREATE TYPE mood AS ENUM (...)`). Provides type safety and better semantics than check constraints.</p> + <div class="card-content-wrapper"><p class="summary">User-defined enumerated types (<span class="term"><a href="https://www.postgresql.org/docs/current/sql-createtype.html" target="_blank">`CREATE TYPE mood AS ENUM (...)`</a></span>). Provides type safety and better semantics than check constraints. <a href="https://www.postgresql.org/docs/current/datatype-enum.html" target="_blank">[docs]</a></p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseEnum" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseEnum"> <h6>Definition</h6> @@ -603,7 +614,7 @@ </ul> <h6>Modification</h6> <ul> - <li>Add values: `ALTER TYPE primary_color ADD VALUE 'yellow' AFTER 'blue';` (Cannot easily remove or reorder values).</li> + <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> </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> @@ -613,15 +624,15 @@ <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">UUID</span>, Geometric types (PostGIS foundation), Network types (`inet`), and <span class="term">Hstore</span> (key/value).</p> + <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> <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 `gen_random_uuid()` (requires `pgcrypto` extension) or client-side.</li> - <li><strong>Geometric Types (`point`, `line`, `lseg`, `box`, `path`, `polygon`, `circle`)</strong>: Basic 2D geometric types. Indexed using GiST. Foundation for the powerful PostGIS extension.</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.</li> - <li><strong>`Hstore` 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>`TIMESTAMP WITH TIME ZONE` (`timestamptz`) vs `TIMESTAMP WITHOUT TIME ZONE` (`timestamp`)</strong>: + <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>: <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> @@ -639,19 +650,19 @@ <div class="col-lg-4 col-md-6"> <div class="info-card type-btree" id="card-btree"> <div class="card-body"><h5><i class="bi bi-diagram-3"></i> B-Tree Index</h5> - <div class="card-content-wrapper"><p class="summary">Default index type. Excellent for equality (`=`), range (`<`, `>`, `BETWEEN`), `IN`, `IS NULL`, and sorting.</p> + <div class="card-content-wrapper"><p class="summary">Default index type. Excellent for equality (`=`), range (`<`, `>`, `BETWEEN`), `IN`, `IS NULL`, and sorting. <a href="https://www.postgresql.org/docs/current/indexes-types.html#INDEXES-TYPES-BTREE" target="_blank">[docs]</a></p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseBtree" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseBtree"> <h6>Core Use Case</h6> <p>The workhorse index for most relational data. Stores indexed values in a sorted tree structure, allowing efficient lookups, range scans, and ordered retrieval.</p> <h6>Supported Operators</h6> - <p>`=`, `<`, `<=`, `>`, `>=`, `BETWEEN`, `IN`, `IS NULL`, `IS NOT NULL`.</p> + <p>`=`, `<`, `<=`, `>`, `>=`, `BETWEEN`, `IN`, `IS NULL`, `IS NOT NULL`. <a href="https://www.postgresql.org/docs/current/btree-behavior.html" target="_blank">[details]</a></p> <h6>Sorting</h6> - <p>Can satisfy `ORDER BY` clauses matching the index definition (including ASC/DESC, NULLS FIRST/LAST), potentially avoiding a separate sort step.</p> + <p>Can satisfy `ORDER BY` clauses matching the index definition (including ASC/DESC, NULLS FIRST/LAST), potentially avoiding a separate sort step. <a href="https://www.postgresql.org/docs/current/indexes-ordering.html" target="_blank">[docs]</a></p> <h6>Examples</h6> <ul> - <li>`CREATE INDEX idx_users_email ON users (email);` (For `WHERE email = '...'`)</li> - <li>`CREATE INDEX idx_orders_created ON orders (created_at DESC);` (For `WHERE created_at > '...' ORDER BY created_at DESC`)</li> + <li><code><a href="https://www.postgresql.org/docs/current/sql-createindex.html" target="_blank">CREATE INDEX</a> idx_users_email ON users (email);</code> (For `WHERE email = '...'`)</li> + <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> @@ -661,22 +672,22 @@ <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">JSONB</span>, <span class="term">Arrays</span>, <span class="term">Hstore</span>, Full-Text Search. Indexes elements *within* values.</p> + <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> <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.</p> + <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).</li> - <li><strong>JSONB:</strong> Supports `@>`, `<@`, `?` (key exists), `?|` (any key exists), `?&` (all keys exist). Use `jsonb_ops` (default) or `jsonb_path_ops` (optimized for `@>`).</li> - <li><strong>Hstore:</strong> Supports `@>`, `?`, `?|`, `?&`.</li> - <li><strong>Full-Text Search (`tsvector`):</strong> Supports `@@` (match) operator.</li> + <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> </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.</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>Index size can be large, but often smaller than GiST for the same data.</li> </ul> <h6>Example</h6> @@ -688,23 +699,23 @@ <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: Geometric, Full-Text Search, Ranges. Handles overlap/containment, nearest-neighbor.</p> + <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> <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.</p> + <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 (PostGIS):</strong> Supports spatial operators like intersection (`&&`), containment (`@`, `~`), distance (`<->` for KNN).</li> - <li><strong>Range Types:</strong> Supports overlap (`&&`), containment (`@>`, `<@`), adjacency (`-|-`), etc.</li> - <li><strong>Full-Text Search (`tsvector`):</strong> Supports `@@` (match) operator. Often faster updates but slower searches than GIN for FTS.</li> - <li><strong>Other extensions (`hstore`, `pg_trgm` for fuzzy search):</strong> Can provide GiST support.</li> + <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> </ul> <h6>Performance</h6> <ul> <li>Generally faster to build/update than GIN.</li> <li>Search performance can be slower than GIN for types GIN handles well (like JSONB/Array containment).</li> - <li>Handles KNN (K-Nearest Neighbor) searches efficiently (e.g., find points closest to X).</li> + <li>Handles KNN (K-Nearest Neighbor) searches efficiently (e.g., find points closest to X). <a href="https://www.postgresql.org/docs/current/gist-implementation.html#GIST-KNN" target="_blank">[KNN]</a></li> </ul> <h6>Example</h6> <p><code>CREATE INDEX idx_events_times_gist ON events USING GIST (event_times);</code> (for `event_times tstzrange`)<br/> @@ -715,11 +726,11 @@ <div class="col-lg-4 col-md-6"> <div class="info-card type-brin" id="card-brin"> <div class="card-body"><h5><i class="bi bi-bounding-box-circles"></i> BRIN Index</h5> - <div class="card-content-wrapper"><p class="summary">Block Range Index. Very small index for huge tables where values correlate strongly with physical storage order (e.g., timestamps).</p> + <div class="card-content-wrapper"><p class="summary">Block Range Index. Very small index for huge tables where values correlate strongly with physical storage order (e.g., timestamps). <a href="https://www.postgresql.org/docs/current/brin.html" target="_blank">[docs]</a></p> <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.</p> + <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> <h6>Ideal Scenario</h6> <ul> <li>Very large tables (many GBs/TBs).</li> @@ -744,13 +755,13 @@ <div class="col-lg-4 col-md-6"> <div class="info-card type-hash" id="card-hash"> <div class="card-body"><h5><i class="bi bi-hash"></i> Hash Index</h5> - <div class="card-content-wrapper"><p class="summary">Supports only equality (`=`) lookups. Less commonly used than B-Tree. Fully WAL-logged and usable since v10.</p> + <div class="card-content-wrapper"><p class="summary">Supports only equality (`=`) lookups. Less commonly used than B-Tree. Fully WAL-logged and usable since v10. <a href="https://www.postgresql.org/docs/current/indexes-types.html#INDEXES-TYPES-HASH" target="_blank">[docs]</a></p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseHash" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseHash"> <h6>Core Concept</h6> <p>Stores a hash of the indexed value, mapping hashes to heap TIDs. Designed for fast equality checks.</p> <h6>Supported Operators</h6> - <p>Only `=`.</p> + <p>Only `=`. <a href="https://www.postgresql.org/docs/current/hash-behavior.html" target="_blank">[details]</a></p> <h6>Historical Context & Quirks</h6> <ul> <li>Before PostgreSQL v10, Hash indexes were not WAL-logged, meaning they had to be rebuilt after a crash and couldn't be replicated. This severely limited their use.</li> @@ -770,27 +781,27 @@ <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">Partial</span>, <span class="term">Expression</span>, <span class="term">Covering (INCLUDE)</span>, and <span class="term">Concurrent</span> indexes for optimization.</p> + <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> <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>Partial Indexes:</strong> Index only a subset of rows defined by a `WHERE` clause. + <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. <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> - <li><strong>Expression Indexes (Functional Indexes):</strong> Index the result of a function or expression applied to one or more columns. + <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>Example: `CREATE INDEX idx_users_email_lower ON users (lower(email));` (for `WHERE lower(email) = '...'`)</li></ul> </li> - <li><strong>Covering Indexes (INCLUDE clause):</strong> Store additional, non-key columns directly in the index leaf pages. - <ul><li>Allows <span class="term">Index-Only Scans</span> where the query can be satisfied entirely from the index without visiting the table heap.</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>Example: `CREATE INDEX idx_items_name ON items (name) INCLUDE (price, category);` (for `SELECT price FROM items WHERE name = '...'`)</li></ul> </li> - <li><strong>Concurrent Index Builds:</strong> Create indexes without blocking writes (`INSERT`/`UPDATE`/`DELETE`) on the table. + <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>Syntax: `CREATE INDEX CONCURRENTLY ...;`</li> <li>Essential for adding indexes to busy production tables with minimal downtime.</li> - <li>Similar `REINDEX CONCURRENTLY` exists.</li></ul> + <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> </ul> </div> @@ -806,13 +817,13 @@ <div class="col-lg-4 col-md-6"> <div class="info-card type-window" id="card-window"> <div class="card-body"><h5><i class="bi bi-layout-three-columns"></i> Window Functions</h5> - <div class="card-content-wrapper"><p class="summary">Perform calculations across sets of table rows related to the current row. Uses the `OVER()` clause (`PARTITION BY`, `ORDER BY`).</p> + <div class="card-content-wrapper"><p class="summary">Perform calculations across sets of table rows related to the current row. Uses the <span class="term"><a href="https://www.postgresql.org/docs/current/tutorial-window.html" target="_blank">`OVER()` clause</a></span> (`PARTITION BY`, `ORDER BY`).</p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseWindow" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseWindow"> <h6>Core Concept</h6> - <p>Unlike aggregate functions which collapse rows, window functions return a value for *each* row based on a "window" of related rows.</p> + <p>Unlike aggregate functions which collapse rows, window functions return a value for *each* row based on a "window" of related rows. <a href="https://www.postgresql.org/docs/current/functions-window.html" target="_blank">[docs]</a></p> <h6>Syntax</h6> - <p><code>function_name() OVER ( [PARTITION BY expr_list] [ORDER BY expr_list] [frame_clause] )</code></p> + <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> @@ -820,9 +831,9 @@ </ul> <h6>Common Functions</h6> <ul> - <li><strong>Ranking:</strong> `ROW_NUMBER()`, `RANK()`, `DENSE_RANK()`, `NTILE(n)`.</li> + <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> `LAG()`, `LEAD()`, `FIRST_VALUE()`, `LAST_VALUE()`, `NTH_VALUE()`.</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> </ul> <h6>Example Use Cases</h6> <p>Calculating running totals, ranking results within categories, finding previous/next values, computing moving averages.</p> @@ -834,7 +845,7 @@ <div class="col-lg-4 col-md-6"> <div class="info-card type-cte" id="card-cte"> <div class="card-body"><h5><i class="bi bi-file-earmark-ruled"></i> Common Table Expressions (CTEs)</h5> - <div class="card-content-wrapper"><p class="summary">Define temporary, named result sets within a query using `WITH`. Improves readability. Supports `RECURSIVE` for hierarchies.</p> + <div class="card-content-wrapper"><p class="summary">Define temporary, named result sets within a query using <span class="term"><a href="https://www.postgresql.org/docs/current/queries-with.html" target="_blank">`WITH`</a></span>. Improves readability. Supports <span class="term"><a href="https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-RECURSIVE" target="_blank">`RECURSIVE`</a></span> for hierarchies.</p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseCte" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseCte"> <h6>Basic CTE</h6> @@ -850,7 +861,7 @@ SELECT * FROM orders WHERE region IN (SELECT region FROM top_regions);</code></pre> <h6>Recursive CTEs</h6> - <p>Used for querying hierarchical data (e.g., organizational charts, parts explosion, graph traversal).</p> + <p>Used for querying hierarchical data (e.g., organizational charts, parts explosion, graph traversal). <a href="https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-RECURSIVE" target="_blank">[docs]</a></p> <pre><code>WITH RECURSIVE subordinates AS ( -- Non-recursive term (anchor) SELECT employee_id, name, manager_id, 1 as level @@ -866,17 +877,17 @@ 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 MATERIALIZED (...)` (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>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> </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.</p> + <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> </div> </div> </div> <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 `DISTINCT ON (...)` 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.</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> @@ -885,7 +896,7 @@ 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.</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> </ul> <h6>Use Case: "Latest/Greatest per Group"</h6> <p>Very common for finding the most recent entry for each item.</p> @@ -902,7 +913,7 @@ ORDER BY device_id, event_timestamp DESC;</code></pre> <div class="col-lg-4 col-md-6"> <div class="info-card type-upsert" id="card-upsert"> <div class="card-body"><h5><i class="bi bi-node-plus"></i> INSERT ... ON CONFLICT (UPSERT)</h5> - <div class="card-content-wrapper"><p class="summary">Atomic "UPSERT" operation. Handles unique constraint violations by either doing nothing (`DO NOTHING`) or updating the existing row (`DO UPDATE`).</p> + <div class="card-content-wrapper"><p class="summary">Atomic "UPSERT" operation. Handles unique constraint violations by either doing nothing (`DO NOTHING`) or updating the existing row (`DO UPDATE`). <a href="https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT" target="_blank">[docs]</a></p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseUpsert" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseUpsert"> <h6>Syntax</h6> @@ -941,23 +952,23 @@ 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 `UPDATE ... FROM`, `DELETE ... USING` for joining in modifications. `RETURNING` clause gets back modified rows.</p> + <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> <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> <p>Allows joining other tables directly into `UPDATE` or `DELETE` statements to determine which rows to modify or filter based on related data.</p> <pre><code>-- Update order status based on payment status -UPDATE orders o +<a href="https://www.postgresql.org/docs/current/sql-update.html" target="_blank">UPDATE</a> orders o SET status = 'shipped' FROM payments p WHERE o.order_id = p.order_id AND p.status = 'paid'; -- Delete users who haven't logged in recently -DELETE FROM users u +<a href="https://www.postgresql.org/docs/current/sql-delete.html" target="_blank">DELETE</a> FROM users u USING user_last_login l WHERE u.user_id = l.user_id AND l.last_login < NOW() - INTERVAL '1 year';</code></pre> <h6>RETURNING Clause</h6> - <p>Appends a list of values from the rows modified by an `INSERT`, `UPDATE`, or `DELETE` statement to the command result.</p> + <p>Appends a list of values from the rows modified by an `INSERT`, `UPDATE`, or `DELETE` statement to the command result. <a href="https://www.postgresql.org/docs/current/dml-returning.html" target="_blank">[docs]</a></p> <ul> <li>Useful for getting default values (like serial IDs) after an `INSERT`, or confirming exactly which rows were affected.</li> <li>Avoids a separate `SELECT` statement, improving efficiency and atomicity (within the same command).</li> @@ -979,11 +990,11 @@ RETURNING log_id;</code></pre> <div class="col-lg-4 col-md-6"> <div class="info-card type-lateral" id="card-lateral"> <div class="card-body"><h5><i class="bi bi-link-45deg"></i> Lateral Joins</h5> - <div class="card-content-wrapper"><p class="summary">`LATERAL` allows a subquery in the `FROM` clause to reference columns from preceding `FROM` items. Like a correlated `foreach` loop.</p> + <div class="card-content-wrapper"><p class="summary"><span class="term"><a href="https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-LATERAL" target="_blank">`LATERAL`</a></span> allows a subquery in the `FROM` clause to reference columns from preceding `FROM` items. Like a correlated `foreach` loop.</p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseLateral" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseLateral"> <h6>Core Concept</h6> - <p>Normally, a subquery in the `FROM` clause executes independently. `LATERAL` makes it dependent, executing it *for each row* of the preceding table, allowing the subquery to use values from that row.</p> + <p>Normally, a subquery in the `FROM` clause executes independently. `LATERAL` makes it dependent, executing it *for each row* of the preceding table, allowing the subquery to use values from that row. <a href="https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-LATERAL" target="_blank">[docs]</a></p> <h6>Syntax</h6> <pre><code>SELECT ... FROM table_a a, LATERAL (subquery using a.columns) sub @@ -1026,15 +1037,15 @@ LEFT JOIN LATERAL ( <div class="col-lg-4 col-md-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">`READ COMMITTED` (default), `REPEATABLE READ`, `SERIALIZABLE`. Control transaction visibility. `RR` can cause serialization failures.</p> + <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> <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.</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">serialization failure</span> (error 40001) if it detects a potential anomaly that would violate serializability. Requires application retry logic.</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.</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> </ul> <h6>Choosing a Level</h6> <ul> @@ -1042,31 +1053,31 @@ LEFT JOIN LATERAL ( <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> </ul> <h6>Setting Level</h6> - <p><code>SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;</code><br/> - <code>START TRANSACTION ISOLATION LEVEL SERIALIZABLE;</code></p> + <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/> + <code><a href="https://www.postgresql.org/docs/current/sql-start-transaction.html" target="_blank">START TRANSACTION ISOLATION LEVEL</a> SERIALIZABLE;</code></p> </div> </div> </div> <div class="col-lg-4 col-md-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 `LOCK TABLE`. Application-level <span class="term">Advisory Locks</span>.</p> + <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> <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> <ul> <li>Managed automatically by MVCC and DML commands.</li> - <li>`SELECT ... FOR UPDATE`/`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><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> </ul> <h6>Table-Level Locks (Explicit)</h6> <ul> - <li>`LOCK TABLE table_name [IN lock_mode]`: 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`.</li> + <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>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., `pg_advisory_lock(key)`, `pg_advisory_xact_lock(key)`).</li> + <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> @@ -1074,11 +1085,11 @@ LEFT JOIN LATERAL ( <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).</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> </ul> <h6>Monitoring</h6> - <p>Use the `pg_locks` 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.</p> </div> </div> </div> @@ -1093,27 +1104,27 @@ LEFT JOIN LATERAL ( <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 (bloat), updates visibility map, prevents <span class="term">TXID Wraparound</span>. <span class="term">Autovacuum</span> is critical.</p> + <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> <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.</p> + <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 Visibility Map (VM):</strong> Marks blocks containing only visible-to-all tuples, enabling Index-Only Scans.</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 TXID Wraparound:</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><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> </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>`VACUUM FULL [tablename]`</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 `pg_repack` extension as an online alternative.</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> </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).</li> - <li>**Essential for health.** Default settings are often too conservative for busy databases. Requires tuning (`autovacuum_max_workers`, `autovacuum_vacuum_scale_factor`, `autovacuum_analyze_threshold`, etc.). Monitor its activity!</li> + <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> </ul> </div> </div> @@ -1121,11 +1132,11 @@ 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.</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. <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).</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). <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> @@ -1133,7 +1144,7 @@ LEFT JOIN LATERAL ( <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>Stored in system catalog `pg_statistic`.</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> <ul> @@ -1143,20 +1154,20 @@ 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 (`autovacuum_analyze_scale_factor` + `autovacuum_analyze_threshold`). Tuning may be required.</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. <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.</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. <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. `EXPLAIN ANALYZE` runs the query and shows actual times and row counts. Use `BUFFERS` for cache info.</p> + <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> <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.</p> + <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> @@ -1164,7 +1175,7 @@ LEFT JOIN LATERAL ( </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. `EXPLAIN (ANALYZE, BUFFERS) ...`</li> + <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> @@ -1178,40 +1189,41 @@ LEFT JOIN LATERAL ( <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><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 `explain.depesz.com`, `explain.dalibo.com`, PEV (`pev.dalibo.com`) 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.</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: `pg_stat_activity` (connections), `pg_stat_statements` (query stats), `pg_stat_user_tables` (table usage), `pg_locks`.</p> + <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> <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>`pg_stat_activity`</strong>: Shows information about currently active backend processes/connections. + <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. <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> - <li><strong>`pg_stat_statements`</strong> (`CREATE EXTENSION pg_stat_statements;`): Tracks execution statistics for all normalized queries executed. **Crucial for identifying expensive queries.** + <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>Use `pg_stat_statements_reset()` to clear stats.</li></ul> </li> - <li><strong>`pg_stat_user_tables` / `pg_stat_all_tables`</strong>: Shows table access statistics. + <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. <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>`pg_statio_user_tables` / `pg_statio_all_tables`</strong>: Shows table I/O statistics. + <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> - <li><strong>`pg_locks`</strong>: Shows currently held locks. Useful for diagnosing locking contention and deadlocks.</li> - <li><strong>`pg_stat_replication`</strong>: Monitor streaming replication status on the primary.</li> - <li><strong>`pg_stat_wal_receiver`</strong>: Monitor streaming replication status on the standby.</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> </ul> </div> </div> @@ -1219,27 +1231,27 @@ LEFT JOIN LATERAL ( <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 `postgresql.conf`: memory (`shared_buffers`, `work_mem`), WAL (`max_wal_size`), Checkpoints, Autovacuum. Use `pgtune`.</p> + <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> <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 `postgresql.conf` (requires reload or restart for many settings) and `pg_hba.conf` (authentication, requires reload). Use `ALTER SYSTEM SET ...` to modify `postgresql.conf` via SQL (writes to `postgresql.auto.conf`, overrides main file).</p> + <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:** (See Memory card) `shared_buffers`, `work_mem`, `maintenance_work_mem`, `effective_cache_size`. Critical for performance.</strong></li> - <li><strong>WAL:** `wal_level` (`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.</strong></li> - <li><strong>Checkpoints:** `checkpoint_timeout` (max time between checkpoints), `checkpoint_completion_target` (spread checkpoint I/O over time, e.g., 0.9). Controls recovery time vs. I/O spikes.</strong></li> - <li><strong>Autovacuum:** (See VACUUM card) `autovacuum`, `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.**</strong></li> - <li><strong>Planner:** `random_page_cost`, `seq_page_cost`, `effective_cache_size`, `enable_*` flags (e.g., `enable_bitmapscan`). Influences query plan choices.</strong></li> - <li><strong>Connections:** `max_connections` (set high enough for clients + pooler), `superuser_reserved_connections`.</strong></li> - <li><strong>Logging:** `log_destination`, `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.</strong></li> + <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> </ul> <h6>Tools & Approach</h6> <ul> - <li>Start with defaults or recommendations from `pgtune` (online tool or script) based on system resources.</li> + <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>Use `SHOW parameter_name;` or query `pg_settings` view to see current values.</li> - <li>Use `SELECT pg_reload_conf();` to apply changes that don't require a 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> </ul> </div> </div> @@ -1254,22 +1266,22 @@ LEFT JOIN LATERAL ( <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 `PL/pgSQL`. Others include `PL/Python`, `PL/Perl`, `PL/v8`.</p> + <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> <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> <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.</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).</li> - <li><strong>`PL/Perl` (`plperl`)</strong>: Write functions in Perl.</li> - <li><strong>`PL/v8` (`plv8`)</strong>: Write functions in JavaScript (using the V8 engine). Useful for JSON processing, web-related logic.</li> - <li><strong>`SQL`</strong>: Simple functions written purely in SQL. Can be inlined by the planner.</li> - <li><strong>`C`</strong>: Highest performance, allows low-level access, but more complex development and deployment.</li> + <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> </ul> <h6>Creating Functions</h6> - <pre><code>CREATE OR REPLACE FUNCTION get_user_count() + <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 $$ DECLARE user_count integer; @@ -1279,69 +1291,69 @@ BEGIN END; $$ LANGUAGE plpgsql;</code></pre> <h6>Procedures (Postgres 11+)</h6> - <p>Use `CREATE PROCEDURE ... LANGUAGE ... AS $$ ... $$;`. 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> + <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 (`CREATE TRIGGER ... EXECUTE FUNCTION my_trigger_func();`) to execute automatically on DML events (BEFORE/AFTER INSERT/UPDATE/DELETE).</p> + <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> </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 `CREATE EXTENSION`. Huge ecosystem (PostGIS, TimescaleDB, Citus, pg_stat_statements).</p> + <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> <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.</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. <a href="https://www.postgresql.org/docs/current/extend-extensions.html" target="_blank">[docs]</a></p> <h6>Managing Extensions</h6> <ul> - <li>`CREATE EXTENSION extension_name [SCHEMA schema_name];`: Installs the extension. Requires extension files to be present on the server.</li> - <li>`\dx` (in psql): List installed extensions.</li> - <li>`ALTER EXTENSION extension_name UPDATE [TO 'new_version'];`: Upgrades an extension.</li> - <li>`DROP EXTENSION extension_name;`: 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];`</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> </ul> <h6>Notable Extensions (Examples)</h6> <ul> - <li><strong>PostGIS:</strong> Adds comprehensive support for geographic objects and spatial queries. Industry standard for geospatial data.</li> - <li><strong>TimescaleDB:</strong> Turns PostgreSQL into a powerful time-series database with automatic partitioning (hypertables), specialized functions, and compression.</li> - <li><strong>Citus:</strong> Distributes PostgreSQL horizontally for sharding and parallel query processing.</li> - <li><strong>`pg_stat_statements`</strong>: Tracks query execution statistics (essential for performance tuning).</li> - <li><strong>`pgcrypto`</strong>: Provides cryptographic functions (hashing, encryption).</li> - <li><strong>`uuid-ossp`</strong> / <strong>`pgcrypto`</strong>: Functions to generate UUIDs.</li> - <li><strong>`hstore`</strong>: Key-value data type.</li> - <li><strong>`pg_trgm`</strong>: Trigram matching for fuzzy string search.</li> - <li><strong>`pg_cron`</strong>: In-database job scheduler (like cron).</li> - <li><strong>`pg_repack`</strong>: Online table reorganization (alternative to `VACUUM FULL`).</li> + <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> </ul> <h6>Finding Extensions</h6> - <p>PostgreSQL Extension Network (PGXN), GitHub, vendor websites.</p> + <p><a href="https://pgxn.org/" target="_blank">PostgreSQL Extension Network (PGXN)</a>, GitHub, vendor websites.</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 `CREATE FOREIGN TABLE`. Connect to other PG dbs, MySQL, files, etc.</p> + <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> <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.</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://www.postgresql.org/docs/current/ddl-foreign-data.html" target="_blank">[docs]</a></p> <h6>Setup Steps</h6> <ol> - <li><strong>Install FDW Extension:</strong> `CREATE EXTENSION postgres_fdw;` (for connecting to other PG dbs) or `oracle_fdw`, `mysql_fdw`, `file_fdw`, etc.</li> - <li><strong>Create Server:</strong> `CREATE SERVER remote_pg_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'other_host', dbname 'other_db', port '5432');`</li> - <li><strong>Create User Mapping:</strong> `CREATE USER MAPPING FOR current_user SERVER remote_pg_server OPTIONS (user 'remote_user', password 'remote_pass');` (maps local user to remote credentials)</li> + <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>CREATE FOREIGN TABLE remote_users ( + <pre><code><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 `IMPORT FOREIGN SCHEMA ...` to import definitions automatically). + (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). </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.</p> + <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> <h6>Benefits</h6> <ul> <li>Unified view of distributed data.</li> @@ -1364,28 +1376,28 @@ $$ LANGUAGE plpgsql;</code></pre> <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">Hot Standby</span>). Async or Sync commit. Uses <span class="term">Slots</span>.</p> + <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> <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 continuously replays these WAL records to keep its data files nearly identical to the primary's.</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 `hot_standby = on` (default) on the standby, it can serve read-only queries while continuously applying WALs.</li> - <li>Read queries on the standby see a slightly delayed view of the data. Can configure `max_standby_streaming_delay` / `max_standby_archive_delay` to cancel queries conflicting with WAL application.</li> + <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> </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>Synchronous:</strong> Primary waits for confirmation from one or more synchronous standbys (`synchronous_standby_names`) 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><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> </ul> <h6>Replication Slots</h6> <ul> - <li>`CREATE_REPLICATION_SLOT` / `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.**</li> + <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> </ul> <h6>Use Cases</h6> <p>Read scaling, High Availability (HA) failover, near real-time data warehousing feed.</p> @@ -1395,19 +1407,19 @@ $$ LANGUAGE plpgsql;</code></pre> <div class="col-lg-4 col-md-6"> <div class="info-card type-logical-rep" id="card-logical-rep"> <div class="card-body"><h5><i class="bi bi-file-diff"></i> Logical Replication</h5> - <div class="card-content-wrapper"><p class="summary">Replicates logical data changes (row changes). Allows cross-version, selective replication via Publisher/Subscriber model.</p> + <div class="card-content-wrapper"><p class="summary">Replicates logical data changes (row changes). Allows cross-version, selective replication via <a href="https://www.postgresql.org/docs/current/logical-replication-publication.html" target="_blank">Publisher</a>/<a href="https://www.postgresql.org/docs/current/logical-replication-subscription.html" target="_blank">Subscriber</a> model. <a href="https://www.postgresql.org/docs/current/logical-replication.html" target="_blank">[docs]</a></p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseLogicalRep" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <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).</li> + <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 `wal_level = logical` on the publisher.</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> </ul> <h6>Publisher/Subscriber Model</h6> <ul> - <li><strong>Publisher:</strong> The source database. Define a `PUBLICATION` specifying which tables (or all tables) to publish changes for. `CREATE PUBLICATION mypub FOR TABLE users, orders;`</li> - <li><strong>Subscriber:</strong> The target database. Define a `SUBSCRIPTION` connecting to the publisher and mapping to the publication. `CREATE SUBSCRIPTION mysub CONNECTION '...' PUBLICATION mypub;`</li> + <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> </ul> <h6>Advantages over Streaming Replication</h6> @@ -1419,7 +1431,7 @@ $$ LANGUAGE plpgsql;</code></pre> </ul> <h6>Limitations</h6> <ul> - <li>Does not replicate DDL changes (schema changes must be applied manually on both sides).</li> + <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 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> @@ -1432,15 +1444,15 @@ $$ LANGUAGE plpgsql;</code></pre> <div class="col-lg-4 col-md-6"> <div class="info-card type-ha-tools" id="card-ha-tools"> <div class="card-body"><h5><i class="bi bi-shield-shaded"></i> HA / Failover Tools</h5> - <div class="card-content-wrapper"><p class="summary">External tools often used to manage replication topology, monitor health, and automate failover (e.g., Patroni, repmgr).</p> + <div class="card-content-wrapper"><p class="summary">External tools often used to manage replication topology, monitor health, and automate failover (e.g., <a href="https://patroni.readthedocs.io/" target="_blank">Patroni</a>, <a href="https://repmgr.org/" target="_blank">repmgr</a>).</p> <button class="btn btn-sm details-toggle" type="button" data-bs-toggle="collapse" data-bs-target="#collapseHaTools" aria-expanded="false">Details <i class="bi bi-chevron-down"></i></button></div></div> <div class="collapse collapse-content" id="collapseHaTools"> - <p>While PostgreSQL provides the core replication mechanisms, automating the detection of primary failure and promotion of a standby requires external tooling.</p> + <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>Patroni:</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>repmgr:</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>Pgpool-II:</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><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> </ul> <h6>Key HA Concepts Managed by Tools</h6> @@ -1465,18 +1477,18 @@ $$ LANGUAGE plpgsql;</code></pre> <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.</p> + <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> <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><code>pg_dump</code> 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 creates a script file containing SQL commands (`CREATE TABLE`, `COPY` data, `CREATE INDEX`, etc.) needed to recreate the database.</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 `pg_restore`.</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> <ul> @@ -1491,18 +1503,18 @@ $$ LANGUAGE plpgsql;</code></pre> <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><code>pg_dumpall</code> dumps all databases plus global objects (roles, tablespaces).</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> </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.</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. <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><code>pg_basebackup</code> 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 server (like a standby) 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> @@ -1529,22 +1541,22 @@ $$ LANGUAGE plpgsql;</code></pre> <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">WAL Archiving</span>.</p> + <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> <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).</p> + <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> <h6>Requirements</h6> <ol> - <li><strong>Physical Base Backup:</strong> Taken using `pg_basebackup` or similar method.</li> - <li><strong>Continuous WAL Archiving:</strong> The primary server must be configured (`wal_level = replica` or higher, `archive_mode = on`, `archive_command = '...'`) to continuously copy completed WAL segments to a separate, safe archive location.</li> + <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> </ol> <h6>Recovery Process</h6> <ol> <li>Restore the chosen physical base backup to a new data directory.</li> - <li>Create a `recovery.signal` file (Postgres 12+) or `recovery.conf` file (older versions) in the restored data directory.</li> - <li>Configure `restore_command` 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 `recovery_target_time`, `recovery_target_xid`, `recovery_target_name`, or `recovery_target = 'immediate'` (recover to end of WALs). Use `recovery_target_action = 'promote'` (PG12+) or `standby_mode = 'off'` (older) to bring the server up after reaching the target.</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> </ol> <h6>Benefits</h6> @@ -1553,7 +1565,7 @@ $$ LANGUAGE plpgsql;</code></pre> <li>Provides fine-grained disaster recovery capability.</li> </ul> <h6>Tools</h6> - <p>Tools like `pgBackRest` or `Barman` significantly simplify managing base backups, WAL archiving, and the PITR process, adding features like parallel backup/restore, incremental backups, retention policies, and validation.</p> + <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> </div> </div> </div> @@ -1567,20 +1579,20 @@ $$ LANGUAGE plpgsql;</code></pre> <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., `scram-sha-256`, `md5`, `peer`, `ldap`).</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"><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> <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> <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:</p> + <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: + <li><strong>`method`</strong>: 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>`reject`: Deny connection unconditionally.</li> @@ -1604,36 +1616,36 @@ $$ LANGUAGE plpgsql;</code></pre> <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">Role</span> concept. Use `GRANT`/`REVOKE` for privileges. Set `DEFAULT PRIVILEGES`. Row-Level Security (RLS).</p> + <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> <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> <ul> - <li>Combine users and groups: `CREATE ROLE name [WITH options];`</li> - <li>Options: `LOGIN`/`NOLOGIN`, `SUPERUSER`/`NOSUPERUSER`, `CREATEDB`/`NOCREATEDB`, `CREATEROLE`/`NOCREATEROLE`, `INHERIT`/`NOINHERIT`, `REPLICATION`/`NOREPLICATION`, `BYPASSRLS`/`NOBYPASSRLS`, `CONNECTION LIMIT n`, `PASSWORD 'pass'`, `VALID UNTIL '...`, `IN ROLE role1, ...`, `ROLE role1, ...`, `ADMIN role1, ...`.</li> - <li>Use `ALTER ROLE ...` to modify, `DROP ROLE ...` to remove.</li> + <li>Combine users and groups: <span class="term"><a href="https://www.postgresql.org/docs/current/sql-createrole.html" target="_blank">`CREATE ROLE name [WITH options];`</a></span> <a href="https://www.postgresql.org/docs/current/database-roles.html" target="_blank">[docs]</a></li> + <li>Options: `LOGIN`/`NOLOGIN`, `SUPERUSER`/`NOSUPERUSER`, `CREATEDB`/`NOCREATEDB`, `CREATEROLE`/`NOCREATEROLE`, `INHERIT`/`NOINHERIT`, `REPLICATION`/`NOREPLICATION`, `BYPASSRLS`/`NOBYPASSRLS`, `CONNECTION LIMIT n`, `PASSWORD 'pass'`, `VALID UNTIL '...`, `IN ROLE role1, ...`, `ROLE role1, ...`, `ADMIN role1, ...`. <a href="https://www.postgresql.org/docs/current/role-attributes.html" target="_blank">[attributes]</a></li> + <li>Use <span class="term"><a href="https://www.postgresql.org/docs/current/sql-alterrole.html" target="_blank">`ALTER ROLE ...`</a></span> to modify, <span class="term"><a href="https://www.postgresql.org/docs/current/sql-droprole.html" target="_blank">`DROP ROLE ...`</a></span> to remove.</li> </ul> <h6>Privileges (`GRANT`/`REVOKE`)</h6> <ul> - <li>Control access to database objects.</li> - <li>Syntax: `GRANT {privilege [, ...]|ALL} ON {object_type|ALL TABLES IN SCHEMA ...} object_name TO {role_name|PUBLIC};`</li> + <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>Privileges: `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `TRUNCATE`, `REFERENCES`, `TRIGGER`, `CREATE`, `CONNECT`, `TEMPORARY`, `EXECUTE`, `USAGE`.</li> - <li>`PUBLIC` pseudo-role grants to all roles. Use sparingly.</li> - <li>Use `REVOKE` to remove privileges.</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> </ul> <h6>Default Privileges</h6> <ul> - <li>`ALTER DEFAULT PRIVILEGES [FOR ROLE creator] [IN SCHEMA schema] GRANT ... ON TABLES TO target_role;`</li> + <li><span class="term"><a href="https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html" target="_blank">`ALTER DEFAULT PRIVILEGES [FOR ROLE creator] [IN SCHEMA schema] GRANT ... ON TABLES TO target_role;`</a></span></li> <li>Automatically apply specified privileges to objects created *in the future* by the specified `creator` role (or current role if omitted) within the specified schema (or current db if omitted).</li> <li>Essential for ensuring application roles automatically get permissions on new tables created by migration tools/roles.</li> </ul> <h6>Row-Level Security (RLS)</h6> <ul> - <li>Define policies (`CREATE POLICY ...`) that restrict which *rows* a user can view or modify within a table, based on user characteristics or data values.</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 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 `ALTER TABLE ... ENABLE ROW LEVEL SECURITY;`.</li> - <li>Roles need `BYPASSRLS` attribute to ignore policies (use with extreme care).</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> </ul> </div> </div> @@ -1641,29 +1653,30 @@ $$ 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 (`ssl=on`, certs) and enforce via `pg_hba.conf` (`hostssl`).</p> + <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> <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> <p>Protect data in transit between clients and the database server from eavesdropping and tampering, especially over untrusted networks.</p> <h6>Server Configuration (`postgresql.conf`)</h6> <ul> - <li>`ssl = on`: Enable SSL/TLS support.</li> - <li>`ssl_cert_file = 'server.crt'`: Path to the server's certificate file.</li> - <li>`ssl_key_file = 'server.key'`: Path to the server's private key file. Permissions must be restrictive (e.g., `0600`).</li> - <li>`ssl_ca_file = 'root.crt'`: (Optional) Path to trusted Certificate Authority certs for verifying client certificates.</li> - <li>`ssl_ciphers = 'HIGH:!aNULL'`: (Optional) Configure allowed cipher suites.</li> + <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> </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.</li> + <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>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> </ul> <h6>Client Configuration</h6> <ul> - <li>Clients specify connection parameters to control SSL usage.</li> - <li>Connection String/Libpq: `sslmode` parameter (`disable`, `allow`, `prefer`, `require`, `verify-ca`, `verify-full`). + <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> @@ -1671,7 +1684,7 @@ $$ LANGUAGE plpgsql;</code></pre> <li>Other parameters: `sslcert`, `sslkey`, `sslrootcert`.</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`).</p> + <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> </div> </div> </div> @@ -1685,12 +1698,12 @@ $$ LANGUAGE plpgsql;</code></pre> <div class="col-lg-6 col-md-6"> <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 (`\?`, `\d`, `\l`, `\timing`, `\e`, `\copy`, etc.) for efficiency.</p> + <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> <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 `\`)</h6> + <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> <ul> <li><strong>Help & Info:</strong> <ul> @@ -1704,7 +1717,7 @@ $$ LANGUAGE plpgsql;</code></pre> <li>`\q`: Quit psql.</li> </ul> </li> - <li><strong>Object Inspection (`\d` family):</strong> + <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> @@ -1739,47 +1752,47 @@ $$ LANGUAGE plpgsql;</code></pre> </li> <li><strong>Data Import/Export:</strong> <ul> - <li>`\copy table FROM 'file' [options]`: Client-side copy (psql reads file).</li> + <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 `COPY` for server-side file access).</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> </ul> <h6>Customization</h6> - <p>`.psqlrc` 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, aliases (`\set`), prompts.</p> </div> </div> </div> <div class="col-lg-6 col-md-6"> <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 `pgAdmin` (open source), DBeaver (universal), DataGrip (commercial).</p> + <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> <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>pgAdmin:</strong> + <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>DBeaver:</strong> + <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> - <li><strong>DataGrip (JetBrains):</strong> + <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> <li><strong>Other Tools:</strong> <ul><li>psql (already covered - powerful CLI)</li> - <li>Navicat for PostgreSQL (Commercial)</li> - <li>Postico (macOS native, Commercial)</li> - <li>TablePlus (macOS/Windows/Linux native, Commercial/Free limited)</li> - <li>SQL Developer (Oracle tool, supports Postgres via extension)</li></ul> + <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> </ul> <h6>Choosing a Tool</h6> @@ -1797,14 +1810,14 @@ $$ LANGUAGE plpgsql;</code></pre> <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!</p> + <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> <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> <ul> <li>If you create a table like `CREATE TABLE MyUsers (...)`, PostgreSQL stores it as `myusers`. Subsequent queries like `SELECT * FROM MyUsers;` or `SELECT * FROM myusers;` will both work because the unquoted identifier in the query is also folded to lowercase.</li> <li>If you create a table using double quotes `CREATE TABLE "MyUsers" (...)`, PostgreSQL stores the name exactly as `MyUsers` (preserving case).</li> - <li>To query this case-sensitive table, you *must* use double quotes: `SELECT * FROM "MyUsers";`. Querying `SELECT * FROM MyUsers;` or `SELECT * FROM myusers;` will fail (unless a lowercase table `myusers` also happens to exist).</li> + <li>To query this case-sensitive table, you *must* use double quotes: `SELECT * FROM "MyUsers";`. Querying `SELECT * FROM MyUsers;` or `SELECT * FROM myusers;` will fail (unless a lowercase table `myusers` also happens to exist). <a href="https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS" target="_blank">[docs]</a></li> </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> @@ -1816,22 +1829,22 @@ $$ LANGUAGE plpgsql;</code></pre> <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">bloat</span>). Regular `VACUUM` (usually via <span class="term">Autovacuum</span>) is mandatory for performance and TXID wraparound prevention.</p> + <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> <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.</p> + <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> <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>TXID Wraparound Failure:</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><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> </ul> <h6>Action Required</h6> <ul> - <li>Ensure Autovacuum is enabled (`autovacuum = on`).</li> - <li>**Monitor Autovacuum:** Check logs (`log_autovacuum_min_duration`), `pg_stat_user_tables` (last vacuum times, dead tuples).</li> - <li>**Tune Autovacuum:** Adjust settings (`*_scale_factor`, `*_threshold`, `*_cost_delay`, `*_cost_limit`, `max_workers`) based on table size, update frequency, and system resources. Defaults are often insufficient for active databases.</li> + <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> </ul> </div> @@ -1840,21 +1853,21 @@ $$ LANGUAGE plpgsql;</code></pre> <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 (PgBouncer, Pgpool-II) highly recommended for apps with many connections.</p> + <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> <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.</p> + <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> <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.</p> + <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> <ul> - <li><strong>PgBouncer:</strong> Lightweight, single-purpose connection pooler. Very popular, stable, low overhead. Offers different pooling modes: + <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> - <li><strong>Pgpool-II:</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><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> </ul> <h6>Benefits of Pooling</h6> <ul> @@ -1870,30 +1883,30 @@ $$ LANGUAGE plpgsql;</code></pre> <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: `REVOKE` create, use dedicated schemas.</p> + <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> <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`.</li> - <li>By default, the special `PUBLIC` 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>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 `search_path` where they have `CREATE` permission, which is typically `public`.</li> + <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>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).</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> </ul> <h6>Recommended Practice</h6> <ol> <li>**Revoke Default Privileges:** As a superuser, immediately after database creation: - <pre><code>REVOKE CREATE ON SCHEMA public FROM PUBLIC; + <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> - <li>**Create Dedicated Schemas:** Use schemas to organize objects logically (e.g., `CREATE SCHEMA app_schema;`, `CREATE SCHEMA reporting;`).</li> - <li>**Grant Permissions Explicitly:** Grant necessary `USAGE` and object-specific privileges (SELECT, INSERT, etc.) on dedicated schemas/tables to specific application roles.</li> - <li>**Set `search_path`:** Configure the `search_path` 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;`, `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> </ol> </div> </div> @@ -1901,11 +1914,11 @@ $$ 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.</p> + <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> <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 `search_path` setting in order, using the first matching object found.</p> + <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> <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> <h6>Setting the Path</h6> @@ -1914,15 +1927,16 @@ $$ LANGUAGE plpgsql;</code></pre> <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><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> <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.</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 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> </ul> <h6>Best Practice</h6> <ul> - <li>Always be aware of the current `search_path` (`SHOW search_path;`).</li> + <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>Follow best practices for the `public` schema (revoke default create privileges).</li> @@ -1933,19 +1947,19 @@ $$ 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">Role</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 and groups. 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.</p> + <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> <ul> - <li>A role that can log in has the `LOGIN` attribute (`CREATE ROLE myuser WITH LOGIN PASSWORD '...';` is equivalent to `CREATE USER ...`).</li> + <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> </ul> <h6>Membership and Inheritance</h6> <ul> - <li>Roles can be members of other roles: `GRANT group_role TO user_role;`.</li> - <li>By default (`INHERIT` attribute), a role automatically inherits all privileges granted directly to the roles it is a member of.</li> + <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> </ul> <h6>Benefits</h6> @@ -1956,7 +1970,7 @@ $$ LANGUAGE plpgsql;</code></pre> <h6>Managing Membership</h6> <ul> <li>`GRANT group_role TO user_role;`</li> - <li>`REVOKE group_role FROM 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> </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> @@ -1969,7 +1983,8 @@ $$ LANGUAGE plpgsql;</code></pre> </div> <!-- /container --> -<footer class="container text-center"> +<footer class="container text-center"> + <!-- Updated Year --> <p>© 2025 David Veksler</p> </footer>