PostgreSQL Storage Internals

A. Storage & Internals

Tablespaces

Explanation: Tablespaces allow storing database objects (tables, indexes) in different physical locations. Useful for:

• Managing disk space across multiple drives

• Optimizing performance (fast vs slow storage)

• Isolating heavily accessed tables

sql

-- Create tablespace (requires superuser)
CREATE TABLESPACE fast_storage LOCATION '/mnt/ssd_data';
CREATE TABLESPACE archive_storage LOCATION '/mnt/hdd_archive';

-- Create table in specific tablespace
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_data JSONB
) TABLESPACE fast_storage;

-- Create index in different tablespace
CREATE INDEX idx_orders_date ON orders USING btree(order_date) 
TABLESPACE fast_storage;

-- Move existing table
ALTER TABLE large_table SET TABLESPACE archive_storage;

-- List tablespaces
SELECT spcname, pg_tablespace_location(oid) as location
FROM pg_tablespace;

-- Default tablespace
SHOW default_tablespace;

WAL (Write-Ahead Logging) 

Explanation: PostgreSQL's core durability mechanism. All changes are written to WAL before being applied to data files. Ensures:

• Crash recovery

• Point-in-time recovery (PITR)

• Replication

sql

-- WAL configuration
SHOW wal_level;          -- minimal, replica, or logical
SHOW archive_mode;       -- on/off for PITR
SHOW wal_keep_segments;  -- How many WAL files to keep

-- Check WAL usage
SELECT * FROM pg_stat_archiver;  -- Archive process stats

-- Force WAL rotation (switch to new segment)
SELECT pg_switch_wal();  -- PostgreSQL 10+
-- Or pg_switch_xlog() for older versions

-- Check WAL location
SELECT pg_current_wal_lsn();  -- Current WAL position
SELECT pg_walfile_name('0/15E6B90');  -- Convert LSN to filename

-- Important WAL files location
SHOW data_directory;  -- Usually $PGDATA/pg_wal

-- WAL archiving setup (postgresql.conf):
-- archive_mode = on
-- archive_command = 'cp %p /wal_archive/%f'

Vacuum & Autovacuum 

Explanation: Crucial maintenance process that:

1. Removes dead rows (MVCC cleanup)

2. Updates statistics for query planner

3. Prevents transaction ID wraparound

sql

-- Manual VACUUM
VACUUM;                     -- Just cleanup dead rows
VACUUM FULL;                -- Rewrites table (locks table!)
VACUUM ANALYZE;             -- Cleanup + update statistics
VACUUM VERBOSE orders;      -- Specific table with output

-- Autovacuum configuration
SHOW autovacuum;                    -- on/off
SHOW autovacuum_vacuum_threshold;   -- Default 50
SHOW autovacuum_vacuum_scale_factor; -- Default 0.2
SHOW autovacuum_analyze_threshold;   -- Default 50
SHOW autovacuum_analyze_scale_factor; -- Default 0.1

-- Monitor autovacuum
SELECT 
    schemaname, 
    relname,
    n_dead_tup as dead_rows,
    n_live_tup as live_rows,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC;

-- Force autovacuum on specific table
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05);
ALTER TABLE orders SET (autovacuum_vacuum_threshold = 1000);

-- Transaction ID wraparound prevention
SELECT 
    datname,
    age(datfrozenxid) as tx_age,
    current_setting('autovacuum_freeze_max_age') as freeze_max_age
FROM pg_database
ORDER BY tx_age DESC;

Bloat & Table Maintenance

Explanation: Bloat occurs when tables/indexes occupy more space than needed due to MVCC dead rows or inefficient storage.

sql

-- Check table bloat
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(relid)) as total_size,
    pg_size_pretty(pg_relation_size(relid)) as table_size,
    n_dead_tup,
    n_live_tup,
    ROUND(100.0 * n_dead_tup / (n_live_tup + n_dead_tup), 2) as dead_pct
FROM pg_stat_user_tables
WHERE n_live_tup > 10000
ORDER BY dead_pct DESC;

-- Check index bloat
SELECT 
    schemaname,
    tablename,
    indexrelname,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size,
    idx_scan,
    idx_tup_read
FROM pg_stat_user_indexes
WHERE idx_scan = 0  -- Unused indexes
ORDER BY pg_relation_size(indexrelid) DESC;

-- Rebuild bloated table (requires downtime)
VACUUM FULL orders;  -- Locks table, rewrites it

