4 ms·

> Queries went from a 12 hour table scan to ~2 minutes, and production queries from 45 sec to 400ms. Hmm, why do you have such large tables and then do full sc

by alecco 6y ago

> Queries went from a 12 hour table scan to ~2 minutes, and production queries from 45 sec to 400ms. Hmm, why do you have such large tables and then do full scans? Edit: and if it's analytics why were you using a row store instead of a columnar database? It's like saying changing to a Ford Mustang was 10x faster than a rusty John Dere combine.

lumost 6y ago
Data is the size that it happens to be for many businesses. this could easily have been a large user view history table or similar online use case that folks happened to need analytic capabilities on.
- wiredfool 6y ago
  In this case, it's the output from ~10k simulations, each over up to a million zones. Production queries will select either one row by primary key, or sum metrics on up to a million rows over a set of the values.
mastazi 6y ago
> and if it's analytics why were you using a row store instead of a columnar database? This point confuses me, parent is telling us how they went from a row-based (pgsql) to a column-based DB (Clickhouse), and you asked them why they didn't?
- alecco 6y ago
  There are many, many ways to avoid a full scan. Fixing a dumb query and proper indexing (partial indexes if this is expensive), materialized views, and several more. It's very common the developers either resist or are not aware of how to use a database. A full scan is dumb and will retrieve all data of this big table without any filtering or even order. This is highly suspicious. And in the very, very rare case it is absolutely necessary to do a full scan, then Postgres might not be a good idea. Why is it being bashed? I read "Postgres was 12h slow" not "we picked the wrong tool and of course Postgres was slow". Don't blame the hammer. IME, it's common for programmers to not be educated on what modern databases can do. This was made worse with all the NoSQL mania ending up with the MongoDB is web scale. We are still dealing with the fallout of that.
  - wiredfool 6y ago
    FWIW, This project was one that I came in at the deployment stage, and the original requirements were for something about 10x smaller. Budget Gone. Shit happens. There were many places where the right choices weren't made for this, and could have been made better. But even with optimizations in the schema speeding things up by a factor of 3, we would have been looking at something that still didn't hit the performance needs. I'm guessing that to do the reorg on the full DB, we would be looking at O(week). Effective indexes are great, but when one optimized index is tens of GB and takes a day to create, you start to look elsewhere.
    - alecco 6y ago
      That story makes a lot of sense. I took issue with your original comment as it was short on details and could be interpreted in a different light.
bdcravens 6y ago
> why were you using a row store instead of a columnar database? According to this comment (https://news.ycombinator.com/item?id=26187645 https://news.ycombinator.com/item?id=26187645) they were using a columnar extension in Postgresql.
- wiredfool 6y ago
  That's not me.
wiredfool 6y ago
Why did I do a full scan? Because I wanted to see just how bad it was, and I wanted to see roughly what was in the database. I ran: `select count (distinct foo)` and got ~9000 distinct items over 2B rows in 12 hours. Running the same query in Clickhouse was O(minute) As a little extra comparison, the total size of the Clickhouse database is 1/2 the size of the indexes in Postgres.

by alecco 6y ago

lumost 6y ago

Data is the size that it happens to be for many businesses. this could easily have been a large user view history table or similar online use case that folks happened to need analytic capabilities on.

wiredfool 6y ago
In this case, it's the output from ~10k simulations, each over up to a million zones. Production queries will select either one row by primary key, or sum metrics on up to a million rows over a set of the values.

mastazi 6y ago

> and if it's analytics why were you using a row store instead of a columnar database? This point confuses me, parent is telling us how they went from a row-based (pgsql) to a column-based DB (Clickhouse), and you asked them why they didn't?

alecco 6y ago
There are many, many ways to avoid a full scan. Fixing a dumb query and proper indexing (partial indexes if this is expensive), materialized views, and several more. It's very common the developers either resist or are not aware of how to use a database. A full scan is dumb and will retrieve all data of this big table without any filtering or even order. This is highly suspicious. And in the very, very rare case it is absolutely necessary to do a full scan, then Postgres might not be a good idea. Why is it being bashed? I read "Postgres was 12h slow" not "we picked the wrong tool and of course Postgres was slow". Don't blame the hammer. IME, it's common for programmers to not be educated on what modern databases can do. This was made worse with all the NoSQL mania ending up with the MongoDB is web scale. We are still dealing with the fallout of that.
- wiredfool 6y ago
  FWIW, This project was one that I came in at the deployment stage, and the original requirements were for something about 10x smaller. Budget Gone. Shit happens. There were many places where the right choices weren't made for this, and could have been made better. But even with optimizations in the schema speeding things up by a factor of 3, we would have been looking at something that still didn't hit the performance needs. I'm guessing that to do the reorg on the full DB, we would be looking at O(week). Effective indexes are great, but when one optimized index is tens of GB and takes a day to create, you start to look elsewhere.
  - alecco 6y ago
    That story makes a lot of sense. I took issue with your original comment as it was short on details and could be interpreted in a different light.

bdcravens 6y ago

> why were you using a row store instead of a columnar database? According to this comment (https://news.ycombinator.com/item?id=26187645 https://news.ycombinator.com/item?id=26187645) they were using a columnar extension in Postgresql.

wiredfool 6y ago
That's not me.

wiredfool 6y ago

Why did I do a full scan? Because I wanted to see just how bad it was, and I wanted to see roughly what was in the database. I ran: `select count (distinct foo)` and got ~9000 distinct items over 2B rows in 12 hours. Running the same query in Clickhouse was O(minute) As a little extra comparison, the total size of the Clickhouse database is 1/2 the size of the indexes in Postgres.