Partitioning

Range partitioning divides data based on value ranges.

How Range Partitioning Works

Table: orders
Partition by: created_at

Partition 2024-Q1: Jan-Mar 2024
Partition 2024-Q2: Apr-Jun 2024
Partition 2024-Q3: Jul-Sep 2024
Partition 2024-Q4: Oct-Dec 2024

Range Partitioning Example

-- PostgreSQL
CREATE TABLE orders (
    id SERIAL,
    user_id INTEGER,
    amount DECIMAL,
    created_at TIMESTAMP
) PARTITION BY RANGE (created_at);

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');

Range Partitioning Benefits

Benefit	Description
Query efficiency	Scan only relevant partitions
Data management	Easy archival of old partitions
Maintenance	Drop old partitions quickly
Time-series	Natural fit for time-based data

Range Partitioning Challenges

1. Hotspots
   - Recent partitions get more traffic
   - Current quarter is busiest

2. Uneven Distribution
   - Some partitions may be larger
   - Uneven data distribution

3. Partition Management
   - Must create new partitions proactively
   - Future partitions needed

When to Use Range Partitioning

Use when:
- Time-series data (logs, events)
- Data has natural range
- Queries often filter by range
- Old data can be archived/dropped

Examples:
- Order history by date
- Log entries by timestamp
- Sensor data by time

Partition Pruning

Query: SELECT * FROM orders WHERE created_at > '2024-04-01'

Without pruning: Scan all partitions
With pruning: Scan only Q2, Q3, Q4 partitions

Partition pruning = query only relevant partitions

Partition Maintenance

-- Create new partition
CREATE TABLE orders_2025_q1 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

-- Drop old partition (fast delete)
DROP TABLE orders_2023_q1;

-- Detach for archival
ALTER TABLE orders DETACH PARTITION orders_2023_q1;

Hash partitioning distributes data using a hash function.

How Hash Partitioning Works

Table: users
Partition by: hash(user_id)

Partition 0: hash(user_id) % 4 = 0
Partition 1: hash(user_id) % 4 = 1
Partition 2: hash(user_id) % 4 = 2
Partition 3: hash(user_id) % 4 = 3

user_id 1 → hash(1) % 4 = 1 → Partition 1
user_id 2 → hash(2) % 4 = 2 → Partition 2

Hash Partitioning Example

-- PostgreSQL
CREATE TABLE users (
    id SERIAL,
    name VARCHAR(100),
    email VARCHAR(255)
) PARTITION BY HASH (id);

CREATE TABLE users_p0 PARTITION OF users
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE users_p1 PARTITION OF users
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

CREATE TABLE users_p2 PARTITION OF users
    FOR VALUES WITH (MODULUS 4, REMAINDER 2);

CREATE TABLE users_p3 PARTITION OF users
    FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Hash Partitioning Benefits

Benefit	Description
Even distribution	Hash function spreads data evenly
No hotspots	Uniform access pattern
Simple	Easy to implement
Scalable	Add more partitions as needed

Hash Partitioning Challenges

1. Range queries inefficient
   - Must scan all partitions
   - No partition pruning for ranges

2. Rebalancing
   - Adding partitions requires rehashing
   - Data movement needed

3. No ordering
   - Data not sorted within partitions

When to Use Hash Partitioning

Use when:
- Even distribution needed
- Primary access is by key
- Range queries not important
- High write throughput needed

Examples:
- User sessions by session_id
- Product catalog by product_id
- Session storage by user_id

Hash vs Range Partitioning

Aspect	Hash	Range
Distribution	Even	May be uneven
Range queries	Inefficient	Efficient
Key lookups	Efficient	Efficient
Hotspots	None	Recent ranges
Ordering	No	Yes

List partitioning divides data based on discrete values.

How List Partitioning Works

Table: users
Partition by: country

Partition US: country = 'US'
Partition EU: country IN ('UK', 'DE', 'FR')
Partition Asia: country IN ('JP', 'CN', 'IN')
Partition Other: all other countries

List Partitioning Example

-- PostgreSQL
CREATE TABLE users (
    id SERIAL,
    name VARCHAR(100),
    country VARCHAR(2)
) PARTITION BY LIST (country);

CREATE TABLE users_us PARTITION OF users
    FOR VALUES IN ('US', 'CA', 'MX');

CREATE TABLE users_eu PARTITION OF users
    FOR VALUES IN ('UK', 'DE', 'FR', 'ES');

CREATE TABLE users_asia PARTITION OF users
    FOR VALUES IN ('JP', 'CN', 'IN', 'KR');

List Partitioning Benefits

Benefit	Description
Logical grouping	Data organized by category
Compliance	Data residency requirements
Query efficiency	Query specific category
Easy management	Drop/backup by category

List Partitioning Challenges

1. Uneven distribution
   - Some lists may have more data
   - US partition much larger than others

2. Adding new values
   - Must add new partition
   - Or handle in 'other' partition

3. Cross-partition queries
   - Queries across lists scan multiple partitions

When to Use List Partitioning

Use when:
- Data has clear categories
- Compliance requirements (data residency)
- Queries filter by category
- Different retention policies per category

Examples:
- Users by country/region
- Orders by status
- Products by category
- Multi-tenant systems

Multi-Level Partitioning

-- Combine partitioning types
CREATE TABLE orders (
    id SERIAL,
    user_id INTEGER,
    created_at TIMESTAMP,
    region VARCHAR(10)
) PARTITION BY LIST (region)
  SUBPARTITION BY RANGE (created_at);

-- Level 1: By region
-- Level 2: By date within region

Partitioning Best Practices

1. Choose partition key wisely: Based on query patterns
2. Monitor partition sizes: Ensure even distribution
3. Plan for growth: Create partitions proactively
4. Archive old partitions: Drop or detach old data
5. Test partition pruning: Verify queries use partitions