Partition key: why 20 posts can touch 20 pages

In our simulation on 2026-11-13, fetching one user's latest 20 posts touched 19.99 data pages in arrival order and 1.49 when grouped by user and sorted newest first. A partition key groups the user's rows, while a clustering key orders them within that partition.

Why does arrival order scatter your rows?

The model assumes 8,192-byte pages with no page or row metadata: floor(8,192 / 200) = 40 rows per page. In layout A, rows fill pages in the order they arrive, so each page holds whatever 40 posts came in at that moment, from anyone.

Because posts from different users are interleaved, one user's latest 20 rows usually land on separate pages. Our simulation averaged 19.99 pages per read.

What we ran

A dependency-free Node script builds 2,000,000 posts for 20,000 users across 2025, arriving mixed together, and stores them two ways. Layout A is a row table in arrival order with a B-tree index on (user_id, created_at). Layout B is a wide-column partition: partition key user_id, clustering key created_at descending. It then reads the latest 20 posts for 1,000 random users, cold, and traces every row it decodes back to its page.

Latest 20 posts, average of 1,000 users	Arrival order + index	Partition, newest first
Data pages touched	19.99	1.49
Rows on those pages	799.68	59.52
Rows returned	20	20
Index pages (modeled B-tree)	3.04	2

The touched model pages contained 799.68 rows on average; the query decoded and returned only 20. Counting the modeled index pages too, the totals are 23.03 against 3.49 pages, 6.6x apart.

What do a partition key and a clustering key change?

Wide-column stores such as Apache Cassandra, and Bigtable before it, keep rows grouped by key and sorted inside each group. This CQL schema groups posts by user and orders them by post_id descending (Cassandra CQL data definition):

-- posts_by_user.cql
CREATE TABLE posts_by_user (
  user_id uuid,
  post_id timeuuid,
  body    text,
  PRIMARY KEY ((user_id), post_id)
) WITH CLUSTERING ORDER BY (post_id DESC);

The partition key, user_id, decides which partition holds a row, and the partitioner hashes it to choose the nodes that store it, the same idea as in consistent hashing. The clustering key, post_id, sorts rows inside the partition, here newest first. "Latest 20 posts" becomes: find the partition, read its first 20 rows.

In the simulated file, those 20 rows sit next to each other, so they fill one page, or two when they cross a page boundary. That boundary is why the average is 1.49 and not 1. Cassandra merges data from memtables and relevant SSTables, so this result predicts no physical I/O count (storage engine). The ordering does carry over, though: within each sorted file, the rows you want sit next to each other.

Cassandra's guide says to design tables around the queries you will run, one table per query pattern if needed (data modeling introduction). You give up ad hoc queries across users in return.

Is an index on user_id not enough?

A non-covering secondary index leaves the table rows scattered. The (user_id, created_at) index in layout A pointed straight at the 20 rows, examining exactly 20 index entries, and the read still touched 19.99 data pages, because the matching rows occupied 19.99 distinct pages on average.

A covering index behaves differently. If the index also stores the columns the profile shows, for example (user_id, created_at) INCLUDE (body) in PostgreSQL, the index entries are themselves sorted by user and the query can be answered from them. PostgreSQL still checks row visibility and may fetch heap pages for recently changed rows (index-only scans). The cost is a second copy of those columns on disk and one more structure to update on every insert.

A clustered index gets the same locality for the table itself. InnoDB stores the table in primary key order (MySQL manual), so a primary key of (user_id, created_at, post_id) groups each user's rows the way a partition does on one machine. PostgreSQL's CLUSTER reorders a table once, but later rows go wherever there is space (PostgreSQL CLUSTER). In Cassandra the partition key also decides which nodes own the data.

What happens when one partition gets huge?

The simulation adds one account with 1,000,000 posts. Its latest 20 came from one data page in this run: the partition starts on a page boundary because the preceding 2,000,000 rows exactly fill 50,000 model pages. Its logical payload is 10,000 times an average user's:

Large account: 1,000,000 × 200 = 200,000,000 bytes
Average account: (2,000,000 / 20,000) × 200 = 20,000 bytes
Payload ratio: 200,000,000 / 20,000 = 10,000

The payload calculation excludes storage metadata, indexes, compression and replication. The simulation does not measure what such a partition costs in a real cluster: compaction, repair, and load on the replicas that own it. One partition key maps to one replica set, so every read and write for that account lands on the same nodes, however large the cluster. A feed that fans out from such accounts has the same problem, covered in designing an Instagram-style feed.

How do month buckets work?

Cassandra's guide shows the usual fix: add a time bucket to the partition key (refining a data model). Changing the primary key requires a new table:

-- posts_by_user_month.cql
CREATE TABLE posts_by_user_month (
  user_id    uuid,
  year_month text,      -- '2026-11'
  post_id    timeuuid,
  body       text,
  PRIMARY KEY ((user_id, year_month), post_id)
) WITH CLUSTERING ORDER BY (post_id DESC);

Each month is its own partition, which the hash may place on different nodes. Month buckets limit the time span represented by each partition. Size still depends on posting rate, retention and late writes. Estimate the busiest account's rows per month before choosing the bucket width.

The read changes: to get the latest 20, read this month's partition; if it has fewer than 20 rows, read last month's, and keep stepping back. With 14 posts this month and 6 last month, that is two partition reads. For a user who posted twice in a year, the loop can touch many empty months, so keep a small table of which months have posts. This month's partition for a busy account still takes all of its new writes.

What we saw when we ran it

We ran node labs/sd-24/count-pages-per-profile-read.mjs on our Mac on 2026-11-13, and it finished in 0.3 s. The output has two columns, row table and wide-column, each averaged over 1000 sampled users. For the latest 20 posts, the row table touched 19.99 data pages and the wide-column layout touched 1.49.

Our terminal after the sd-24 run, with page and row counts for both layouts.

The March 2025 read surprised us. The wide-column layout still touched fewer data pages, 1.21 against 8.55, yet it examined 29.22 rows to return 8.55, while the row table examined exactly the 8.55 rows it returned. Newest-first order pays off most for the query it was built for, and a date range read on the same partition can decode rows it then throws away. If your second most common query is a date range, count its rows too before you settle on a layout.

The advice we follow here comes from Cassandra's data modeling introduction: design tables around the queries you will run, one table per query pattern if needed.

The official Apache Cassandra data modeling introduction page.

Limits of this test

• It counts page touches in a fixed layout, not disk reads from Cassandra, ScyllaDB or MySQL. Every query starts cold; real systems cache hot pages and index blocks.
• Layout B models one fully compacted file. A real wide-column store can spread one partition across a memtable and several SSTables until compaction runs.
• Fixed 200-byte rows, 8 KiB pages with no metadata, and evenly spread posting are simplifications. Real users post very unevenly.
• Wall times in the output are in-memory and vary run to run; the page counts are the result.

Our take

When one query dominates, such as a profile showing someone's newest posts, store rows in that query's order: a partition per user sorted newest first, or a clustered primary key in SQL. In this fixed-layout simulation that cut data pages touched 13.4x. The trade is flexibility: Cassandra queries across users often need another query-specific table; SQL can use other indexes on the same table. A heavy account needs a bucket chosen from its real posting rate. For the step before this one, deciding what to keep together in one record, see embed vs reference for an order.

The users, posts and the 1,000,000-post account are synthetic, made from a fixed seed. The layouts are typical designs for teaching, not any company's system, and nothing here describes how a specific social network stores its data.