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.


  • 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.


  • I have a compound key which I will split and turn into columns and indexes.
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.


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
  • Why are you moving from NoSQL to RDBMS? I don't understand the problem that you are trying to solve here Commented Jul 24, 2023 at 13:47
  • @FrancescoMantovani to design a database structure that is efficient for intensive updates and scalable. Reason to move is that current architecture does not do well with queries that are not based on compound key and have to do too many things in memory. Commented Jul 24, 2023 at 15:43
  • I totally agree with @LaurenzAlbe , the attr1 and attr2 need to become 2 separate columns. Are you on-prem or on the Cloud? Commented Jul 25, 2023 at 9:02
  • I'm on cloud, but needs to be portable. So need to go with something that's "standard" somehow. Indeed, I'm moving attributes to a separate column. Remaining question is if is worth 1 huge table or separate per account. (300-400 updates / minute in total and 500-3000 rows / account / day) Commented Jul 25, 2023 at 9:03
  • Well, what do you mean for "per account"? Are these "Accounts" customers? In that case I will use a different database per customer. You need to move away from the NoSQL way of thinking. No huge tables. On what Cloud are you? Commented Jul 25, 2023 at 9:07

1 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.

  • Thanks Laurenz. I didn't know about the update of JSON issue. But I felt it's anyway a bad idea, and though to represent the "json" in 2 columns (key + value) where the key is the old JSON attribute. And the SQL will group those and return like 1 line. Do you see a better approach? Commented Jul 25, 2023 at 8:07
  • my other question, that I would love to get an answer. Should I create different table for each account? or just add account_id as a key and have one huge table? Using the approach to have json split in key + value, I estimate to have about 3k rows per day added per account (having 2000 accounts) Commented Jul 25, 2023 at 8:10
  • I can't tell what is better. Make sure that you don't get too many tables, but also make sure that tables don't get too large. Consider partitioning. Concerning your first comment: yes, that may be a viable solution. Commented Jul 25, 2023 at 20:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.