database-postgres-architect_skill

Overview

This skill designs high-performance PostgreSQL databases and produces production-ready architecture tailored for modern workloads. It covers schema optimization, indexing strategies, partitioning, replication, and tuning for PostgreSQL 17. Outputs include DDL, index definitions, postgresql.conf recommendations, and HA/replication guidance. It is focused on measurable performance and scalability outcomes.

How this skill works

I analyze workload type, data volume, and QPS targets, then run a schema and config review to identify top bottlenecks. The process delivers quick wins (top 3 fixes) and, when required, a comprehensive architecture including normalization/denormalization tradeoffs, index strategy, partitioning plan, and PG17-specific tuning. For HA and scale, I propose replication, failover patterns, and connection pooling recommendations.

When to use it

• Designing a new PostgreSQL database for production applications
• Resolving performance issues: slow queries, high latency, or index problems
• Scaling beyond ~100GB or targeting 10k+ QPS
• Planning migration or upgrade to PostgreSQL 17
• Building high-availability deployments with 99.9%+ uptime SLAs

Best practices

• Start with workload classification (OLTP/OLAP/HTAP/time-series) to guide schema and index choices
• Use declarative partitioning for large tables and verify pruning with EXPLAIN ANALYZE
• Prefer appropriate column types (SMALLINT/INTEGER/BIGINT, TIMESTAMPTZ, JSONB) to save space and improve planner estimates
• Create covering and partial indexes where appropriate and monitor pg_stat_user_indexes to drop unused ones
• Tune memory: shared_buffers 25–40% RAM, effective_cache_size 50–75% RAM, and set work_mem per-operation carefully
• Use PgBouncer for connection pooling and CONCURRENTLY for zero-downtime index builds

Example use cases

• Quick health check that surfaces the top 3 performance issues and immediate fixes within minutes
• Full architecture design for an OLTP app: schema DDL, indexes, partitioning by created_at, and PG17 tuning
• Tune an analytics cluster: increase parallel workers, adjust checkpointing, and partition large fact tables
• Design HA for production: streaming replication topology, synchronous standby choices, and Patroni failover plan
• Migrate from MySQL/Oracle: map data types, propose identity/UUID strategies, and plan logical replication