Hacker News Reader

dbnewbieon Ask HN: 120M rows Postgres – how can I speed up queries?

Excellent, thanks for the suggestion. I hadn’t heard of this lib until now. And, seeing that this is developed by Citus makes it automatically 10x better.

dbnewbieon Ask HN: 120M rows Postgres – how can I speed up queries?

Interesting, that does sound like an advanced indexing technique, but also sounds like a really good idea. It reminds me of the old flat file database formats I read about.

dbnewbieon Ask HN: 120M rows Postgres – how can I speed up queries?

Thanks! That’s awesome. I know roughly how to understand PG query plans, but it doesn’t ever seem to actually help me figure out a solution. This service looks like it will help quite a bit.

dbnewbieon Ask HN: 120M rows Postgres – how can I speed up queries?

Thank you for the suggestion. I don’t know for certain that it’s a tenable solution, because we are using some services that would take some time to vertically integrated if we were to go to a colo. But when we reach that scale that will definitely be an effort worth exploring.

dbnewbieon Ask HN: 120M rows Postgres – how can I speed up queries?

OK that makes sense, and I’ll do a bit of reading on materialized view functionality before actually implementing anything.

dbnewbieon Ask HN: 120M rows Postgres – how can I speed up queries?

Ok awesome, and thanks again!

dbnewbieon Ask HN: 120M rows Postgres – how can I speed up queries?

I have not looked into “index encoding”. In fact, I haven’t even heard of that, thank you for the suggestion!

In terms of execution plan, the query we are doing is relatively basic even though it includes some aggregation. The aggregation is rule (CASE) based and very simple. It feels like there is no way to quickly (sub-50ms) retrieve information from a database once you reach the high tens of millions of rows.

dbnewbieon Ask HN: 120M rows Postgres – how can I speed up queries?

Thank you an absolute ton! I’m gonna be watching that scaling video from Pycon this afternoon!

We definitely have a decent configuration, in terms of using the hardware best for our typical workload. We also have indexes on the fields we are using for filtering. It’s crazy, I put this question here because I feel like this is an inevitable thing that everybody just runs into over and over with scaling and the only real out is sharding, so I’m glad to see you a bunch of suggestions here and I’m going to watch that video as I mentioned. Thanks again.

dbnewbieon Ask HN: 120M rows Postgres – how can I speed up queries?

Thanks a ton for the help, I will take a look into what we might be able to do in terms of adding a materialized aggregate view!