Event Analytics on JSON Properties in PostgreSQL Tested

Event data arrives as JSON because it is the format every client and collector can write. It stays JSON in the database because nobody wants to run a migration for each new property a product team decides to track. Both are reasonable. The trouble is the part in between: turning a pile of loosely structured events into answers about users, funnels and revenue that people will believe.

This guide covers event analytics when the events live in PostgreSQL as jsonb. It is the method I would follow, with measurements from a 2.25-million-event table that I built for the purpose. The data is synthetic, so the proportions describe my generator and not real traffic, and the lessons are about shape and cost.

The envelope and the properties

Every event has two parts that deserve different treatment. The envelope is what every event has: a name, a timestamp, a user id (or anonymous id), perhaps a session. The properties are what depends on the event: the plan chosen at signup, the amount of a purchase, the search term.

Make the envelope real columns. They are in every query, they have types that never vary, and indexes on them (name and time; user, name and time) serve almost every question. Leave the properties in jsonb until a property proves itself worth promoting.

That split decides where the slowness is, and the measurement surprised me. I ran five common questions on the raw table and on a typed copy:

question                                      raw jsonb    typed copy
daily active users (distinct user per day)    468 ms       436 ms
signup to purchase within 7 days (funnel)     670 ms       540 ms
top searches (reads a property)                75 ms        44 ms
revenue by plan (reads amount and plan)       n/a         207 ms (raw needs guards)
revenue by day (reads amount)                 108 ms        2.3 ms (facts table)

The first two questions use only envelope columns, and the JSON barely mattered: 468 against 436 ms. The questions that read a property, searches and revenue, are the ones that gained, from 1.7 times to 47 times. In other words, most of the cost people attribute to "JSON is slow" is really "the table is big and the query scans it." A good index on the envelope fixes that. The property parsing is a smaller cost that matters when a query reads one property over millions of rows.

The typed copy was 341 MB against 473 MB for the raw table with the same two indexes. A table of only the events worth a facts table, purchases and signups, was 50 MB. Size is what keeps a dashboard's working set in memory.

Step 1: shape the properties before the first event

How properties are named and typed decides how painful everything after is. The measurements in designing JSON event properties for analysis compared four designs of the same 500,000 purchases: verbose nested keys cost 292 bytes per event, short flat keys 161, a hybrid of columns plus a small jsonb 118, and key/value arrays 265. A filtered sum took 44, 34, 24 and 1,025 ms. Short, flat, consistently named properties with one type each are cheapest, and the key/value array shape that looks flexible is the slowest to query.

The design rules that came out of it are worth repeating because they are cheap to follow on day one and expensive to retrofit: one name and one type per property, units in the key or a neighbor, no null for "not applicable", and a registry of names with types that the ingestion path checks. A schema in that path turns userId next to user_id into a rejected event on the day it appears (writing your first JSON Schema).

Step 2: write the metric definitions down

Before any optimization, write what each metric means. "Daily active users" sounds obvious, and a surprising amount of disagreement comes from the details.

