PostgreSQL Database Architect

Purpose & When-To-Use

Primary trigger conditions:

• Designing new PostgreSQL database for production application
• Performance issues with existing PostgreSQL deployment (slow queries, high latency)
• Scaling PostgreSQL beyond 100GB or 10k QPS
• High availability requirement (99.9%+ uptime SLA)
• Migration to PostgreSQL from other databases (MySQL, Oracle, SQL Server)
• PostgreSQL version upgrade planning (to PostgreSQL 17)
• Cloud migration (AWS RDS/Aurora, GCP Cloud SQL, Azure Database for PostgreSQL)

When NOT to use this skill:

• Database-agnostic schema design → use database-schema-designer
• Query-level optimization only → use database-optimization-analyzer
• Data migration/ETL → use database-migration-generator
• NoSQL databases (MongoDB, Cassandra) → use database-mongodb-architect (future)

Value proposition: Optimizes PostgreSQL schema, indexes, and configuration for 2-10x performance improvement. PostgreSQL 17 delivers 2x write throughput in high-concurrency workloads and 20x vacuum memory reduction compared to PostgreSQL 16 (PostgreSQL.org 2025).

Pre-Checks

Required inputs validation:

NOW_ET = "2025-10-26T18:30:00-04:00"

assert workload_type in ["oltp", "olap", "htap", "time-series"], "Valid workload type required"
assert data_volume_estimate is not None, "Data volume estimate required (GB or row count)"
assert queries_per_second_target > 0, "QPS target required"

# Version check
if postgresql_version < 17:
    warn("PostgreSQL 17+ recommended for vectored I/O and improved parallelism")

# Cloud vs self-hosted
if deployment_environment in ["aws-rds", "gcp-cloudsql", "azure-postgres"]:
    note("Managed service constraints apply (limited postgresql.conf access)")

Authority checks:

• Read access to existing schema (if analyzing existing DB)
• PostgreSQL EXPLAIN permissions for query analysis
• superuser or elevated privileges for configuration recommendations (not required for read-only analysis)

Source citations (accessed 2025-10-26T18:30:00-04:00):

• PostgreSQL 17 Performance Improvements: https://www.pgedge.com/blog/postgresql-17-a-major-step-forward-in-performance-logical-replication-and-more
• PostgreSQL Tuning Best Practices 2025: https://www.mydbops.com/blog/postgresql-parameter-tuning-best-practices
• PostgreSQL Indexing Strategies: https://www.freecodecamp.org/news/postgresql-indexing-strategies/
• PostgreSQL Documentation: https://www.postgresql.org/docs/current/

Procedure

Tier 1 (≤2k tokens): Quick PostgreSQL Health Check

Goal: Identify top 3 performance bottlenecks and provide immediate tuning recommendations in <10 minutes.

Steps:

1. Analyze workload characteristics

   • OLTP (high concurrency, small transactions) → prioritize indexing, connection pooling
   • OLAP (analytical queries, large scans) → prioritize parallelism, partitioning
   • HTAP (hybrid) → balance both strategies
   • Time-series (append-heavy, time-based queries) → prioritize BRIN indexes, time-based partitioning

2. Quick schema review

   • Identify missing indexes on foreign keys (common oversight)
   • Check for tables >1M rows without partitioning
   • Flag SERIAL vs IDENTITY (prefer IDENTITY in PG 10+)
   • Detect overuse of TEXT when VARCHAR(n) or specific types appropriate

3. Memory configuration quick scan

   • shared_buffers: Should be 25-40% of total RAM (accessed 2025-10-26T18:30:00-04:00)
     • If <25% → recommend increase (reduces disk I/O)
     • If >40% → may cause OS cache duplication, recommend 25-40% range
   • effective_cache_size: Should be 50-75% of total RAM
     • Used by query planner to estimate index vs seq scan cost
   • work_mem: Should be 4-16MB per connection
     • Too low → disk-based sorts (slow)
     • Too high → memory exhaustion with many connections

4. Output quick wins (top 3 highest-impact fixes)

   • Example: "Add B-tree index on users(email) for login queries → 50x speedup"
   • Example: "Increase shared_buffers from 128MB to 8GB (25% of 32GB RAM) → reduce disk reads 60%"
   • Example: "Partition orders table by created_at (monthly) → reduce scan time from 45s to 2s"

Token budget checkpoint: ~1.8k tokens for workload analysis, schema scan, config review, quick wins output.

Tier 2 (≤6k tokens): Comprehensive PostgreSQL Architecture Design

