data-relational-databaseUse this skill when asked about PostgreSQL, MySQL, relational database, partitioning, replication, indexing, vacuum, connection pooling, query optimization, EXPLAIN, CTE, window functions, or transaction isolation. This skill enforces: PostgreSQL architecture understanding (MVCC, WAL, vacuum), indexing strategies (B-tree, GiST, GIN, BRIN), partitioning (ran…
Independent GitHub snapshot · Automated classification · Original documentation governs
Design relational database schemas with proper indexing, partitioning, replication, connection pooling, and query optimization strategies.
Exact user phrases: "PostgreSQL", "MySQL", "relational database", "partitioning", "replication", "indexing", "vacuum", "connection pooling", "query optimization", "EXPLAIN", "CTE", "window function", "transaction isolation", "migration", "PgBouncer", "MVCC", "WAL", "B-tree", "GiST", "GIN", "BRIN".
Before activating, verify:
Database schema with indexes, partition configuration, replication setup, and query optimization plan as SQL and YAML.
-- Table DDL with partitioning
-- Index creation statements
-- Replication configuration
-- Migration statements
# Connection pool config
# Replication setup
# Vacuum settings
No preamble. No postamble. No explanations. No filler/hedging/transitions. Compress output — why use many token when few do trick.
300 lines of SQL and configuration.
Normalize to 3NF by default. Denormalize only for read-heavy analytical queries. Every table needs a primary key (UUID or BIGSERIAL). Foreign keys with indexes on referencing columns. Naming: snake_case for tables, columns, indexes; uq_ unique, ix_ index, fk_ foreign key, pk_ primary key. Use CHECK constraints for domain integrity. Use ENUM or reference tables for constrained values.
CREATE TABLE customers (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
status customer_status NOT NULL DEFAULT 'active',
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
B-tree: default for equality and range queries on high-cardinality columns. GiST: full-text search, geometric data, range type overlap. GIN: JSONB, array containment, full-text search tsvector. BRIN: large sequential tables where rows are physically ordered (time-series). Partial indexes for filtered queries. Composite indexes with leading column matching the most selective filter. Covering indexes (INCLUDE columns) for index-only scans. Avoid over-indexing on write-heavy tables.
-- B-tree composite: equality then range
CREATE INDEX ix_orders_customer_created
ON orders (customer_id, created_at DESC);
-- Partial: only active orders
CREATE INDEX ix_orders_active
ON orders (created_at DESC)
WHERE status = 'pending';
-- GIN: JSONB path operations
CREATE INDEX ix_products_attrs
ON products USING GIN (attributes jsonb_path_ops);
-- BRIN: time-series with 100000 block range
CREATE INDEX ix_events_ts
ON events USING BRIN (occurred_at)
WITH (pages_per_range = 100000);
Range partition by date for time-series data. List partition by category/region. Hash partition for load balancing when no natural partition key. Sub-partitioning for multi-dimensional data. Each partition is a table — indexes must be created per partition or on parent (PG 12+). Partition pruning requires WHERE clause on partition key. Avoid too many partitions (>1000).
CREATE TABLE events (
id BIGSERIAL, occurred_at TIMESTAMPTZ NOT NULL,
event_type TEXT NOT NULL, payload JSONB
) PARTITION BY RANGE (occurred_at);
CREATE TABLE events_2025_q1 PARTITION OF events
FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');
CREATE TABLE events_2025_q2 PARTITION OF events
FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');
Streaming replication: primary ships WAL to standbys. Synchronous for zero data loss (2 safe). Asynchronous for performance (slight lag). Cascading replication for geographic distribution. Logical replication: publish/subscribe at table level, cross-version compatible, supports selective replication. Conflict resolution for bidirectional: last-write-wins or custom handler.
# postgresql.conf streaming replication
wal_level: replica
max_wal_senders: 10
wal_keep_size: 1024 # MB
synchronous_standby_names: 'FIRST 2 (standby1, standby2)'
PgBouncer in transaction mode (default). Pool size = max_connections * 0.9 / pool_mode_transaction. Reserve pools for admin connections. Statement mode only for prepared-statement-free workloads. Session mode for session-scoped features (LISTEN/NOTIFY, temp tables). Configure timeouts to prevent idle consumption.
# pgbouncer.ini
[databases]
* = host=localhost port=5432
[pgbouncer]
pool_mode: transaction
default_pool_size: 25
max_client_conn: 200
reserve_pool_size: 5
reserve_pool_timeout: 5.0
server_idle_timeout: 300
client_idle_timeout: 600
EXPLAIN ANALYZE to find seq scans, nested loops, sorts. Identify: sequential scans on large tables, nested loop joins without indexes, excessive tuple deformations, temp file sorts. CTE optimization: PG 12+ inlines CTEs automatically when no side effects; use MATERIALIZED or NOT MATERIALIZED to control. Window functions: prefer ROWS frame over RANGE for performance; filter early with subqueries.
-- Check for sequential scans
EXPLAIN (ANALYZE, BUFFERS, TIMING) SELECT * FROM orders
WHERE customer_id = 'abc' AND created_at > '2025-01-01';
-- CTE with explicit materialization control
WITH filtered AS NOT MATERIALIZED (
SELECT * FROM orders WHERE status = 'shipped'
)
SELECT customer_id, COUNT(*) FROM filtered
WHERE total > 100
GROUP BY customer_id;
READ COMMITTED: default PostgreSQL, row-level lock only for concurrent writes. REPEATABLE READ: snapshot isolation, no dirty/non-repeatable reads, serialization failures on conflict. SERIALIZABLE: true serial execution, highest overhead, retry on 40001. Snapshot isolation in PostgreSQL prevents read-write conflicts that InnoDB allows. Use explicit locks (SELECT FOR UPDATE) sparingly and always with NOWAIT or SKIP LOCKED.
-- SKIP LOCKED for job queues
BEGIN;
SELECT * FROM jobs
WHERE status = 'pending'
ORDER BY priority DESC
LIMIT 10
FOR UPDATE SKIP LOCKED;
COMMIT;
Tools: Alembic (Python), Flyway (Java), Sqitch (language-agnostic), Liquibase (XML/YAML/JSON). Principles: every migration has forward and rollback script; migrations are idempotent; never modify existing migrations after merge; test migrations against a copy of production data. Zero-downtime migrations require additive schema changes (new columns nullable, backfill, then add NOT NULL). Avoid long-running locks by using CREATE INDEX CONCURRENTLY and ALTER TABLE ... SET NOT NULL with low lock timeouts.
-- Safe index creation without blocking writes
CREATE INDEX CONCURRENTLY ix_orders_new
ON orders (customer_id, created_at DESC);
pg_stat_statements for query performance tracking. pg_stat_activity for active connections and long-running queries. auto_explain for logging slow queries automatically. pg_stat_bgwriter for checkpoint and buffer management. pg_stat_replication for replication lag monitoring. Set up alerting for: replication lag > 10 seconds, long-running queries > 30 seconds, deadlocks, connection pool exhaustion, WAL generation rate spikes.
-- Long running queries
SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state
FROM pg_stat_activity
WHERE state != 'idle' AND now() - pg_stat_activity.query_start > interval '30 seconds'
ORDER BY duration DESC;
-- Replication lag
SELECT application_name, state, sync_state,
pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
FROM pg_stat_replication;
WAL archiving enables PITR to any point in time between the base backup and the last archived WAL. Configure archive_mode = on and archive_command to copy completed WAL segments to durable storage. Use pg_basebackup for physical base backups. Test recovery by restoring to a separate instance and running consistency checks. Retention policy varies: 7-30 days of PITR for most production systems, longer for compliance. Barman, pgBackRest, and WAL-G provide enterprise backup management.
-- Enable WAL archiving (postgresql.conf)
archive_mode = on
archive_command = 'cp %p /backup/wal/%f'
archive_timeout = 60
CockroachDB, YugabyteDB, and Google Spanner are distributed SQL databases providing horizontal scalability and global replication with ACID transactions.
-- CockroachDB: geo-partitioned table
CREATE TABLE user_data (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
region STRING NOT NULL, name STRING, data JSONB
) PARTITION BY LIST (region) (
PARTITION us_east VALUES IN ('us-east-1'),
PARTITION eu_west VALUES IN ('eu-west-1')
);
ALTER PARTITION us_east OF TABLE user_data
CONFIGURE ZONE USING constraints = '[+region=us-east-1]';
Spanner is a globally-distributed, strongly consistent relational database combining ACID with horizontal scalability using TrueTime for external consistency.
CREATE TABLE orders (
id INT64 NOT NULL, customer_id INT64 NOT NULL,
total NUMERIC NOT NULL, created_at TIMESTAMP NOT NULL
) PRIMARY KEY (id, created_at);
CREATE TABLE order_items (
order_id INT64 NOT NULL, item_id INT64 NOT NULL,
product_id INT64 NOT NULL, quantity INT64 NOT NULL,
CONSTRAINT FK_order_items FOREIGN KEY (order_id) REFERENCES orders (id)
) PRIMARY KEY (order_id, item_id),
INTERLEAVE IN PARENT orders ON DELETE CASCADE;
PostgreSQL's MVCC creates dead tuples that vacuum removes. Autovacuum runs automatically but needs tuning for write-heavy tables. Key parameters: autovacuum_vacuum_scale_factor (default 0.2, too high for large tables), autovacuum_vacuum_threshold, autovacuum_vacuum_cost_limit. For large tables (> 10GB), set per-table autovacuum settings. Monitor n_dead_tup in pg_stat_user_tables — if it grows continuously, autovacuum is not keeping up.
-- Per-table autovacuum tuning for write-heavy table
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_vacuum_threshold = 10000,
autovacuum_vacuum_cost_limit = 2000
);
-- Check vacuum progress
SELECT relname, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;
New Index
├── Equality + range on high-cardinality? → B-tree
├── JSONB/array/tsvector queries? → GIN
├── Geometric/full-text/range overlap? → GiST
├── Time-series, correlated physical order? → BRIN
└── Spatial? → SP-GiST
Need replication?
├── HA, same version, all tables? → Streaming (async or sync)
├── Cross-version, subset of tables? → Logical
├── Distribution with conflict handling? → Logical (bidirectional)
└── Reporting offload? → Streaming read replica
HA requirements:
├── RTO < 30s, RPO = 0 → Synchronous streaming replication + Patroni
├── RTO < 5min, RPO < 10s → Asynchronous streaming + Patroni
├── RTO < 15min, RPO < 1min → WAL archiving + recovery
└── Multi-region DR → Logical replication across regions
EXPLAIN (ANALYZE, BUFFERS, TIMING) for all query performance investigationsrandom_page_cost = 1.1 for SSD storage (default 4.0 is for HDD)pg_stat_user_tables for seq_scan, n_tup_hot_upd (hot updates), n_dead_tupBIGSERIAL over SERIAL (no 32-bit overflow)TIMESTAMPTZ always, never TIMESTAMP without timezoneIDENTITY columns over SERIAL in PG 10+ for better permission managementeffective_cache_size to 50-75% of total RAM for better query plansVACUUM during low-write windows for large tablespg_restore from backups quarterlypg_stat_statements to identify top resource-consuming queriesauto_explain for queries exceeding 5 seconds in developmentSKIP LOCKED for queue-style workloadsCONCURRENTLY to avoid blocking writes| Feature | PostgreSQL | MySQL | CockroachDB | Spanner |
|---|---|---|---|---|
| ACID | Full | Varies by engine | Serializable | External consistency |
| Index types | B-tree, GiST, GIN, BRIN, SP-GiST, Hash | B-tree, Hash, Full-text, Spatial | B-tree, GIN, Inverted | Global secondary |
| Replication | Streaming, Logical | Async, Semisync, Group | Raft consensus | TrueTime + Paxos |
| Partitioning | Range, List, Hash (native) | Range, List, Hash, Key | Range, List, Hash | Interleaved |
| Extensions | Extensive (PostGIS, pgvector, etc.) | Limited | PG-compatible | SQL standard |
| Clustering | Patroni, repmgr, pg_auto_failover | InnoDB Cluster, Group Replication | Built-in | Built-in |
| Multi-region | Via logical replication | Via replication | Configurable | Automatic |
PostgreSQL vs MySQL: PG has better SQL compliance, more index types, richer extension ecosystem, and superior MVCC implementation. MySQL has better replication tooling (Group Replication, InnoDB Cluster), more managed cloud options, and simpler configuration for basic use cases. PG is通常 preferred for complex queries, data analytics, and applications needing advanced features (PostGIS, pgvector, full-text search).
Relational vs NoSQL: relational databases provide ACID transactions, strong consistency, and rich query capabilities. NoSQL databases provide horizontal scalability, flexible schemas, and specialized data models (document, key-value, wide-column). Use relational when data integrity and complex queries are paramount. Use NoSQL when scale, schema flexibility, or specialized access patterns matter more.
pg_stat_bgwriter for checkpoint frequency. Too-frequent checkpoints = too much WAL.| Tool | Purpose |
|---|---|
| pg_stat_statements | Query performance tracking |
| auto_explain | Automatic slow query logging |
| PgBouncer | Connection pooling |
| pgBackRest / WAL-G | Backup and recovery |
| Patroni | High availability management |
| pgBadger | Log analysis, performance reports |
| pganalyze / PGMustard | Query optimization (commercial) |
| Alembic / Flyway / Sqitch | Schema migrations |
| pg_dump / pg_restore | Backup/restore utilities |
| TimescaleDB | Time-series extension with auto-partitioning |
| PostGIS | Geographic information system extension |
| pgvector | Vector similarity search for AI/ML |
Relational Database Selection
├── Transaction volume?
│ ├── High (10k+ write tps) → PostgreSQL / MySQL with connection pooling
│ ├── Very high (100k+ tps) → CockroachDB / Yugabyte (distributed SQL)
│ └── Low (< 1k tps) → SQLite / Single-node PostgreSQL
├── Consistency requirements?
│ ├── Strong consistency → PostgreSQL / MySQL (single-primary)
│ ├── Eventual → Distributed SQL (CockroachDB, TiDB)
│ └── Configurable → Yugabyte (tunable consistency)
├── Geo-distributed reads?
│ ├── Yes → CockroachDB (global distribution, follower reads)
│ └── No → Single-region PostgreSQL / MySQL
└── Managed or self-hosted?
├── Managed → RDS / Cloud SQL / Aurora
└── Self-hosted → PostgreSQL on K8s (CloudNativePG, Patroni)
Decision criteria: Evaluate throughput, consistency, geo-distribution, and operational team capacity.
-- relational_database/orders_partitioning.sql
CREATE TABLE orders (
order_id BIGSERIAL,
customer_id INTEGER NOT NULL,
order_date DATE NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
status TEXT NOT NULL DEFAULT 'pending'
) PARTITION BY RANGE (order_date);
CREATE TABLE orders_2024_q1 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE orders_2024_q2 PARTITION OF orders
FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date DESC);
CREATE INDEX idx_orders_status ON orders (status) WHERE status IN ('pending', 'processing');
# relational_database/connection_pool.py
from psycopg2 import pool
from contextlib import contextmanager
class DatabasePool:
def __init__(self, dsn: str, min_conn: int = 4, max_conn: int = 20):
self.pool = pool.ThreadedConnectionPool(min_conn, max_conn, dsn)
@contextmanager
def get_connection(self):
conn = self.pool.getconn()
try:
yield conn
conn.commit()
except Exception:
conn.rollback()
raise
finally:
self.pool.putconn(conn)
CREATE INDEX CONCURRENTLY.auto_explain; monitor with pg_stat_statements.max_connections = max_worker_processes * 2; use PgBouncer for connection pooling.| Anti-Pattern | Consequence | Solution |
|---|---|---|
| No connection pooling | Connection storm kills DB | Deploy PgBouncer / RDS Proxy |
| SELECT * in production apps | Unnecessary I/O, no index usage | Always specify columns |
| Missing foreign key indexes | Lock escalation on DELETE/UPDATE | Index all FK columns |
| Running VACUUM FULL regularly | Table bloat + downtime during reindex | Use pg_repack instead |
Ignoring EXPLAIN ANALYZE | Persistent slow queries | Profile every query before deploy |
WHERE clause partial indexes for sparse query patterns (e.g., only active orders).INCLUDE clause to create covering indexes for index-only scans.scram-sha-256 for PostgreSQL password auth; disable trust and md5 in production.ssl_min_protocol_version = 'TLSv1.3'.current_setting('app.tenant_id').pgaudit extension; log all DDL and DML on sensitive tables.data-etl-pipeline for loading data into relational schemas
data-data-warehouse for dimensional modeling from relational sources