undefined | Better HN

0 pointsyen2231y ago0 comments

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

0 comments

simonw1y ago

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

adhamsalama1y ago

Isn't Clickhouse relational?

3 more replies

xarope1y ago

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)

adhamsalama1y ago

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?

yen223OP1y ago

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.

j / k navigate · click thread line to collapse

0 comments

simonw1y ago

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

adhamsalama1y ago

Isn't Clickhouse relational?

3 more replies

xarope1y ago

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!).

adhamsalama1y ago

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?

yen223OP1y ago

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.

j / k navigate · click thread line to collapse