Aggregate time windows

Example setup

Connect through PGWire to a streaming catalog where you can create tables. Run these examples in a test schema. They use the following source tables:

SQL

CREATE TABLE events (user_id BIGINT, event_type VARCHAR, ts TIMESTAMP);
CREATE TABLE orders (amount DOUBLE, ts TIMESTAMP);
CREATE TABLE trades (price DOUBLE, created_at TIMESTAMP);

Create the views before sending input. Use current timestamps when trying event-time windows, and allow time for a window to close before expecting aggregate results.

CREATE MATERIALIZED VIEW

Materialized views aggregate data over time windows. They require a WITH clause specifying window parameters.

SQL

-- Tumbling window: 1-minute SUM/COUNT aggregation
CREATE MATERIALIZED VIEW sales_per_minute AS
  SELECT SUM(amount) AS total, COUNT(*) AS cnt
  FROM orders
  WITH (window_type='tumbling', window_size='1 minute', timestamp_column='ts');

SQL

-- Sliding window: 5-minute average sliding every 30 seconds
CREATE MATERIALIZED VIEW avg_price_5m AS
  SELECT AVG(price) AS avg_price, COUNT(*) AS cnt
  FROM trades
  WITH (window_type='sliding', window_size='5 minutes',
        slide_interval='30 seconds', timestamp_column='created_at');

Ingestion timestamp mode

If your source data doesn’t have a timestamp column, you can omit timestamp_column to window by ingestion timestamp (__boils_meta_timestamp) instead of event time:

SQL

-- Windows based on when data was ingested by the server
CREATE MATERIALIZED VIEW events_per_minute AS
  SELECT COUNT(*) AS cnt
  FROM events
  WITH (window_type='tumbling', window_size='1 minute');

This is useful for sources without timestamps (Kafka streams, API events) or when ingestion time is the relevant time dimension.

WITH parameters

Parameter	Required	Description
window_type	Yes	'tumbling' or 'sliding'
window_size	Yes	Window duration: '<N> <unit>' where unit is seconds, minutes, hours, or days
timestamp_column	No	Column used for windowing. If omitted, uses server ingestion timestamp (__boils_meta_timestamp)
slide_interval	Sliding only	How often the window advances. Must evenly divide window_size
output_topic	No	Output topic name. Defaults to {view_name}_matview

Output schema

The output topic contains the columns from your SELECT plus two automatic columns:

• window_start — start of the aggregation window (TIMESTAMP)
• window_end — end of the aggregation window (TIMESTAMP)

Supported materialized-view operations

Materialized views support windowed aggregations, GROUP BY, scalar functions and CASE. An existing WHERE clause filters rows within the selected time window. Validate more complex queries with your server release before depending on them.

Dropping materialized views

SQL

DROP MATERIALIZED VIEW sales_per_minute;
DROP MATERIALIZED VIEW IF EXISTS avg_price_5m;

This stops window processing and deregisters the view. The output topic and its data remain.

Reading results and handling failures

Message sequenceWindow output follows closure and processing

Windowed output is not guaranteed exactly once. Reconcile after failures before replaying source input.

A regular view runs when queried. Streaming views transform arriving rows into derived topics; materialized views write results when their windows close. A successful source write does not mean those derived results are already available.

Do not assume materialized-view output triggers every live subscription or derived view. Check the intended workflow before building a chain of dependent outputs. After a failure, reconcile window results before replaying input; exactly-once output is not guaranteed.

Choose the query interface using the compatibility matrix. PGWire and managed DuckDB do not currently promise identical visibility of unpublished recent rows.

window_size must not exceed 80% of hot-tier retention. Keep enough retained data for the windows you intend to calculate.