Hot tier in PostgreSQL
Active rows stay in the heap and native indexes for low-latency reads and writes.
Early development · PostgreSQL tiered storage
Keep hot data in PostgreSQL. Query one table.
KoldStore is an open-source tiered-storage extension for tables that grow forever. Hot rows stay in the PostgreSQL heap; older rows flush to compressed Parquet; applications keep querying the same table with normal SQL.
- 89%
- smaller footprint
- 99%
- smaller hot table
- 43×
- faster VACUUM
- Open source
- Apache 2.0
- Built with Rust
- PostgreSQL 15–18
Live query path
Same table. Hot and cold. One query.
Client / ORMApplicationdrivers · SQL · ORMs
Original relationPostgreSQL Tablesame name · same schema
Transparent planKoldMergeScannewest visible row wins
Working setHot RowsPostgreSQL heap + indexes
Historical rowsCold Parquetmanifest → segment files
KoldStore PostgreSQL tiered-storage features
Cold tier in open Parquet
Historical rows flush to filesystem, S3/MinIO, GCS, or Azure Blob — storage you control.
One table, normal SQL
Same table, drivers, and ORMs — KoldMergeScan merges hot and cold tiers transparently.
Adopt incrementally
Enable tiered storage on existing tables with a primary key — no schema redesign.
How it works
Tiered storage on one PostgreSQL table.
- 1
Keep the hot working set in PostgreSQL
Manage a normal heap table; the hot tier keeps active rows on native indexes.
- 2
Flush older rows to the cold Parquet tier
Auto-flush or flush_table moves excess history to compressed storage you control.
- 3
Query one table across both tiers
KoldMergeScan merges hot heap rows and cold Parquet through the original relation.
Start with SQL
Enable KoldStore with ALTER TABLE.
Create a normal heap table with a primary key, point it at registered storage, and keep querying the same relation after flush.
Open the complete quickstartCREATE EXTENSION IF NOT EXISTS koldstore; SELECT koldstore.register_storage( name => 'local-dev', storage_type => 'filesystem', base_path => '/tmp/koldstore-demo', credentials => '{}'::jsonb, config => '{}'::jsonb); CREATE TABLE messages ( id bigint PRIMARY KEY, body text NOT NULL, created_at timestamptz NOT NULL DEFAULT now()); ALTER TABLE messages SET ( koldstore_enabled = true, koldstore_storage = 'local-dev', koldstore_hot_row_limit = 1000, koldstore_min_flush_rows = 1, koldstore_max_rows_per_file = 1000); INSERT INTO messages (id, body)SELECT gs, 'row ' || gs FROM generate_series(1, 1012) AS gs; SELECT koldstore.flush_table(table_name => 'messages'::regclass); SELECT count(*) FROM messages; -- still 1012 via KoldMergeScanBenchmarks
Storage wins after flush.
KoldStore is a storage lifecycle tool for tiered PostgreSQL tables — not a universal query accelerator. After flush, the hot working set stays small; cold history lives in zstd Parquet outside the primary heap.
89% smaller total footprint
99% smaller PostgreSQL hot table
43× faster VACUUM FULL
| Result | Before → after flush | Tradeoff |
|---|---|---|
| Total footprint (hot + cold) | 5.85 GiB → 671 MiB | 89% smaller |
| └ hot in PostgreSQL (heap + __cl) | 5.85 GiB → 72 MiB | 99% smaller |
| └ cold Parquet | — → 599 MiB | outside the database |
| Indexes (hot + __cl) | 415 MiB → 11.5 MiB | 97% smaller |
| VACUUM (FULL, ANALYZE) | 149.29 s → 3.44 s | 43× faster |
DML and query path (contextual)
Foreground DML and PK lookups are not guaranteed to improve. The figures below come from a historical dirty-tree single sample on the 10M-row storage run and are shown for workload context only — treat them as indicative, not as a release claim. Cold-path lookups can be slower than heap-only PostgreSQL; async mode reports mirror catch-up as separate work.
| Operation | PostgreSQL only | KoldStore async | Trade-off |
|---|---|---|---|
| INSERT | 94,302 ops/s | 107,030 ops/s | ≈ within noise — not a product win |
| UPDATE | 69,239 ops/s | 52,446 ops/s | historical single sample; 24% lower |
| DELETE | 119,350 ops/s | 179,882 ops/s | single-sample — do not claim faster |
| Hot-only PK lookup | 1,800 ops/s | 1,825 ops/s | ≈ same |
| Hot+cold PK lookup | 1,793 ops/s | 1,529 ops/s | 15% slower |
| Cold-only PK lookup | 1,762 ops/s | 1,242 ops/s | 30% slower |
Sample: 10M wide rows, hot_row_limit = 100000 (local PG16.13, 2026-07-20). Managed sizes include the hot heap and change-log mirror. Storage wins are the primary result; DML/query rows are a single-sample snapshot pending guarded repeated publication. Results vary by schema, hardware, storage backend, and workload.
Use cases
For tables that keep growing.
AI memory
Keep model outputs and conversation history in a hot/cold tiered table without rewriting retrieval SQL.
Messages & chat
Keep recent messages hot in PostgreSQL while older threads stay queryable from cold Parquet.
Audit logs
Retain append-heavy event history in open Parquet instead of growing the heap forever.
Events & IoT
Store notifications, activity, and telemetry while indexes, VACUUM, and backups stay manageable.
KoldStore by KalamDB
Keep more data. Keep PostgreSQL.
No replacement database. No proprietary archive format. No application query rewrite.