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/Schema Design

Schema Design

6 pages in this section.

1

Schema Design Basics

Surrogate vs natural keys and naming conventions.

Sql Schema Design Constraints Basics Schema

2

Primary & Foreign Keys

Referential integrity and ON DELETE actions.

Sql Schema Design Constraints Primary And Foreign Keys

3

CHECK & UNIQUE Constraints

Enforce rules close to data.

Sql Schema Design Constraints Check And Unique Constraints

4

Generated & Identity Columns

GENERATED ALWAYS AS IDENTITY patterns.

Sql Schema Design Constraints Generated And Identity Columns

5

DDL Transactions

What is atomic in Postgres DDL.

Sql Schema Design Constraints Ddl Transactions

6

Schema Design Best Practices

Constraints over app-only validation.

Sql Schema Design Constraints Best Practices List