Database.Middle.How does database partitioning work?

Database partitioning is a technique used to split a large table into smaller, more manageable pieces, called partitions, while keeping them logically part of the same table. This improves performance, scalability, and sometimes maintenance.

---

🧠 Why Partition?

• ✅ Query performance: Only scan relevant partition(s)
• ✅ Manageability: Easier to archive, purge, or back up
• ✅ Parallelism: Different partitions can be read/written in parallel
• ✅ Maintenance: Indexes and constraints apply per partition

---

📦 Types of Partitioning

Type	How it works	Example
Range	Split by a range of values	Date ranges, ID ranges
List	Split by discrete values	Country = US, UK, FR
Hash	Split using a hash function	Even data distribution
Composite	Combines two types (e.g., range + hash)	Date → then hash within each month

---

🔍 Example: Range Partitioning (PostgreSQL)

CREATE TABLE sales (
  id SERIAL,
  sale_date DATE NOT NULL,
  amount NUMERIC
) PARTITION BY RANGE (sale_date);

Then define partitions:

CREATE TABLE sales_2023_q1 PARTITION OF sales
  FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');

CREATE TABLE sales_2023_q2 PARTITION OF sales
  FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');

Now, when you query by date:

SELECT * FROM sales WHERE sale_date = '2023-02-10';

PostgreSQL automatically routes the query to sales_2023_q1 only → faster.

🧪 Other Partitioning Examples

✅ List Partitioning (MySQL, PostgreSQL 11+)

CREATE TABLE users (
  id INT,
  country_code TEXT
) PARTITION BY LIST (country_code);

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

CREATE TABLE users_uk PARTITION OF users
  FOR VALUES IN ('UK');

🧪 Other Partitioning Examples

✅ List Partitioning (MySQL, PostgreSQL 11+)

CREATE TABLE users (
  id INT,
  country_code TEXT
) PARTITION BY LIST (country_code);

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

CREATE TABLE users_uk PARTITION OF users
  FOR VALUES IN ('UK');

✅ Hash Partitioning (PostgreSQL / MySQL)

Useful for spreading rows evenly when ranges/lists aren’t practical.

CREATE TABLE logs (
  id INT,
  message TEXT
) PARTITION BY HASH (id);

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);
-- etc.

🚦 Partition Pruning

Modern databases (PostgreSQL, MySQL, Oracle, etc.) prune unneeded partitions automatically during query execution, so only relevant partitions are scanned.

This boosts performance dramatically for filtered queries like:

SELECT * FROM sales WHERE sale_date BETWEEN '2023-01-01' AND '2023-01-31';

⚠️ Downsides / Gotchas

Concern	Explanation
❌ Complexity	More complex DDL and indexing
❌ Not always faster	Especially if queries span many partitions
❌ Insert routing overhead	Database must decide where to insert
❌ Feature limitations	Some constraints or foreign keys may be restricted

✅ Summary

Concept	Explanation
Partitioning	Break large table into smaller parts
Benefit	Better performance, easier maintenance
Types	Range, List, Hash, Composite
Use Cases	Time-series, multi-region, log data, huge tables