An active user needs an event definition (any event, or only meaningful ones), a time zone for the day (UTC, or each user's), and an identity (user_id, or an anonymous id stitched to one). My query counts distinct user_id over date_trunc('day', ts), in the session's zone, and gives 71 days of data. Change the zone and users near midnight move to a neighboring day.

A funnel needs ordered steps, a window, and a rule for repeated steps. Mine is signup, then a purchase by the same user within 7 days after it:

select count(distinct s.user_id) as signups, count(distinct p.user_id) as converted
from ev_t s
left join ev_t p on p.user_id = s.user_id and p.name = 'purchase'
                and p.ts > s.ts and p.ts <= s.ts + interval '7 days'
where s.name = 'signup';
-- 25000 | 125

25,000 signups and 125 converted: 0.5 percent. In my synthetic data purchases are scattered across users independently of signups, so the conversion is tiny and the figure means nothing. The point is the definition. The left join keeps users who did not convert in the denominator. The condition p.ts > s.ts enforces order. The window is in the join. Change any of them and the number changes. A funnel that is not defined in code is a funnel that two analysts compute differently.

Retention needs a cohort (users active in week 0), a return event and a period. I computed it on weekly buckets of activity; on my uniform synthetic data it was 100 percent, which is not informative, but the pattern is the same: group to one row per user and period first, then self-join the periods, not the events.

Whatever the metric, put its SQL in a view with the definition in a comment. The view is the single place that says what "active" means.

Step 3: promote what the dashboards read

When a property shows up in a recurring query, give it a column. The cheapest, safest way is a derived table refreshed from the raw events, as in extracting JSON properties into typed columns every night:

• a typed an_facts of purchases and signups loaded in 1.14 s from 2,000,000 events;

• a watermark insert of the 50,000 new events in 0.2 s;

• ON CONFLICT DO NOTHING so that a rerun or an overlapping window is harmless;

• pg_input_is_valid so that a bad amount becomes NULL and a counted anomaly, not a failed run.

The result served daily revenue in 2.3 ms against 108 ms from the raw events. If the questions are stable, this is the best return on effort in the whole guide.

Step 4: index for the lookups that remain

Some questions have to touch the raw events: "show me the events for this search term", "find the session where this happened". Those are selective lookups, and indexes help them, if they are the right kind and the filter is selective (indexing hot JSON properties for dashboards):

selective lookup (2,000 of 2.25M rows)   no index 65 ms   expression index 4.7 ms (15 MB)
                                         GIN 6.1 ms (26 MB)   partial index 1.8 MB
broad filter (4 percent of rows)         no index 85 ms   expression index 148 ms

The results to remember: partial indexes for properties that belong to one event type, GIN only when the keys are unpredictable and the queries are written as containment, and no index at all for a filter that returns several percent of the table, where it is slower than a scan. GIN also cost 57 percent more on writes (341 ms to 535 ms for 100,000 inserts), which an event table feels.

Step 5: expect types to drift, and guard

An event pipeline has many writers, and writers change. A property that was a number becomes a string in one client, an object in another, null in a third. The failure is loud and sudden: invalid input syntax for type numeric: "n/a", and the dashboard is down (handling JSON properties that change type).

In my table the amount of 300,000 purchases was a number 297,110 times, a string 2,429 times, an object 131 times, null 143 times and absent 187 times. The sum of the numbers was 13,278,707.0. Accepting numeric-looking strings gave 13,370,500.0, a difference of 91,793 that depends on a decision, not on arithmetic. A function that normalizes each shape returned a value for 299,327 events and NULL for 673; written as a SQL function with a CASE it still failed on "n/a", because the planner evaluates constant sub-expressions early, and written in plpgsql it worked. Put the rule in one function, write down which interpretation the dashboards use, and send the 673 to whoever owns the producer.

Step 6: backfill when the shape changes

Eventually a property is renamed or converted: amount becomes amount_cents. Old events must follow, or readers must handle both forever. I tried three ways on 1.5 million events, with 186,767 to convert (backfilling historic events after a schema change):

one UPDATE            1.37 s   265 MB of WAL   186,767 dead rows   table 213 -> 239 MB
75 committed batches  1.78 s   same dead rows, short transactions, resumable
rewrite into new table 1.21 s + 0.44 s for the key   no dead rows   215 MB

All three gave the same total of 909,045,150 cents. The batch method is the one for production tables because it can be stopped and does not hold locks for long. The rewrite is the cleanest, and needs a plan for writes during the copy. Whichever you use, make it idempotent by selecting only rows that still have the old shape, deploy readers that accept both shapes first, and verify with an aggregate before and after.

Step 7: watch the data itself

A design like this runs on a few checks that look at the data, not the code:

1. Key counts. Which keys exist per event name and how often. A new key is a deploy you did not hear about; a key that vanished is a client that stopped sending it.

2. Type counts. jsonb_typeof per key. A second type for a key is the early warning for the failure above.

3. Volume per day and per name. A sudden drop is an outage upstream. A sudden rise is a loop or a bot.

4. Lateness. The gap between event time and arrival time. Late events break a nightly job that assumes yesterday is complete; a lookback window handles it.

5. Reconciliation. Sums and counts in derived tables against the raw table for the same period. If they differ, something dropped rows.

Each of them is a single query, and together they take a few seconds on a table of this size. Schedule them with the nightly job and alert on the output.

One funnel, three answers

The funnel deserves a closer look, because it is where definitions bite hardest. I ran what sounds like the same question three ways on the same 2.25 million events. The question: of the users who signed up, how many bought within 7 days?

join on any signup, purchase within 7 days after that signup       125 users converted
first signup per user, purchase within 7 days of the first one       0 users converted
single window pass over signup rows, purchase within 7 days of each  135 signup events

Three queries, three numbers: 125, 0 and 135. None of them is a bug. They count different things.

The first joins every signup event of a user to purchases after it. In my synthetic data each user has ten signup events (the generator repeated them), so a user converts if any of the ten signups is followed by a purchase within the window. The second picks each user's first signup and measures from there: of the 125 users who ever purchased after their first signup, none purchased within 7 days of it, because the later signups are what made the first query succeed. The third counts rows in the signup stream, 250,000 of them, and reports 135 events, not users. The unit of the count is the whole difference: events, not users.

Real data has its own version of this. Users sign up more than once (a new device, a reinstall), purchases follow other purchases, and the "first" event may not be the interesting one. The definition has to say what is counted (users or events), what starts the clock (the first signup or any), what ends it, and what happens with repeats. Put those in the metric's description and in the SQL comment, and test the query on a handful of users you can follow by hand. When two dashboards disagree, this is almost always why.

Sessions are a definition too

Many questions want sessions: events grouped by a gap of inactivity. PostgreSQL has no session function, and window functions build one in two steps. Mark each event that starts a session (the first, or one more than 30 minutes after the previous event of the same user), then take a running sum of the marks:

select user_id, ts, name,
       sum(new_session) over (partition by user_id order by ts) as session_no
from (select user_id, ts, name,
             case when ts - lag(ts) over (partition by user_id order by ts) > interval '30 minutes'
                    or lag(ts) over (partition by user_id order by ts) is null
                  then 1 else 0 end as new_session
      from ev_t) x;

On the 2.25 million typed events it took 2.98 s and produced 2,000,913 sessions, 1.12 events per session. That ratio says more about my generator, which spreads events evenly in time and so produces almost no sessions with more than one event, than about any product. On a real table the same query is the foundation for session length, pages per session and bounce rate, and it is the most expensive query in this guide. Compute it once into a table keyed by user and session, refreshed with the nightly job, and join to that. The 30 minute rule is a convention, not a fact: write it as a parameter in a view so that changing it is one edit.

Duplicates and late events

Two quieter problems appear in real event tables, and both change counts. Duplicates come from retries: a client sends an event, the network drops the response, and it sends again. A cheap check counts rows that share user, name and timestamp: in my table 2 events did, which is nothing, and in a real pipeline with retries it can be a few percent. De-duplicate on an event id supplied by the client at creation time, and enforce it with a unique constraint at ingest. Late events arrive after the day they belong to has been processed: a mobile app that was offline uploads yesterday's events this morning. A nightly job that only reads "today's events" never sees them. A lookback window (reprocess the last few days every night) with idempotent inserts fixes it (extracting JSON properties into typed columns every night), at the price of a few days of numbers that can still change. Say so on the dashboard.

A worked report: from events to a weekly review

To show how the pieces fit, here is the path for one request: "give me a weekly view of signups, first purchases and revenue by plan."

I start with the registry. The three events involved are signup (with plan), purchase (with amount and currency) and the envelope. The registry says amount is a number in currency units, and the ingestion check rejects events where it is not. That decision removes the type-drift problem for new data. For the old data I count the types of amount (a number for 297,110 purchases and something else for 2,890) and decide that the weekly numbers use only the number-typed ones, with the excluded count shown beside them.

Next the definitions. A signup is the first signup event of a user, and a first purchase is the first purchase after it. Weeks start on Monday in UTC. Revenue is the sum of the number-typed amounts, in the currency of the event, which I report separately by currency. I write them in a view with comments.

Then the data. I extract signups and purchases into a facts table with user_id, ts, plan, amount and currency, loaded in about a second and kept current by a watermark insert that takes 0.2 s for 50,000 new events. The weekly query runs against 500,000 rows with an index on (day, currency), in a couple of milliseconds, instead of 108 ms against 2.25 million rows of JSON.

Finally the checks. Row counts in the facts table against the raw events for the same days; sums of amount by day in both; a count of rejected values by reason. Those go in the job's output. When the product team renames plan to tier in the client next quarter, the key count shows plan falling and tier rising on the day it ships, the extraction reads both for a while, a backfill rewrites the old rows in batches, and the report does not change.

None of this needs a special tool. It needs the habits: define the metric, shape the data once, check it every night.

Retention: delete by partition, not by DELETE

Event tables only grow, and a policy for old events matters before storage does. The obvious implementation, a nightly DELETE of rows older than the cutoff, is the wrong one for a table of this size. I compared it with dropping a partition on the same data, with the events split by month.

remove January (892,799 events)
DELETE from a plain table      373 ms   48 MB of WAL   table still 292 MB until vacuum full
DROP of a monthly partition      3 ms   91 kB of WAL   space returned at once

The delete scanned and marked 892,799 rows, wrote 48 MB of log, and left the table the same size, since the space is reusable but not returned to the operating system. Dropping a partition took 3 ms and wrote almost nothing, and the disk space came back immediately. The same partitioning helps reads: a query for one week touched a single partition (ev_p_2026_03) and read about 41,600 rows through its index, with the other months excluded by the planner.

So partition event tables by time from the beginning. Monthly partitions suit a table with millions of events a month; daily ones suit hundreds of millions. Create next month's partition ahead of time, keep a job that drops the expired ones, and keep the typed facts table (which is much smaller) for longer than the raw events. Retention is also a privacy decision. Events often contain identifiers, and the shortest time you can keep them is the one that survives an audit.

Identity is the hardest column

Everything above counted a user_id, and in a real product that is the weakest column. A visitor has an anonymous id before they sign in, another on a second device, and a user id afterwards. Counting distinct ids overstates users, and joining anonymous events to a user needs an identity map.

The simple version is a table that maps each anonymous id to the user id it was later seen with, populated from the login event, and an analytics view that resolves every event to coalesce(mapped_user_id, anonymous_id). Two details matter. The mapping has a time: events before the login belong to the user only if you decide that earlier activity on the same device counts as theirs, which is a product and privacy decision, not a technical one. And merges happen: two anonymous ids can turn out to be one person, and counts change retroactively when they do. Say on the dashboard that unique users are approximate.

Whatever you choose, keep the raw identifiers in the raw events and resolve identity in a derived table. Then the rule can change without touching the data, and you can rebuild history when it does.

Privacy belongs in the design

Analytics events are a standing collection of personal data, usually collected for a different reason than the one for which they get used. A few measures cost little and help a lot. Keep personal fields (emails, names, free text) out of event properties and hold an id instead. Hash or drop IP addresses at ingest. Put a retention limit on the raw table. Make deleting a user's events a query you can run, which partitioning by time does not help with, so keep an index on the user id. And log who queries the raw events, since a table of what every user did is sensitive in itself.

This is also where redaction of what you log comes in (hiding secrets from JSON logs): the pipeline that moves events is full of places where a token or an email can leak into an error message.

Making the numbers trustworthy

An analytics number is only useful if people believe it, and belief comes from being able to explain it. A few practices have worked across every dataset in this series.

Show the denominator and the exclusions. A revenue figure that says "from 297,110 purchases with a numeric amount; 2,890 excluded" is easier to trust than one that says only the sum, and it gives readers the means to challenge it. The same goes for a funnel, which should say what it counted and over what window.

Keep a reconciliation. Pick one number that exists in two places and compare them every night: the total of amounts in the raw events and in the facts table, the count of signups in the events and in the application database. When they differ, the job reports it and nobody has to discover it in a meeting.

Version the definitions. When "active user" changes from any event to a meaningful event, the history changes with it. Record the change date, and where continuity matters, compute both definitions for the overlap period so the chart can show the step.

Test the queries. A metric view can have a unit test: a few hand-written events whose expected counts you know, run through the view, with the result compared. That is a small amount of work and it catches the day somebody edits the view and the funnel drops to zero.

Keep the vocabulary stable. Event names are an interface. Renaming signup to user_registered breaks every query that mentions it and every dashboard that was built on those queries, and the old events keep the old name. Treat event names like column names in a public table: add, do not rename, and when you must, publish the mapping.

Where this stops

PostgreSQL carries event analytics a long way. With a typed facts table, a couple of partial indexes and a retention policy (drop or archive raw partitions older than the questions need) it handles tens of millions of events comfortably on one machine. Beyond that, the economics change: a columnar engine compresses repeated keys and scans only the columns a query touches, and DuckDB, ClickHouse and the warehouses make a different trade. The reading rules in this guide carry over, since the shape of the data and the definitions of the metrics are the same. If you start there, the structured-file articles are a good companion (JSON problems worked from real data, analyzing a GitHub events dump).

The PostgreSQL pieces used here are documented in JSON functions and operators, INSERT ... ON CONFLICT and index types. To look at an event while you design a registry, paste it into the JSON formatter, and draft its schema with the JSON schema generator.

A decision table

When a dedicated analytics stack is the better answer

The volume is large and growing. Past tens of millions of events, a columnar store is cheaper per query and per byte. Do not stretch a transactional database further than it pays.

Analysts need a point-and-click tool. Product analytics platforms provide funnels, retention and cohorts without SQL. If that is the need, buying is cheaper than building.

You need real-time dashboards. A nightly job gives yesterday's numbers. Streaming aggregation is a different system.

The questions are one-offs. For a single question over a dump, read the file directly with DuckDB. A pipeline is for questions you ask every week.