Goal: Generate production-ready PostgreSQL architecture with optimized schema, indexes, partitioning, and configuration.

Extends T1 with:

5. Detailed schema design

   Normalization strategy:

   • 3NF (Third Normal Form): Default for OLTP workloads requiring data consistency
   • Denormalization: Strategic for read-heavy workloads (e.g., pre-join user + profile into single table)
   • Materialized views: For complex aggregations (refresh strategies: CONCURRENTLY or scheduled)

   Data type optimization:

   • Use SMALLINT (2 bytes) vs INTEGER (4 bytes) vs BIGINT (8 bytes) appropriately
   • Prefer TIMESTAMPTZ (timezone-aware) over TIMESTAMP for UTC storage
   • Use JSONB (binary JSON, indexable) over JSON (text-based)
   • Leverage UUID v4 for distributed ID generation or IDENTITY for centralized

   Constraints and validation:

   • PRIMARY KEY with appropriate type (BIGSERIAL for high-volume tables)
   • FOREIGN KEY with ON DELETE CASCADE/SET NULL (avoid orphans)
   • CHECK constraints for data validation
   • UNIQUE constraints with partial indexes for conditional uniqueness

6. Advanced indexing strategies

   Index types and use cases:

   Index Type	Use Case	Example
   B-tree (default)	Equality, range queries	CREATE INDEX idx_users_email ON users(email);
   Hash	Equality only (faster than B-tree for exact match)	CREATE INDEX idx_sessions_hash ON sessions USING HASH(session_id);
   GIN (Generalized Inverted)	Full-text search, JSONB, arrays	CREATE INDEX idx_posts_fts ON posts USING GIN(to_tsvector('english', content));
   GiST (Generalized Search Tree)	Geometric data, full-text, range types	CREATE INDEX idx_locations_gist ON locations USING GIST(coordinates);
   BRIN (Block Range Index)	Large tables with natural ordering (time-series)	CREATE INDEX idx_logs_brin ON logs USING BRIN(created_at);
   Partial	Index subset of rows	CREATE INDEX idx_active_users ON users(last_login) WHERE active = true;
   Covering (INCLUDE)	Include non-key columns	CREATE INDEX idx_orders_cover ON orders(user_id) INCLUDE (total_amount, status);

   Index maintenance:

   • REINDEX for bloated indexes (track with pg_stat_user_indexes)
   • CONCURRENTLY for zero-downtime index creation: CREATE INDEX CONCURRENTLY idx_name ON table(column);
   • Monitor index usage with pg_stat_user_indexes.idx_scan (drop unused indexes)

7. Table partitioning for scalability

   Partitioning strategies (PostgreSQL 10+ declarative partitioning):

   Range partitioning (most common, time-based):

   CREATE TABLE orders (
     id BIGSERIAL,
     user_id BIGINT,
     created_at TIMESTAMPTZ NOT NULL,
     total_amount NUMERIC(10,2)
   ) PARTITION BY RANGE (created_at);
   
   CREATE TABLE orders_2025_01 PARTITION OF orders
     FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
   CREATE TABLE orders_2025_02 PARTITION OF orders
     FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
   

   List partitioning (categorical data):

   CREATE TABLE users PARTITION BY LIST (country_code);
   CREATE TABLE users_us PARTITION OF users FOR VALUES IN ('US');
   CREATE TABLE users_eu PARTITION OF users FOR VALUES IN ('DE', 'FR', 'UK');
   

   Hash partitioning (distribute evenly when no natural partition key):

   CREATE TABLE sessions PARTITION BY HASH (session_id);
   CREATE TABLE sessions_0 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 0);
   CREATE TABLE sessions_1 PARTITION OF sessions FOR VALUES WITH (MODULUS 4, REMAINDER 1);
   

   Partition pruning (PostgreSQL 17 improvement):

   • Query planner automatically skips irrelevant partitions
   • Example: SELECT * FROM orders WHERE created_at >= '2025-10-01' only scans Oct 2025 partition
   • Monitor with EXPLAIN ANALYZE to verify pruning: look for "Partitions pruned: N"

8. Query optimization and EXPLAIN ANALYZE

   Reading EXPLAIN ANALYZE output:

   EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = 'user@example.com';
   

   Key metrics:

   • Seq Scan (sequential scan) → full table scan, usually bad for large tables
   • Index Scan → good, using index
   • Index Only Scan → best, all data from index (covering index)
   • Bitmap Heap Scan → combines multiple indexes
   • Parallel Seq Scan → multi-core scan (PostgreSQL 9.6+, improved in 17)

   Common optimization patterns:

   • Replace SELECT * with specific columns (especially with large TEXT/JSONB)
   • Use LIMIT to reduce result set size
   • Rewrite subqueries as JOINs or CTEs (Common Table Expressions)
   • Leverage LATERAL JOIN for row-dependent subqueries

