What is the difference between table partitioning and sharding, and when do you need each?
What is the difference between table partitioning and sharding, and when do you need each?
Partitioning splits one logical table into pieces inside one database server. Sharding splits data across multiple servers, each holding a subset, and the application or a proxy routes queries.
Strategies: range (dates), list (regions), hash (even spread).
-- PostgreSQL declarative partitioning
CREATE TABLE events (
id BIGINT GENERATED ALWAYS AS IDENTITY,
created_at TIMESTAMPTZ NOT NULL,
payload JSONB,
PRIMARY KEY (id, created_at) -- the partition key must be part of the primary key
) PARTITION BY RANGE (created_at);
CREATE TABLE events_2026_11 PARTITION OF events
FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
Benefits:
DELETE.Notes: PostgreSQL requires unique constraints to include the partition key. MySQL requires the partition key inside every unique key and does not support foreign keys on partitioned InnoDB tables. SQL Server uses partition functions and schemes; Oracle offers many partition types. Queries that do not filter on the key touch every partition and can get slower.
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.