Denormalise person properties

[mariusandra]

Is your feature request related to a problem?

We have a few problems with the persons table:

• Clickhouse collapsing merge trees: Make person and person_distinct_id deletion efficient #4242
• Problem with timestamp precision: Increase ClickHouse Person DateTime precision #4260
• Issues with $set_once: Looks like set_once is not working as expected #4082
• Adding $increment in addition to $set and $set_once: Add $increment for numerical user props plugin-server#326

These problems all require some changes to the person tables and thus it makes sense to tackle them all together.

Describe the solution you'd like

I'd like to denormalise the person properties JSON field into a separate person_properties table. This means one property per user per row. This lets us update different properties independent of one another, and avoids sending multi-kilobyte JSON blobs to and from postgres. That in turn greatly improves data integrity, as we will get rid of the case where two plugin servers are updating (different!) properties on the user at the same time, and both read the same huge input json, modify one field. Then the second server, not knowing the first server already saved its changes, will override the first server's changes when writing its modified json back into postges.

This person properties table could start with collapsing merge tree on clickhouse, saving future refactoring.

Describe alternatives you've considered

In PostHog/plugin-server#326 @yakkomajuri went through some effort for $increment to update the field in the JSON via postgres. This avoids losing numbers when when two $increment calls update the same field at the same time, but the other server could still $set and override the entire properties json.

Additional context

:gottagofast:

Thank you for your feature request – we love each and every one!

[yakkomajuri]

Yeah so I was thinking about this as well - it was pointed out that $increment had a race condition but $set and $set_once have been that way for a little while...

I think as an initial step in the plugin server we could use jsonb_build_object for the others, as you mentioned.

But I do think this approach here warrants consideration. The current way of storing props is certainly messy for updates, and encourages logic to move from the db layer to the plugin server layer (causing stuff like the race conditions issue).

[mariusandra]

From a business perspective, denormalized user properties gets us closer to this line item:

... as "edge cases" and "losing data" are things you don't really want in that usecase :).

[macobo]

I assume this is intended for clickhouse specifically.

On clickhouse, my worry is that this will complicate queries at a significant performance loss due to needing additional joins on every query and perhaps also complicating our sharding story.

If we wanted to pull out columns we could do it the same way we do it for events (extra columns) on the persons table which would speed up some queries but would not solve the issues listed above.

[mariusandra]

I'm mainly interested in improving data integrity, and as of such this mostly applies to the source of truth: postgres.

However even with denormalized "source" properties, it might still make sense to store a cached/computed json on the person. For compatibility reasons, if nothing else.

[Twixes]

AFAIK – disclaimer: I'm definitely not a ClickHouse expert – ClickHouse joins are expensive enough to make this type of denormalization pretty bad for scalability (as opposed to denormalized columns which are pretty cheap).

[fuziontech]

@EDsCODE and I did an experiment a while ago storing property key values in a key value table like this. It does not perform well. The best thing we can do is add a ton of columns programmatically. This is the beauty of a columnar DB. The more we denormalize into columns the faster the query

[mariusandra]

@fuziontech "in a key value table like this" refers to clickhouse, right? Useful context, thanks. However now I'm still talking about storing this in postgres. I guess adding columns dynamically in postgres is a rather eccentric idea from which we want to socially distance ourselves.

Anyway, here at team extensibility we're stuck behind a fork in the road:

1. Use the postgres jsonb_build_object when $set and $set_once come in to update individual properties (the json field) on a Person.
2. Denormalise properties into another table (by person_id) and update them with normal SQL statements. Then select and combine all of them into a json object and store that on Person for quick access, probably directly via a SQL query.
3. Same, but also get rid of the json field and update.

My fear is that option 1 that we will default to has a much lower throughput ceiling than options 2 and 3. Imagine a person with 1MB of json properties. That could easily happen if someone adds $set: { avatar: '1MB data-url' }. Then imagine serialising and deserialising that 20 times per second in a Postgres UPDATE statement, since someone thought it a good idea to write a plugin that adds $increment: { user_lifetime_event_count: 1 } to every event.

My gut feeling says we might run into deadlocks if we go for the first option, and then have to implement option 2 in a rush during an outage.

[posthog-bot]

This issue hasn't seen activity in two years! If you want to keep it open, post a comment or remove the stale label – otherwise this will be closed in two weeks.

[posthog-bot]

This issue was closed due to lack of activity. Feel free to reopen if it's still relevant.