9. PostgreSQL 17 specific optimizations

   Vectored I/O (accessed 2025-10-26T18:30:00-04:00):

   • Reduces I/O operations by batching multiple block reads
   • Configure io_combine_limit (default: 128kB)
   • 2x write throughput in high-concurrency workloads

   Vacuum performance:

   • 20x memory reduction in vacuum process
   • Configure autovacuum_work_mem (default: -1, inherits maintenance_work_mem)
   • Monitor vacuum with pg_stat_progress_vacuum

   Parallelism improvements:

   • Expanded parallelism for FULL OUTER JOIN and aggregates
   • Configure max_parallel_workers_per_gather (default: 2, recommend 4-8 for OLAP)
   • Configure max_parallel_workers (total parallel workers across queries)

   Incremental sort optimization:

   • Handles larger datasets with minimal memory
   • Automatically used when appropriate (no config needed)

10. Performance tuning configuration

    Memory parameters (postgresql.conf):

    # For 32GB RAM server (OLTP workload)
    shared_buffers = 8GB           # 25% of RAM
    effective_cache_size = 20GB    # 60% of RAM
    work_mem = 16MB                # per operation (scale down if many connections)
    maintenance_work_mem = 1GB     # for VACUUM, CREATE INDEX
    autovacuum_work_mem = 1GB      # vacuum memory
    

    Connection pooling:

    • Use PgBouncer or pgpool-II for connection pooling
    • PostgreSQL connections are heavy (each fork costs ~10MB memory)
    • Recommended: max_connections = 100 (or lower), pool at application layer

    Checkpointing:

    checkpoint_timeout = 15min     # reduce for faster crash recovery
    checkpoint_completion_target = 0.9  # spread writes over 90% of checkpoint interval
    

    Write-Ahead Log (WAL):

    wal_level = replica            # for streaming replication
    max_wal_size = 4GB             # allow larger WAL before checkpoint
    min_wal_size = 1GB
    

11. High availability architecture

    Streaming replication (synchronous vs asynchronous):

    • Asynchronous: Default, minimal performance impact, potential data loss on primary failure
    • Synchronous: Zero data loss, higher latency (wait for standby ACK)
    • Configure synchronous_commit = on and synchronous_standby_names

    Logical replication (PostgreSQL 10+):

    • Replicate specific tables/databases (not entire cluster)
    • Supports cross-version replication (e.g., PG 16 → PG 17)
    • Use for multi-region deployments or selective replication

    Failover strategies:

    • Automatic failover: Use Patroni + etcd/Consul for distributed consensus
    • Manual failover: pg_ctl promote on standby
    • Cloud-managed: AWS RDS Multi-AZ, GCP Cloud SQL HA, Azure Flexible Server HA

Authority sources (accessed 2025-10-26T18:30:00-04:00):

• PostgreSQL 17 Features: https://www.pgedge.com/blog/postgresql-17-a-major-step-forward-in-performance-logical-replication-and-more
• PostgreSQL Performance Tips: https://www.postgresql.org/docs/current/performance-tips.html
• PostgreSQL Tuning 2025: https://dbadataverse.com/tech/postgresql/2025/02/postgresql-configuration-parameters-best-practices-for-performance-tuning
• Index Types: https://www.postgresql.org/docs/current/indexes-types.html

Output: Complete PostgreSQL architecture including schema DDL, index definitions, partitioning strategy, postgresql.conf tuning parameters, and HA architecture diagram.

Token budget checkpoint: ~5.5k tokens (includes T1 + comprehensive schema design + indexing + partitioning + tuning).

T3: Enterprise PostgreSQL Architecture (≤12k tokens)

Goal: Advanced multi-region, disaster recovery, and large-scale PostgreSQL deployment for >1TB data or >100k QPS.

Extends T2 with:

12. Multi-region architecture

    • Cross-region read replicas: Reduce latency for global users
    • Geo-partitioning: Store EU users in EU region (GDPR compliance)
    • Conflict resolution: For multi-master setups (Postgres-BDR or Citus)

