[yigitcan07]

I've found out key/value databases pushes for better architectural designs in enterprise environments. Especially in companies where different teams are responsible for a given business capability and it needs to scale above 1+ million users.

Postgres flexibility enables for design that is hard to scale. Both in terms of maintainability and performance. Enforcing K/V as a default database in one of my previous companies worked wonders.

[m11a]

I don't think there's anything unscalable about Postgres, or RDBMS's in general. I've seen even poorly tuned Postgres with unnormalised table designs work fine at a decent scale, to the point where I'm convinced that Postgres with a decent table design gets you very far.

As in: far enough that if you outscaled it, you'd be able to afford a team of excellent engineers to write an appropriate database system.

Almost all companies don't need the hyper scaling NoSQL databases supposedly promise. What they do often eventually realise is that they want the querying power and additional ACID guarantees of a typical relational database, so they end up developing a shitty relational database on top of a NoSQL database.

[yigitcan07]

My current company uses Postgres by default and we have a lot of different usecases. Again, another million+ users company with 10+ countries. It does indeed scale.

The problem is how people think about SQL vs K/V. They fall into the normalization trap a lot and create complex procedures and read operations. This usage causes once a month DB CPU spikes and some inident.

We are currently advocating for; de-normalized tables with K/V usage of Postgres and pushing the complexity to the application layer. Essentially, use Postgres at its bare minimums.

In short, to make Postgres scale; you essentially need to forget your "expert SQL knowledge" and use it as a K/V.

[m11a]

I think a balance is needed. Completely denormalised isn’t a good idea because if you have a single table with a large row, but a single column receives most the updates and it updates a lot, you’re going to have tons of dead tuples and write churn/overhead whenever updating a row in that table.

But I agree that some people go too far with normalisation. When done reasonably, with awareness of access patterns and application behaviour, I think it’s important though.

[h_tbob]

So like how do you do that? How do you store the user record for example as kv?

[yigitcan07]

It mostly revolves around; understanding your primary business concept that write operations revolve around (aggregate root) and duplicating data for different read scenarios (view models).

For example imagine you have an "E-commerce" product which you can change details about. The "Product" would be a write-model that you store as K/V. It would accept operations such as; "change price", "change category" etc. Your key would be "product id" and the value would be the whole object represented as json etc.

For every write operation you would read the write-model from the database, deserialize, modify it, put it back. Changes to the write-model would trigger events and you could build different read-models to access the data.