Comment on Optimizing Postgres table layout for maximum efficiencyparentComments−rtuin1yGood observation. The updates are as much HOT-updates as possible. I wasn't familiar with BRIN-indexes before, so I have to look into that.At first glance our solution follows a similar approach, let me elaborate:- no index columns are updated ever, only inserted- all tables are partitioned based on date (partition range is 1 month)- for some tables there is another layer of partitioning (3 sub-partitions, based on one specific column)- finding an appropriate fillfactor is important to improve the speed of UPDATE statements (HOT-updates)- standard vacuum / auto vacuum settings work great for us so far.- to improve ANALYZE performance, set column statistics of value-only columns to 0.
Comments
Good observation. The updates are as much HOT-updates as possible. I wasn't familiar with BRIN-indexes before, so I have to look into that.
At first glance our solution follows a similar approach, let me elaborate:
- no index columns are updated ever, only inserted
- all tables are partitioned based on date (partition range is 1 month)
- for some tables there is another layer of partitioning (3 sub-partitions, based on one specific column)
- finding an appropriate fillfactor is important to improve the speed of UPDATE statements (HOT-updates)
- standard vacuum / auto vacuum settings work great for us so far.
- to improve ANALYZE performance, set column statistics of value-only columns to 0.