Getting started/Overview

Move cold rows out of Postgres without leaving Postgres.

pg_strata is an open-source extension that ages rows out of hot tables into columnar files on object storage, then answers ordinary SQL against both tiers as one table. No forked server, no second database, no application changes.

Postgres 15, 16 and 17 PostgreSQL licence S3, Google Cloud Storage, Azure Blob and local disk

Install in two minutes Read the quickstart

$ sudo apt install postgresql-17-pg-strata
$ psql -c "create extension pg_strata; select strata.version();"
 version
---------
 0.9.2

$ pgxn install pg_strata
$ psql -c "create extension pg_strata; select strata.version();"
 version
---------
 0.9.2

$ docker run -d -p 5432:5432 ghcr.io/pgstrata/postgres:17-strata0.9
$ psql postgres://postgres@localhost -c "create extension pg_strata; select strata.version();"
 version
---------
 0.9.2

How tiering works

Skip to the quickstart →

You attach a tier policy to a table. The policy names a predicate that decides which rows are cold, usually an age on a timestamp column, and a storage target. A background worker runs the policy on a schedule, writes matching rows to Parquet files on the target, verifies the checksum, then deletes them from the heap in one transaction.

Reads never change. The planner sees a partitioned table with one hot partition backed by the heap and one cold partition backed by a foreign scan. Predicates on the age column are pushed down to file-level statistics, so a query for last week's rows never opens a file from last year.

hot tier: heap

events

Row store. Indexes, updates, and everything you already do.

policy: strata.attach

occurred_at < now() - 30 days

Runs every hour. Batches of 50,000 rows. Verified before delete.

cold tier: s3://analytics/events/

2026/08/events-0412.parquet

Columnar, zstd compressed, min/max stats per file. Still one table in SQL.

Install

Supported versions →

pg_strata ships as a native extension with one shared library. Install the package for your Postgres major version, preload the library, restart, then create the extension in each database that needs it.

Install the package

Debian and Ubuntu packages come from the community apt repository. The pgxn client builds from source on any platform with pg_config on the path.

$ sudo apt install postgresql-17-pg-strata

$ brew install pgstrata/tap/pg_strata

$ git clone https://github.com/pgstrata/pg_strata.git && cd pg_strata
$ make && sudo make install   # needs libarrow-dev and libcurl

Preload the library and restart

The tiering worker starts with the server, so the library has to be in shared_preload_libraries.

postgresql.conf

shared_preload_libraries = 'pg_strata'
strata.worker_interval   = '1h'
strata.batch_size        = 50000

Create the extension

Run once per database. It creates the strata schema, the catalog views, and the background worker registration.

psql

create extension pg_strata;
select strata.version();
 version
---------
 0.9.2

Quickstart

Full function reference →

Ten minutes from an empty database to a table that keeps its last thirty days on disk and everything older in object storage. All of it is SQL.

Register a storage target

Targets are named once and reused across tables. Credentials come from the server environment or an instance role, never from SQL.

sql

select strata.create_target(
  'analytics',
  url    => 's3://acme-analytics/events/',
  region => 'eu-west-1',
  codec  => 'zstd'
);

Attach a tier policy to a table

The predicate is any boolean expression over the table's columns. Rows for which it is true are eligible to move. partition_by decides the file layout on the target.

sql

create table events (
  id           bigint generated always as identity primary key,
  account_id   bigint not null,
  kind         text   not null,
  payload      jsonb,
  occurred_at  timestamptz not null default now()
);

select strata.attach(
  'events',
  target       => 'analytics',
  cold_when    => 'occurred_at < now() - interval ''30 days''',
  partition_by => 'date_trunc(''month'', occurred_at)'
);

Tip. Attach before the table grows. Moving a billion existing rows works, but the first run will take as long as a full table scan.

Run the policy and check the result

The worker runs on its own schedule. Call strata.run to move rows now and see what happened.

sql

select * from strata.run('events');
 moved_rows | files_written | bytes_hot_freed | duration
------------+---------------+-----------------+----------
    2140533 |            43 |       1.8 GB    | 00:02:11

select tier, count(*) from strata.explain('events') group by 1;
 tier | count
------+---------
 hot  |  912004
 cold | 2140533

Query as if nothing moved

Filters on the tiering column are pushed down to file statistics. This query touches the heap and exactly one Parquet file.

sql

select kind, count(*)
from   events
where  occurred_at >= '2026-07-01'
and    occurred_at <  '2026-08-15'
group by kind
order by 2 desc;
    kind    |  count
