Partitioning

Spicy — senior dev territoryDatabase

ELI5 — The Vibe Check

Partitioning divides a huge table into smaller physical chunks while still appearing as one table to your queries. Like splitting a giant spreadsheet by year — one partition per year — so queries only scan the relevant chunk. Faster queries, same interface.

Real Talk

Table partitioning divides a large table into smaller partitions based on a partitioning key (RANGE, LIST, or HASH). The partitions are physical storage units but the table appears unified to queries. The query planner performs partition pruning to scan only relevant partitions. PostgreSQL supports declarative partitioning natively.

Show Me The Code

CREATE TABLE logs (
  id BIGSERIAL,
  created_at DATE,
  message TEXT
) PARTITION BY RANGE (created_at);

CREATE TABLE logs_2024 PARTITION OF logs
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

When You'll Hear This

"Partition the logs table by month to keep queries fast as it grows." / "Partition pruning means old partitions are never scanned for recent queries."

Related Terms

Index

A database index is like the index in the back of a book. Without it, the database reads every single row to find what you want.

intermediateDatabase

Query Optimization

Query optimization is the art of making slow database queries fast. Add an index here, rewrite that subquery as a JOIN, fetch only the columns you need.

advancedDatabase

Sharding

Sharding splits your database across multiple servers based on some rule — like user IDs 1-1M on server 1, 1M-2M on server 2.

advancedDatabase

Table

A database table is exactly like a spreadsheet tab. It has columns across the top (name, email, age) and rows going down (one per person).

beginnerDatabase

Back to Browse Random Term