Looking at the query I think it only returns a single row per post. So not really duplicate data. It all looks very straight forward and you’d think all the “_id” and “id” columns are indexed.

I asked for an EXPLAIN ANALYZE plan to see what really happens and where the most time is spent.

If it’s indexes we’ll see quickly. It might strangely be in the WHERE clause. Not sure what Hot_rank()'s implementation is. But we’ll find that out too if we can get the plan timings. Without looking at the numbers it’s all just guessing.

And I can’t run them myself since I don’t have access to a busy instance with their amount of production data. It’s the thing about databases - what runs fast in dev, doesn’t always translate to real workloads.

Thanks. I’ve asked in there for some EXPLAIN ANALYZE plans, so we can see which parts are slow and why. I also think running pgbouncer or a similar connection pooler would be a good idea. Running a production public facing service with only 5 db connections is asking for trouble.

No, joins are always faster. If you ultimately need to combine the data for the app, the database will be faster than your code can do it, since that’s what it was built to do.

Where are the bottlenecks? Frontend servers or on the db? I have a lot of experience running postgres at scale.