-- Rebuild index (less intrusive)
REINDEX INDEX CONCURRENTLY idx_orders_date;  -- PostgreSQL 12+
-- Or for older versions:
-- CREATE INDEX CONCURRENTLY idx_new ON orders(date);
-- DROP INDEX CONCURRENTLY idx_old;

-- Prevent bloat: Use appropriate FILLFACTOR
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    data JSONB
) WITH (FILLFACTOR = 70);  -- Leave 30% space for HOT updates

B. Transactions Deep Dive

MVCC Internals

Explanation: Multi-Version Concurrency Control implementation details - how PostgreSQL stores multiple row versions.

sql

-- View system columns (hidden by default)
SELECT 
    xmin,          -- Transaction ID that created this version
    xmax,          -- Transaction ID that deleted/updated this version (or 0)
    cmin,          -- Command identifier within transaction
    cmax,          -- Same as cmin for deleted
    ctid,          -- Physical location (page, tuple)
    tableoid,      -- Table OID
    oid,           -- Object ID (if table has OIDs)
    * 
FROM employees 
LIMIT 1;

-- Check current transaction ID
SELECT txid_current();

-- Check transaction age
SELECT age(txid_current());  -- Number of transactions since

-- See which transactions are in progress
SELECT 
    pid,
    usename,
    application_name,
    state,
    xact_start,
    query
FROM pg_stat_activity 
WHERE state = 'active';

-- Transaction visibility rules:
-- 1. Row visible if: xmin < current_txid AND (xmax = 0 OR xmax > current_txid)
-- 2. Also depends on transaction isolation level

Snapshot Isolation

Explanation: How PostgreSQL provides consistent views of data during transactions.

sql

-- Create a transaction snapshot
BEGIN;
SELECT txid_current_snapshot();  -- Returns: xmin:xmax:xlist
-- Example: '100:104:101,102' means:
-- - All transactions < 100 are visible
-- - All transactions >= 104 are invisible
-- - Transactions 101,102 are in-progress (visibility depends)

-- REPEATABLE READ isolation (snapshot at transaction start)
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts WHERE id = 1;  -- Sees snapshot
-- Other transactions' changes won't be visible
COMMIT;

-- Check transaction visibility with system functions
SELECT 
    xmin,
    xmax,
    pg_xact_status(xmin) as creator_status,
    pg_xact_status(xmax) as deleter_status
FROM employees
LIMIT 5;

-- Force snapshot synchronization issues
-- Session 1:
BEGIN;
UPDATE accounts SET balance = balance + 100 WHERE id = 1;

-- Session 2 (REPEATABLE READ):
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM accounts WHERE id = 1;  -- Sees old value
-- Cannot update until Session 1 commits/rollbacks

Long-running Transactions Issues

Explanation: Problems caused by transactions that stay open for too long.

sql

-- Find long-running transactions
SELECT 
    pid,
    usename,
    application_name,
    now() - xact_start as duration,
    state,
    query
FROM pg_stat_activity 
WHERE state IN ('idle in transaction', 'active')
  AND now() - xact_start > interval '5 minutes'
ORDER BY duration DESC;

