What is table partitioning? | SQL Interview Questions

What You'll Learn

What partitioning is
Partitioning types
When to use it

What is Partitioning?

Partitioning divides a large table into smaller, manageable pieces while appearing as one table.

Types of Partitioning

Range Partitioning

query.sqlSQL

CREATE TABLE orders (
  id INT,
  order_date DATE,
  total DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

List Partitioning

query.sqlSQL

CREATE TABLE users (
  id INT,
  name VARCHAR(100),
  country VARCHAR(50)
)
PARTITION BY LIST (country) (
  PARTITION p_americas VALUES IN ('USA', 'Canada', 'Mexico'),
  PARTITION p_europe VALUES IN ('UK', 'Germany', 'France'),
  PARTITION p_asia VALUES IN ('Japan', 'China', 'India')
);

Hash Partitioning

query.sqlSQL

CREATE TABLE sessions (
  id INT,
  user_id INT,
  data TEXT
)
PARTITION BY HASH (user_id)
PARTITIONS 4;

Benefits

Faster queries (partition pruning)
Easier maintenance (drop/archive partitions)
Parallel query execution
Better I/O distribution

Partition Pruning

query.sqlSQL

-- Only scans p2024 partition
SELECT * FROM orders WHERE order_date >= '2024-01-01';

-- Must scan all partitions (no pruning)
SELECT * FROM orders WHERE total > 1000;

When to Partition

Tables with millions+ rows
Time-series data
Data with clear boundaries
Need to archive/delete old data efficiently

Managing Partitions

query.sqlSQL

-- Add partition
ALTER TABLE orders ADD PARTITION (
  PARTITION p2025 VALUES LESS THAN (2026)
);

-- Drop partition (faster than DELETE)
ALTER TABLE orders DROP PARTITION p2022;

-- Reorganize partition
ALTER TABLE orders REORGANIZE PARTITION pmax INTO (
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);