What is the difference between horizontal and vertical database partitioning?

Question

Anonymous · Accepted Answer

Horizontal partitioning splits a table by rows. For event logs, I might put each
day or month in its own partition, with every partition keeping the same
columns. Vertical partitioning splits by columns, so frequently used fields can
be stored separately from large or rarely used ones.

With billions of time-series events, the main benefit of horizontal partitioning
is that a query for, say, the last seven days can skip older partitions
entirely. That means less data to scan and often faster queries. It also makes
it easier to archive or drop old logs without rewriting the whole table. If the
data is distributed across servers, partitions can help spread storage and
workload, though partitioning a table does not automatically do that.

The caveat is that a query across the full history may still read every
partition. And if most queries only need a few fields, vertical partitioning or
columnar storage may help too. So I’d choose time-based horizontal partitions
primarily when queries and retention policies follow time.

What is the difference between horizontal and vertical database partitioning?

Practice this question with AI

Go Premium

Community Answers

Unlock Full Access

Practice More Questions