React
React Native
Node.js
Python
Rust
PostgreSQL
Solana
Claude
AI Agents
Go

PostgreSQL SME Cookbook

Sections

PostgreSQL Core & SQL

PostgreSQL Foundations PostgreSQL Basics Postgres Architecture System Catalogs Database, Schema & Search Path Versioning & Upgrade Policy PostgreSQL vs MySQL vs SQLite When to Use Postgres / When Not To PostgreSQL Fundamentals Best Practices

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

PostgreSQL Data Types In Depth Data Types Basics Numeric & Text Types Date, Time & Timestamptz UUID, Enum & Domain Types Arrays & Composite Types JSONB Operators & Indexing Data Types Best Practices

The Schema & Constraints Blueprint Schema Design Basics Primary & Foreign Keys CHECK & UNIQUE Constraints Generated & Identity Columns DDL Transactions Soft Deletes vs Hard Deletes Schema Design Best Practices

How Indexes Work Indexes Basics B-tree Indexes Partial & Covering Indexes GIN & GiST BRIN Index-Only Scans Indexes Best Practices

MVCC Key Points Transactions Basics Isolation Levels MVCC Visibility Row-Level Locks Deadlocks Serialization Anomalies Long Transactions Concurrency Best Practices

Advanced SQL Demystified Advanced SQL Basics Common Table Expressions Window Functions Recursive CTEs LATERAL Joins Pivot & Crosstab Patterns Advanced SQL Best Practices

PL/pgSQL Explained Functions Basics PL/pgSQL Essentials Stored Procedures & CALL Volatility & STRICT SECURITY DEFINER Risks Testing Database Functions Functions Best Practices

The Trigger Model Triggers Basics Transition Tables Audit Triggers When Not to Use Triggers Rules (Legacy)Triggers Best Practices

Tooling, Clients & Schema Workflow

The PostgreSQL Cluster Installation Basics initdb & Data Directory postgresql.conf Essentials pg_hba.conf & Authentication Systemd & Service Management Installation Best Practices

The psql Client psql Basics psql Scripting pgAdmin & DBeaver COPY & Bulk Load Client Encoding & Locale psql Best Practices

Roles & RLS In Depth Roles Basics GRANT/REVOKE Patterns Default Privileges Row-Level Security Bypass RLS & Superuser RLS Performance Permissions Best Practices

The Schema Change Blueprint Migrations Basics Flyway & Liquibase Expand/Contract Pattern Zero-Downtime DDL Rollback Strategy Large Table Migrations Migrations Best Practices

PostgreSQL Rules Overview Postgres Project Rules Checklist Naming & Style Rules Index & Query Rules Migration Safety Rules Security Rules ADR Template for Postgres PostgreSQL Rules Best Practices

Data Modeling & Architecture

Data Modeling Foundations Data Modeling Basics Entity-Relationship Modeling Domain-Driven Schema Boundaries ADR: Single Database vs Per-Service DB Schema Evolution Strategy Data Modeling Best Practices

Normalization vs Denormalization Normalization Basics 3NF & BCNF Intentional Denormalization Materialized Views Denormalization Best Practices

The Partitioning Model Partitioning Basics Declarative Partitioning DDL Partition Pruning Retention & Detach Partitioning Pitfalls Partitioning Best Practices

Multi-Tenant Patterns Explained Multi-Tenant Basics Shared Schema + tenant_id Row-Level Security Tenancy Schema-per-Tenant Database-per-Tenant Multi-Tenant Best Practices

Modeling Defect Scenarios Key Points Defect Scenarios Basics Fan-Out Join Explosion Missing Foreign Keys Enum Migration Pain Timestamp Without Timezone Bugs Defect Scenarios Best Practices

Performance & Query Tuning

How EXPLAIN Works EXPLAIN Basics Seq Scan vs Index Scan Nested Loop, Hash Join, Merge Join Sort & Hash Aggregates Bad Plan Case Studies EXPLAIN Best Practices

The Query Planner Statistics Basics ANALYZE & Autoanalyze Extended Statistics Planner GUCs Statistics Best Practices

Index Design In Detail Index Design Basics Multicolumn Index Order Duplicate & Redundant Indexes REINDEX & REINDEX CONCURRENTLY Index Maintenance Best Practices

Vacuum & Bloat Demystified Vacuum Basics Autovacuum Tuning Transaction ID Wraparound Table & Index Bloat TOAST & Large Values Vacuum Best Practices

The Connection Pooling Blueprint Pooling Basics PgBouncer Modes Prepared Statements & Pooling Role & Timeout per Pool Pooling Best Practices

OLTP vs OLAP Tuning Workload Basics Read Replicas for Reporting Parallel Query work_mem & sort/hash tuning OLTP/OLAP Best Practices

