PostgreSQL SME Cookbook

Sections

PostgreSQL Core & SQL

PostgreSQL Basics Postgres Architecture System Catalogs Database, Schema & Search Path Versioning & Upgrade Policy PostgreSQL Fundamentals Best Practices

SQL Querying Basics Aggregates & GROUP BY Subqueries & EXISTS UNION, INTERSECT, EXCEPT NULL Semantics SQL Querying Best Practices

Data Types Basics Numeric & Text Types Date, Time & Timestamptz UUID, Enum & Domain Types Arrays & Composite Types Data Types Best Practices

Schema Design Basics Primary & Foreign Keys CHECK & UNIQUE Constraints Generated & Identity Columns DDL Transactions Schema Design Best Practices

Indexes Basics B-tree Indexes Partial & Covering Indexes GIN & GiST Indexes Best Practices

Transactions Basics Isolation Levels MVCC Visibility Row-Level Locks Deadlocks Concurrency Best Practices

Advanced SQL Basics Common Table Expressions Window Functions Recursive CTEs Advanced SQL Best Practices

Functions Basics PL/pgSQL Essentials Stored Procedures & CALL Volatility & STRICT Functions Best Practices

Triggers Basics Transition Tables Audit Triggers Triggers Best Practices

Tooling, Clients & Schema Workflow

Installation Basics initdb & Data Directory pg_hba.conf & Authentication Systemd & Service Management Installation Best Practices

psql Basics psql Scripting pgAdmin & DBeaver psql Best Practices

Roles Basics GRANT/REVOKE Patterns Default Privileges Row-Level Security Permissions Best Practices

Migrations Basics Flyway & Liquibase Expand/Contract Pattern Migrations Best Practices

Postgres Project Rules Checklist Naming & Style Rules Index & Query Rules Migration Safety Rules Security Rules PostgreSQL Rules Best Practices

Data Modeling & Architecture

Data Modeling Basics Entity-Relationship Modeling Domain-Driven Schema Boundaries Data Modeling Best Practices

Normalization Basics 3NF & BCNF Intentional Denormalization Denormalization Best Practices

Partitioning Basics Declarative Partitioning DDL Partition Pruning Partitioning Best Practices

Multi-Tenant Basics Row-Level Security Tenancy Multi-Tenant Best Practices

Defect Scenarios Basics Fan-Out Join Explosion Missing Foreign Keys Defect Scenarios Best Practices

Performance & Query Tuning

EXPLAIN Basics Nested Loop, Hash Join, Merge Join Sort & Hash Aggregates EXPLAIN Best Practices

Statistics Basics ANALYZE & Autoanalyze Statistics Best Practices

Index Design Basics Multicolumn Index Order Duplicate & Redundant Indexes Index Maintenance Best Practices

Vacuum Basics Transaction ID Wraparound Vacuum Best Practices

Pooling Basics PgBouncer Modes Pooling Best Practices

Workload Basics OLTP/OLAP Best Practices

Replication, HA & Backup

Streaming Replication Basics Synchronous vs Asynchronous Replication Slots Streaming Replication Best Practices

Logical Replication Basics Publications & Subscriptions Logical Replication Best Practices

HA Basics Patroni & etcd/Consul Connection Proxies HA Best Practices

Backup Basics Object-Level Backup Backup Best Practices

PITR Basics archive_mode & archive_command Recovery Target Options PITR Best Practices

DR Basics Cross-Region Replicas DR Best Practices

Extensions & Specialized Workloads

Extensions Basics Trusted vs Untrusted Extensions Extension Upgrade Path Extensions Best Practices

Full-Text Search Basics GIN Indexes for FTS Full-Text Search Best Practices

pgvector Basics IVFFlat vs HNSW Indexes RAG Storage Schema pgvector Best Practices

PostGIS Basics Geometry vs Geography Spatial Indexes PostGIS Best Practices

Time-Series Basics Time-Series Best Practices

Security, Operations & Cloud

Security Basics SSL/TLS Configuration SCRAM Authentication pgaudit & Compliance Logging Security Best Practices

