PostgreSQL is an OLTP database at heart, built to take a stampede of small transactions and never lose one, and it’s been doing that job for so long that people forget to ask whether it can do anything else. It can. That’s what this post is about: how far you can push a plain Postgres instance into analytical territory before you genuinely need a warehouse, demonstrated with a decade of Indian stock market data on the cheapest laptop Apple sells.

The two-database habit

The standard architecture at most companies I’ve worked at looks the same. There’s the transactional database, where latency is measured in milliseconds and queries are boring on purpose: find by ID, update this row, insert that one. And then there’s the analytical store (Redshift, Snowflake, BigQuery), where queries scan months of history and nobody blinks if they take thirty seconds. Between them sits an ETL pipeline, and inside that pipeline lives most of the operational misery.

The textbook framing:

  • OLTP (Online Transaction Processing) optimizes for millisecond response on narrow queries. Find order 4812. Set status to shipped. Row-oriented storage, B-tree indexes, MVCC. The whole design assumes you want a few rows fast.
  • OLAP (Online Analytical Processing) optimizes for wide scans over deep history. Month-over-month sales by region for the last three years. You trade response time for the ability to chew through billions of rows.
        OLTP                          OLAP
  ┌──────────────┐             ┌──────────────┐
  │ ms latency   │             │ complex aggs │
  │ point reads  │   ~ETL~►    │ full scans   │
  │ writes/upd.  │             │ history      │
  └──────────────┘             └──────────────┘
        fresh ◄────── ETL delay ──────► stale

Nobody defends this split because they love it. They defend it because each side is genuinely good at its job. But the seams cost you, and the costs compound:

Consistency drift. Two systems means two versions of the truth, and the pipeline between them is where they diverge. Every data team has a story about the dashboard that disagreed with the application by 2% and the week spent finding out why.

Movement is slow and fragile. ETL jobs fail at 3 a.m., backfills take days, and schema changes upstream break transforms downstream in ways you discover from an angry Slack message.

Staleness limits what you can build. If analytics runs on yesterday’s snapshot, every feature that needs current aggregates (fraud scoring, live leaderboards, usage-based billing) either can’t be built or gets built badly against the OLTP store, where it then causes incidents.

It’s expensive. Two databases, one pipeline, and usually a person or three whose whole job is keeping the pipeline alive.

HTAP, or: what if the seam just wasn’t there

Hybrid Transactional/Analytical Processing (HTAP) is the industry’s answer: one system that serves both workloads, so the transactional row is available to analytics the moment it commits. The commercial offerings are real and increasingly mainstream: Snowflake’s Unistore, SingleStore (formerly MemSQL), and Citus, which Microsoft acquired and pushed fully open source.

The interesting part is how they do it, because none of them found a magic storage format that’s good at both jobs. Snowflake’s hybrid tables show the trick clearly: a write lands in a row-oriented store to keep single-row transactions fast, and the engine copies it into a columnar representation behind the scenes. Analytical queries route to the columnar copy, which for big scans can be on the order of 50x faster, while point reads and updates hit the row store. Same table, two physical layouts, transparent routing.

Which means HTAP isn’t free. You’re paying write amplification for every insert (two representations to maintain), the internal replication has to be strongly consistent or your “real-time analytics” becomes eventually-right analytics, and ACID semantics have to hold across both layouts. You’re still paying for the seam, just less than an ETL pipeline costs.

The thesis of this post: if data is the actual business, and at Arcesium, where I ran this experiment, wrangling enormous financial datasets is the product, the same architecture is worth building yourself, on top of Postgres, where you control every layer of it. The building blocks already exist as extensions.

The experiment

I pulled ten years of historical Indian F&O candle data, a hair over 100 million rows, and loaded it into Postgres 14 on my MacBook Air M1. That’s 8 GB of RAM and about 250 GB of SSD. A local Datadog agent captured the metrics for each run.

The schema is deliberately plain:

CREATE TABLE historic_candle_sticks (
    stock_name  text,
    open        numeric,
    high        numeric,
    low         numeric,
    close       numeric,
    candle_at   timestamptz
);

We’ll climb three levels: plain heap table, TimescaleDB hypertable, and columnar storage, measuring what each one buys and what it costs.

Level 1: the plain heap table

Baseline first. Bulk-inserting 100M rows into a vanilla table, ingestion holds up respectably at the start and then sags. As the table and its indexes outgrow shared_buffers, every insert starts paying for buffer evictions and index page splits, and the write rate develops that familiar downward slope.

Point queries are, of course, instant with an index. This is what Postgres was built for. The pain shows up when you ask an analytical question, like aggregating a single stock’s candles across ten years: EXPLAIN ANALYZE shows a parallel sequential scan wading through the entire heap, because the planner has no way to know that January 2016 lives nowhere near the pages it’s reading.

Level 2: TimescaleDB hypertables

Market candles are time-series data, and time-series is a solved problem in the Postgres ecosystem. TimescaleDB’s core construct is the hypertable: to you it looks and queries like one table, but underneath it’s automatically partitioned into chunks by time. One command converts an existing table, data and all:

SELECT create_hypertable('historic_candle_sticks', 'candle_at',
                         migrate_data => true);

Two things change immediately. Ingestion stops sagging: writes land in the newest chunk, whose indexes are small and stay hot in memory, so the insert rate holds nearly flat where the plain table drooped.

And the aggregate query gets its real win from chunk exclusion: the planner knows the time range each chunk covers and skips the irrelevant ones outright, so a query over one year touches one year’s chunks instead of a decade’s heap. Same SQL, dramatically smaller working set.

