Building an Analytics Data Warehouse on a Single Server

The default architecture is usually oversized

The reference architecture for analytics — a streaming bus, a distributed warehouse, an orchestration platform, a transformation framework, a BI layer — was designed for organizations with petabytes and a data platform team. Adopting it for 50 GB of event data means paying that complexity tax with none of the benefit.

Modern hardware and embedded columnar engines have moved the threshold dramatically. A single server with 16 GB of RAM handles a workload that would have required a cluster a decade ago.

What one server actually holds

Rough sizing for web analytics events, assuming a normalized schema and columnar compression:

The compression ratio is not optimistic. Event data is dominated by low-cardinality columns — country, browser, device type, path — and dictionary plus run-length encoding handles those extremely well.

A billion events is more than most websites will ever generate. If you are below it, a single node is not a compromise.

The stack

Ingest (Express)  ──▶  PostgreSQL  ──▶  DuckDB  ──▶  Dashboard
   validate            durable          columnar      API + SQL editor
   batch               source of        read layer
                       truth                │
                                            ▼
                                    Parquet on S3/R2
                                      (cold tier)

Four components, two of which are databases you were probably going to run anyway.

PostgreSQL is the write path and source of truth. Everything durable lands here first — see designing the ingestion pipeline.

DuckDB is the read layer. Embedded, so there is no service to operate — it is a library your API imports.

Parquet on object storage is the cold tier for data older than the hot window.

The API serves aggregates and, optionally, read-only SQL.

Transformation without an orchestrator

You need scheduled transformations — rollups, sessionization, cohort tables. You very likely do not need Airflow to run them.

A SQL file per model plus a small runner covers most of it:

const MODELS = [
    'models/sessions.sql',
    'models/daily_rollup.sql',
    'models/cohort_retention.sql',
];

async function runModels(conn) {
    for (const file of MODELS) {
        const sql = await readFile(file, 'utf8');
        const t0 = Date.now();
        await conn.run(sql);
        logger.info({ file, ms: Date.now() - t0 }, 'model built');
    }
}

Each model is a CREATE OR REPLACE TABLE … AS SELECT, ordered by dependency. Run it on a cron. Log durations so you can see a model getting slower before it becomes an incident.

This handles ordered dependencies, is debuggable by reading one file, and has no infrastructure. Graduate to a real orchestrator when you have branching dependency graphs, retries with backfill semantics, or several people editing models concurrently — not before.

Rollups are where the performance comes from

The single highest-leverage optimization is not query tuning. It is not querying raw events for things that can be pre-aggregated.

CREATE OR REPLACE TABLE daily_rollup AS
SELECT site_id,
       timestamp::DATE AS day,
       path, country, device_type, referrer_host,
       count(*)                          AS pageviews,
       count(DISTINCT session_id)        AS sessions,
       approx_count_distinct(visitor_id) AS visitors
FROM events
GROUP BY 1,2,3,4,5,6;

A year of raw events might be 200 million rows; the same year rolled up is a few hundred thousand. Dashboard queries hit the rollup and return instantly regardless of underlying volume.

Keep raw events for the queries that genuinely need them — funnels, path analysis, ad-hoc investigation — and serve every standard dashboard tile from rollups. The distinction is worth being deliberate about: a rollup cannot answer a question about sequence, only about totals.

Tiering keeps storage cost flat

Once the hot window is defined — 30 days is a reasonable default — everything older moves to Parquet:

COPY (SELECT * FROM events WHERE timestamp < current_date - 30)
  TO 's3://analytics/cold/' (FORMAT PARQUET, PARTITION_BY (year, month));

CREATE OR REPLACE VIEW events AS
  SELECT * FROM events_hot
  UNION ALL
  SELECT * FROM read_parquet('s3://analytics/cold/**/*.parquet');

Queries keep saying FROM events. Recent windows never touch object storage; historical queries transparently span both tiers. Export, verify, then delete from hot — in that order, inside a transaction, because an overlap between tiers double-counts every metric.

Operational essentials

Running this on one machine means the machine's failure modes are yours:

• Backups. pg_dump on a schedule to off-box storage. DuckDB is derived state and can be rebuilt from Postgres, so it needs no backup — but only if you have actually tested the rebuild.
• Monitoring. Sync lag, queue depth, dropped-event count, model build durations, disk free. Sync lag is the earliest indicator of nearly every problem.
• Resource limits. Set DuckDB's memory_limit and threads to match the container, not the host. Defaults sized from the host will get you OOM-killed inside a constrained container.
• A tested restore. An untested backup is a hypothesis. Restore it somewhere, at least once, before you need it.

When to scale out

Honest signals that a single node is no longer right:

• Sustained ingest exceeding what one node absorbs — tens of thousands of events per second, not a lunchtime spike.
• Many concurrent analysts. DuckDB is one process sharing CPU with your app; dozens of simultaneous heavy queries is not its shape.
• Multiple independent consumers of the raw event stream, needing replay.
• Availability requirements that a single machine cannot meet regardless of capacity.
• Working set genuinely exceeding RAM after rollups and tiering — and note that rollups and tiering usually fix this before hardware does.

Notably absent: "we have a lot of data." Volume alone is handled by tiering. Scale out for concurrency, availability, and organizational structure — those are the constraints a bigger machine cannot solve.

Frequently asked questions

Can one server really run an analytics warehouse?

For the volumes most teams actually have, comfortably. Modern hardware handles tens of gigabytes of columnar data on a single node, and the distributed alternatives were designed for problems most sites never reach.

Do I need dbt or Airflow for transformations?

Not at this scale. Ordered SQL scripts run on a schedule with logging and failure alerts cover the same ground. Orchestrators earn their keep with complex dependency graphs and multiple teams.

Where does the performance actually come from?

Rollups. Precomputing daily aggregates turns dashboard queries that scan millions of rows into ones that scan hundreds, and it is a much bigger lever than tuning the raw queries.

How do I keep storage costs flat as data grows?

Tier it. Keep recent partitions hot and move older ones to compressed Parquet in object storage, stitched back through a view — see DuckDB + Parquet cold storage.

What signals mean it is time to scale out?

Sustained memory pressure after rollups and tiering, ingestion falling behind during peaks, or concurrency demands a single node cannot serve. Data volume alone is rarely the reason.

The point

The complexity of your data stack should track the complexity of your problems, not the size of the companies whose blog posts you read. Start on one server, measure, and add distribution when a specific limit forces it. InsightsTrack ships this architecture in a single docker-compose up — Postgres, DuckDB, rollups, optional Parquet tiering, and a SQL editor — because for most sites, that is genuinely the right size. See Parquet cold storage, the sync engine, and DuckDB vs ClickHouse.