Partitioning and filter pushdown

A few types of Materialize collections are durably written to storage: materialized views, tables, and sources.

Internally, each collection is stored as a set of runs of data, each of which is sorted and then partitioned up into individual parts, and those parts are written to object storage and fetched only when necessary to satisfy a query. Materialize will also periodically compact the data it stores, to consolidate small parts into larger ones or discard deleted rows.

For materialized views and tables (including read-only tables created from sources), you can use the PARTITION BY option to declare the expected internal ordering of the data. If the data has that ordering, optimizations like filter pushdown can be more effective, which in turn can make queries and other operations more efficient.

Syntax

The option PARTITION BY <column list> declares that a materialized view or table should be partitioned by the listed columns. For example, a table that stores an append-only collection of events may want to partition the data by time:

CREATE TABLE events (event_ts timestamptz, body jsonb)
WITH (
    PARTITION BY (event_ts)
);

This PARTITION BY clause declares that events with similar event_ts timestamps should be stored together.

When multiple columns are specified, rows are partitioned lexicographically. For example, PARTITION BY (event_date, event_time) would partition first by the created date; if many rows have the same event_date, those rows would be partitioned by the event_time column. Durable collections without a PARTITION BY option can be partitioned arbitrarily.

Requirements

Materialize currently imposes some restrictions on the list of columns in the PARTITION BY clause. These restrictions describe the orderings Materialize can provide, and are enforced when you create the object.

• This clause must list a prefix of the columns in the collection. For example:
  • if you’re creating a table that partitions by a single column, that column must be the first column in the table’s schema definition;
  • if you’re creating a table that partitions by two columns, those columns must be the first two columns in the table’s schema definition and listed in the same order.
• Only certain types of columns are supported. This includes:
  • all fixed-width integer types, including smallint, integer, and bigint;
  • date and time types, including date, time, timestamp, timestamptz, and mz_timestamp;
  • string types like text and bytea;
  • boolean and uuid;
  • record types where all fields types are supported.

Filter pushdown

Suppose that our example events table has accumulated years’ worth of data, but we’re running a query that matches only rows from a narrow range of timestamps.

SELECT * FROM events
WHERE event_ts >= TIMESTAMPTZ '2024-10-01' AND event_ts < TIMESTAMPTZ '2024-10-02';

This query returns only rows with similar values for event_ts: timestamps within a single day. If rows with similar event_ts values are stored close together, the rows that pass this filter live in a small subset of parts, and Materialize can skip fetching the rest.

Materialize tracks a small amount of metadata for every part, including the range of possible values for many columns. When it can determine that none of the data in a part will match a filter, it will skip fetching that data from object storage. This optimization is called filter pushdown, and when you’re querying with a selective filter against a large collection, it can save a great deal of time and computation.

Materialize always attempts to apply filter pushdown, but it is most effective when similar rows are stored together. Whether rows are stored together depends on your data and the order in which the data was written. You cannot control this layout with the PARTITION BY option itself. In practice, Materialize currently stores data sorted by the collection’s leading columns, so the order of columns in your schema influences it. The option declares that ordering rather than creating it.

To maximize the effectiveness of filter pushdown, you can:

• Add a filter that only matches a narrow range of values in a single column.
• Filter on a column that appears early in the collection’s column list, and whose values correlate with the order in which rows were written. A timestamp on an append-only collection is a straightforward example, as is an identifier that increases over time (e.g., UUIDv7).

To measure the effectiveness of filter pushdown, use EXPLAIN FILTER PUSHDOWN to see the number of parts and bytes your query would need to fetch.

Filters that consist of arithmetic, date math, and comparisons are generally eligible for pushdown. More complex filters might not be. Note that eligibility is not the same as pruning: a filter can be eligible and still fetch every part, depending on how the data is laid out.

Some common functions, such as casting from a string to a timestamp, can prevent filter pushdown for a query. For similar functions that do allow pushdown, see the pushdown functions documentation.

Examples

These examples create real objects. After you have tried the examples, make sure to drop these objects and spin down any resources you may have created.

The PARTITION BY clauses below declare the ordering each collection expects. Because the option does not change how data is stored, these examples store and fetch the same data without them. The clause still records the expected ordering, and Materialize validates it when you create the object.

Partitioning by timestamp

For timeseries or “event”-type collections, it’s often useful to partition the data by timestamp.

1. First, create a table called events.

   -- Create a table of timestamped events. Note that the `event_ts` column is
   -- first in the column list and in the parition-by clause.
   CREATE TABLE events (
       event_ts timestamptz,
       content text
   ) WITH (
       PARTITION BY (event_ts)
   );
   

2. Insert a few records, one “older” record and one more recent.

   INSERT INTO events VALUES (TIMESTAMPTZ '2024-10-01 12:00:00+00', 'hello');
   INSERT INTO events VALUES (TIMESTAMPTZ '2025-10-01 12:00:00+00', 'world');
   

3. Run a select statement against a narrow range of timestamps. This should return only the more recent of the two rows.

   SELECT * FROM events WHERE event_ts >= TIMESTAMPTZ '2025-01-01';
   

4. To verify that Materialize fetched only the parts that contain data in that range, run an EXPLAIN FILTER PUSHDOWN statement.

   EXPLAIN FILTER PUSHDOWN FOR
   SELECT * FROM events WHERE event_ts >= TIMESTAMPTZ '2025-01-01';
   

If you query a range that no event falls into, you’ll notice that not only does the query return zero rows, but the explain shows that we fetched zero parts.

Partitioning by category

Other datasets don’t have a strong timeseries component, but they do have a clear notion of type or category. For example, suppose you have a collection of music venues spread across the world that you regularly query by a single country.

1. First, create a table called venues, partitioned by country.

   -- Create a table for our venue data.
   -- Once again, the partition column is listed first.
   CREATE TABLE venues (
       country_code text,
       id bigint,
       name text
   ) WITH (
       PARTITION BY (country_code)
   );
   

2. Insert a few records with different country codes.

   INSERT INTO venues VALUES ('US', 1, 'Rock World');
   INSERT INTO venues VALUES ('CA', 2, 'Friendship Cove');
   

3. Query for venues in particular countries.

   SELECT * FROM venues WHERE country_code IN ('US', 'MX');
   

4. Run EXPLAIN FILTER PUSHDOWN to check that we’re filtering out parts that don’t include data that’s relevant to the query.

   EXPLAIN FILTER PUSHDOWN FOR
   SELECT * FROM venues WHERE country_code IN ('US', 'MX');