Monitoring Basics pg_stat_statements postgres_exporter & Grafana Monitoring Best Practices

Logging Basics csvlog & JSON Logs Logging Best Practices

Managed Postgres Basics Google Cloud SQL & AlloyDB Managed Postgres Best Practices

Capacity Basics Connection Math Storage Growth Forecasting Capacity Planning Best Practices

Ecosystem & Team

Tools Basics pgBackRest pgbadger Essential Tools Best Practices

Agent Skills Basics EXPLAIN Review Skill Migration Safety Skill Failover Drill Skill Agent Skills Best Practices

Git for Database Work Basics Code Review for SQL Git Best Practices

Linux CLI for DBAs Basics IO & Memory Tools CLI Best Practices

Onboarding Basics App Dev → DBA Path Skills Matrix Team & Onboarding Best Practices

Tech-Lead Craft

Incident Basics Incident Response Best Practices

Change Management Basics Maintenance Windows Change Management Best Practices

Tech Lead Basics on Postgres Query Review Standards Mentoring App Teams Technical Leadership Best Practices

Stakeholder Basics Data Retention & Legal Holds Working with Engineering Best Practices

Reference: Multi-Tenant SaaS Reference: Analytics Replica Topology Reference: pgvector RAG Store Case Studies Best Practices

Governance Basics Naming & Documentation Standards Governance Best Practices

/

/

© 2026 @csjcode|SystemsArchitect.io: Cloud Systems Tools|Terms

Search Documentation

Search across all documentation pages

PostgreSQL SME Cookbook

From MVCC and EXPLAIN through replication, Patroni, pgvector, PostGIS, managed cloud Postgres, and incident response - what every PostgreSQL specialist should know.

6 pages

PostgreSQL Basics, Postgres Architecture, System Catalogs, Database, Schema & Search Path, Versioning & Upgrade Policy, PostgreSQL Fundamentals Best Practices

6 pages

SQL Querying Basics, Aggregates & GROUP BY, Subqueries & EXISTS, UNION, INTERSECT, EXCEPT, NULL Semantics, SQL Querying Best Practices

Data Types & JSONB

6 pages

Data Types Basics, Numeric & Text Types, Date, Time & Timestamptz, UUID, Enum & Domain Types, Arrays & Composite Types, Data Types Best Practices

6 pages

Schema Design Basics, Primary & Foreign Keys, CHECK & UNIQUE Constraints, Generated & Identity Columns, DDL Transactions, Schema Design Best Practices

5 pages

Indexes Basics, B-tree Indexes, Partial & Covering Indexes, GIN & GiST, Indexes Best Practices

Transactions & MVCC

6 pages

Transactions Basics, Isolation Levels, MVCC Visibility, Row-Level Locks, Deadlocks, Concurrency Best Practices

5 pages

Advanced SQL Basics, Common Table Expressions, Window Functions, Recursive CTEs, Advanced SQL Best Practices

Functions & PL/pgSQL

5 pages

Functions Basics, PL/pgSQL Essentials, Stored Procedures & CALL, Volatility & STRICT, Functions Best Practices

Triggers & Rules

4 pages

Triggers Basics, Transition Tables, Audit Triggers, Triggers Best Practices

5 pages

Installation Basics, initdb & Data Directory, pg_hba.conf & Authentication, Systemd & Service Management, Installation Best Practices

4 pages

psql Basics, psql Scripting, pgAdmin & DBeaver, psql Best Practices

5 pages

Roles Basics, GRANT/REVOKE Patterns, Default Privileges, Row-Level Security, Permissions Best Practices

4 pages

Migrations Basics, Flyway & Liquibase, Expand/Contract Pattern, Migrations Best Practices

PostgreSQL Rules

6 pages

Postgres Project Rules Checklist, Naming & Style Rules, Index & Query Rules, Migration Safety Rules, Security Rules, PostgreSQL Rules Best Practices

4 pages

Data Modeling Basics, Entity-Relationship Modeling, Domain-Driven Schema Boundaries, Data Modeling Best Practices

4 pages

Normalization Basics, 3NF & BCNF, Intentional Denormalization, Denormalization Best Practices

4 pages

