3 ms·

Not at all too sales-y. I'm all for keeping as much as possible in your initial Postgres deployment as possible. If your team isn't having to work around thing

Not at all too sales-y. I'm all for keeping as much as possible in your initial Postgres deployment as possible. If your team isn't having to work around things and things "just work" it's a wonderful conjunction of requirements and opportunity. It's incredible how much you can get out of a single instance, really remarkable. I'd also add it's still worth it even if there is a little pain. But I've found that once I cross about 8-12 terabytes of data I need to specialize, and that a pure columnar solution like ClickHouse really begins to shine even compared to hybrid solutions given the amortized cost of most analytic workloads. This difference quickly adds up and I think at that scale really makes a difference to the developer experience that a switch is worth the consideration. Otherwise stick to Postgres and save your org some money and more importantly sanity. You reach a point when you have enough queries doing enough work that the extra I/O and memory required by PAX/hybrid becomes noticeably more costly than pure columnar, at least for the workloads that I have experience with. ClickHouse is now in my toolbox right alongside Postgres with things to deploy that I can trust to get the job done.

nijave 2y ago
A few other things I can think of as well - you'd probably at least want a read replica so you're not running queries on your primary db - if you're going to the trouble of setting up a column store, it seems likely you're wanting to integrate other data sources so need some ETL regardless - usually column store is more olap with lower memory and fast disks whereas operational is oltp with more memory and ideally less disk io usage I suppose you could get some middle ground with PG logical rep if you're mainly integrating PG data sources
akulkarni 2y ago
YMMV but our largest internal dogfooded Timescale instance is 100s of terabytes https://www.timescale.com/blog/how-we-scaled-postgresql-to-350-tb-with-10b-new-records-day https://www.timescale.com/blog/how-we-scaled-postgresql-to-3... (Post is a year old, IIRC the database is over one petabyte now)
- tomnipotent 2y ago
  Totally doable, of course. But I'll need fewer ClickHouse servers for the same amount of data, and I'll get more utilization out of them with faster query times. High selectivity combined with colocated row data means that hybrid storage formats will need to read more I/O, use more memory, and churn through more buffer for the same queries.
  - jascha_eng 2y ago
    At the risk of getting my CEO angry (sorry Ajay :D): ClickHouse is great. But it also means hosting another database and losing ACID compliance, the question is often not ClickHouse vs Timescale but Postgres + ClickHouse vs just Timescale. In general the argument I was originally trying to make is not to never use ClickHouse, I think it's a great product. But if you already are on postgres, it might just be easier to give Timescale a try than to adapt everything to work with ClickHouse right away. There is more to consider here than raw query speed. And while I'm sure the systems behave differently scale and speed wise, I also wouldn't say Timescale looses straight up, there is situations where Timescale is faster and if it really breaks down for a use-case nothing stops you from still doing the postgres to ClickHouse migration. In the end timescale is just a better postgres, so there is no lock in.
saisrirampur 2y ago
Great summary and spot on! Once you reach that TBs scale Postgres is hard to scale. Yes, you could make Postgres scale to larger scales but it would need deep expertise and architecting and the experience wouldn’t be “it just works”. Ex-Citus here, we had PB scale deployments which needed multiple months of effort to implement and an expert team to manage. Eventually many (ex: CloudFlare, Heap) migrated to purpose built stores like ClickHouse, Singlestore. And not to forget storage costs. Sure there was compression/columnar in Postgres/Citus too - but it didn’t fare well compared to pure columnar stores. (Disclaimer: This is Sai from ClickHouse/PeerDB team)

nijave 2y ago

A few other things I can think of as well - you'd probably at least want a read replica so you're not running queries on your primary db - if you're going to the trouble of setting up a column store, it seems likely you're wanting to integrate other data sources so need some ETL regardless - usually column store is more olap with lower memory and fast disks whereas operational is oltp with more memory and ideally less disk io usage I suppose you could get some middle ground with PG logical rep if you're mainly integrating PG data sources

akulkarni 2y ago

YMMV but our largest internal dogfooded Timescale instance is 100s of terabytes https://www.timescale.com/blog/how-we-scaled-postgresql-to-350-tb-with-10b-new-records-day https://www.timescale.com/blog/how-we-scaled-postgresql-to-3... (Post is a year old, IIRC the database is over one petabyte now)

tomnipotent 2y ago
Totally doable, of course. But I'll need fewer ClickHouse servers for the same amount of data, and I'll get more utilization out of them with faster query times. High selectivity combined with colocated row data means that hybrid storage formats will need to read more I/O, use more memory, and churn through more buffer for the same queries.
- jascha_eng 2y ago
  At the risk of getting my CEO angry (sorry Ajay :D): ClickHouse is great. But it also means hosting another database and losing ACID compliance, the question is often not ClickHouse vs Timescale but Postgres + ClickHouse vs just Timescale. In general the argument I was originally trying to make is not to never use ClickHouse, I think it's a great product. But if you already are on postgres, it might just be easier to give Timescale a try than to adapt everything to work with ClickHouse right away. There is more to consider here than raw query speed. And while I'm sure the systems behave differently scale and speed wise, I also wouldn't say Timescale looses straight up, there is situations where Timescale is faster and if it really breaks down for a use-case nothing stops you from still doing the postgres to ClickHouse migration. In the end timescale is just a better postgres, so there is no lock in.

saisrirampur 2y ago

Great summary and spot on! Once you reach that TBs scale Postgres is hard to scale. Yes, you could make Postgres scale to larger scales but it would need deep expertise and architecting and the experience wouldn’t be “it just works”. Ex-Citus here, we had PB scale deployments which needed multiple months of effort to implement and an expert team to manage. Eventually many (ex: CloudFlare, Heap) migrated to purpose built stores like ClickHouse, Singlestore. And not to forget storage costs. Sure there was compression/columnar in Postgres/Citus too - but it didn’t fare well compared to pure columnar stores. (Disclaimer: This is Sai from ClickHouse/PeerDB team)