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/Testing Database Functions

#Testing Database Functions1 page

Pages tagged Testing Database Functions across 1 section.

Functions & PL/pgSQL

Testing Database Functions