Cassandra LIMIT and ALLOW FILTERING

LIMIT and ALLOW FILTERING are two CQL clauses that control how many rows a query returns and whether Cassandra permits non-key filtering. Used correctly, LIMIT improves performance. ALLOW FILTERING, on the other hand, should be used with caution because it can trigger expensive cluster-wide scans.

LIMIT

LIMIT caps the number of rows returned by a SELECT query. Cassandra stops reading as soon as it collects the requested number of rows, saving both CPU and network resources.

Basic LIMIT

SELECT * FROM messages
WHERE room_id = 'chat-1'
ORDER BY sent_at DESC
LIMIT 20;

This returns the 20 most recent messages in room chat-1. Cassandra reads just 20 rows from disk and sends them immediately — it does not scan the entire partition.

LIMIT Without a Partition Key

SELECT * FROM customers LIMIT 5;

Without a WHERE clause, Cassandra scans all partitions and returns the first 5 rows it encounters. The result order is not predictable — it depends on which node responds first. Use this only during development.

PER PARTITION LIMIT

PER PARTITION LIMIT returns a fixed number of rows from each partition rather than a total across all partitions. This is useful when each partition represents a logical entity (like a chat room or a user) and you want the top N items per entity.

-- Last 3 messages from every chat room:
SELECT * FROM messages PER PARTITION LIMIT 3;

Partition: room_id='chat-1'    Partition: room_id='chat-2'
  msg1 (newest)                  msg1 (newest)
  msg2                           msg2
  msg3 (stop)                    msg3 (stop)

Combined LIMIT and PER PARTITION LIMIT

-- Top 3 messages from each room, but stop after 100 total rows:
SELECT * FROM messages
PER PARTITION LIMIT 3
LIMIT 100;

Pagination with LIMIT

Cassandra drivers handle server-side paging automatically. When you call a query with a page size in the driver, the driver uses LIMIT internally and tracks a paging state token to fetch the next page. Manual token-based pagination in CQL looks like this:

-- Page 1:
SELECT customer_id, first_name FROM customers LIMIT 20;

-- Page 2 (token continues where page 1 left off):
SELECT customer_id, first_name FROM customers
WHERE token(customer_id) > token([last-uuid-from-page-1])
LIMIT 20;

ALLOW FILTERING

Cassandra requires the partition key in every efficient query. When a WHERE clause filters on a column that is not part of the primary key, Cassandra refuses the query by default and shows an error:

InvalidRequest: Cannot execute this query as it might involve
data filtering and thus may have unpredictable performance.
Use ALLOW FILTERING if you want to execute this query.

Adding ALLOW FILTERING overrides this protection and forces Cassandra to execute the query anyway.

SELECT * FROM customers
WHERE email = 'alice@example.com'
ALLOW FILTERING;

What ALLOW FILTERING Actually Does

ALLOW FILTERING tells Cassandra to fetch all rows that match the partition key filter (or all rows in the table if no partition key is given) and then test each row against the non-key condition in memory. On a small table with 100 rows, this is harmless. On a table with 50 million rows, this reads 50 million rows to find a few matches.

Without partition key + ALLOW FILTERING:

[Node A] → scans all rows → filters by email
[Node B] → scans all rows → filters by email
[Node C] → scans all rows → filters by email
[Coordinator] → merges results → returns to app

Cost: 3 × full table scan

When ALLOW FILTERING Is Acceptable

Situation                                   ALLOW FILTERING OK?
──────────────────────────────────────────────────────────────────
Table has fewer than ~1,000 rows            Yes — cost is trivial
Partition key is specified and partition    Yes — filters within
  is small (few rows)                       one small partition
Large table, no partition key               No — full cluster scan
Frequent production query                   No — redesign the table
One-time data inspection / debug            Yes — run once, not in
                                            production code path

Alternatives to ALLOW FILTERING

For any query that regularly needs a non-key filter, use one of these approaches instead.

Option 1: Secondary Index

CREATE INDEX ON customers (email);

SELECT * FROM customers WHERE email = 'alice@example.com';
-- No ALLOW FILTERING needed

Option 2: SASI Index (for text search)

CREATE CUSTOM INDEX ON customers (email)
  USING 'org.apache.cassandra.index.sasi.SASIIndex';

SELECT * FROM customers WHERE email LIKE '%@example.com';

Option 3: Create a Lookup Table

CREATE TABLE customers_by_email (
  email       TEXT PRIMARY KEY,
  customer_id UUID
);

Insert a row into this table whenever you add a customer. Queries by email become efficient partition key lookups.

Summary

Clause             Purpose                    Best Practice
──────────────────────────────────────────────────────────────────
LIMIT n            Cap total rows returned    Always use in production
PER PARTITION      Cap rows per partition     Great for "top-N per
  LIMIT n                                    entity" patterns
ALLOW FILTERING    Run non-key WHERE clause   Use only for small or
                                             one-time queries; avoid
                                             on large production tables

LIMIT is a free performance win — always set a sensible limit on queries that could return many rows. PER PARTITION LIMIT gives you per-entity pagination without extra application logic. ALLOW FILTERING is a development tool, not a production strategy. Whenever you find yourself reaching for ALLOW FILTERING on a large table, treat it as a signal to redesign the table, add an index, or create a lookup table.

Previous lesson

Back to course

Next lesson