Postgres Pro

Postgres Pro is a community skill for advanced PostgreSQL database administration, covering performance tuning, replication setup, partitioning strategies, monitoring configuration, and security hardening for production database environments.

What Is This?

Overview

Postgres Pro provides patterns for managing PostgreSQL databases at scale in production environments. It covers query performance tuning with EXPLAIN analysis, index selection, and configuration parameter adjustment, replication setup with streaming replication and logical replication for read scaling, table partitioning strategies using range, list, and hash partitioning for large datasets, monitoring configuration with pg_stat_statements, auto_explain, and alerting thresholds, and security hardening with SSL configuration, row-level security, and audit logging. The skill enables database administrators to operate PostgreSQL reliably under high-concurrency workloads with proper monitoring and failover capabilities.

Who Should Use This

This skill serves database administrators managing production PostgreSQL clusters, backend engineers optimizing query performance for high-traffic applications, and SRE teams implementing monitoring and failover for database reliability.

Why Use It?

Problems It Solves

Default PostgreSQL configuration is tuned for compatibility rather than performance and needs adjustment for production workloads. Identifying slow queries and their root causes requires proper logging and statistics collection. Setting up replication with automatic failover demands careful configuration and testing. Large tables degrade query performance without partitioning to limit scan scope.

Core Highlights

Performance tuner adjusts shared_buffers, work_mem, and effective_cache_size based on available resources. Replication manager configures streaming and logical replication with monitoring. Partition advisor recommends partitioning schemes based on query patterns and data distribution. Security auditor reviews authentication, encryption, and access control settings.

How to Use It?

Basic Usage

-- Performance tuning configuration
ALTER SYSTEM SET
  shared_buffers = '4GB';
ALTER SYSTEM SET
  effective_cache_size = '12GB';
ALTER SYSTEM SET
  work_mem = '64MB';
ALTER SYSTEM SET
  maintenance_work_mem = '1GB';
ALTER SYSTEM SET
  random_page_cost = 1.1;
ALTER SYSTEM SET
  effective_io_concurrency = 200;

-- Enable query statistics
CREATE EXTENSION IF NOT EXISTS
  pg_stat_statements;

-- Find slowest queries
SELECT query,
  calls,
  total_exec_time /
    1000 AS total_sec,
  mean_exec_time AS avg_ms,
  rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Check index usage
SELECT schemaname, tablename,
  indexrelname,
  idx_scan,
  pg_size_pretty(
    pg_relation_size(
      indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(
  indexrelid) DESC;

Real-World Examples

-- Table partitioning by date range
CREATE TABLE events (
  id BIGINT GENERATED ALWAYS
    AS IDENTITY,
  event_type VARCHAR(50) NOT NULL,
  payload JSONB NOT NULL,
  created_at TIMESTAMPTZ
    NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_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');

-- Row-level security
ALTER TABLE orders
  ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation
  ON orders
  USING (
    tenant_id = current_setting(
      'app.tenant_id')::INT
  );

-- Connection monitoring
SELECT state,
  COUNT(*) AS connections,
  MAX(EXTRACT(EPOCH FROM
    (NOW() - state_change)))
    AS max_duration_sec
FROM pg_stat_activity
WHERE datname = current_database()
GROUP BY state;

Advanced Tips

Use pg_repack to rebuild tables and indexes online without holding exclusive locks during maintenance windows. Configure auto_explain with log_min_duration to automatically log execution plans for queries exceeding a threshold. Implement connection pooling with PgBouncer in transaction mode for applications with many short-lived connections.

When to Use It?

Use Cases

Build a multi-tenant SaaS database with row-level security policies and per-tenant performance isolation. Create a time-series data platform with partitioned tables and automated partition management. Implement a read replica architecture with streaming replication and automatic failover monitoring.

Related Topics

Database administration, query optimization, replication, table partitioning, and database security.

Important Notes

Requirements

PostgreSQL 14 or later for modern partitioning and performance features. Administrative access for system configuration changes. Monitoring infrastructure for collecting and alerting on database metrics.

Usage Recommendations

Do: test configuration changes in staging before applying to production databases. Monitor replication lag and connection counts with automated alerts. Use pg_stat_statements to identify and optimize the highest-impact queries regularly.

Don't: change shared_buffers or work_mem without understanding the available memory and concurrent connection count. Enable synchronous replication without testing the latency impact on write operations. Skip VACUUM and ANALYZE maintenance which keeps statistics current for the query planner.

Limitations

Some configuration changes require a PostgreSQL restart and cannot be applied with reload alone. Partitioning adds complexity for queries that span many partitions without proper pruning. Logical replication does not replicate DDL changes and requires manual schema synchronization.