[yen223]

I almost have the opposite view:

When starting out you can get away with using a simple Postgres database. Postgres is fine for low-traffic projects with minimal latency constraints, and you probably want to spend your innovation tokens elsewhere.

But in very high-traffic Production cases with tight latency requirements, you will start to see all kinds of weird and wacky traffic patterns, that barebones Postgres won't be able to handle. It's usually in these cases where you'd need to start exploring alternatives to Postgres. It's also in these cases where you can afford to hire people to manage your special database needs.

[simonw]

Have you worked on any examples of projects that started on PostgreSQL and ended up needing to migrate to something specialized?

[yen223]

I did, twice.

The second time, we had a reporting system that eventually stored billions of rows per day in a Postgres database. Processing times got so bad that we decided to migrate to Clickhouse, resulting in a substantial boost to query times. I maintain that we haven't exhausted all available optimisations for Postgres, but I cannot deny that the migration made sense in the long run - OLTP vs OLAP and all that.

(The first time is a funny story that I'm not quite ready to share.)

[simonw]

That makes a lot of sense to me. One of my strongest hints that a non-relational data store might be a good idea is "grows by billions of rows a day".

[adhamsalama]

Isn't Clickhouse relational?

[crabmusket]

It does allow you to query with SQL, but it's meant for OLAP workloads, not OLTP. Its internal architecture and storage is different to what you'd usually think of as a relational database, like Postgres. See https://clickhouse.com/docs/en/concepts/why-clickhouse-is-so...

The term "relational" is overloaded. Sometimes it means "you can use SQL" and sometimes it means "OLTP with data stored in an AoS btree".

(And sometimes, a pet peeve of mine, it means "data with relationships" which is based on misunderstanding the term "relation". If someone asks you if "your data is relational" they are suffering from this confusion.)

[yen223]

Clickhouse is a SQL database, so I guess it is?

(Strictly speaking since a "relation" in the original Codd-paper sense is a table, anything with tables is relational. I don't know if that's what people mean by "relational", plus I don't know what counts as "non-relational" in that sense)

[simonw]

Kind of? By "relational" there I meant "traditional relational databases like MySQL and PostgreSQL that are optimized for transactions and aren't designed for large scale analytics".

[xarope]

Right, OLTP vs OLAP are very different workloads (using the car analogy, that would be like using a ferrari to tow a trailer, and an F250 to... oh wait, an F250 can do anything!).

But seriously though, even if you use postgres, as a former DBA (DB2 and Oracle) I would have tuned the OLTP database very differently to the OLAP database, and I don't mean just indexes, but even during ETL from OLTP->OLAP you might decide to de-normalize columns on the OLAP side simply to speed up queries (OLAP databases are the sort of database you were warned about, where indexes can be 10x the data size)

[adhamsalama]

Well, this isn't specific to Postgres, is it?

If you were storing billions of rows per day in MySQL, SQL Server, or Oracle, it still wouldn't be able to handle it, would it?

[yen223]

That's right. The key difference is using row-based vs column-based databases (i.e. OLTP vs OLAP). Any good database person should be cringing at the thought of using Postgres (or MySQL, Oracle, Sql Server, etc) for pulling reporting data.

That said, no regrets using Postgres there. If we started with Clickhouse the project could have not launched as quickly as it did, and that would have given us more problems.