-- Problems caused:
-- 1. MVCC bloat accumulation (can't vacuum dead rows)
-- 2. Replication lag
-- 3. Transaction ID wraparound risk
-- 4. Table/row locks preventing other operations

-- Check oldest transaction preventing cleanup
SELECT 
    datname,
    age(datfrozenxid) as oldest_tx_age,
    pg_size_pretty(pg_database_size(datname)) as db_size
FROM pg_database
ORDER BY oldest_tx_age DESC;

-- Terminate problematic transactions
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE pid = 12345;  -- Use with caution!

-- Set statement timeout
SET statement_timeout = '5min';  -- Kill queries taking >5min

-- Monitor locks caused by long transactions
SELECT 
    locktype,
    relation::regclass as relation,
    mode,
    granted,
    pid,
    usename
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE a.state = 'active'
  AND now() - a.xact_start > interval '1 minute';

C. Partitioning

Table Partitioning

Explanation: Splitting large tables into smaller physical pieces (partitions) while maintaining a single logical view.

sql

-- Create partitioned table (declarative partitioning - PostgreSQL 10+)
CREATE TABLE sales (
    id SERIAL,
    sale_date DATE NOT NULL,
    region TEXT NOT NULL,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (sale_date);

-- Create partitions for each month
CREATE TABLE sales_2024_01 PARTITION OF sales
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE sales_2024_02 PARTITION OF sales
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Create default partition (catch-all)
CREATE TABLE sales_default PARTITION OF sales DEFAULT;

-- Benefits:
-- 1. Faster queries via partition pruning
-- 2. Easier data management (archiving, dropping)
-- 3. Parallel operations
-- 4. Better maintenance (vacuum, reindex per partition)

Range / List / Hash Partitions

Explanation: Different partitioning strategies for different data patterns.

sql

-- 1. RANGE Partitioning (for dates, numbers)
CREATE TABLE orders (
    order_id SERIAL,
    order_date DATE NOT NULL,
    customer_id INT,
    total_amount DECIMAL(10,2)
) PARTITION BY RANGE (order_date);

-- Create quarterly partitions
CREATE TABLE orders_2024_q1 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

-- 2. LIST Partitioning (for discrete values)
CREATE TABLE customers (
    customer_id SERIAL,
    name TEXT,
    country_code CHAR(2)
) PARTITION BY LIST (country_code);

-- Create partitions by country
CREATE TABLE customers_us PARTITION OF customers
FOR VALUES IN ('US', 'CA', 'MX');

CREATE TABLE customers_eu PARTITION OF customers
FOR VALUES IN ('DE', 'FR', 'UK', 'IT');

-- 3. HASH Partitioning (for even distribution)
CREATE TABLE logs (
    log_id BIGSERIAL,
    log_time TIMESTAMPTZ,
    log_data JSONB
) PARTITION BY HASH (log_id);

-- Create 4 hash partitions
CREATE TABLE logs_p0 PARTITION OF logs FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE logs_p1 PARTITION OF logs FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE logs_p2 PARTITION OF logs FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE logs_p3 PARTITION OF logs FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Partition Pruning

Explanation: Query optimizer eliminates unnecessary partitions from scan.

sql

-- Enable/disable partition pruning
SET enable_partition_pruning = on;  -- Default

-- Example queries with pruning:
-- 1. Direct equality (list partitioning)
EXPLAIN ANALYZE
SELECT * FROM customers 
WHERE country_code = 'US';  -- Only scans customers_us partition

-- 2. Range condition (range partitioning)
EXPLAIN ANALYZE
SELECT * FROM orders 
WHERE order_date BETWEEN '2024-01-15' AND '2024-01-20';
-- Only scans orders_2024_q1 partition

-- 3. JOIN with partition key
EXPLAIN ANALYZE
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.country_code = 'US'
  AND o.order_date >= '2024-01-01';

-- Check if pruning occurred
EXPLAIN (ANALYZE, VERBOSE) 
SELECT * FROM sales 
WHERE sale_date = '2024-01-15';
-- Look for: "Seq Scan on sales_2024_01" only

-- Force partition-wise joins (PostgreSQL 11+)
SET enable_partitionwise_join = on;
SET enable_partitionwise_aggregate = on;

-- Attach/detach partitions
-- Move old data to separate table
ALTER TABLE sales DETACH PARTITION sales_2023_01;
-- Then can archive or backup the detached table

-- Add new partition
CREATE TABLE sales_2024_03 PARTITION OF sales
FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

-- Partitioning best practices:
-- 1. Choose partition key carefully (most queries filter on it)
-- 2. Keep partitions manageable size (1-10GB each)
-- 3. Create indexes on each partition
-- 4. Monitor partition count (too many partitions hurts performance)

Partition Management Examples

sql

-- Create index on all partitions automatically
CREATE INDEX CONCURRENTLY idx_sales_date ON sales(sale_date);

-- Vacuum specific partition
VACUUM ANALYZE sales_2024_01;

-- Check partition sizes
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
WHERE tablename LIKE 'sales_%'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Dynamic partition creation (using triggers - older method)
-- PostgreSQL 10+ uses declarative partitioning (shown above)

-- Sub-partitioning (partition of partitions)
CREATE TABLE sales_2024_01_usa PARTITION OF sales_2024_01
FOR VALUES IN ('US') 
PARTITION BY LIST (state_code);

-- IMPORTANT: Partitioning works best when:
-- 1. Most queries use partition key in WHERE clause
-- 2. Data volume > 10GB
-- 3. Data has natural time or category boundaries
-- 4. You need data lifecycle management (archiving old data)

Key Performance Insights:

1. Partition Pruning: Works with =, IN, BETWEEN, <, > on partition key

2. Indexes: Created per partition; can have different indexes per partition

3. Constraints: Each partition can have additional CHECK constraints

4. Foreign Keys: Can't reference partitioned tables directly

5. UNIQUE Constraints: Must include partition key

Partitioning is a powerful feature but requires careful planning and maintenance. Always test with your workload pattern before implementing in production.