-- Create partitioned parent table
CREATE TABLE orders (
    order_id   BIGSERIAL,
    order_date DATE NOT NULL,
    amount     NUMERIC
) PARTITION BY RANGE (order_date);

-- Create partitions
CREATE TABLE orders_2024 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE orders_2025 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- Query works on parent — partition pruning happens automatically
SELECT * FROM orders WHERE order_date = '2025-06-01';

-- Check if partition pruning is working
EXPLAIN SELECT * FROM orders
WHERE order_date = '2025-03-15';

-- Good output: only orders_2025 scanned
-- Bad output: all partitions scanned → no pruning

-- Ensure partition pruning is enabled
SHOW enable_partition_pruning;
SET enable_partition_pruning = on;

-- Find tables with high dead tuple ratio
SELECT
    schemaname,
    relname AS table_name,
    n_live_tup,
    n_dead_tup,
    round(n_dead_tup::numeric /
        NULLIF(n_live_tup + n_dead_tup, 0) * 100, 1) AS dead_pct,
    last_autovacuum,
    last_vacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 15;

-- Fix 1: Standard VACUUM (no lock, space reusable)
VACUUM VERBOSE mytable;

-- Fix 2: VACUUM FULL (reclaims disk, takes EXCLUSIVE LOCK!)
VACUUM FULL mytable;

-- Fix 3: pg_repack (online defrag — no exclusive lock)
-- pg_repack -d mydb -t mytable

-- Check TOAST strategy for each column
SELECT attname, attstorage
FROM pg_attribute
WHERE attrelid = 'mytable'::regclass
  AND attnum > 0;

-- Change storage strategy for a column
ALTER TABLE mytable
  ALTER COLUMN doc_content SET STORAGE EXTERNAL;
-- EXTERNAL = no compression, fast access

-- Check XID age per database (alert if > 1.5 billion)
SELECT
    datname,
    age(datfrozenxid) AS xid_age,
    round(age(datfrozenxid)::numeric / 2100000000 * 100, 1) AS pct_used
FROM pg_database
ORDER BY xid_age DESC;

-- Check per-table (find which table is holding old XID)
SELECT relname, age(relfrozenxid) AS table_xid_age
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 10;

-- Force freeze old XIDs
VACUUM FREEZE mytable;

── Global tuning (postgresql.conf) ──────────────────

# More workers for concurrent vacuuming
autovacuum_max_workers        = 6      # default: 3

# Check tables more frequently
autovacuum_naptime            = '30s'  # default: 1min

# Trigger vacuum when 5% rows are dead (not 20%)
autovacuum_vacuum_scale_factor = 0.05  # default: 0.2

# Allow autovacuum to work faster
autovacuum_vacuum_cost_limit  = 1000  # default: 200
autovacuum_vacuum_cost_delay  = '2ms' # default: 20ms

── Per-table override for very large/hot tables ──────
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_vacuum_threshold    = 1000
);

# Cost assigned per vacuum I/O operation
vacuum_cost_page_hit    = 1    # buffer cache hit
vacuum_cost_page_miss   = 10   # disk read
vacuum_cost_page_dirty  = 20   # dirty page write

# When accumulated cost hits limit, sleep for delay duration
autovacuum_vacuum_cost_limit  = 200   # default
autovacuum_vacuum_cost_delay  = '20ms' # default

# For NVMe/fast storage — let autovacuum run at full speed
autovacuum_vacuum_cost_limit  = 2000
autovacuum_vacuum_cost_delay  = '0ms'

-- Find sessions blocking autovacuum (idle-in-transaction)
SELECT
    pid,
    usename,
    state,
    wait_event_type,
    wait_event,
    now() - xact_start AS xact_duration,
    left(query, 80) AS query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
ORDER BY xact_start;

-- Find oldest running transaction (the XID bottleneck)
SELECT pid, usename, state,
    now() - xact_start AS age,
    backend_xmin
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY backend_xmin;

-- Terminate the blocker
SELECT pg_terminate_backend(pid);

# Install pg_repack
yum install pg_repack17

# Repack a specific table online
pg_repack -d mydb -t orders --no-superuser-check

# Repack all tables in a database
pg_repack -d mydb

-- Step 1: Set wal_level = logical in postgresql.conf
-- wal_level = logical

-- Step 2: Create a replication user
CREATE ROLE repl_user WITH REPLICATION LOGIN PASSWORD 'secret';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO repl_user;

-- Step 3: Create publication for specific tables
CREATE PUBLICATION pub_orders
    FOR TABLE orders, order_items;

-- Or publish ALL tables
CREATE PUBLICATION pub_all FOR ALL TABLES;

-- Step 4: Tables must exist on subscriber first
CREATE TABLE orders ( ... );

-- Step 5: Create subscription
CREATE SUBSCRIPTION sub_orders
    CONNECTION 'host=192.168.1.51 port=5432 dbname=mydb user=repl_user password=secret'
    PUBLICATION pub_orders;

-- Step 6: Monitor subscription status
SELECT * FROM pg_stat_subscription;
SELECT * FROM pg_replication_slots;  -- on publisher

-- List all replication slots and WAL retained
SELECT
    slot_name,
    active,
    restart_lsn,
    confirmed_flush_lsn,
    pg_size_pretty(pg_wal_lsn_diff(
        pg_current_wal_lsn(), restart_lsn)) AS wal_retained
FROM pg_replication_slots;

-- Drop an inactive/stale replication slot
SELECT pg_drop_replication_slot('stale_slot_name');

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_port         = 6432
listen_addr         = *
auth_type           = scram-sha-256
pool_mode           = transaction      # recommended
max_client_conn     = 1000
default_pool_size   = 25
server_idle_timeout = 600