13. Sharding strategies

    • Citus extension: Horizontal scaling across multiple PostgreSQL nodes
    • Application-level sharding: Partition data by tenant ID or user ID
    • FDW (Foreign Data Wrappers): Access remote PostgreSQL databases transparently

14. Backup and disaster recovery

    • pg_basebackup: Physical backup (binary copy)
    • pg_dump/pg_restore: Logical backup (SQL dump)
    • Point-in-Time Recovery (PITR): Restore to specific timestamp using WAL archives
    • RPO/RTO targets: Design backup frequency and restore testing cadence

15. Monitoring and observability

    • pg_stat_statements: Track slow queries
    • pg_stat_activity: Monitor active connections and queries
    • pg_stat_bgwriter: Background writer statistics
    • Third-party tools: pgAdmin, Datadog, Prometheus + Grafana

16. Security hardening

    • SSL/TLS: Encrypt connections (ssl = on in postgresql.conf)
    • pg_hba.conf: Configure host-based authentication (restrict by IP, require SSL)
    • Row-level security (RLS): Fine-grained access control
    • Encryption at rest: Use LUKS or cloud-native encryption (AWS RDS encryption, GCP CMEK)

Authority sources (accessed 2025-10-26T18:30:00-04:00):

• PostgreSQL Replication: https://www.postgresql.org/docs/current/high-availability.html
• Backup and Recovery: https://www.postgresql.org/docs/current/backup.html
• Security: https://www.postgresql.org/docs/current/runtime-config-connection.html#RUNTIME-CONFIG-CONNECTION-SSL

Output: Full enterprise-grade PostgreSQL architecture including multi-region replication, sharding design, backup/DR plan, monitoring dashboard, and security configuration.

Token budget checkpoint: ~11k tokens (includes T1 + T2 + enterprise architecture).

Decision Rules

When to abort:

• PostgreSQL version not supported (<9.6 EOL) → recommend upgrade path
• Insufficient input data (no schema, no workload characteristics) → request data model and access patterns
• Contradictory requirements (e.g., "sub-millisecond latency" + "10TB OLAP queries") → clarify priorities

Ambiguity thresholds:

• Index selection: Only recommend index if >10% query acceleration (avoid index bloat)
• Partitioning threshold: Only partition tables >1M rows or >1GB size
• Replication: Require explicit availability SLA (99%, 99.9%, 99.99%) to recommend synchronous vs asynchronous

Prioritization logic:

1. Correctness first: Schema normalization for data integrity (unless denormalization justified)
2. Quick wins: Missing indexes on foreign keys (common 10-100x speedup)
3. Scalability: Partitioning for large tables before they become unmanageable
4. High availability: Replication setup only after performance baseline established

PostgreSQL principle application:

• "Optimize for reads": 80% of workloads are read-heavy, prioritize indexes and caching
• "Normalize then denormalize": Start with 3NF, selectively denormalize based on query patterns
• "Monitor before tuning": Use EXPLAIN ANALYZE and pg_stat_statements to identify actual bottlenecks

Output Contract

Schema (JSON):

{
  "schema_design": {
    "tables": [
      {
        "name": "users",
        "columns": [
          {"name": "id", "type": "BIGSERIAL", "constraints": ["PRIMARY KEY"]},
          {"name": "email", "type": "VARCHAR(255)", "constraints": ["NOT NULL", "UNIQUE"]},
          {"name": "created_at", "type": "TIMESTAMPTZ", "default": "NOW()"}
        ],
        "indexes": [
          {"name": "idx_users_email", "type": "B-tree", "columns": ["email"], "unique": true}
        ],
        "partitioning": null
      }
    ],
    "normalization_level": "3NF",
    "estimated_size_gb": 50
  },
  "index_recommendations": [
    {
      "table": "orders",
      "index_name": "idx_orders_user_created",
      "type": "B-tree",
      "columns": ["user_id", "created_at"],
      "rationale": "Covers 80% of queries filtering by user and time range",
      "estimated_speedup": "25x (seq scan 450ms → index scan 18ms)",
      "create_statement": "CREATE INDEX CONCURRENTLY idx_orders_user_created ON orders(user_id, created_at);"
    }
  ],
  "partitioning_strategy": {
    "table": "orders",
    "partition_by": "RANGE",
    "partition_key": "created_at",
    "partition_interval": "monthly",
    "retention_policy": "drop partitions older than 2 years",
    "estimated_query_speedup": "20x (table scan 45s → partition scan 2s)"
  },
  "performance_tuning": {
    "postgresql_conf": {
      "shared_buffers": "8GB",
      "effective_cache_size": "20GB",
      "work_mem": "16MB",
      "maintenance_work_mem": "1GB",
      "max_connections": 100
    },
    "expected_improvement": "60% reduction in disk I/O, 2x query throughput"
  },
  "high_availability": {
    "architecture": "primary + 2 read replicas (async)",
    "failover_strategy": "automatic (Patroni + etcd)",
    "rto": "< 60 seconds",
    "rpo": "< 5 seconds (async replication lag)"
  }
}

