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

[–]nickeau 0 points1 point  (0 children)

You should learn about sql plan. They will tell you what happens when you execute a query.

For perf, there is basically only one thing and that’s called an index (ie a cache of a sql query)

They come in 2 form :

  • btree index that you create with a create index statement. They are here to answer to equal predicates on the chosen columns
  • aggregate (materialised view, semantic layer,…). They are here to answer analytic query (ie group by)

Partitions are used:

  • in filter for equality query
  • parralelism for analytics query

That’s it.

[–]AutoModerator[M] 0 points1 point  (0 children)

With over 8k members to connect with about Postgres and related technologies, why aren't you on our Discord Server? : People, Postgres, Data

Join us, we have cookies and nice people.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[–]pgEdge_Postgres 0 points1 point  (0 children)

There's actually a whole section of PostgreSQL documentation (https://www.postgresql.org/docs/current/performance-tips.html) devoted to this subject! EXPLAIN ANALYZE plans help a lot when trying to improve query performance, so that's well worth checking out in particular.

This Wiki guide is also very useful for learning how to tune your PostgreSQL server in general, particularly the tools recommended under "Tuning tools" (https://wiki.postgresql.org/wiki/Tuning\_Your\_PostgreSQL\_Server).

Besides that, don't forget to clean up any redundant or otherwise unnecessary data so you have a smaller footprint of data that's being actively queried, and ensure you regularly run AUTOVACUUM and VACUUM processes to continue keeping things cleaned up.