The Vibe Coding Philosophy
Writing a database extension used to feel like performing open-heart surgery with a butter knife. One slip in C memory management and the entire cluster crashes. Vibe Coding is a mindset shift enabled by Rust and pgrx: moving from “don’t break it” to “build it as fast as you can think it.”
- Fearless Exploration: Rust’s compiler handles the safety, so you can focus on the algorithms.
- Instant Gratification:
cargo pgrx runcompiles and launches a live Postgres instance in seconds. - Deep Integration: Access low-level Postgres hooks without the boilerplate of C macros.
PostgreSQL Storage Internals
The 8KB Page
To understand why we need a new storage model, we must first look at how the standard Heap Access Method works. Postgres organizes data into 8KB pages.
The Buffer Manager Overhead
Every time you read a row, Postgres loads an entire 8KB page into the Buffer Manager. This is efficient for OLTP, but creates massive IO overhead for analytical time-series scans that only need a few columns from billions of rows.
The 1D Storage Trap
Standard B-Trees
Standard Postgres uses the Heap to store rows mostly unordered as they arrive. To find data, we use B-Trees. But a B-Tree is fundamentally one-dimensional.
The Composite Index Trap
When you create a composite index on (tenant_id, time), you are sorting your data by tenant first, then by time.
WHERE tenant_id = 1 AND t > now() - interval '1h'— FAST.WHERE t > now() - interval '1h'— SLOW (Fragmented across all tenants).
The Scan Gap Animation
In traditional storage, data for a specific timeframe across multiple tenants is scattered. The database must scan many pages and filter out irrelevant rows. Spiral changes this geometry.
The Spiral Proposal
The Gravity of Large Datasets
In a growing system, data has Entropy. As you ingest billions of rows, the traditional Heap becomes a massive “Gravity Well.” Standard 8KB pages are too small to handle the weight of analytical scans.
The Spiral Metaphor: Cosmic Storage
Spiral reimagines storage as a Spiral. Fresh data arrives at high velocity in the center, and as it ages, it migrates to outer “Lanes” (rollups).
Spiral’s User Interface: Magic Comments
Instead of complex DDL, Spiral parses standard SQL comments to define your analytics pipeline.
CREATE TABLE asset_ticks (
t timestamptz NOT NULL,
symbol_id int REFERENCES symbols(id), -- Auto-detected Tenant
price double precision, -- Spiral: ohlc, stats, sketch
vol int -- Spiral: sum
);
Mathematics of Locality: Z-Ordering
To solve the Composite Index Trap, we use Z-Ordering (The Morton Curve). It interleaves the bits of multiple coordinates to create a single value that preserves multidimensional locality.
Decoding the Z-Value Formula
- (x_i, y_i): Individual bits of your coordinates.
- (2^{2i+1}) and (2^{2i}): Masks that “zip” bits together.
- The Result: A single scalar that keeps conceptual neighbors physically close.
Z-Ordering Interactive Visualization
Hover over the grid to see how the Morton Curve visits every point, linearizing space while keeping blocks physically close.
Standard vs. Z-Order: The Frontend Scan
Select a range! Row-Major (Standard) is perfect for horizontal scans, but fails for Vertical or Cluster slices. Z-Order provides “Fair Locality” for any shape by grouping data into power-of-two blocks.
What is a Table Access Method (TAM)?
Postgres 12 decoupled storage from the executor via the TAM API.
- Implement callbacks for
insert,scan,delete. - Control physical byte layout on disk.
Interactive: Direct Seek vs Page Traversal
Observe how the Spiral TAM calculates the address and jumps directly to the data.
Rust Implementation: repr(C) Structs
Ensure binary storage is hardware-compatible using #[repr(C)].
#[derive(Debug, Clone, Copy)]
#[repr(C)]
pub struct SpiralingRow {
pub t: i64, // 8 bytes
pub tenant_id: i32, // 4 bytes
pub value: f64, // 8 bytes
pub padding: [u8; 40], // Total 64 bytes
}
The Binary Packing Logic
spiral_pack_delta reads from an unlogged table and packs it into the binary file.
let offset = (t * BUNDLE_SIZE as i64) + (tenant_id * ROW_SIZE as i64);
let bytes: [u8; ROW_SIZE] = unsafe { std::mem::transmute(data) };
file.seek(SeekFrom::Start(offset as u64))?;
file.write_all(&bytes)?;
EXPLAIN ANALYZE: Before & After TAM
Notice how Buffers: shared hit disappears.
-- BEFORE: Standard Heap Scan
-> Parallel Seq Scan on ticks (actual time=428.215..428.215 rows=1000000)
Buffers: shared hit=4218
-- AFTER: Spiral TAM Direct Seek
-> Custom Scan (Spiral Binary Map) (actual time=0.076..0.076 rows=1)
Buffers: shared hit=2 (catalog only)
Direct IO: 64 bytes read via seek()
Incremental View Maintenance (IVM)
In standard Postgres, REFRESH MATERIALIZED VIEW rebuilds everything. Spiral tracks “dirty buckets” in a transactional changelog.
The Streaming Hierarchy in Motion
Watch data points flow from raw insert to cascading rollups.
Mathematical Engine: Welford’s Algorithm
How do we “merge” a standard deviation? Spiral uses Welford’s Algorithm to store moments ((n, M_1, M_2, M_3, M_4)).
Parallel Aggregation Lifecycle
Follow a data point from SQL INSERT to final analytical projection.
Stress Test — The 1B Scalability
Achieved ingestion rates exceeding 1.9 Million rows per second.
| Phase | Ingest Rate | Duration |
|---|---|---|
| Bulk Load | 1,940,482 rows/s | 0.51s (1M sample) |
| Backfill | 302,413 rows/s | 3.30s (1M sample) |
Conclusion: The Future is Extensible
PostgreSQL is a platform for data structures.
- Rust is the Key: Memory safety makes internals accessible.
- Math is the Leverage: Smart algorithms beat brute-force hardware.
Spiral achieves performance thought impossible in a general-purpose database by using smart math and custom storage.
Presented at PGDay Blumenau 2026.
