4 ms·
It sounds like BRIN could replace partitioning on some cases. Am I right? Assuming you have a huge log table, partitioned by week, would this be a better fit?
by therealunreal 11y ago
It sounds like BRIN could replace partitioning on some cases. Am I right? Assuming you have a huge log table, partitioned by week, would this be a better fit?
- endymi0n 11y agoInitially I had the same thought, as it's much less hassle. Unfortunately, other b-tree indexes in the table(s) then won't benefit from this optimization and continue growing. Also, the technique of dropping old partitions at once doesn't work anymore. So no replacement for partitions.
- saosebastiao 11y agoFor a log table or anything immutable and write heavy, definitely. That being said it might be difficult to know when you won't get any benefit out of it unless you have control or knowledge of how rows are laid out in the table space. For example, deleting some rows based off of a fairly random criteria may make Postgres insert into those spaces on subsequent writes (after a vacuum), which could "pollute" the block ranges with non-ordinal data and make the block ranges less targeted.