Brief — derivative summary

PgSeries — "TimescaleDB lite", works everywhere

pgseries · Version v0.1 · Published 2026-05-09T13:00:00Z · canonical SPEC.md →

Summary, not the spec. Skim this for shape, architecture, scope, risks, decisions, and open questions in 5–10 minutes; consult SPEC.md for the full text.

01Goal #

PgSeries delivers TimescaleDB-shape time-series ergonomics on any Postgres 17+ — managed, self-hosted, or laptop — using only pure SQL and PL/pgSQL. The single-file install gives users hypertables, continuous aggregates, columnar compression, and retention policies under the same names and call shapes TimescaleDB users already know, trading the ratio of C-extension columnstores for the reach of "runs anywhere Postgres runs".

Ships in baby steps: v0.1 is just managed time partitioning; each subsequent release adds one self-contained capability. The headline 10 billion rows with 2–4× compression target lands at v0.6.

Time-series sibling of PgQue — same install model, same Apache-2.0 license, same anti-extension posture. PgQue's PgQ engine is embedded for the snapshot/tick semantics that continuous aggregates and the job scheduler need.

02Headline numbers #

Postgres versions	17 · 18	no 16, no <16
License	Apache-2.0	no TSL carve-outs
Scale target (v0.6)	10¹⁰ rows	single series table
Compression	2–4×	mixed metric workloads
Default chunks	365	one per day · 1-year retention
Rows per chunk	~2.7 × 10⁷	at the 10B-row target
Per-row footprint	~150 B	narrow · few columns · append-only · no bloat
Steady-state ingest (avg)	~317 rows/sec	10¹⁰ ÷ 1 year
Scale-bench ingest (avg)	~3 800 rows/sec	10¹⁰ ÷ 30 days
Peak, single writer	≥10 K rows/sec	narrow · prepared · batched ~100/INSERT · async commit
Peak, parallel writers	≥100 K rows/sec	32–64 conns · batched · async commit · 16+ vCPU
Peak, COPY / binary	≥250 K rows/sec · target ≥1 M	4+ streams (1 M target at 16+) · batch loads
Raw heap + indexes	~1.5 TiB	at 150 B/row · ~0.7–3 TiB schema-dep · pre-bloat
Compressed (2–4×)	~400–800 GiB	typical
Per-chunk segmentby cap	~10⁴	beyond drops to ~1.5×
Reference benchmark	local NVMe	~128 GiB RAM · PG 18

Caveat on the numbers above. Sized for narrow time-series tables (a handful of columns) and append-only ingest. Bloat from sustained UPDATE/DELETE, pinned xmin horizons, or autovacuum lag can double raw footprint. Wider schemas, jsonb tags, or extra secondary indexes can multiply by 2–5×. Treat the schema-dependent range as a soft band, not a ceiling.

Ingest is a first-class concern. PgSeries must not be the bottleneck. AFTER STATEMENT triggers fire once per statement (not per row); no per-row PL/pgSQL on the hot path; no triggers on leaf partitions. A control run against a hand-rolled partitioned table without PgSeries must come within 5 % on the same hardware. Compression (v0.6) runs out-of-band against cold chunks — the hot ingest path stays within 5 % of v0.1 with compression scheduled.

Per-column-type compression target. integer 5–8× · timestamp 6–10× · low-cardinality text 10–20× · float64 with lossy requantization 3–5× · float64 raw 1.5–2.5×.

03Release stages #

Baby steps. Each version is a tagged release with its own bundle and acceptance set; each adds one capability.