Required fields: schema_design (tables with columns and indexes), performance_tuning (postgresql_conf parameters).

Optional fields: partitioning_strategy (only if tables >1M rows), high_availability (only if HA requirement specified).

Examples

# Example: E-commerce platform (OLTP workload)
input:
  workload_type: oltp
  data_volume: "500GB (5M users, 50M orders)"
  queries_per_second: 5000
  availability_sla: 99.95%
  deployment: aws-rds-postgres-17

output:
  schema_design:
    users: 3NF normalized, BIGSERIAL id, VARCHAR email (indexed)
    orders: partitioned by created_at (monthly), indexed on user_id + status
  indexes:
    - users(email) B-tree UNIQUE → login queries 50x faster
    - orders(user_id, created_at) B-tree → user history 25x faster
    - orders(status) partial WHERE status != 'completed' → active orders
  partitioning:
    orders: RANGE by created_at, monthly, 24 partitions (2 years)
  performance_tuning:
    shared_buffers: 16GB (25% of 64GB), work_mem: 8MB, max_connections: 200
  high_availability:
    primary + 2 read replicas (async), Patroni failover, RTO <60s

Quality Gates

Token budgets (enforced):

• T1: ≤2,000 tokens - quick health check with top 3 optimization recommendations
• T2: ≤6,000 tokens - comprehensive architecture with schema, indexes, partitioning, tuning, and HA design
• T3: ≤12,000 tokens - enterprise architecture with multi-region, sharding, DR, monitoring, and security hardening

Accuracy requirements:

• All DDL statements must be valid PostgreSQL 17 syntax
• Memory configuration calculations verified against server RAM
• Index recommendations validated with EXPLAIN ANALYZE cost estimates

Safety constraints:

• No DROP statements without explicit approval
• No ALTER TABLE on production without maintenance window
• No synchronous replication without understanding latency impact

Auditability:

• Cite PostgreSQL documentation version for all recommendations
• Include EXPLAIN ANALYZE output for index justification
• Document performance improvement estimates with methodology

Determinism:

• Same workload + same data model → same schema design
• Configurable thresholds (index selectivity, partitioning size, memory percentages)

Resources

Official PostgreSQL documentation:

• PostgreSQL 18 Documentation (latest): https://www.postgresql.org/docs/current/
• Performance Tips: https://www.postgresql.org/docs/current/performance-tips.html
• Indexes: https://www.postgresql.org/docs/current/indexes.html
• Partitioning: https://www.postgresql.org/docs/current/ddl-partitioning.html
• High Availability: https://www.postgresql.org/docs/current/high-availability.html

PostgreSQL 17 features and tuning:

• PostgreSQL 17 Performance: https://www.pgedge.com/blog/postgresql-17-a-major-step-forward-in-performance-logical-replication-and-more
• Tuning Best Practices 2025: https://www.mydbops.com/blog/postgresql-parameter-tuning-best-practices
• Configuration Guide 2025: https://dbadataverse.com/tech/postgresql/2025/02/postgresql-configuration-parameters-best-practices-for-performance-tuning

Schema design and indexing:

• Database Schema Best Practices: https://www.bytebase.com/blog/top-database-schema-design-best-practices/
• Advanced Indexing Strategies: https://www.freecodecamp.org/news/postgresql-indexing-strategies/
• Index Best Practices: https://www.mydbops.com/blog/postgresql-indexing-best-practices-guide

Tools and extensions:

• pgAdmin: https://www.pgadmin.org/
• PgBouncer (connection pooling): https://www.pgbouncer.org/
• Patroni (HA): https://patroni.readthedocs.io/
• Citus (sharding): https://www.citusdata.com/

Related skills:

• database-schema-designer: Database-agnostic schema design
• database-optimization-analyzer: Query-level performance tuning
• database-migration-generator: Data migration and ETL
• cloud-aws-architect: AWS RDS/Aurora PostgreSQL deployment
• cloud-gcp-architect: GCP Cloud SQL PostgreSQL deployment
• cloud-azure-architect: Azure Database for PostgreSQL deployment