Partitioning_basics

Chapter 38: Table Partitioning Basics

Dividing Large Tables

---

38.1 Understanding Partitioning

Table partitioning splits a large table into smaller, more manageable pieces called partitions.

                    Partitioning Benefits
    ========================================================================

    ┌─────────────────────────────────────────────────────────────────────┐
    │                    Why Partition?                                     │
    │                                                                      │
    │   Performance:                                                      │
    │   ───────────                                                       │
    │   • Faster queries - only scan relevant partitions                   │
    │   • Better index efficiency                                         │
    │   • Parallel query execution                                        │
    │                                                                      │
    │   Management:                                                        │
    │   ──────────                                                        │
    │   • Easy to drop old data (drop partition)                         │
    │   • Archive old data to cheaper storage                            │
    │   • Easier maintenance (vacuum, analyze)                           │
    │                                                                      │
    │   Availability:                                                     │
    │   ────────────                                                      │
    │   • Partition-level backup/restore                                 │
    │   • Reduced impact of failures                                     │
    │                                                                      │
    └─────────────────────────────────────────────────────────────────────┘

---

38.2 Creating Partitioned Tables

-- Create range-partitioned table
CREATE TABLE orders (
    order_id BIGSERIAL,
    user_id INTEGER NOT NULL,
    order_date DATE NOT NULL,
    total DECIMAL(10,2),
    status VARCHAR(20)
) PARTITION BY RANGE (order_date);

-- Create partitions
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 TABLE orders_2024_q3 PARTITION OF orders
    FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');

CREATE TABLE orders_2024_q4 PARTITION OF orders
    FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');

-- Create default partition (for out-of-range values)
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

---

38.3 Partition Types

-- List partition (by specific values)
CREATE TABLE products (
    product_id SERIAL,
    name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2)
) PARTITION BY LIST (category);

CREATE TABLE products_electronics PARTITION OF products
    FOR VALUES IN ('Electronics', 'Computers', 'Phones');

CREATE TABLE products_clothing PARTITION OF products
    FOR VALUES IN ('Clothing', 'Shoes', 'Accessories');

-- Hash partition
CREATE TABLE user_sessions (
    session_id BIGSERIAL,
    user_id INTEGER,
    created_at TIMESTAMP DEFAULT NOW()
) PARTITION BY HASH (user_id);

CREATE TABLE user_sessions_0 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE user_sessions_1 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE user_sessions_2 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE user_sessions_3 PARTITION OF user_sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

---

38.4 Managing Partitions

-- Check which partition data goes to
EXPLAIN SELECT * FROM orders WHERE order_date = '2024-06-15';

-- List partitions
SELECT
    schemaname,
    tablename,
    partitionname,
    partitionordinal
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY partitionordinal;

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

-- Detach a partition
ALTER TABLE orders DETACH PARTITION orders_2024_q1;

-- Attach a partition
ALTER TABLE orders ATTACH PARTITION orders_2024_q1
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

-- Drop old partition
DROP TABLE orders_2023_q1;

---

Summary

Type	Best For
Range	Dates, numbers
List	Categories, regions
Hash	Even distribution

---

Next: Chapter 39: Range & List Partitioning