The sleeper feature is native compression. Older chunks, which in a time-series workload are effectively immutable, can be compressed automatically on a policy or manually by time range. On this dataset that took ~10 GB down to roughly 1 GB. A 10x saving isn’t just a storage line item: compressed chunks mean fewer pages read from disk, which is what analytical scan performance comes down to.

Level 3: columnar, for when your data isn’t a time series

Chunk-by-time only helps when time is your axis. For everything else, the answer is changing the storage layout itself. Citus ships a columnar access method for Postgres (descended from the old cstore_fdw work, now fully open source as of Citus 11), and using it is one clause:

CREATE TABLE simple_columnar (i int8) USING columnar;

The mental model: a row table appends rows one after another,

row store:            columnar store:
| a | b | c | d |     | a | a | a |
| a | b | c | d |     | b | b | b |
| a | b | c | d |     | c | c | c |
                      | d | d | d |

while columnar ingests rows in bulk into a stripe of up to 150,000 rows, and inside each stripe the values of each column sit contiguously, divided into chunks of 1,000 rows. For every chunk, every column, it records the min, max, and count.

That layout is why the whole thing works, for three separate reasons. First, a query that touches four columns of a forty-column table reads a tenth of the data; the other thirty-six columns are never fetched. Second, those per-chunk min/max stats act as a free, automatic index: a filter like price > 4000 lets the scan skip every chunk whose max is below the threshold without decompressing it. Columnar tables have no B-trees at all and mostly don’t miss them. Third, similar values stored adjacently compress extremely well, and the data stays compressed in the buffer cache, so you’re effectively multiplying your RAM and your disk bandwidth at the same time.

The cost shows up on the transactional side, and it’s worth stating plainly because it’s exactly the tradeoff HTAP exists to route around. “Find by ID” on a columnar table means scanning chunks, not walking a B-tree. The format is append-only in spirit: deletes don’t reclaim space, updates write new data, and because there’s no row-level construct to lock, DML takes a table-level lock. You do not put your orders table on columnar. You put the copy of it that analysts hammer on columnar.

The columnar demo

Citus in a container, sixty seconds:

docker run -d --name citus -p 5432:5432 \
  -e POSTGRES_PASSWORD=... citusdata/citus:11.1
docker exec -it citus psql -U postgres

Generate and load 100M synthetic page views:

\copy (SELECT s % 307, (random()*5000)::int,
              '203.0.113.' || (s % 251),
              now() + random() * interval '60 seconds'
       FROM generate_series(1,100000000) s)
  TO '/tmp/views.csv' WITH CSV

\copy page_views FROM '/tmp/views.csv' WITH CSV

Raw scans over 100M rows are already respectable. Columnar scans parallelize cleanly across cores, since carving a stripe-organized scan into worker slices is exactly the shape max_parallel_workers wants. But the pattern that makes this production-worthy is the incremental rollup. Define an aggregate table:

CREATE TABLE daily_page_views (
    tenant_id   int,
    day         date,
    page_id     int,
    view_count  bigint,
    PRIMARY KEY (tenant_id, day, page_id)
);

and keep it current with an upsert (INSERT ... ON CONFLICT (tenant_id, day, page_id) DO UPDATE SET view_count = daily_page_views.view_count + EXCLUDED.view_count) that only considers rows that arrived since the last run. Today’s partial numbers stay live as the day progresses; you never re-aggregate history. A small PL/pgSQL function on a schedule kept my columnar copy within a minute of the source table.

Wiring it together: the poor man’s Unistore

At this point we’ve reinvented the two halves of a hybrid table: a row-store heap for transactions and a columnar twin for analytics. What remains is the plumbing between them, in escalating order of seriousness:

  1. Audit trigger. An AFTER INSERT trigger on the heap table appends into the columnar table. Trivial to write, synchronous, and it taxes your transactional write path. Fine for modest write rates.
  2. Scheduled PL/pgSQL copy. The rollup pattern above: a function sweeps new rows over on a timer. Sub-minute freshness, zero load on the write path, at the cost of that minute.
  3. Change data capture. The production option: Debezium tails Postgres’s logical replication slot and streams every change to the columnar side (or anywhere else). No triggers, no polling, and freshness measured in seconds. This is, structurally, the same internal replication Snowflake runs inside Unistore. You’re just operating it yourself.

The one piece the paid products give you that you have to build is routing: your application has to know which table to hit for which query. Today that’s a code convention. The version I’d like to build someday is a Postgres extension (CREATE EXTENSION makes this pluggable) with a planner hook that inspects the query shape and routes it: point lookups and DML to the heap, wide aggregates to the columnar twin. One table name, two storage engines, no application awareness. That’s the whole HTAP pitch, reconstructed from open-source parts.

What the ladder looks like from the top

  plain heap ──► hypertable ──► columnar twin ──► CDC + routed queries
   (OLTP)        (time-series     (analytics       (homegrown HTAP)
                  analytics)       at scale)

Every project starts at the left of that ladder, and each rung is an incremental migration (one extension, one CREATE TABLE ... USING, one replication slot) rather than a re-platforming project.

Not every analytical use case needs an ETL pipeline and a warehouse invoice. A single Postgres instance on a fanless laptop absorbed 100 million rows and served aggregate queries over them at speeds most teams would happily ship, and the same techniques scale to real hardware with plenty of headroom. The prerequisite isn’t a bigger budget; it’s understanding your data’s shape and your query patterns well enough to pick the right storage layout for each. Plenty of serious engineering organizations have figured this out and run their analytics on plain Postgres.