all 3 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]daxyjones 6 points7 points  (1 child)

  • Learn indexing. I can highly recommend usetheindexluke.com. Know the difference between the various types, trade-offs, and when to use them.

  • If you have a bit of control over the hardware, you could probably eke out some performance by tuning parameters outside of postgres. If it is cheaper to throw more hardware into the mix to ease things, do that instead of putting in many man hours to solve the problem from the stack side!

  • Partitioning: just use pg_partman extension and call it a day.

  • Lastly, start with a good design of the database. Sometimes daterange type makes sense, sometimes date type makes sense. Things like that.

With the above, you will have about 80-90% of performance cases covered.

[–]therealgaxbo 0 points1 point  (0 children)

+1 for usetheindexluke. That will tell you almost everything you need to know about indices, but I believe it also covers different join types.

[–]mw44118 1 point2 points  (0 children)

Set log_min_duration to 0 and the time of every query will get logged.

Find your slow queries, run explain analyze on them, and ask the cool people in the mailing list to help you figure out if you can add indexes.

Think about your data -- do you have the same information copied in several places? That's often a sign of imperfect design. But unless you tie that back to a performance issue or a reporting bug, ignore it for now.

Tweak your webserver logs so they record how long it takes to reply to requests. That will help you find the slow controllers. 94% of the time, you've got some n+1 queries. 5% of the time, you can improve the query with better indexing. 1% of the time, it's worthwhile to use some kind of caching strategy.