Moving to SQL. How to design the database structure for intensive updates on JSON fields?

Question

I'm moving from a nosql database to PostgreSQL and my challenge is how to design it to be scalable using my data structure. Last time I used an SQL db was like years ago. Since than only nosql, so I need some help.

Usecase:

• An analytical system that stores counts (metrics) and that's flexible enough to store similar other rows.
• It does have a similar structure for all accounts, so I considered a single big table is better than having one per account_id
• currently there are like 500 rows/day/account_id and JSON has between 30-1000 elements.

Currently:

• I have a compound key which I will split and turn into columns and indexes.

account_id
type
date
resource_id (optional)

• value field currently is a JSON, which I see would fit within JSON type. Those JSON's have many different keys, so there is no real structure (at least 60% have a structure that can't be defined).

What the system does is to insert a row per day usually and then do many subsequent updates.

Example:

1023, "display", 20230706, some_unique_id, {"attr1":1, "attr2:20}

And afterwards we only have updates (increments) on attr1, attr2 and so on.

My questions are:

• would such a design work with realtime updates, which will be a few thousands per minute
• should I better use inserts into another temporarily table and than do increments using batches from inserts?
• are there other optimizations I should consider already? (partitioning, different table per account?)

Edit: Just came with an idea that I could have keys from the JSON object into a new column and index that as well and do the increments (updates) directly to that. Cons to this is that the index would be really big and single table might not be a good idea.

So it will become

account_id: integer, indexed
type: string, indexed
date: string, indexed (stored as 20230512)
resource_id: null, indexed
key_name: string, indexed
value: integer, this will get updates

Answer 1

I think that that is a bad design for any relational database, but particularly for PostgreSQL. Every time you modify the value of one attribute in a JSON value, PostgreSQL will create an entirely new version of the whole row including the complete JSON value. This will lead to a lot of data churn, and on top of that, every UPDATE will generate a "dead tuple" that has to be cleaned up by autovacuum.

Any attribute that you want to update frequently has to be stored outside the JSON in its own table column, and it should not be indexed. That way, you can hope for efficient HOT updates.