Distributing large volumes of data is achieved in two main ways:
Partitioning: Logical division of a single table within one database into segments (partitions) based on a certain key, usually date or range of values. This allows for quick operations on individual sections, speeds up searches, and facilitates maintenance.
Sharding: Physical separation of data across multiple databases/servers based on a specific algorithm — the table is actually duplicated across different clusters, each containing its data segment.
The advantages of partitioning — there is no need to maintain separate business logic for routing queries, everything happens "transparently" for the application; disadvantages — limited by the capabilities of a single DBMS.
Sharding provides horizontal scaling (the limit depends only on the number of servers), but requires complex synchronization, routing, and handling of "cross-shard" queries.
-- Basic partitioned table CREATE TABLE orders ( id SERIAL PRIMARY KEY, customer_id INT, order_date DATE ) PARTITION BY RANGE (order_date); CREATE TABLE orders_2023 PARTITION OF orders FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
Question: Is it possible to move rows between partitions "on the fly" without blocking the main table?
Answer: In most DBMSs, moving a row between partitions is equivalent to deleting and inserting — such operations may block rows and even the table itself, especially if triggers or foreign keys are involved. This should be considered during mass "rollovers" of data between sections.
-- ALTER TABLE ... MOVE PARTITION generally requires extra attention to locks. It's better to do this during low-load times.
Story 1: In a project, analytical reports were built across all partitions at once, without considering that a partitioned table with thousands of sections created giant execution plans. As a result — a sharp increase in execution time and server load. Solution: increase the number of partitions corresponding to the real business dimensions of the query and optimize scan plans.
Story 2: When adding sharding, the non-uniqueness of identifiers between shards was neglected. Key conflicts often arose during cross-shard aggregation.
Story 3: Automatic archiving of "old" partitions deleted them without rechecking foreign relations, leading to loss of connections with other tables and loss of some "live" data. After that, the entire logic of partition deletion was rewritten with multi-tests for consistency.