------------+--------
 page_view  | 401288
 checkout   |  38127
 signup     |   9410

-- explain shows both tiers in one plan
--   Append
--     Seq Scan on events_hot        (rows=912004)
--     Strata Scan on events_cold    (files=1 of 43, skipped=42)

SQL functions

Configuration →

Everything lives in the strata schema. Functions that change state need the strata_admin role or table ownership; the read-only ones need only select on the table.

Signature	What it does
`strata.create_target(name, url, region, codec)`	Registers an object storage location. Returns the target id.
`strata.attach(table, target, cold_when, partition_by)`	Attaches a tier policy. The table becomes a two-tier partitioned table in place, no rewrite.
`strata.detach(table, recall boolean)`	Removes the policy. With `recall => true` every cold row is read back into the heap first.
`strata.run(table)`	Runs the policy now instead of waiting for the worker. Returns a summary row.
`strata.recall(table, where text)`	Moves matching cold rows back to the heap, for backfills or a regulatory hold.
`strata.explain(table)`	One row per file and per hot partition with row counts, byte sizes and min/max of the tiering column.
`strata.verify(table)`	Re-reads every cold file and checks its footer checksum against the catalog.
`strata.version()`	Installed extension version as text.

Catalog views

View	Columns of note
`strata.policies`	`table_name`, `target`, `cold_when`, `last_run_at`, `next_run_at`
`strata.files`	`path`, `rows`, `bytes`, `min_key`, `max_key`, `checksum`
`strata.worker_activity`	`state`, `table_name`, `rows_moved`, `started_at`

Configuration

Compatibility →

Server settings, set in postgresql.conf or with alter system. Everything except the preload takes effect on reload.

Setting	Default	Notes
`strata.worker_interval`	`1h`	How often the worker checks each policy. Minimum 1 minute.
`strata.batch_size`	`50000`	Rows per file write and delete transaction. Larger batches mean fewer files and longer locks on the hot partition.
`strata.max_file_bytes`	`256MB`	Files are closed and a new one opened past this size, regardless of batch.
`strata.verify_before_delete`	`on`	Read the written file back and compare checksums before removing rows from the heap. Turn off only on local disk targets.
`strata.cold_scan_workers`	`4`	Parallel file readers per cold scan. Bounded by `max_worker_processes`.
`strata.cache_dir`	`pg_strata_cache`	Local directory for recently read cold files. Relative paths are under the data directory.
`strata.cache_max_bytes`	`4GB`	Least recently used eviction once the cache passes this size.

Compatibility and licence

Changelog →

Supported versions

Each minor release is tested against the three most recent Postgres majors on Linux x86_64 and arm64, and on macOS for development.

pg_strata	Postgres 15	16	17	18
`0.9.x`	yes	yes	yes	beta
`0.8.x`	yes	yes	yes	no
`0.7.x`	yes	yes	no	no

Storage backends: Amazon S3 and any S3-compatible service, Google Cloud Storage, Azure Blob Storage, and a local directory for testing.

Licence

pg_strata is released under the PostgreSQL licence, the same permissive licence as the server. You can use it, modify it and redistribute it, commercially or not, with attribution.

Copyright (c) 2024-2026, the pg_strata authors Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph appear in all copies.

Trademark note: Postgres and PostgreSQL are trademarks of the PostgreSQL Community Association. pg_strata is a community project and not affiliated with the association.

Changelog

All releases on GitHub →

v0.9.2 2 September 2026

Faster cold scans and a recall function

strata.recall moves cold rows back into the heap by predicate. Useful for backfills and legal holds.
Cold scans now read Parquet row groups in parallel; strata.cold_scan_workers controls the fan-out. Typical wide scans are 2 to 3 times faster.
Min/max statistics are kept for every column, not only the tiering column, so filters on account_id also skip files.
Fixed a crash when a policy predicate referenced a dropped column.

v0.9.0 14 July 2026

Postgres 17 support and Azure Blob backend

Builds and passes the full suite on Postgres 17. Postgres 14 is no longer supported.
New az:// target scheme for Azure Blob Storage, authenticated with a managed identity or a connection string in the environment.
The worker now writes files before it takes the delete lock, shrinking lock time on the hot partition to milliseconds.
Breaking: strata.attach arguments are keyword-only. Positional calls from 0.8 fail with a clear error.

v0.8.4 28 April 2026

Verification on by default

strata.verify_before_delete now defaults to on. Every written file is read back and checksummed before rows leave the heap.
Added strata.verify(table) for auditing existing cold files.
Local disk targets accept relative paths under the data directory.