-- Enable in postgresql.conf (requires restart)
-- shared_preload_libraries = 'pg_stat_statements'

-- Enable the extension
CREATE EXTENSION pg_stat_statements;

-- Top 10 queries by total execution time
SELECT
    round(total_exec_time::numeric, 2) AS total_ms,
    calls,
    round(mean_exec_time::numeric, 2)  AS avg_ms,
    round(stddev_exec_time::numeric, 2) AS stddev_ms,
    rows,
    left(query, 80) AS query_snippet
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Reset statistics
SELECT pg_stat_statements_reset();

-- Partial index: only index active orders (not all 50M rows)
CREATE INDEX idx_active_orders
    ON orders(customer_id)
    WHERE status = 'ACTIVE';

-- Covering index: include extra columns to avoid table fetch
-- (Index-Only Scan becomes possible)
CREATE INDEX idx_orders_covering
    ON orders(customer_id)
    INCLUDE (order_date, amount);

-- Query that can use covering index (Index-Only Scan)
SELECT customer_id, order_date, amount
FROM orders
WHERE customer_id = 12345;

-- Force index scan for testing only
SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 99;
SET enable_seqscan = on; -- always reset after testing

-- Update statistics
ANALYZE orders;

-- EXPLAIN: plan only, no execution, no actual timings
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- EXPLAIN ANALYZE: executes the query + actual timings
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

-- EXPLAIN (ANALYZE, BUFFERS): shows buffer hits/misses
-- Shared hit = from shared_buffers (fast)
-- Shared read = from disk (slow)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42;

-- EXPLAIN (ANALYZE, BUFFERS, SETTINGS, WAL, FORMAT JSON)
-- Full diagnostic output for pgBadger / explain.dalibo.com
EXPLAIN (ANALYZE, BUFFERS, SETTINGS, WAL, FORMAT JSON)
SELECT * FROM orders WHERE customer_id = 42;

# Typical Patroni HA stack
┌─────────────────────────────────────────────┐
│  App → HAProxy (VIP via Keepalived)          │
│         ↓               ↓                   │
│  PostgreSQL Primary   PostgreSQL Replica     │
│  + Patroni            + Patroni             │
│         ↓               ↓                   │
│         └── etcd cluster (3 nodes) ─────────┘
└─────────────────────────────────────────────┘

# Check cluster status
patronictl -c /etc/patroni/patroni.yml list

# Manual switchover
patronictl -c /etc/patroni/patroni.yml switchover

-- On PRIMARY: lag per standby (bytes and estimated time)
SELECT
    application_name,
    client_addr,
    state,
    sync_state,
    pg_size_pretty(sent_lsn - replay_lsn)  AS replay_lag_bytes,
    write_lag,
    flush_lag,
    replay_lag
FROM pg_stat_replication;

-- On STANDBY: how far behind in seconds
SELECT
    now() - pg_last_xact_replay_timestamp() AS replication_delay,
    pg_is_in_recovery()                     AS is_standby,
    pg_last_wal_receive_lsn()               AS received_lsn,
    pg_last_wal_replay_lsn()                AS replayed_lsn;

# Step 1: Install new PostgreSQL 17 binaries (don't init cluster)
yum install postgresql17-server

# Step 2: Initialize new cluster (empty)
/usr/pgsql-17/bin/initdb -D /pgdata/pgsql17/data

# Step 3: Stop old cluster
systemctl stop postgresql-15

# Step 4: Run pg_upgrade check first (dry run)
/usr/pgsql-17/bin/pg_upgrade \
    -b /usr/pgsql-15/bin \
    -B /usr/pgsql-17/bin \
    -d /pgdata/pgsql15/data \
    -D /pgdata/pgsql17/data \
    --check

# Step 5: Run actual upgrade (--link = hard-link, no copy)
/usr/pgsql-17/bin/pg_upgrade \
    -b /usr/pgsql-15/bin \
    -B /usr/pgsql-17/bin \
    -d /pgdata/pgsql15/data \
    -D /pgdata/pgsql17/data \
    --link

# Step 6: Start new cluster and run post-upgrade tasks
systemctl start postgresql-17
./analyze_new_cluster.sh  # generated by pg_upgrade

-- Enable in postgresql.conf (requires restart)
-- shared_preload_libraries = 'pg_cron'
-- cron.database_name = 'mydb'

CREATE EXTENSION pg_cron;

-- Schedule a job: delete old logs every day at 3 AM
SELECT cron.schedule(
    'cleanup-old-logs',
    '0 3 * * *',
    $$DELETE FROM app_logs WHERE created_at < NOW() - INTERVAL '90 days'$$
);

-- Schedule a job every 5 minutes
SELECT cron.schedule('refresh-stats', '*/5 * * * *',
    $$CALL refresh_materialized_views()$$);

-- List all scheduled jobs
SELECT * FROM cron.job;

-- View execution history
SELECT * FROM cron.job_run_details ORDER BY start_time DESC LIMIT 10;

-- Step 1: Check current connection usage
SELECT
    max_conn,
    used_conn,
    superuser_reserved,
    max_conn - used_conn AS free_conn
FROM (
    SELECT
        current_setting('max_connections')::int AS max_conn,
        COUNT(*) AS used_conn,
        current_setting('superuser_reserved_connections')::int AS superuser_reserved
    FROM pg_stat_activity
) t;

-- Step 2: Find connection counts per user/database
SELECT usename, datname, state, COUNT(*)
FROM pg_stat_activity
GROUP BY usename, datname, state
ORDER BY COUNT(*) DESC;

-- Step 3: Find idle/idle-in-transaction sessions to terminate
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - xact_start > interval '30 minutes';