PostgreSQL Schema Design for Analytics

The first version of your events table will outlive every other decision in your analytics project. Dashboards get rewritten, collectors get replaced, but the rows you store today are the rows you will query in three years. Good PostgreSQL schema design for analytics means choosing a handful of column types and one derivation strategy before the table holds a billion rows.

In this article you define the canonical events table for Acme Shop, the same one the collector from the analytics API in Node.js writes to. You also derive sessions and users tables from it, and you build a daily_pageviews rollup. Everything is plain SQL that you can paste into psql, with one note: the derived-table statements take a $1 placeholder for the last run time. In psql, replace it with a literal such as '-infinity' for the first full build.

PostgreSQL schema design for analytics does not need many tables. The schema here is deliberately small: one append-only fact table, three derived tables, and a short list of naming rules. You will see why each type was picked, which mistakes to avoid, and where to stop. Indexes and partitioning belong to a later article, and query patterns belong to the SQL article, so this one stays on structure.

I assume you can read SQL and have PostgreSQL running locally. I ran every statement in this article on PostgreSQL 18. Nothing here needs more than PostgreSQL 12, except one optional function that I flag where it appears.

What PostgreSQL Schema Design Must Do for Analytics

An application schema models the current state of the world. A customer has one email address, and an update overwrites the old one. An analytics schema models what happened, and nothing is ever overwritten. That difference drives every choice below.

Four requirements shape the design. First, writes are constant and reads are bulk, so the table is append-only. Second, event types change weekly, so the shape must absorb new properties without a migration. Third, the same event may arrive twice, so the table needs a natural key for deduplication. Fourth, reports rarely read raw rows, so you need cheap derived tables for the common questions.

