Data partitioning: how to structure a dataset so queries don’t crawl

Every scraping pipeline eventually hits the same wall. The collection side works fine, jobs run, pages come in, rows land in storage. Then six months in, someone runs a query that used to take two seconds and it takes four minutes. Nothing broke. The dataset just grew, and the storage layout that was fine at ten million rows is not fine at two billion.

Partitioning is the fix, but it’s also one of the easiest things to do badly. Pick the wrong key and you either don’t help the query planner at all, or you create millions of tiny files that make things worse than no partitioning at all. Here’s how it actually works, from the storage layer up.

What partitioning actually does

A partition is just a way of physically splitting a dataset into separate chunks based on the value of one or more columns, and storing enough metadata that a query engine can figure out which chunks it needs to touch before it reads anything.

That last part is the whole point. Without partitioning, a query like “give me everything scraped on August 3rd” has to scan every row in the table (or every file in the bucket) and check the date on each one. With partitioning by date, the engine looks at the partition metadata, sees that August 3rd data lives in exactly one folder or one set of files, and reads only that. This is called partition pruning, and it’s the mechanism that makes partitioning worth doing at all. If your query engine doesn’t support pruning, or your query pattern doesn’t let it prune anything, partitioning buys you nothing.

This applies whether you’re running Postgres with declarative partitioning, or dumping Parquet files into object storage and querying them with something like DuckDB or Athena. The physical implementation differs, but the goal is identical: let the engine skip the parts of the dataset that can’t contain the answer.

Pick the key that matches how you actually query

The single biggest mistake is choosing a partition key based on what feels like a natural grouping instead of what the queries actually filter on.

If every downstream query filters by scrape date, “when did this row land,” then partition by date. If most queries filter by domain, because you’re pulling per-site price histories or checking scrape coverage for one target, then domain is a better key, or a compound key of domain plus date. If you partition by date but every query filters by domain, the engine still has to open every partition and scan for matching rows. You’ve added complexity with no pruning benefit.

For a scraping pipeline specifically, the query patterns tend to fall into a small number of buckets: “what did we collect in this time window,” “what does the history look like for this one target,” and “show me everything from this proxy pool or this job run for debugging.” Whatever key shows up most often in the WHERE clause of your real queries is the one that should drive the partition scheme. Not the one that looks tidiest in a folder listing.

Compound partitioning, date first then domain (or the reverse), is common precisely because a lot of pipelines need both access patterns. The tradeoff is more partitions overall, which brings up the next problem.

The small files problem

Partitioning too finely is the failure mode nobody warns you about until they’ve lived it. If you partition scraped data by date and hour and domain, and you’re running thousands of domains, you can end up with tens of thousands of partitions per day, each holding a handful of rows.

This hurts in two ways. First, most query engines pay a fixed cost per partition just to open it, read its metadata, and decide whether to include it. If a query has to touch ten thousand partitions even after pruning, that per-partition overhead dominates the runtime and eats whatever benefit pruning gave you. Second, if you’re on object storage, a huge number of small files is directly expensive: more list operations, more GET requests, more latency from round trips to the storage backend. A single 500MB file reads faster than five hundred 1MB files covering the same rows, every time.

The general rule that holds across most engines: keep partition file sizes in the tens to low hundreds of megabytes, and avoid partition keys with very high cardinality unless you’re also batching writes so each partition accumulates a reasonable amount of data before it’s sealed. Partitioning by exact timestamp or by a random UUID is almost always wrong for this reason. Partitioning by domain across ten thousand distinct domains, each with light traffic, has the same problem in a different shape.

Compaction is not optional

Streaming or incremental pipelines write small files by nature. A scraper job that lands results every few minutes produces a lot of small partitions before anyone’s had a chance to consolidate them. This is fine as a write pattern. It’s a problem if those small files are also what gets queried.

The standard answer is compaction: a periodic job that reads a partition’s small files, rewrites them as fewer, larger files, and replaces the originals. This is a maintenance cost you have to actually schedule and monitor, not a one-time setup step. A pipeline that writes constantly and never compacts will degrade in exactly the way partitioning was supposed to prevent, just on a longer timescale. If you’re on a table format that supports it (Iceberg, Delta, Hudi), compaction is usually a built-in operation. If you’re managing raw Parquet in a bucket yourself, it’s a job you write and own.

When partitioning makes things worse

Partitioning isn’t free, and it’s worth being honest about when it doesn’t help.

If your dataset is small enough that a full scan already runs in a second or two, partitioning adds file-management overhead for no measurable gain. If your queries are unpredictable, ad hoc exploration where the filter column changes every time, no single partition key will get pruned consistently, and you’re paying the overhead of partition metadata for queries that touch every partition anyway. And if your write pattern is high-frequency with low volume per key, you’re set up for the small-files problem regardless of how sensible the key looks on paper.

A useful gut check before partitioning anything: write down the five queries you actually run most often against this dataset. If four of the five filter on the same column, that’s your partition key. If they don’t overlap at all, partitioning by a single dimension isn’t going to solve your problem, and you’re better off looking at indexing, columnar storage, or just accepting the current scan cost.

A partitioning scheme that holds up for scraped data

For most pipelines pulling data at scale, on either the collection side or the resulting dataset, a scheme that tends to hold up looks like this: partition by date at the day or hour grain depending on volume, with domain or source as a secondary column that’s indexed or sorted within the partition rather than used as its own partition level. Date-first partitioning naturally aligns with how scraped data ages, since old partitions stop changing and can be compacted once and left alone, while the current day’s partition is the only one still receiving writes. Domain sits inside that as a sort key, which speeds up domain-filtered queries without multiplying the partition count by every distinct source you track.

The specific grain, hourly versus daily versus weekly, depends entirely on your write volume. A pipeline collecting a few thousand rows a day should probably partition weekly or it’ll hit the small-files problem immediately. A pipeline collecting tens of millions of rows a day needs hourly or it’ll create partitions too large to prune efficiently within a single day.

None of this is a one-time decision. Query patterns shift as a project grows, and a partition scheme that made sense at launch can become the bottleneck a year later. The fix is the same one that got you here: look at what the queries actually filter on, and adjust the key to match.

If you’re building out the scraping and pipeline side of this and want more on how the collection layer feeds into storage decisions like this, Data Research Tools covers the infrastructure end to end.

Get new guides and videos first — join the Telegram channel.