coulomb/plenty-gobble
5
0
Fork 0
Code Issues Pull requests Projects Releases Packages Wiki Activity Actions
plenty-gobble/INTENT.md
tegwick 3701d1ae1c Add INTENT, research notes and architecture/use-case specs 
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>

Assistant: claude-code
Assistant-Model: opus
Assistant-Process: 1370019@bnt-lap001
Assistant-Session: d462dd63-16de-4189-ae70-b9cd6566bf87
2026-10-11 20:22:37 +02:00

884 lines
22 KiB
Markdown
Executable file
Raw Permalink Blame History

# poly-gobble — INTENT
## Purpose
`poly-gobble` explores and implements an adaptive, object-store-native analytical data engine with PostgreSQL as its control plane, SQL personality, catalog, and governance layer.
The project is inspired by the architectural properties of modern observability data stores such as Bronto, but is not intended to reproduce any proprietary implementation. Its goal is to establish an open, composable architecture for very large append-oriented datasets that combines:
- PostgreSQL for metadata, transactions, identity, policy, tenancy, and relational integration;
- immutable columnar segments for bulk historical data;
- object storage for low-cost scale;
- adaptive, hierarchical data-skipping;
- vectorized analytical execution;
- workload-aware physical optimization;
- elastic distributed query execution when a single node is no longer sufficient.
The central idea is:
> **Use PostgreSQL as the brain of the data system without requiring PostgreSQL heap storage to hold all of the data.**
---
## Problem
Large telemetry and event datasets have characteristics that do not align well with conventional row-oriented OLTP storage:
- they are mostly append-only;
- old data is rarely updated;
- schemas are sparse and evolve continuously;
- query selectivity varies enormously;
- most queries touch only a small fraction of retained data;
- retention periods can be long;
- ingest rates can be high;
- storage economics matter as much as query latency;
- compute demand is highly bursty.
Traditional PostgreSQL can handle substantial event workloads, but at larger scales the cost of MVCC, WAL, indexes, tuple overhead, vacuum, and storage/compute coupling becomes increasingly misaligned with the workload.
Conversely, lakehouse-style storage is cheap and scalable, but often lacks the transactional catalog, governance, multi-tenancy, relational integration, and ergonomic SQL control plane that PostgreSQL already provides extremely well.
`poly-gobble` aims to combine these strengths.
---
## Core Thesis
The system should separate **logical database semantics** from **physical bulk-data storage**.
A logical relation may span multiple physical tiers:
```text
logical relation
|
+--------------+--------------+
| |
hot tail sealed data
| |
PostgreSQL heap columnar segments
|
object storage
```
Applications should query one logical system while the engine decides:
- which storage tier contains relevant data;
- which segments can be skipped;
- which row groups or blocks can be skipped;
- which columns need to be decoded;
- which execution engine should run the query;
- whether work should stay local or fan out across stateless workers.
---
## Design Principles
### 1. PostgreSQL is the control plane
PostgreSQL owns the authoritative state for:
- tenants;
- datasets;
- logical schemas;
- segment metadata;
- identity and authorization;
- row-level and tenant-level policy;
- physical-layout policies;
- query statistics;
- optimization history;
- lifecycle state;
- retention policy;
- transactional publication of newly sealed segments.
PostgreSQL should be the place where the system explains itself.
---
### 2. Historical bulk data is immutable
Once sealed, a data segment is immutable.
Immutability allows the system to avoid most of the machinery required for general-purpose transactional updates and enables:
- aggressive compression;
- large contiguous columnar files;
- cheap replication;
- deterministic caching;
- simple consistency semantics;
- reproducible query behavior;
- reliable metadata summaries.
Corrections should normally create replacement or superseding segments rather than mutate existing files in place.
---
### 3. Avoid reading data before optimizing how fast it can be read
The first performance question is not:
> How quickly can this segment be scanned?
It is:
> Can this segment be proven irrelevant without opening it?
Query execution should therefore apply hierarchical elimination:
```text
dataset
-> time / partition pruning
-> segment synopsis
-> row-group / block synopsis
-> column projection
-> decoding
-> predicate evaluation
```
A successful query engine should often answer a query by reading only a tiny fraction of the retained bytes.
---
### 4. Physical representation is adaptive
Different fields and datasets should not be forced into one indexing strategy.
The engine should profile observed data and choose suitable physical summaries according to characteristics such as:
- type;
- cardinality;
- repetition;
- null frequency;
- entropy;
- numerical range;
- average width;
- query frequency;
- predicate type;
- selectivity.
Potential representations include:
- min/max summaries;
- dictionaries;
- Bloom filters;
- token or n-gram filters;
- sketches;
- zonemaps;
- promoted physical columns;
- residual semi-structured maps;
- no additional index at all.
Adaptation must be policy-driven and auditable.
---
### 5. Sparse schemas remain sparse
The system must support datasets with large and evolving key spaces.
Frequently used or structurally important properties may be promoted into first-class physical columns.
Rare or unstable properties may remain in a residual map or semi-structured column.
Schema evolution should be normal operation, not an exceptional migration event.
---
### 6. Query workload is feedback
The system should observe how it is queried.
Query telemetry may influence:
- field promotion;
- synopsis generation;
- Bloom-filter placement;
- token indexes;
- compaction strategy;
- clustering;
- materialized aggregates;
- caching;
- segment size;
- partition layout.
The database is therefore a controlled adaptive system:
```text
queries
|
v
query telemetry
|
v
workload model
|
v
optimization policy
|
v
physical layout
|
+--------------------> future queries
```
Adaptation must remain explainable, reversible, and governed by explicit policy.
---
### 7. Compute and storage are decoupled
Object storage should hold sealed bulk data.
Compute should be disposable.
A single-node vectorized engine should be preferred as long as it is sufficient. Distributed execution should be introduced only when measured workload requires it.
The intended progression is:
```text
single PostgreSQL instance
+
single vectorized analytical worker
|
v
parallel local execution
|
v
multiple stateless workers
|
v
elastic distributed execution
```
Scale-out is an optimization, not the starting point.
---
### 8. Open formats before custom formats
The project should prefer established, inspectable formats and standards where they satisfy requirements.
Initial preferences:
- Parquet for sealed columnar segments;
- Zstandard where appropriate for compression;
- S3-compatible object storage;
- PostgreSQL for catalog and control;
- DuckDB / pg_duckdb for early analytical execution;
- OpenTelemetry as an important initial ingest model.
A custom storage format should only be introduced when measurements demonstrate that established formats impose a material limitation.
---
### 9. PostgreSQL integration should deepen gradually
The project should not begin with a PostgreSQL fork.
The intended evolution is:
1. PostgreSQL catalog + external columnar objects;
2. `pg_duckdb` or equivalent analytical execution;
3. custom segment planner and query router;
4. stateless distributed workers;
5. PostgreSQL FDW and/or `CustomScan` integration;
6. custom Table Access Method only if justified by evidence.
Each deeper integration step must earn its complexity.
---
### 10. Multi-tenancy is architectural, not decorative
The system should be designed from the start for secure multi-tenant operation.
Tenant isolation must cover:
- catalogs;
- logical relations;
- object namespaces;
- credentials;
- query execution;
- caches;
- metadata;
- workload statistics;
- adaptive optimization.
A future hosted service must not require retrofitting tenancy into a single-tenant storage model.
---
## Initial Architecture
```text
PostgreSQL
+----------------------+
| tenant catalog |
| dataset catalog |
| segment catalog |
| schema registry |
| policies |
| query statistics |
| optimization state |
+----------+-----------+
|
SQL/API
|
+----------v-----------+
| query planner/router |
+----------+-----------+
|
+----------------+----------------+
| |
hot-tail sealed data
PostgreSQL |
object store
|
+---------------+---------------+
| | |
segment segment segment
Parquet Parquet Parquet
+ stats + stats + stats
+ Bloom + Bloom + Bloom
|
v
vectorized execution
DuckDB first
|
distributed workers
when justified
```
---
## Ingestion Model
An initial ingestion path may look like:
```text
OpenTelemetry / HTTP / files
|
v
ingest gateway
|
v
queue / stream layer
|
v
PostgreSQL hot tail
|
v
segment builder
|
+-----+------------------------------+
| schema discovery |
| type inference |
| field profiling |
| field promotion |
| dictionary decisions |
| synopsis generation |
| token summaries |
| compression |
+-----+------------------------------+
|
v
Parquet segment
|
v
object storage
|
v
transactional publication in PostgreSQL
```
The stream layer is optional for early prototypes. Correct architectural boundaries matter more than reproducing production-scale infrastructure immediately.
---
## Segment Model
A **segment** is the fundamental sealed physical unit.
A segment should have at least:
- tenant identity;
- dataset identity;
- object location;
- minimum timestamp;
- maximum timestamp;
- row count;
- compressed size;
- schema identifier or hash;
- partition attributes;
- statistics;
- synopsis metadata;
- lifecycle state;
- creation and sealing timestamps.
Example conceptual catalog relation:
```sql
telemetry_segment (
segment_id,
tenant_id,
dataset_id,
min_timestamp,
max_timestamp,
object_uri,
row_count,
compressed_bytes,
schema_hash,
partition_tags,
synopsis_uri,
created_at,
sealed_at
)
```
The catalog is authoritative. Object existence alone does not make a segment visible to queries.
---
## Adaptive Synopsis
The first major research artifact of `poly-gobble` should be a general **segment synopsis** model.
A synopsis should summarize enough information to reject irrelevant segments without opening the underlying data.
Possible per-field metadata:
```text
field name
logical type
occurrence count
null count
distinct estimate
minimum
maximum
dictionary summary
Bloom filter
token Bloom / n-gram synopsis
histogram or sketch
```
The synopsis model must support versioned policies.
Example:
```text
SynopsisPolicy/v1
```
A policy determines which structures are generated from a `FieldProfile`.
The resulting decisions should be inspectable.
---
## Query Planning
A query should progress through increasingly expensive stages.
```text
1. logical planning
2. tenant/policy enforcement
3. time pruning
4. partition pruning
5. segment synopsis pruning
6. row-group/block pruning
7. column projection
8. vectorized scan
9. local aggregation
10. distributed merge if necessary
```
The engine should expose why segments were selected or discarded.
An `EXPLAIN`-style surface is a first-class requirement.
Example conceptual output:
```text
Segments total: 18,422
Removed by time: 17,901
Removed by partition: 311
Removed by synopsis: 183
Segments opened: 27
Row groups opened: 41
Bytes physically read: 812 MB
Logical dataset size: 8.4 TB
```
Data-skipping efficiency is a principal system metric.
---
## Query Execution
The initial execution engine should favor DuckDB or `pg_duckdb`.
Reasons include:
- vectorized execution;
- excellent Parquet support;
- predicate pushdown;
- projection pushdown;
- metadata inspection;
- compact deployment;
- ability to operate directly against object storage;
- low operational complexity.
Distributed execution may later use stateless workers that receive:
- object ranges or segment identifiers;
- projected columns;
- predicates;
- aggregation descriptions;
- limits / Top-K requirements.
Workers should perform partial aggregation whenever possible and return compact intermediate results.
---
## Hot Tail
Very recent data may remain temporarily in PostgreSQL to provide immediate query visibility.
The exact boundary should be configurable.
Example:
```text
0-5 minutes PostgreSQL hot tail
>5 minutes sealed object-store segments
```
Queries must transparently combine both.
The hot-tail mechanism is a latency optimization, not a second independent datastore.
---
## First Target Workload
The first target workload is high-volume observability and event data:
- logs;
- traces;
- metrics-derived events;
- audit events;
- operational events.
This workload is useful because it stresses:
- append throughput;
- sparse schema;
- high-cardinality identifiers;
- timestamp predicates;
- selective lookup;
- free-text search;
- aggregations;
- long retention;
- multi-tenancy.
The architecture should nevertheless remain general enough to support other append-oriented analytical datasets later.
---
## Non-Goals
The initial project is **not** intended to:
- replace PostgreSQL for OLTP;
- implement arbitrary row-level UPDATE semantics on sealed data;
- reproduce Elasticsearch APIs;
- reproduce Bronto internals;
- build a general Spark replacement;
- create a new object store;
- invent a new columnar file format before necessary;
- build a distributed scheduler before single-node limits are measured;
- optimize benchmark numbers at the expense of architectural clarity;
- provide every possible analytical database feature in the first versions.
---
## Technical Hypotheses
The project should explicitly test the following hypotheses.
### H1 — Data skipping dominates scan optimization
For common selective telemetry queries, hierarchical pruning can reduce physical I/O enough that object-store latency is not the dominant cost.
### H2 — Parquet is sufficient for the first storage engine
Modern Parquet statistics, encoding, row groups, and Bloom-filter support can provide enough low-level structure to avoid an early custom format.
### H3 — PostgreSQL is a strong control-plane database
PostgreSQL can provide transactional catalog semantics, identity, tenancy, policy, relational integration, and optimizer coordination without becoming the bulk-data bottleneck.
### H4 — DuckDB delays the need for distributed compute
A vectorized single-node engine can satisfy surprisingly large analytical workloads, allowing the project to postpone distributed execution until justified.
### H5 — Workload feedback improves physical organization
Observed query patterns can materially improve future query cost through controlled adaptation of physical layout and synopsis strategy.
### H6 — A unified logical SQL surface is practical
Users should be able to query hot PostgreSQL data and cold object-store data through one coherent logical model without application-level routing.
---
## Evolution Plan
### V0 — Feasibility
Establish:
- PostgreSQL catalog;
- Parquet segment writer;
- local/object-store segment storage;
- DuckDB/pg_duckdb reads;
- logical dataset abstraction;
- basic time and partition pruning.
Success means a query can transparently combine hot and sealed data.
---
### V1 — Segment Synopsis
Add:
- field profiling;
- min/max metadata;
- cardinality estimation;
- Bloom filters;
- synopsis catalog;
- segment elimination;
- pruning metrics;
- `EXPLAIN` visibility.
Success means selective queries read only a small minority of relevant segments.
---
### V2 — Adaptive Physical Layout
Add:
- policy-driven synopsis selection;
- field promotion;
- residual sparse-field representation;
- workload telemetry;
- policy versioning;
- optimization recommendations.
Success means the system can explain why a field is represented in a particular way.
---
### V3 — Self-Optimization
Add:
- workload-driven adaptations;
- automatic policy recommendations;
- controlled re-compaction;
- materialized summaries where justified;
- adaptation history;
- rollback capability.
Success means the system can improve measured workloads without losing auditability.
---
### V4 — Distributed Query Execution
Add:
- stateless workers;
- segment assignment;
- partial aggregation;
- Top-K reduction;
- distributed merge;
- elastic worker scaling.
Success means scan-heavy analytical queries scale approximately with available workers until object-store or network limits dominate.
---
### V5 — Deep PostgreSQL Integration
Evaluate:
- FDW;
- asynchronous FDW execution;
- `CustomScan`;
- planner hooks;
- custom relation statistics;
- alternative Table Access Methods.
Adopt only the mechanisms whose performance or ergonomics justify their maintenance cost.
---
## Success Criteria
`poly-gobble` succeeds if it demonstrates that a PostgreSQL-centered architecture can offer:
1. a coherent SQL and governance surface across hot and cold data;
2. cheap retention on object storage;
3. aggressive data skipping;
4. efficient columnar execution;
5. transparent multi-tenancy;
6. adaptive physical organization;
7. scale-up before scale-out;
8. scale-out without redesigning storage;
9. auditable optimization decisions;
10. materially better cost characteristics than storing equivalent long-retention telemetry in conventional indexed row storage.
Performance claims must be supported by reproducible benchmarks.
---
## Benchmarking Principles
Benchmarks should measure both latency and economics.
Important metrics include:
- ingest rows/s;
- ingest bytes/s;
- sealing latency;
- compression ratio;
- object count;
- metadata size;
- segments considered;
- segments eliminated;
- row groups eliminated;
- logical bytes covered;
- physical bytes read;
- time to first row;
- total query latency;
- CPU time;
- memory usage;
- object-store requests;
- compute cost per query;
- storage cost per retained TB-month.
Benchmarks should distinguish at least:
- highly selective lookup;
- time-window scan;
- high-cardinality lookup;
- free-text search;
- Top-K;
- grouped aggregation;
- full analytical scan.
---
## Security and Isolation
The architecture must make tenant boundaries explicit.
At minimum:
- all catalog rows are tenant-scoped;
- object paths are tenant-scoped;
- credentials are least-privilege;
- workers receive only authorized segments;
- caches must not leak cross-tenant data;
- query statistics must preserve isolation;
- adaptive policies must not use unauthorized cross-tenant signal unless explicitly configured.
Security decisions belong in the control plane and must remain enforceable even when execution is delegated to stateless workers.
---
## Observability
`poly-gobble` must observe itself.
Every query should expose or record:
- planning time;
- execution time;
- candidate segments;
- skipped segments;
- reason for each pruning stage;
- bytes requested;
- bytes read;
- columns decoded;
- worker utilization;
- cache effects;
- synopsis effectiveness.
The system's own telemetry is part of the feedback loop that drives adaptation.
---
## Intellectual-Property Boundary
The project may study publicly documented systems and academic or industry techniques for architectural insight.
It must not intentionally reproduce proprietary implementations.
Implementations should prefer established and independently motivated techniques such as:
- Parquet metadata;
- Bloom filters;
- min/max summaries;
- BRIN-like range summaries;
- zonemaps;
- sketches;
- workload-aware physical design;
- immutable object-store segments;
- vectorized execution.
If the project approaches functionality covered by active third-party patent claims, those claims should be reviewed before commercial deployment.
---
## Repository Character
`poly-gobble` is an **experimental systems project with a path toward production**.
The repository should favor:
- small composable components;
- explicit interfaces;
- measurable hypotheses;
- reproducible benchmarks;
- explainable decisions;
- versioned policies;
- minimal hidden magic;
- progressive sophistication.
Complexity must follow evidence.
---
## Guiding Questions
Development should repeatedly ask:
- What can we avoid reading?
- What can we avoid indexing?
- What can remain immutable?
- What belongs in PostgreSQL?
- What belongs in object storage?
- What should be learned from workload?
- What should remain policy rather than mechanism?
- Can the current scale be handled by one node?
- Does deeper PostgreSQL integration actually buy us anything?
- Can the system explain every optimization it performs?
---
## One-Line Intent
> **poly-gobble turns PostgreSQL into the control plane and SQL personality of an adaptive, object-store-native columnar data engine that learns how to avoid reading data.**
Reference in a new issue View git blame Copy permalink