Replication, HA & Backup

Streaming Replication Explained Streaming Replication Basics Synchronous vs Asynchronous Replication Slots Replication Monitoring Cascading Replicas Streaming Replication Best Practices

The Logical Replication Model Logical Replication Basics Publications & Subscriptions Logical vs Physical Upgrade with Logical Replication Logical Replication Best Practices

HA & Failover Key Points HA Basics Patroni & etcd/Consul Connection Proxies Split-Brain Prevention HA Testing HA Best Practices

Backup & Restore Foundations Backup Basics pg_dump & pg_restore Object-Level Backup Backup Verification Backup Best Practices

Point-in-Time Recovery In Depth PITR Basics archive_mode & archive_command Recovery Target Options PITR Drill Runbook Cloud PITR PITR Best Practices

RTO & RPO Demystified DR Basics Cross-Region Replicas DR Runbook & Comms DR Game Days DR Best Practices

Extensions & Specialized Workloads

The Extension System Extensions Basics Trusted vs Untrusted Extensions Extension Upgrade Path Extension Allowlist Policy Extensions Best Practices

How Full-Text Search Works Full-Text Search Basics tsvector & tsquery GIN Indexes for FTS Hybrid Search Full-Text Search Best Practices

pgvector In Depth pgvector Basics IVFFlat vs HNSW Indexes RAG Storage Schema pgvector at Scale pgvector Best Practices

PostGIS Foundations PostGIS Basics Geometry vs Geography Spatial Indexes PostGIS Performance PostGIS Best Practices

Time-Series Patterns Explained Time-Series Basics TimescaleDB Continuous Aggregates OLAP Offload ADR Time-Series Best Practices

Security, Operations & Cloud

The Security Hardening Blueprint Security Basics SSL/TLS Configuration SCRAM Authentication pgaudit & Compliance Logging Encryption at Rest Security Best Practices

Monitoring & Metrics Key Points Monitoring Basics pg_stat_activity & Locks pg_stat_statements postgres_exporter & Grafana SLO Dashboards Monitoring Best Practices

Logging & Audit Trails Explained Logging Basics csvlog & JSON Logs Audit Triggers vs pgaudit Forensic Queries Logging Best Practices

Managed PostgreSQL Overview Managed Postgres Basics AWS RDS & Aurora Postgres Google Cloud SQL & AlloyDB Neon & Supabase Cloud Selection ADR Managed Postgres Best Practices

Capacity Planning In Depth Capacity Basics Connection Math Storage Growth Forecasting Scale-Up vs Scale-Out Sharding Last Resort Capacity Planning Best Practices

Ecosystem & Team

The PostgreSQL Toolchain Tools Basics pgBackRest pg_repack pgbadger pganalyze / OtterTune Essential Tools Best Practices

The Agent Skill Model Agent Skills Basics EXPLAIN Review Skill Migration Safety Skill Failover Drill Skill Incident Triage Skill Agent Skills Best Practices

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

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

Team Collaboration Key Points Onboarding Basics App Dev → DBA Path Skills Matrix Pairing on Query PRs Team & Onboarding Best Practices

Tech-Lead Craft

Troubleshooting Foundations Incident Basics Lock Storm Triage Replication Lag Emergency Disk Full on PGDATA Corruption & Index Invalidation Incident Response Best Practices

Enterprise Delivery Explained Change Management Basics Maintenance Windows Uptime SLOs for Datastores Rollback for Failed Migrations Change Management Best Practices

Tech Leadership Key Points Tech Lead Basics on Postgres Query Review Standards Mentoring App Teams Handling Index Requests Technical Leadership Best Practices

The Product Collaboration Blueprint Stakeholder Basics Data Retention & Legal Holds Cost Conversations Working with Engineering Best Practices

Case Studies In Depth Reference: Multi-Tenant SaaS Reference: Analytics Replica Topology Reference: pgvector RAG Store Before/After: Major Version Upgrade Before/After: Query Optimization Win Case Studies Best Practices

Governance Demystified Governance Basics Naming & Documentation Standards Extension & Plugin Governance Upgrade Calendar PostgreSQL Roadmap Tracking Career Growth: DBA → Staff Data Engineer Governance Best Practices

More

Ajuste de OLTP vs OLAP

Ajuste Fino de OLTP vs OLAP

/

/

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

An independent, unofficial educational resource. Not affiliated with, endorsed, or sponsored by the brand/trademark maintainers/owners.

Search Documentation

Search across all documentation pages

PostgreSQL SME/Tags

Tags

577 tags across the docs.