The picture looks like this:

 browser --> POST /v1/events --> events  (append-only facts)
                                   |
              +--------------------+--------------------+
              |                    |                    |
           sessions              users           daily_pageviews
        (1 row per visit)   (1 row per person)   (1 row per day + page)
              |                    |                    |
              +---------- GET /v1/stats/* ---------------+

The raw table is the source of truth. The three tables on the second level are caches that you can drop and rebuild. If you remember only one rule from this article, make it that one: derived tables never hold information that raw events lack.

The events Table

Here is the full definition. Read it once, then the sections below explain each decision.

CREATE TABLE events (
  event_id      uuid        PRIMARY KEY,
  event_name    text        NOT NULL
                CHECK (event_name ~ '^[a-z][a-z0-9_]{0,63}$'),
  occurred_at   timestamptz NOT NULL,
  received_at   timestamptz NOT NULL DEFAULT now(),
  anonymous_id  text        NOT NULL,
  user_id       text,
  session_id    text        NOT NULL,
  page_url      text,
  page_path     text GENERATED ALWAYS AS (
                  COALESCE(substring(page_url from '^https?://[^/?#]+(/[^?#]*)'), '/')
                ) STORED,
  referrer      text,
  user_agent    text,
  properties    jsonb       NOT NULL DEFAULT '{}'::jsonb
                CHECK (jsonb_typeof(properties) = 'object')
);

This matches the canonical event shape used across the series: the ten fields from the JSON payload become columns, with two additions. received_at is server-side metadata that the browser never sends, and page_path is a generated column. Both are explained below.

If you created the starter table in the collector article, you do not need to start over. That table differs in two ways: it requires page_url, and it lacks the checks and page_path. This statement brings it in line:

ALTER TABLE events
  ALTER COLUMN page_url DROP NOT NULL,
  ADD COLUMN page_path text GENERATED ALWAYS AS (
    COALESCE(substring(page_url from '^https?://[^/?#]+(/[^?#]*)'), '/')
  ) STORED,
  ADD CONSTRAINT events_event_name_check
    CHECK (event_name ~ '^[a-z][a-z0-9_]{0,63}$'),
  ADD CONSTRAINT events_properties_check
    CHECK (jsonb_typeof(properties) = 'object');

Adding a stored generated column rewrites the table, so on a large table run it in a quiet window. The constraints also scan every existing row, and they fail if old data breaks them.

Why event_id is the primary key

The client generates event_id as a UUID v4 before the first send attempt. That means a retry carries the same identifier, and PostgreSQL can reject the duplicate with INSERT ... ON CONFLICT (event_id) DO NOTHING. This is the foundation of idempotent ingestion, which async event tracking builds on.

The alternative is a bigserial surrogate key. It is smaller and always increasing, but it gives you no deduplication. You would need a second unique constraint on event_id anyway, so the surrogate key costs space and adds nothing. The trade-off to accept is that random UUIDs scatter inserts across the primary key index. For a store handling thousands of events per second, measure insert cost on your own hardware before you worry. PostgreSQL 18 also ships a uuidv7() function that produces time-ordered values. The series keeps client-generated v4, because browsers need no special library for it.

Time: occurred_at versus received_at

Use timestamptz for both. Despite the name, PostgreSQL does not store a time zone with the value. It converts input to UTC on write and converts to the session time zone on read, as the date and time types documentation describes. That makes every comparison unambiguous.

The two columns answer different questions. occurred_at is the browser’s clock at the moment of the action, so it supports ordering within a session. received_at is the server clock, so it supports pipeline monitoring and incremental rebuilds. Browser clocks drift, and users sometimes set them years off. Therefore you should never schedule jobs by occurred_at, and you should clamp wildly wrong values at the collector.

A mistake I have seen in production is a timestamp without time zone column holding local server time. The dashboard looked fine for months. Then the server moved regions, daylight saving shifted an hour, and daily totals no longer matched last quarter’s numbers. The fix was a data migration plus a rule: every time column is timestamptz, and every rollup converts to UTC explicitly.

Strings, identifiers and the generated page_path

Use text everywhere. PostgreSQL stores varchar(n) and text the same way, and a length limit only creates future migrations. Enforce size limits in the collector instead, where you can return a clear error.

anonymous_id and session_id are random strings from the browser, so text fits. user_id stays nullable because it is empty until login or signup. Never name a table user, because it is a reserved word. Use the plural users and the column user_id.

The page_path column is a stored generated column that PostgreSQL computes from page_url on every insert. Reports group by path, not by full URL, because query strings and fragments split one page into thousands of buckets. A generated column means the extraction logic lives in one place and the collector cannot get it wrong. Stored generated columns need PostgreSQL 12 or later. The cost is a few extra bytes per row.

JSONB for Properties

Every event type carries different details. A purchase_completed event has revenue, while a button_click has a button label. Creating a column for every possible property gives you a table with 200 mostly-null columns. Creating one table per event type gives you eight tables that cannot be unioned without pain. A jsonb column sits between these extremes.

The PostgreSQL JSON documentation explains why you want jsonb over json: it stores a decomposed binary form that is faster to process and supports indexing. The plain json type keeps the original text and reparses it on every access. For analytics, always pick jsonb.

Here is a purchase event and the queries that read it:

INSERT INTO events
  (event_id, event_name, occurred_at, anonymous_id, user_id,
   session_id, page_url, properties)
VALUES
  (gen_random_uuid(), 'purchase_completed', '2025-10-02T09:15:30.123Z',
   'anon_7f3a', 'u_1042', 'sess_91bc',
   'https://acme-shop.example/checkout/thanks',
   '{"order_id": "A-1001", "revenue": 59.90, "currency": "USD", "items": 2}');

-- Read a text value
SELECT properties->>'order_id' AS order_id
FROM events WHERE event_name = 'purchase_completed';

-- Read a number: cast explicitly
SELECT sum((properties->>'revenue')::numeric) AS revenue_usd
FROM events
WHERE event_name = 'purchase_completed'
  AND properties->>'currency' = 'USD';

-- Containment test
SELECT count(*) FROM events
WHERE properties @> '{"currency": "USD"}';

The ->> operator returns text, so you must cast numbers yourself. Cast to numeric for money. Here is the trap:

-- WRONG: binary floating point
SELECT 0.1::float8 + 0.2::float8 AS float_sum;   -- 0.30000000000000004

-- RIGHT: exact decimal arithmetic
SELECT 0.1::numeric + 0.2::numeric AS numeric_sum;   -- 0.3

The float result is off by a tiny fraction, and across millions of orders those fractions add up. Finance will find the gap before you do. The delta is exactness, bought at a small cost in speed.

Wrong first: the EAV table

The pattern I most often see in first schemas is the entity-attribute-value table:

-- WRONG: one row per property
CREATE TABLE event_properties (
  event_id uuid,
  key      text,
  value    text
);

Every event with six properties becomes seven rows, every report needs a self-join per property, and every value is a string. Reading the revenue of an order means joining the table to itself twice and casting. It is slow to write, slow to read and awkward to validate.

The right design keeps the properties inside the event row, as in the events table above. One insert writes one row, and one scan reads everything about an event. The delta is that you traded relational purity for locality, which is the right trade for append-heavy data.

When to promote a key to a column

JSONB is flexible, but it is not free. Each query that touches a key pays to extract it, PostgreSQL keeps no per-key statistics by default, and a typo in a key name silently returns null. I use a simple promotion rule. When a property appears in the filter or group-by of three or more regular reports, add a column and backfill it.

For revenue, a view is often enough and costs nothing to maintain:

CREATE VIEW purchases AS
SELECT
  event_id,
  occurred_at,
  user_id,
  properties->>'order_id'                AS order_id,
  (properties->>'revenue')::numeric(12,2) AS revenue,
  properties->>'currency'                AS currency
FROM events
WHERE event_name = 'purchase_completed';

Reports then read purchases like a normal table, and the casting logic lives in one place. If the view becomes a bottleneck, promote the columns and keep the view name as a compatibility layer. Indexing JSONB keys is covered in the PostgreSQL performance tuning article.

Constraints That Protect the Table

Validation belongs in the collector, but the database is the last line of defence. Two constraints in the definition above cost almost nothing and catch real bugs.

• The event_name pattern check rejects names with spaces, capitals or punctuation, which stops synonyms such as Page View from leaking in.
• The jsonb_typeof(properties) = 'object' check rejects arrays and bare strings, so properties->>'key' always works.

You could use a stricter list check for the six canonical names. I avoid it because adding a new event type would require an ALTER TABLE on the busiest table in the system. Keep the strict allow-list in the collector, where a deploy changes it, and keep the database check loose.

Mark columns NOT NULL when the collector guarantees them. A null in session_id breaks every session report quietly. By contrast, page_url, referrer and user_agent stay nullable, because server-to-server events may lack them.

One privacy rule belongs here. The table has no IP address column, and it never will. If you need geography, derive a country at the collector and store only that in properties. The cookieless article covers hashed identifiers, and it states clearly when it extends this table.

Deriving the sessions Table

A session is a group of events from one visitor with no gap above 30 minutes. The browser assigns session_id, so the database does not need to detect gaps. It only needs to summarise each session. That is cheap SQL:

CREATE TABLE sessions (
  session_id    text        PRIMARY KEY,
  anonymous_id  text        NOT NULL,
  user_id       text,
  started_at    timestamptz NOT NULL,
  ended_at      timestamptz NOT NULL,
  event_count   integer     NOT NULL,
  landing_path  text,
  referrer      text
);

INSERT INTO sessions
SELECT
  session_id,
  min(anonymous_id),
  (array_agg(user_id ORDER BY occurred_at DESC)
     FILTER (WHERE user_id IS NOT NULL))[1],
  min(occurred_at),
  max(occurred_at),
  count(*),
  (array_agg(page_path ORDER BY occurred_at))[1],
  (array_agg(referrer ORDER BY occurred_at)
     FILTER (WHERE referrer IS NOT NULL))[1]
FROM events
WHERE session_id IN (
  SELECT session_id FROM events WHERE received_at >= $1
)
GROUP BY session_id
ON CONFLICT (session_id) DO UPDATE SET
  anonymous_id = EXCLUDED.anonymous_id,
  user_id      = EXCLUDED.user_id,
  started_at   = EXCLUDED.started_at,
  ended_at     = EXCLUDED.ended_at,
  event_count  = EXCLUDED.event_count,
  landing_path = EXCLUDED.landing_path,
  referrer     = EXCLUDED.referrer;

The inner query picks every session that received an event since the last run, and the outer query recomputes those sessions from all of their events. Recomputing whole sessions matters. If you only aggregated the new events, a session split across two batches would get the wrong start time and count. The ON CONFLICT clause, described in the INSERT reference, makes the job safe to run twice. Pass the previous run’s start time as $1, with a small overlap. Because received_at is the server clock, a browser with a wrong clock cannot hide events from this job.

Notice that user_id takes the last known value. A visitor who signs up mid-session has early events with a null user_id and later events with a value. The session row picks up the value, so session reports attribute the whole visit to the person. Event-level stitching is a query problem, covered in the user analytics SQL article.

Deriving the users Table

An event, a session and a user are three different things. An event is one action, a session is one visit, and a user is one known person across many visits. Do not confuse the last with a visitor: unique visitors are counted by anonymous_id, which changes when someone clears storage or switches device. The users table holds only identified people, so first_seen_at means the first event carrying a user_id, not the first anonymous visit.

CREATE TABLE users (
  user_id       text        PRIMARY KEY,
  first_seen_at timestamptz NOT NULL,
  last_seen_at  timestamptz NOT NULL,
  signup_at     timestamptz
);

INSERT INTO users
SELECT
  user_id,
  min(occurred_at),
  max(occurred_at),
  min(occurred_at) FILTER (WHERE event_name = 'signup_completed')
FROM events
WHERE user_id IS NOT NULL
  AND received_at >= $1
GROUP BY user_id
ON CONFLICT (user_id) DO UPDATE SET
  first_seen_at = LEAST(users.first_seen_at, EXCLUDED.first_seen_at),
  last_seen_at  = GREATEST(users.last_seen_at, EXCLUDED.last_seen_at),
  signup_at     = COALESCE(users.signup_at, EXCLUDED.signup_at);

Unlike sessions, users merge with LEAST and GREATEST, because a person’s first sighting never moves forward in time. The table stores no email, name or other personal data. Join to your application database on user_id when you need those, so deleting a customer from the app database does not require rewriting history.

The daily_pageviews Rollup

The dashboard’s first chart is pageviews per day. Scanning every page_view row for a 90-day chart works at 10,000 rows and hurts at 100 million. A rollup stores one row per day and page, and the chart reads a few thousand rows instead.

CREATE TABLE daily_pageviews (
  day        date   NOT NULL,
  page_path  text   NOT NULL,
  views      bigint NOT NULL,
  visitors   bigint NOT NULL,
  PRIMARY KEY (day, page_path)
);

INSERT INTO daily_pageviews
SELECT
  (occurred_at AT TIME ZONE 'UTC')::date AS day,
  page_path,
  count(*),
  count(DISTINCT anonymous_id)
FROM events
WHERE event_name = 'page_view'
  AND occurred_at >= ((now() AT TIME ZONE 'UTC')::date - 2)::timestamp AT TIME ZONE 'UTC'
GROUP BY 1, 2
ON CONFLICT (day, page_path) DO UPDATE SET
  views    = EXCLUDED.views,
  visitors = EXCLUDED.visitors;

Two details make this rollup trustworthy. First, AT TIME ZONE 'UTC' fixes the day boundary, so the numbers do not shift when the server’s time zone setting changes. The lower bound is converted back from UTC for the same reason. Comparing against a bare date would use the session time zone instead. Second, the job recomputes the last three days from raw events rather than adding to the old total. Late events then land in the right bucket, and a rerun after a crash cannot double-count.

We once double-counted purchases because a rollup job added a delta to the stored total and then crashed after the insert but before saving its checkpoint. The restart added the same delta again. Replacing deltas with a full recompute of a bounded window removed the whole class of bug. The cost is that you re-read three days of data per run, which is acceptable until the table is very large.

A note on visitors: you cannot sum daily visitor counts to get a weekly figure, because one person visits on several days. The row stores a daily distinct count, and nothing more. Weekly uniques need their own query against events.

Rollup Table or Materialized View

PostgreSQL offers materialized views, which look like an easy answer. They are not always the better choice here, and the table below shows why.

Start with a materialized view if your events table is small and you want zero job code. Move to the upsert table once refresh time becomes noticeable. Both give the same chart, and the dashboard cannot tell the difference.

PostgreSQL Schema Design Naming Rules

Naming sounds trivial until three developers invent three styles. Pick these rules once and enforce them in review, because PostgreSQL schema design lives or dies by consistent names.

• Use snake_case for everything, to match the JSON payload exactly.
• Name tables as plural nouns: events, sessions, users.
• Suffix times with _at, dates with _date or name them day, and identifiers with _id.
• Never use reserved words, such as user, order or group, as table or column names.
• Name derived tables by grain: daily_pageviews tells you one row per day and page.

Event names follow the same discipline. The six canonical names are page_view, button_click, form_submit, signup_completed, add_to_cart and purchase_completed. Treat a new name as a schema change: add it to the tracking plan, the collector allow-list and the documentation together.

Applying and Writing the Schema from Node.js

Here is a complete script that creates the tables and inserts one event with a parameterized query. Save only the CREATE TABLE statements from this article as schema.sql, since the upserts need a $1 value. Then install pg, set DATABASE_URL and run the script as an ES module.

// migrate.mjs  (npm install pg)
import { readFile } from 'node:fs/promises';
import { randomUUID } from 'node:crypto';
import pg from 'pg';

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });

// Create the tables only once, so the script can be rerun.
const { rows } = await pool.query("SELECT to_regclass('public.events') AS t");
if (rows[0].t === null) {
  const ddl = await readFile(new URL('./schema.sql', import.meta.url), 'utf8');
  await pool.query(ddl);
}

const event = {
  event_id: process.argv[2] ?? randomUUID(), // pass a fixed UUID to test retries
  event_name: 'page_view',
  occurred_at: new Date().toISOString(),
  anonymous_id: 'anon_7f3a',
  user_id: null,
  session_id: 'sess_91bc',
  page_url: 'https://acme-shop.example/products/42',
  referrer: 'https://www.google.com/',
  user_agent: 'example-agent/1.0',
  properties: { product_id: 42 },
};

const { rowCount } = await pool.query(
  `INSERT INTO events
     (event_id, event_name, occurred_at, anonymous_id, user_id,
      session_id, page_url, referrer, user_agent, properties)
   VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
   ON CONFLICT (event_id) DO NOTHING`,
  [
    event.event_id, event.event_name, event.occurred_at,
    event.anonymous_id, event.user_id, event.session_id,
    event.page_url, event.referrer, event.user_agent,
    JSON.stringify(event.properties),
  ]
);

console.log('inserted rows:', rowCount);
await pool.end();

Run node migrate.mjs 5b30857f-0bfa-48b5-ac0b-5c64e28078d1 twice. The first run reports one inserted row and the second reports zero. That behaviour, not a separate dedupe step, is what protects your counts from retries.

Extending the Schema Later

Schemas change. The safe changes are additive: a new nullable column or a new derived table. In PostgreSQL, adding a nullable column without a default is a quick catalog change and does not rewrite the table. Adding a column with a volatile default, or changing a column type, can rewrite the table and hold a lock, so schedule those carefully.

When a later article adds a column such as a hashed visitor identifier or a partition key, it will say that it extends the definition in this article and link back here. Follow the same habit in your own repository: one canonical schema.sql plus numbered migration files, never ad hoc ALTER statements in a console.

How Real Systems Do This

Matomo stores raw visits and actions in relational tables and builds archive tables for reports, which is the same split between facts and derived data used here. Snowplow loads each event as one row in a wide events table with a fixed set of core columns, and it stores custom entities in separate columns or tables depending on the warehouse. PostHog also keeps one events table with free-form properties in a JSON column, and its ClickHouse setup can materialize frequently queried properties into their own columns. The details differ by product, so check each project’s documentation before you copy a layout.

The common pattern is the same in all three: one wide append-only fact table, flexible properties, and aggregated tables for the reports that people open every day. Your schema follows that pattern at a size one PostgreSQL server can handle.

Decision Framework

1. Is the data an immutable fact about something that happened? If yes, it belongs in events, not in an updated-in-place table.
2. Does every event type use the field? If yes, make it a column. If only some do, put it in properties.
3. Do three or more regular reports filter or group by a JSONB key? If yes, promote it to a column or a view.
4. Can a report be computed from raw events in a few seconds? If not, add a rollup at the grain the report needs.
5. Can the derived table be rebuilt from raw events with one idempotent statement? If not, redesign it before you ship it.

When NOT to Use This

• You expect more than a few hundred million events a month. A single PostgreSQL events table will still work for a while, but a column store such as ClickHouse is built for that load. The warehouse article explains when to move.
• You need cross-site funnels, identity resolution and experiments next week. Buying a product such as PostHog, Mixpanel or Amplitude beats building these features. Own the schema when ownership of data matters more than speed to features.
• Your events carry strict per-field typing, such as a regulated audit trail. A JSONB bag is too loose. Use typed columns or a schema registry instead.

Common Mistakes

• Using timestamp without time zone, which shifts daily totals when the server time zone or daylight saving changes.
• Storing revenue as float or inside JSON as a bare number you sum without casting to numeric, which drifts totals by cents.
• Building an EAV table for properties, which multiplies rows and forces self-joins in every report.
• Keeping an auto-increment key and no unique event_id, which turns every client retry into a duplicate row.
• Updating events in place to fix a bug, which destroys the audit trail and makes rollups impossible to reproduce.
• Adding derived tables that hold facts missing from raw events, which makes them impossible to rebuild.

Key Takeaways

• Keep one append-only events table as the source of truth, and treat everything else as rebuildable.
• Use the client-generated event_id as the primary key so retries cannot create duplicates.
• Store all times as timestamptz, and fix rollup day boundaries to UTC explicitly.
• Choose jsonb for properties, cast to numeric for money, and promote hot keys to columns or views.
• Derive sessions, users and daily_pageviews with upserts that recompute a bounded window.
• Never store raw IP addresses, and keep personal data in the application database.
• Follow strict naming rules, and treat each new event name as a schema change.

FAQ

What is the best PostgreSQL schema for an analytics events table?

Use one append-only table with a UUID event_id primary key, timestamptz columns for time, text columns for identifiers, and a jsonb column for free-form properties. Add derived tables for sessions, users and daily rollups rather than querying raw rows for every report.

Should I use JSONB or separate columns for event properties?

Use both. Put fields that every event shares into columns, and put per-event details into JSONB. When three or more regular reports depend on one JSONB key, promote it to a column or a view.

Should analytics timestamps be timestamp or timestamptz?

Use timestamptz. PostgreSQL normalises it to UTC on write, so comparisons stay correct when server settings change. Plain timestamp keeps whatever clock reading it received and invites daily-total bugs.

How do I avoid duplicate events in PostgreSQL?

Generate a UUID in the browser, make it the primary key, and insert with ON CONFLICT (event_id) DO NOTHING. A retried request then becomes a harmless no-op.

Do I need a sessions table if the browser already sends session_id?

You can group by session_id on the fly, but a derived table gives you start time, duration, landing page and event count in one cheap row. Most session reports then read thousands of rows instead of millions.

Conclusion

Good PostgreSQL schema design for analytics is boring: one fact table, honest types, flexible properties and rebuildable summaries. Get those right, and the later work on batching, tuning and warehouses builds on firm ground. The next article makes the write path non-blocking.

Rule of thumb: store facts once, derive everything else, and never let a derived table know something the raw events do not.

Last updated on 9 October 2026.