Release History
Requirements

Introduction

Quick onboard

Deployment

Data Modeling

Connecting

Write data

Migration

Query

Operations and Maintenance

Performance Tuning

Troubleshooting

Reference Guide

SQL Reference

FAQ

mars3_default_brin

mars3_default_brin is a key feature of the MARS3 storage engine. It provides default BRIN index support at the table level. This allows automatic data filtering during sequential scans (SeqScan) without requiring manual index creation.

Usage

Enable mars3_default_brin by specifying the default_brinkeys parameter in the table definition:

CREATE TABLE t
(c1 BIGINT, c2 BIGINT, c3 BIGINT)
USING mars3
WITH (mars3options='default_brinkeys=30');

This parameter takes an integer value (default is 30):

  • 0: Disables default BRIN indexing.
  • -1: Automatically creates default BRIN indexes on all columns that support the <, >, and = operators.
  • N: Automatically creates default BRIN indexes on the first N columns that support the <, >, and = operators.

Use the mars3_brinkeys function to check which columns in a table have BRIN keys.

Example Usage

  1. Create a table named t using the MARS3 storage engine with three BIGINT columns: c1, c2, and c3. Set default_brinkeys=2 to automatically create BRIN indexes on the first two columns.
CREATE TABLE t
    (c1 BIGINT, c2 BIGINT, c3 BIGINT)
    USING mars3
    WITH (mars3options='default_brinkeys=2');
  1. Insert data into table t. Use the generate_series function to generate values from 1 to 1,000,000 and insert them as rows.
INSERT INTO t SELECT i,i,i FROM generate_series(1,1000000) AS i;
  1. Run VACUUM on table t. For MARS3 tables, this moves row-store data into the columnar layer and builds internal BRIN structures.
VACUUM t;
  1. Query the number of rows in table t where c1 is less than 1000.
  • Since default_brinkeys=2, c1 (one of the first two columns) has an automatically created mars3_default_brin index. The query uses this index to accelerate execution.
SELECT COUNT(*) FROM t WHERE c1 < 1000;
  1. Query the number of rows in table t where c3 is less than 1000.
  • Since default_brinkeys=2, c3 (the third column) does not have an automatically created mars3_default_brin index. This query may not use any index and will likely perform slower than the previous one.
SELECT COUNT(*) FROM t WHERE c3 < 1000;

Key Differences

Comparison with mars3_brin

mars3_brin mars3_default_brin
Creation Requires manual CREATE INDEX Created automatically; no manual action needed
Scan support Filters data only during IndexScan Filters data during both IndexScan and SeqScan
Technology version BRIN v2 BRIN v2
Parameterized queries Supports parametric IndexScan Supports parametric SeqScan
← Previous
mars3_brin
Next →
matrixmgr