Sql313 List58 Explainer54 Advanced53 Best Practices52 Basics50 Postgresql12 Governance11 Migrations9 Postgresql Fundamentals9 Troubleshooting9 Data Types Jsonb8 Index8 Replication8 Sql Querying Essentials8 Transactions Mvcc Concurrency8 Advanced Sql7 Architecture7 Case Studies7 Functions Procedures Plpgsql7 Indexes Access Paths7 Migrations Schema Change7 Point In Time Recovery7 Postgresql Rules7 Roles Permissions Rls7 Schema Design Constraints7 Skills7 Streaming Replication7 Backup Restore6 Capacity Planning Scaling6 Data Modeling Architecture6 Enterprise Delivery6 Essential Postgresql Tools6 Explain Query Planning6 Full Text Search6 High Availability Failover6 Indexes6 Installation Cluster Basics6 Logical Replication6 Managed Cloud Postgresql6 Modeling Defect Scenarios6 Monitoring Metrics6 Multi Tenant Patterns6 Olap6 Partitioning Table Design6 Psql Client Tools6 Security Hardening Compliance6 Tech Leadership6 Triggers Rules Event Logic6 Vacuum Bloat Storage6 Adr5 Backup5 Cloud5 Compliance5 Connection Pooling Workload5 Disaster Recovery Rto Rpo5 Explain5 Extensions Overview5 Index Design Maintenance5 Logging Audit Trails5 Normalization Denormalization5 Oltp5 Oltp Vs Olap Tuning5 Performance5 Pgvector5 Pgvector Embeddings5 Postgis Geospatial5 Product Collaboration5 Query5 Security5 Statistics Planner Tuning5 Team5 Time Series Analytics5 Workload5 Audit4 Data4 Ddl4 Extensions4 Isolation4 Logging4 Logical4 Monitoring4 Mvcc4 Observability4 Partitioning4 Pitr4 Pooling4 Postgis4 Psql4 Replicas4 SaaS4 Schema Design4 Skill4 Standards4 Statistics4 Storage4 Triggers4 Tuning4 Upgrade4 Vacuum4 Aggregates3 Bloat3 CLI3 Concurrency3 Denormalization3 Extension3 Full3 Git3 Incident3 Indexing3 Installation3 Linux Cli3 Normalization3 Onboarding3 Parallel Query3 Pgbouncer3 Postgres3 Recovery3 Reference3 Review3 Rls3 Scaling3 Schema3 Tools3 Wal3 Access Control2 After2 Agent2 Anti Patterns2 App2 Archive2 Authentication2 Autovacuum2 Before2 Capacity2 Cardinality2 Case2 Change2 Collaboration2 Constraints2 Data Integrity2 Dba2 Decision Rights2 Declarative2 Drill2 Encryption2 Failover2 Forecasting2 Functions2 Grafana2 Hash2 Incident Response2 Join2 Large2 Lateral Joins2 Level2 Linux2 Maintenance2 Management2 Mentoring2 Migration2 Mistakes2 Modeling2 Naming2 Operations2 Path2 Patroni2 Pgaudit2 Policy2 Publications2 Query Planner2 Query Planning2 Rag2 Rds2 Reporting2 Retention2 Roles2 Rollback2 Row Level Security2 Rules2 Runbook2 Safety2 Search2 Series2 Sizing2 Sort2 Streaming2 Subscriptions2 Table2 Text2 Time2 Triage2 Tsvector2 Window Functions2 Work2 Workflow2 3nf1 Access Methods1 Agent Skills1 Aggregate1 Ai Workflows1 Alerting1 Allowlist1 Alloydb1 Analytics1 Analyze1 Ann1 Anomalies1 Antipatterns1 Arrays And Composite Types1 Asynchronous1 Aurora1 Autoanalyze1 Automation1 Aws1 B Tree1 B Tree Indexes1 Backup Tools1 Bad1 Bcnf1 Bounded Context1 Brain1 Brin1 Bulk1 Bypass1 Caching1 Calendar1 Capacity Planning1 Career1 Career Growth1 Cascading1 Catalogs1 Change Data Capture1 Change Management1 Check And Unique Constraints1 Checklist1 Citus1 Client1 Client Tools1 Cloud Sql1 Cluster1 Code1 Command1 Comms1 Comparison1 Conceptual Modeling1 Concurrently1 Connection1 Connection Pooling1 Connections1 Consistency1 Consul1 Continuous1 Contract1 Control Files1 Conventions1 Conversations1 Copy1 Corruption1 Cost1 Cost Based Optimization1 Cost Estimation1 Cross1 Csvlog1 Cte1 Ctes1 Data Modeling1 Database1 Database Per Tenant1 Datastores1 Date Time And Timestamptz1 Days1 Dba Toolkit1 Dbaas1 Dbas1 Dbeaver1 Ddd1 Ddl Transactions1 Deadlocks1 Decision1 Decision Provenance1 Default1 Delegation1 Dependencies1 Deployment1 Design1 Detach1 Dev1 Diagnostics1 Directory1 Disaster Recovery1 Disk1 Distributed1 Documentation1 Downsampling1 Downtime1 Dst1 Dump1 Duplicate1 Embeddings1 Emergency1 Encoding1 Engineering1 Enterprise1 Enum1 Erd1 Essential1 Essentials1 Etcd1 Event Logic1 Except1 Exists1 Expand1 Expand Contract1 Exporter1 Extended1 Extensibility1 Failed1 Flyway1 Foreign Keys1 Forensic1 Forensics1 Fts1 Game1 Gcp1 Generated And Identity Columns1 Geography1 Geometry1 Geospatial1 Gin1 Gin And Gist1 Gis1 Grant1 Group By1 Growth1 Guc1 Gucs1 Handling1 Hardening1 Having1 High Availability1 Hnsw1 Holds1 House Style1 Hybrid1 Incident Diagnosis1 Incidents1 Index Only Scans1 Initdb1 Inner1 Integrity1 Intersect1 Invalidation1 Investigation1 Iostat1 Isolation Levels1 Ivfflat1 Joins1 Json1 Jsonb1 Jsonb Operators And Indexing1 Lag1 Latency1 Ldap1 Lead1 Leadership1 Legacy1 Legal1 Lexemes1 Lint1 Liquibase1 Load1 Locale1 Lock1 Locks1 Long Transactions1 Loop1 Major1 Managed Database1 Materialized Views1 Matrix1 Mem1 Memory1 Metadata1 Metrics1 Microservices1 Mode1 Modes1 Multi1 Multi Tenancy1 Multicolumn1 Mvcc Visibility1 Mysql1 Namespace1 Neon1 Nested1 Null1 Numeric And Text Types1 Object1 Offload1 Options1 Orchestration1 Order1 Orphans1 Ottertune1 Outer1 Pairing1 Parallel1 Partial And Covering Indexes1 Pattern Transfer1 Per1 Permissions1 Pg Hba1 Pg Stat Activity1 Pg Stat Statements1 Pgadmin1 Pganalyze1 Pgbackrest1 Pgbadger1 Pgdata1 Physical1 Physical Replication1 Pii1 Pitfalls1 Pivot And Crosstab Patterns1 Pl Pgsql Essentials1 Plan1 Planner1 Planning1 Plpgsql1 Plugin1 Policies1 Postgresql Conf1 Postmortems1 Prepared1 Prevention1 Primary And Foreign Keys1 Privileges1 Procedures1 Project1 Prometheus1 Proxies1 Prs1 Pruning1 Query Optimization1 Query Performance1 Query Plans1 Query Tuning1 Quorum1 Ranking1 Read1 Recursive Ctes1 Recursive Queries1 Redundant1 Reference Architecture1 Referential Integrity1 Refresh1 Region1 Reindex1 Relationships1 Release Process1 Repack1 Replica1 Replication Topology1 Requests1 Resilience1 Response1 Restore1 Review Cadence1 Revoke1 Risk Translation1 Roadmap1 Role1 Row1 Row Level Locks1 Rpo1 Rto1 Scalability1 Scale1 Scan1 Schema Change1 Schema Defects1 Schema Per Tenant1 Schemas1 Scram1 Scripting1 Search Path1 Security Definer Risks1 Select1 Selection1 Semi Join1 Seq1 Serialization Anomalies1 Serverless1 Service1 Sharding1 Shared Schema1 Siem1 Similarity Search1 Slo1 Slos1 Slots1 Soft Deletes Vs Hard Deletes1 Spatial1 Spatial Index1 Split1 Sqlite1 Srid1 Ssl1 Stakeholder1 Stakeholder Communication1 Standby1 Statements1 Store1 Stored Procedures And Call1 Storm1 Strategy1 Studies1 Style1 Subquery1 Supabase1 Superuser1 Synchronous1 System1 Systemd1 Table Design1 Tables1 Target1 Tde1 Team Practices1 Teams1 Tech1 Technical1 Template1 Tenancy1 Tenant1 Tenant Id1 Testing1 Testing Database Functions1 Threat Model1 Three Valued Logic1 Time Series1 Timelines1 Timeout1 Timescaledb1 Timestamptz1 Timezone1 Tls1 Toast1 Tooling1 Topology1 Tracking1 Tradeoffs1 Transaction1 Transactions1 Transition1 Trusted1 Tsquery1 Types1 Union1 Untrusted1 Uptime1 Uuid Enum And Domain Types1 Values1 Vector Search1 Verification1 Version Control1 Versioning1 Vertical1 Views1 Volatility1 Volatility And Strict1 Wal Decoding1 Windows1 Working1 Wraparound1 Zero1