Partitioned materialized views

Extensions

Run the following SQL statement to install the polar_ivm extension:

CREATE EXTENSION IF NOT EXISTS polar_ivm;

Partition-type requirements

Defining-query requirements

• Partition key must appear in GROUP BY: for an aggregate query, the partition key (or its rollup expression) must be listed in the GROUP BY clause.

• Partitioned table cannot appear in a subquery: the partitioned table must not appear inside a WHERE subquery, a FROM subquery, or a CTE.

• Partitioned table cannot be on the nullable side of a JOIN: for a LEFT JOIN, the partitioned table must be on the left; for a RIGHT JOIN, it must be on the right.

• Partitioned table cannot be referenced more than once: the same partitioned table must not appear in the query multiple times.

• Other materialized views cannot be referenced: the defining query must not reference any existing materialized view.

• Volatile functions are not allowed: non-immutable functions such as CURRENT_DATE or random() must not be used.

Incremental refresh mechanism

Partitioned materialized views achieve intelligent incremental refresh through version tracking:

Create a partitioned materialized view

Call the polar_matview.create_partition_mv stored procedure to create a partitioned materialized view:

CALL polar_matview.create_partition_mv(
  mv_schema        => 'public',
  mv_name          => 'my_mv',
  ref_partition_table => 'public.orders'::regclass::oid,
  view_def         => 'SELECT order_date, COUNT(*) AS cnt FROM public.orders GROUP BY order_date',
  use_imci         => FALSE
);

Parameter description:

Alignment detection: the system determines the alignment mode from the query in view_def. If the partition-key expression in GROUP BY matches the source partition key exactly, proportional alignment is used. If GROUP BY uses an IMMUTABLE aggregate expression such as LEFT(col, 6), rollup alignment is detected automatically.

Refresh a partitioned materialized view

Call the polar_matview.refresh_partition_mv stored procedure to refresh a partitioned materialized view:

CALL polar_matview.refresh_partition_mv(
  mv_schema     => 'public',
  mv_name       => 'my_mv',
  use_imci      => FALSE,
  force_refresh => FALSE
);

Parameter description:

Refresh behavior:

• By default, a refresh syncs any partition structure changes on the source table (added or dropped partitions) and then only refreshes partitions whose data version has changed.

• When force_refresh=TRUE is set, the version check is skipped and all partitions are refreshed.

Drop a partitioned materialized view

Call the polar_matview.drop_partition_mv stored procedure to drop a partitioned materialized view together with all its child partitioned materialized views, and to clean up the related metadata:

CALL polar_matview.drop_partition_mv(
  mv_schema => 'public',
  mv_name   => 'my_mv'
);

Parameter description:

Proportional alignment example

The source table is partitioned by day and the materialized view is also partitioned by day, so source partitions and materialized-view partitions correspond one to one.

