PostgreSQL BRIN Index

PostgreSQL BRIN Index is a SQL data type in the PostgreSQL Specific category. Block Range INdex (BRIN) is a lightweight index for large, naturally-ordered tables. Much smaller than B-tree. The syntax is CREATE INDEX idx_brin ON table USING BRIN(column);. It returns bRIN index. A typical example: CREATE TABLE sensor_data ( reading_time TIMESTAMPTZ, value DECIMAL ); -- Insert many rows with sequential timestamps: INSERT INTO sensor_data SELECT generate_series('2026-01-01'::timestamptz, '2026-06-01'::timestamptz, '1 minute'::interval), random() * 100; -- BRIN index (very small, ~few KB): CREATE INDEX idx_sensor_brin ON sensor_data USING BRIN(reading_time); -- vs B-tree index (much larger, ~MB): CREATE INDEX idx_sensor_btree ON sensor_data(reading_time); -- BRIN is great for: -- - Data that is inserted in order -- - Range queries… A close relative is SERIAL, which postgreSQL auto-increment type. Creates an INTEGER column with a sequence. Equivalent to AUTO_INCREMENT in MySQL. A close relative is RETURNING, which postgreSQL extension that returns values from INSERT/UPDATE/DELETE statements (like OUTPUT in SQL Server).