Partitioning Basics, Declarative Partitioning DDL, Partition Pruning, Partitioning Best Practices

3 pages

Multi-Tenant Basics, Row-Level Security Tenancy, Multi-Tenant Best Practices

Defect Scenarios

4 pages

Defect Scenarios Basics, Fan-Out Join Explosion, Missing Foreign Keys, Defect Scenarios Best Practices

4 pages

EXPLAIN Basics, Nested Loop, Hash Join, Merge Join, Sort & Hash Aggregates, EXPLAIN Best Practices

3 pages

Statistics Basics, ANALYZE & Autoanalyze, Statistics Best Practices

4 pages

Index Design Basics, Multicolumn Index Order, Duplicate & Redundant Indexes, Index Maintenance Best Practices

3 pages

Vacuum Basics, Transaction ID Wraparound, Vacuum Best Practices

Connection Pooling

3 pages

Pooling Basics, PgBouncer Modes, Pooling Best Practices

2 pages

Workload Basics, OLTP/OLAP Best Practices

Streaming Replication

4 pages

Streaming Replication Basics, Synchronous vs Asynchronous, Replication Slots, Streaming Replication Best Practices

Logical Replication

3 pages

Logical Replication Basics, Publications & Subscriptions, Logical Replication Best Practices

4 pages

HA Basics, Patroni & etcd/Consul, Connection Proxies, HA Best Practices

Backup & Restore

3 pages

Backup Basics, Object-Level Backup, Backup Best Practices

4 pages

PITR Basics, archive_mode & archive_command, Recovery Target Options, PITR Best Practices

Disaster Recovery

3 pages

DR Basics, Cross-Region Replicas, DR Best Practices

4 pages

Extensions Basics, Trusted vs Untrusted Extensions, Extension Upgrade Path, Extensions Best Practices

Full-Text Search

3 pages

Full-Text Search Basics, GIN Indexes for FTS, Full-Text Search Best Practices

4 pages

pgvector Basics, IVFFlat vs HNSW Indexes, RAG Storage Schema, pgvector Best Practices

4 pages

PostGIS Basics, Geometry vs Geography, Spatial Indexes, PostGIS Best Practices

2 pages

Time-Series Basics, Time-Series Best Practices

5 pages

Security Basics, SSL/TLS Configuration, SCRAM Authentication, pgaudit & Compliance Logging, Security Best Practices

4 pages

Monitoring Basics, pg_stat_statements, postgres_exporter & Grafana, Monitoring Best Practices

Logging & Audit

3 pages

Logging Basics, csvlog & JSON Logs, Logging Best Practices

Managed & Cloud

3 pages

Managed Postgres Basics, Google Cloud SQL & AlloyDB, Managed Postgres Best Practices

Capacity Planning

4 pages

Capacity Basics, Connection Math, Storage Growth Forecasting, Capacity Planning Best Practices

Essential Tools

4 pages

Tools Basics, pgBackRest, pgbadger, Essential Tools Best Practices

5 pages

Agent Skills Basics, EXPLAIN Review Skill, Migration Safety Skill, Failover Drill Skill, Agent Skills Best Practices

3 pages

Git for Database Work Basics, Code Review for SQL, Git Best Practices

3 pages

Linux CLI for DBAs Basics, IO & Memory Tools, CLI Best Practices

Team & Onboarding

4 pages

Onboarding Basics, App Dev → DBA Path, Skills Matrix, Team & Onboarding Best Practices

Incident Response

2 pages

Incident Basics, Incident Response Best Practices

Change Management

3 pages

Change Management Basics, Maintenance Windows, Change Management Best Practices

Technical Leadership

4 pages

Tech Lead Basics on Postgres, Query Review Standards, Mentoring App Teams, Technical Leadership Best Practices

3 pages

Stakeholder Basics, Data Retention & Legal Holds, Working with Engineering Best Practices

4 pages

Reference: Multi-Tenant SaaS, Reference: Analytics Replica Topology, Reference: pgvector RAG Store, Case Studies Best Practices

3 pages

Governance Basics, Naming & Documentation Standards, Governance Best Practices