# Data partitioning

> Splitting a huge table into pieces by a key, so queries only touch what they need.

- Level: Advanced
- Topics: Data
- URL: https://tidbit.legios.com.ar/data-partitioning/
- In Spanish: [Particionado de datos](https://tidbit.legios.com.ar/es/data-partitioning/)

**Data partitioning** means splitting one large table or dataset into smaller pieces, called partitions, according to a column such as date, country or customer ID. To the person writing SQL it still looks like one table, but under the hood each partition is stored separately.

The main payoff is **partition pruning**. If a sales table is partitioned by month and you ask for November, the database reads only the November partition and skips the other years of data entirely. In a data lake the same idea shows up as folders like `year=2026/month=11/` full of Parquet files, and engines like Spark or Athena read only the folders that match. Partitions also make housekeeping cheap: deleting a month of old data is dropping one partition instead of hunting for millions of rows.

When partitions are spread across different servers, it is called **sharding**, and it is how very large apps spread load beyond what one machine can handle. The hard part is picking the key. Partition by something queries rarely filter on and you get no benefit; pick a key where one value gets most of the traffic, such as one giant customer, and you create a **hot partition** that becomes the bottleneck while the others sit idle.

## An analogy

Like an archive that stores invoices in one box per month instead of one giant pile: to find something from November you open one box, and to throw away 2019 you carry out twelve boxes.

## Keep in mind

- Splits a table by a key such as date or region; queries still see one table.
- Partition pruning lets a query skip every partition it does not need.
- Sharding is partitioning across several servers.
- A poorly chosen key gives no speedup or creates a hot partition.

## Go deeper

- [Partition (database) on Wikipedia](https://en.wikipedia.org/wiki/Partition_(database))
- [Table partitioning (PostgreSQL docs)](https://www.postgresql.org/docs/current/ddl-partitioning.html)
- [Shard (database architecture) on Wikipedia](https://en.wikipedia.org/wiki/Shard_(database_architecture))

## Related terms

- [SQL](https://tidbit.legios.com.ar/sql/): The language you use to ask a database questions.
- [Database](https://tidbit.legios.com.ar/database/): An organized store of data that programs can search and update safely.
- [Data lake](https://tidbit.legios.com.ar/data-lake/): Cheap storage where all your data is kept as raw files, to be sorted out later.
- [Parquet](https://tidbit.legios.com.ar/parquet/): A file format that stores tables by column, compressed, so analytics reads far less.
- [Apache Spark](https://tidbit.legios.com.ar/apache-spark/): An open source engine that splits work on huge datasets across many machines.
- [Amazon Athena](https://tidbit.legios.com.ar/amazon-athena/): Run SQL directly on files sitting in S3, with no database to set up.
- [Server](https://tidbit.legios.com.ar/server/): A computer whose job is to answer requests from other computers, all day, every day.
- [Regions and Availability Zones](https://tidbit.legios.com.ar/regions-and-availability-zones/): Where AWS physically lives: cities worldwide, each split into separate data centers.