v0.1	Time partitioning	`create_series_table`, `precreate_chunks`, `move_default`, `show_chunks` / `drop_chunks` (manual), chunk-lease state machine, three roles. External scheduler required.
v0.2	Retention + scheduler + space partitioning	Embed PgQue. `system_jobs` runner; `add_retention_policy`; job control API. `add_dimension(table, col, type, n)` ships here (pulled from v1.0 — TS's biggest insert-speed lever). `pgseries-parallel-copy` chunk-affinity COPY loader: ≥1 M rows/sec aggregate.
v0.3	Caggs (materialized)	`add_continuous_aggregate`, `time_bucket()`, `refresh_continuous_aggregate(start, end)`. AFTER STATEMENT triggers + PgQue tick + data-time log. Backdated DML correct.
v0.4	Real-time aggregation	Partial-form storage; cagg view = `finalize_agg(combine_agg(…))` over materialized + on-the-fly past watermark. v0.3 caggs auto-rebuild. Select-list validation.
v0.5	Hierarchical + helpers	cagg-on-cagg (fixed + calendar buckets), `time_bucket_gapfill()` (single-group-key), `first()` / `last()` with combinefuncs.
v0.6	Columnar compression	`add_compression_policy`, `_compressed` siblings, FOR + dict + null bitmap + lz4, BRIN `minmax_multi_ops`, LATERAL read view, pre-CHECK'd ATTACH swap. Headline 10¹⁰-row scale benchmark blocks here.
v0.7	Late-write staging	`add_recompression_policy`; chunk heap reused as staging; modes `staging` (default) / `error`. `ON CONFLICT` on hot path. No `auto_decompress`.
v0.8	Reorder + obs + hll	`add_reorder_policy` (`CLUSTER`), `hll`, `add_index` (auto-applies to new chunks), full `pgseries_information` surface.
v1.0	API stability + GA	All v0.x APIs frozen; deprecation shims dropped; full doc set; the "safe to depend on" mark. No new headline feature.

Beyond v1.0. DuckDB / Apache Arrow as opt-in acceleration on installs that allow extensions; pure-SQL t-digest; multi-group time_bucket_gapfill() with locf/interpolate; remaining hyperfunctions (counter_agg, time_weight, ASOF, full percentile_agg).

04Architecture #

Seven interlocking pieces describing the v1.0 endpoint. The hot write path stays inside PG's own row-routing; everything PgSeries-specific runs as a PgQue-scheduled job outside the writer's transaction. Each piece is tagged with the version that introduces it.

Time partitioning [v0.1]

Native PG range partitioning. precreate_chunks() stays a week ahead. Misses fall to DEFAULT; move_default() drains it. v0.1 ships these as plain functions — wire to pg_cron or any external scheduler.

Continuous aggregates [v0.3 materialized · v0.4 partial-form · v0.5 hierarchical]

Two structures, not one. PgQue tick = safe-batch boundary; data-time log = per-bucket dirty set. Backdated UPDATE/DELETE on a 2022 row correctly re-materializes the 2022 bucket. Partial-form storage from v0.4 makes avg/stddev/percentile_* correct around the watermark.

Compression [v0.6]

Cold chunks roll into a parallel _compressed sibling — segmentby + orderby + parallel typed arrays in TOAST, BRIN minmax_multi_ops on sidecar stats columns. Not a real columnstore (only a C extension can give you that); pragmatic 2–4× on mixed metric workloads. v0.7 adds the recompression policy that merges staging back automatically.

Read path [v0.6]

Reads union the surviving heap with the compressed sibling via LATERAL after a filtered subquery — quals land on partition pruning + BRIN before any unnest fires. A pgTAP plan-shape test asserts both in CI.

Embedded PgQue [v0.2]

PgQue's pgque-core ships vendored at pgseries/_pgque/ in schema pgseries_pgque. Three uses across the staging plan: cagg invalidation queue (v0.3), system_jobs runner (v0.2), staging→recompression signaling (v0.7). Per-job advisory lock prevents overlapping runs.

Space partitioning [catalog v0.1 · API v0.2]

Hash sub-partitioning is TimescaleDB's biggest single-node insert-speed lever — without it, the current time-range chunk is the write hotspot. Catalog reserves the shape from v0.1; add_dimension() ships at v0.2. After v0.2, every series table's chunk identity is a tuple: (time_lower, time_upper, space_partition_index). Compression, retention, caggs, and the rest treat chunks as a *set*, so each later feature picks up multi-dim without rework.

Job scheduling [v0.2]

No PgSeries-specific scheduler. PgQue's ticker model: pg_cron, pg_timetable, or any external loop calling pgque.ticker(). v0.1 functions are callable directly and become PgQue-scheduled jobs at v0.2.

05Decisions #

Eight architectural decisions, in order.

Embed PgQue, don't depend on it.single-file, single-transaction install on any provider; no "install pgque first" coordination.
DEFAULT-partition stash, not a BEFORE INSERT trigger that does DDL.ATTACH PARTITION conflicts with the parent's running INSERT lock; tuple routing is resolved before BEFORE fires; on a multi-row COPY only the first row could possibly succeed mid-statement.
PgQue tick + data-time invalidation log, two structures.tick alone is monotonic in transaction time, not data time. Backdated DML would silently drift a tick-only design.
Cagg storage is partial-form by default (from v0.4).UNION of finalized values + raw rows produces wrong results for avg, stddev, percentile_*, DISTINCT aggregates around the watermark.
Two siblings per chunk, not three.three-way UNION ALL exposes three relations to the planner with separate stats; chunk exclusion doesn't cross the union boundary. Two branches keep the planner happy.
Pre-installed CHECK constraint before ATTACH.ATTACH otherwise scans the new child to validate the partition constraint; on a non-trivial compressed sibling that blows past a 1s lock_timeout, leaving the parent missing a range.
BRIN minmax_multi_ops, not plain minmax_ops.heap row order on the compressed sibling is segmentby-then-orderby, not time-monotonic; plain minmax widens to whole-chunk ranges. Multi-minmax keeps disjoint intervals.
PG 17 floor.greenfield projects can pick a modern PG; PgSeries leans on PG 17 partitioning ergonomics for simpler code.

06Open questions #

Tracked per version, so a release isn't blocked on a question that doesn't apply.

v0.1: table abstraction name (series_table vs hypertable — trademark check); mover lag SLA without bundled scheduler; default chunk size (1 day vs row-based vs adaptive).
v0.2: embedded-PgQue schema collision (is pgseries_pgque distinctive enough?); pgseries_information view names — mirror or differentiate?
v0.4: when (if ever) is materialized_only the better default? Cagg select-list shapes — which can be lifted in v0.5+?
v0.6: segment row count default (1 000 to start); lossy float requantization — opt-in per column or per policy?
v0.2: add_dimension API shape — hash only, or hash + range? pgseries.tune() apply mode: which GUCs to refuse on managed PG? pgseries-parallel-copy packaging — Python script vs Bash wrapper vs SQL function?
v1.0: which deprecation shims to drop.
Beyond v1.0: DuckDB / Apache-Arrow opt-in acceleration on installs that allow extensions.

07Risks & failure modes #

Past 10¹⁰ rows on a single instance, two things break first.

Cagg refresh latency on dense backfills. Invalidation log gets dense; tunable via coalesce_interval and the refresh window.
PL/pgSQL compression throughput per chunk. Group-by-segmentby costs O(rows/chunk). 27M rows/chunk benchmarks in minutes; >10⁸ rows/chunk would not. add_dimension (v0.2) is the escape hatch — hash sub-partitions reduce rows/chunk.

Watch-outs at v0.1's scale.

Segmentby cardinality > ~10⁴/chunk collapses ratio to ~1.5×. add_compression_policy() warns at 50 000.
Float-heavy workloads compress 1.5–2.5× without lossy requantization. Bit-level codecs (Gorilla) are not feasible in PL/pgSQL.
Direct-to-leaf-partition writes would silently break cagg invalidation. Closed at v0.3 by revoking direct-child INSERT.
Plan-cache invalidation after a compression swap — pooled clients with prepared statements replan on first use.
FK cascades into the source table don't fire statement triggers. Rare in time-series schemas; deferred indefinitely.

08Feature mapping #

Every TimescaleDB surface, with its PgSeries equivalent and the version it lands in. Three buckets — supported, partial, out of scope.

Supported23 mappings

Hypertables	Series tables (range partitioning + DEFAULT-stash + mover)	v0.1
Chunks	One PG partition per chunk	v0.1
`show_chunks` / `drop_chunks`	Same names; manual in v0.1, scheduled in v0.2	v0.1
Roles	`pgseries_reader`, `pgseries_writer`, `pgseries_admin`	v0.1
Retention policies	`add_retention_policy` (refuses chunks overlapping cagg refresh windows from v0.3)	v0.2
Job scheduler	PgQue ticker (`pg_cron` / `pg_timetable` / external); per-job advisory lock	v0.2
Job control	`alter_job`, `pause_job`, `resume_job`, `run_job_now`, retry-with-backoff, DLQ	v0.2
Continuous aggregates	`add_continuous_aggregate`, embedded PgQue + data-time log	v0.3
`time_bucket()`	Pure SQL, full-width + calendar parity	v0.3
`refresh_continuous_aggregate`	Same name, same call shape	v0.3
Manual cagg backfill	`with_data => false` + later refresh	v0.3
Real-time aggregation	`finalize_agg(combine_agg(…))` over union of materialized partials + on-the-fly past watermark	v0.4
Hierarchical caggs	Fixed-width and calendar-width buckets; child width must divide parent's	v0.5
`time_bucket_gapfill()`	Pure SQL, single-group-key	v0.5
`first()` / `last()`	Pure-SQL custom aggregates with combinefuncs	v0.5
Compression policies	`add_compression_policy`	v0.6
Columnar compression	Sibling `_compressed` table; segmentby + orderby; FOR + dict + null bitmap + lz4. Target 2–4×	v0.6
Decompression API	`decompress_chunk`	v0.6
Chunk exclusion	Partition pruning + BRIN `minmax_multi_ops` + view-layer pushdown via `LATERAL`	v0.6
Recompression	`add_recompression_policy` — separate scheduled policy	v0.7
`ts_insert_blocker`	`error`-mode write trigger (default mode is staging-via-heap)	v0.7
Reorder / clustering	`CLUSTER` on chunks via PgQue schedule	v0.8
`timescaledb_information`	Full `pgseries_information` surface (subset shipped earlier)	v0.8
`add_dimension` (space partitioning)	Catalog ready since v0.1; API ships at v0.2 (pulled from v1.0)	v0.2
`pgseries-parallel-copy`	Chunk-affinity sharded `COPY` loader for ≥1 M rows/sec aggregate	v0.2
`pgseries.tune()`	Print + apply recommended GUC bundle (`wal_compression`, `max_wal_size`, `checkpoint_*`, `wal_buffers`, etc.)	v0.1 print · v0.2 apply

Partial or lossy8 mappings

Hyperfunctions	Use PG built-ins (`percentile_cont`, `width_bucket`) and `DISTINCT ON`. PL/pgSQL versions too slow.	—
Approximation sketches	`hll` from v0.8; `tdigest` not on managed PG; pure-SQL t-digest is post-v1.0 conditional.	v0.8
Multi-group `time_bucket_gapfill`	Single-group-key only.	—
Lossless float compression	Gorilla unavailable in PL/pgSQL; only opt-in lossy requantization. Floats compress 1.5–2.5×.	v0.6
Cagg select-list shapes	Aggregates without combinefuncs, `HAVING`, `GROUPING SETS`/`ROLLUP`/`CUBE`, window functions are rejected.	v0.4
`auto_decompress` write mode	Removed. Default is staging-via-heap; recompression policy merges back. The TimescaleDB direction since 2.11.	v0.7
Compression ratio	Target 2–4× on mixed metric workloads (vs Timescale's 8–15× with Gorilla).	v0.6
Direct-to-leaf-partition writes	Revoked at v0.3 to keep statement triggers honest.	v0.3

Out of scope (never, or beyond v1.0)10 mappings

Bit-level codecs	Never in pure SQL.	—
Custom planner / executor nodes	Never without C. Replaced by partition pruning + BRIN + `LATERAL`.	—
True columnar storage	Real columnstores (Citus columnar, Hydra) require an extension.	—
Distributed hypertables	Deprecated upstream as of TimescaleDB 2.14.	—
Tiered storage to object storage	Requires custom storage hooks.	—
Promscale-style ingest caggs	Requires the bypassed insert path.	—
Hypertable → series_table converter	Per-chunk parallel `pg_dump` migration script is the only path.	—
FK cascades into source tables	Statement triggers don't see them. Deferred indefinitely.	—
Multi-group gapfill with `locf`/`interpolate`	Beyond v1.0.	>v1.0
Full hyperfunction parity	Beyond v1.0; `hll` at v0.8 is the start.	>v1.0

09Acceptance criteria #

Each version is its own release-blocker set. A version doesn't ship until every item below for that version (and every prior version) is green on PG 17 and PG 18. Highlights only — see SPEC.md for the full per-version list.

v0.1. \i pgseries.sql installs cleanly; create_series_table + adopt_partitioned_table round-trip; precreate_chunks idempotent; move_default drains …_default with zero rows left under multi-row COPY; chunk-lease state machine invariant; role grants enforced.
v0.2. Bundle install ships pgque-core; smoke test on RDS / Aurora / Cloud SQL / Supabase / Neon / Crunchy Bridge; job overlap prevention. Parallel-COPY ingest benchmark: ≥500 K rows/sec at 8 streams, target ≥1 M at 16+ streams; parallel batched INSERT at 32–64 conns hits ≥250 K. add_dimension upgrade re-partitions in place. pgseries.tune() apply mode validated.
v0.3. Cagg correctness on the straight path; backdated-DML test (UPDATE to a 2022 row re-materializes the 2022 bucket); out-of-window writes show in cagg_lag; AFTER STATEMENT triggers fire under COPY / bulk INSERT … SELECT.
v0.4. Aggregate correctness across the watermark (avg, stddev, percentile_cont); cagg select-list rejection; v0.3 → v0.4 upgrade auto-rebuilds.
v0.5. Cagg-on-cagg alignment errors; calendar-bucket parents; gapfill on empty buckets; first/last under combine.
v0.6. Plan-shape regression (partition pruning + BRIN + zero unnest of skipped); stats hygiene after swap; compression-swap retry; read-only enforcement; compression benchmark vs pg_timeseries; scale benchmark — 10¹⁰ rows on PG 18 / local NVMe / ~128 GiB RAM.
v0.7. Compressed write path (error mode raises; staging round-trips); ON CONFLICT on heap branch.
v0.8. add_index propagation; info-view parity with TimescaleDB; hll inside partial-form caggs.
v1.0. APIs frozen; deprecation shims dropped; v0.8 example workload runs unchanged on v1.0; doc set complete.
Always. Red/green TDD; samospec brief --ai regenerates without verifier-flagged inventions on each release's published SPEC.md.