-- Create the source partitioned table (RANGE partitioned on the single column order_date)
CREATE TABLE orders (
  order_id   BIGSERIAL,
  order_date DATE NOT NULL,
  amount     NUMERIC(12, 2)
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_20260101 PARTITION OF orders
  FOR VALUES FROM ('2026-01-01') TO ('2026-01-02');
CREATE TABLE orders_20260102 PARTITION OF orders
  FOR VALUES FROM ('2026-01-02') TO ('2026-01-03');

-- Create a proportionally aligned partitioned materialized view: GROUP BY uses the partition key order_date itself
CALL polar_matview.create_partition_mv(
  mv_schema        => 'public',
  mv_name          => 'orders_daily_mv',
  ref_partition_table => 'public.orders'::regclass::oid,
  view_def         => 'SELECT order_date, COUNT(*) AS cnt, SUM(amount) AS total FROM public.orders GROUP BY order_date',
  use_imci         => FALSE
);

-- Refresh the partitioned materialized view (only partitions with changed data are refreshed)
CALL polar_matview.refresh_partition_mv(
  mv_schema => 'public',
  mv_name   => 'orders_daily_mv'
);

Rollup alignment example

The source table is partitioned at a fine granularity while the materialized view aggregates at a coarser granularity, so multiple source partitions merge into a single materialized-view partition.

Example: TEXT partition key with LEFT() rollup (daily source partitions to monthly materialized view)

-- ========== 1. Create the daily-partitioned source table (partition key is TEXT in 'YYYYMMDD' format) ==========
DROP SCHEMA IF EXISTS report CASCADE;
CREATE SCHEMA report;

CREATE TABLE report.daily_data (
  log_date TEXT NOT NULL,   -- Format: 'YYYYMMDD', for example, '20240105'
  value    INTEGER
) PARTITION BY RANGE (log_date);

-- Each source partition corresponds to one day
CREATE TABLE report.daily_data_20240105 PARTITION OF report.daily_data
  FOR VALUES FROM ('20240105') TO ('20240106');
CREATE TABLE report.daily_data_20240115 PARTITION OF report.daily_data
  FOR VALUES FROM ('20240115') TO ('20240116');
CREATE TABLE report.daily_data_20240125 PARTITION OF report.daily_data
  FOR VALUES FROM ('20240125') TO ('20240126');
CREATE TABLE report.daily_data_20240205 PARTITION OF report.daily_data
  FOR VALUES FROM ('20240205') TO ('20240206');
CREATE TABLE report.daily_data_20240215 PARTITION OF report.daily_data
  FOR VALUES FROM ('20240215') TO ('20240216');
CREATE TABLE report.daily_data_20240305 PARTITION OF report.daily_data
  FOR VALUES FROM ('20240305') TO ('20240306');

INSERT INTO report.daily_data VALUES
  ('20240105', 20), ('20240115', 20), ('20240125', 20),  -- 2024-01 total 60
  ('20240205', 40), ('20240215', 50),                     -- 2024-02 total 90
  ('20240305', 60);                                        -- 2024-03 total 60

-- ========== 2. Create the monthly rollup materialized view ==========
-- GROUP BY uses LEFT(log_date, 6) to roll the partition key up to year-month; the system detects rollup alignment automatically
-- The 6 daily source partitions are merged into 3 monthly materialized-view partitions
CALL polar_matview.create_partition_mv(
  mv_schema        => 'report',
  mv_name          => 'mv_monthly_summary',
  ref_partition_table => 'report.daily_data'::regclass::oid,
  view_def         => 'SELECT LEFT(log_date, 6) AS month, SUM(value) AS total FROM report.daily_data GROUP BY LEFT(log_date, 6)',
  create_no_data   => TRUE
);

CALL polar_matview.refresh_partition_mv(
  mv_schema => 'report',
  mv_name   => 'mv_monthly_summary'
);

-- ========== 3. Query the result ==========
SELECT * FROM report.mv_monthly_summary ORDER BY month;
--  month  | total
-- --------+-------
--  202401 |    60   -- merged from three daily partitions 20240105/20240115/20240125
--  202402 |    90   -- merged from two daily partitions 20240205/20240215
--  202403 |    60   -- corresponds to one daily partition 20240305

-- ========== 4. Incremental refresh (only partitions with changed data are refreshed) ==========
INSERT INTO report.daily_data VALUES ('20240215', 10);
CALL polar_matview.refresh_partition_mv(
  mv_schema => 'report',
  mv_name   => 'mv_monthly_summary'
);
-- Version tracking detects that only the 2024-02 rollup partition (which contains 20240215) needs to be refreshed;
-- the 2024-01 and 2024-03 partitions remain unchanged and are skipped

-- ========== 5. Refresh again after adding a new daily partition ==========
CREATE TABLE report.daily_data_20240315 PARTITION OF report.daily_data
  FOR VALUES FROM ('20240315') TO ('20240316');
INSERT INTO report.daily_data VALUES ('20240315', 30);

CALL polar_matview.refresh_partition_mv(
  mv_schema => 'report',
  mv_name   => 'mv_monthly_summary'
);
-- The system syncs the newly added partition structure from the source table automatically and rolls the 20240315 data up into the 2024-03 partition
SELECT * FROM report.mv_monthly_summary ORDER BY month;
--  month  | total
-- --------+-------
--  202401 |    60
--  202402 |   100   -- becomes 100 after appending the 10 for 20240215
--  202403 |    90   -- becomes 90 after adding the 30 for 20240315