Designing JSON Event Properties for Analysis From Day One

An analytics event is a name, a time, a user and a bag of properties. The bag is where the trouble starts, because it is free form. A team ships a new feature, adds a few properties, and six months later an analyst is trying to write one query across three spellings of the same idea. Most of that pain can be avoided by deciding how properties are named and shaped before the first event is sent, and most of the rest by knowing what the design costs.

I took one event, a purchase with five properties, and stored 500,000 of them in four designs. Then I compared what they cost in space and in a typical dashboard query.

The four designs

A. Verbose nested. Descriptive names, grouped under a wrapper key:

{"user_identifier": 12, "event_properties": {"purchase_total_amount_in_usd": 42.5,
  "purchase_currency_code": "usd", "purchase_item_count": 2, "purchase_payment_method": "card"}}

B. Short flat keys. The same facts, one level, short names:

{"uid": 12, "amount": 42.5, "cur": "usd", "items": 2, "pay": "card"}

C. Hybrid. user_id, amount and currency are real columns, with numeric(10,2) for the amount. Everything else sits in a small jsonb:

{"items": 2, "pay": "card"}

D. Key/value array. A design some analytics tools use, where every property is an object with a key and a value:

[{"k": "uid", "v": 12}, {"k": "amount", "v": 42.5}, {"k": "cur", "v": "usd"}, ...]

All four hold the same information. The first three differ in how much they repeat and how the database can reach the values; the fourth makes every property uniform at the cost of structure.

What they cost to store

design        document bytes   total per event   table size
A verbose          223            292 B            139 MB
B short flat       104            161 B             77 MB
C hybrid            42            118 B             56 MB   (columns carry the rest)
D key/value        201            265 B            127 MB

The verbose design was 1.8 times the size of the flat one for identical facts. The cost is the key names: purchase_total_amount_in_usd is 28 bytes, repeated in every row, and jsonb stores keys as text in each document. At 500,000 events the difference is 62 MB. At a billion events it is a hundred times that, and the cost is paid again in backups, replicas and memory.

The key/value array is no better than verbose nesting: each pair carries both k and v names, so it repeats a pair of short strings per property.

The hybrid is the smallest, because the numeric and integer columns store values only, with no key names and no per-value header. The small remainder in jsonb carries the properties that are not worth a column.

What they cost to query

The test query was a sum of the amount for card payments:

A  sum((doc->'event_properties'->>'purchase_total_amount_in_usd')::numeric) where ... ->>'purchase_payment_method' = 'card'    44 ms
B  sum((doc->>'amount')::numeric) where doc->>'pay' = 'card'                                                                        34 ms
C  sum(amount) where doc->>'pay' = 'card'                                                                                           24 ms
D  unnest each event's array, find k = 'amount', and check another element for pay = card                                         1,025 ms

All four returned 8,333,366.0. The first three are in the same range, with the typed column the fastest. The key/value design was 30 to 40 times slower, because finding a property means iterating the array, and a condition on a second property means doing it again. It can be indexed (with a GIN index and containment queries), but it asks the analyst to write a join-like query for every question, and that is the larger cost than the 1,025 ms.

The lesson is that a uniform-looking structure is not a convenient one. Dashboards ask "amount where payment method is card", which wants two named fields on the same row.

Rules that keep properties analyzable

Name once and stay with it. Choose a style (snake case is common) and use it for every key. Keep a registry of property names, with a type and a description, and require a change to it for a new property. The registry can be a JSON Schema that the ingestion path checks (writing your first JSON Schema), and it is the place to catch userId next to user_id (naming keys consistently in JSON).

Keep short names for hot properties, and descriptive ones in the registry. The registry maps amount to its meaning. The events carry the short name. A verbose name in each event is paid for by every row.

One type per key, always. amount is a number in every event, not a number in some and "12.50" in others. A key that changes type is the most expensive mistake in analytics data, because every query has to guard it (handling properties that change type).

Put the unit in the contract. Either the key says it (amount_usd, duration_ms) or a neighboring property does (currency). A bare amount of 42.5 means nothing three years later.

Do not use null for "not applicable". Leave the key out. A missing key and a null look the same to most queries and different to some, and you want only one way to say it (JSON columns and schema drift).

Flat unless there is a reason. A nested utm object with campaign, source and medium is a real group and is fine. A wrapper called event_properties around everything adds a level to every path and nothing else.

Cap the cardinality of values that become dimensions. A property such as path with a unique value per event cannot be grouped on. Put high-cardinality text in a field you do not group by, and keep a low-cardinality category next to it.

Promote what the dashboards use. When a property is in the WHERE or GROUP BY of a recurring query, move it to a column (extracting properties into typed columns nightly shows how, and indexing hot JSON properties for dashboards the alternative).

Where the design lives in practice

Most teams do not write events to PostgreSQL directly. They send them through a collector into a warehouse, and the warehouse stores the properties as a JSON or variant column. The same arithmetic applies there: key names repeated per row cost storage and scan time, and a type that varies makes queries fail or lie. Columnar stores compress repeated names well, which reduces the storage difference and not the query one. Check what yours does with a test like this one before you decide that names are free.

For the design of the event as a document, see also using JSON in application design without making a mess, and for the PostgreSQL functions used here the JSON functions page. The Segment spec and similar published event specs are worth reading as examples of a registry. To draft a schema from a sample event, the JSON schema generator can start you off.

When the event design is not the bottleneck

The volume is small. At a few million events a month, the difference between 161 and 292 bytes is not what slows the dashboard. Do not redesign for a size you do not have.

The queries are ad hoc. If analysts explore and do not run fixed dashboards, a flexible shape matters more than the last millisecond. Keep the properties in JSON and promote on demand.

The source is fixed. If you consume a third party's events, you cannot rename their keys. Map them into your own names on ingestion, in one place.

The slowness is somewhere else. A missing index on the timestamp, or a scan of years of history for a daily chart, costs more than the shape of the properties.