Magnus Hagander wrote: > But then consider the same table. Except rows are typically updated once or > twice when they are new, and *then* go read only. And we also have a > process that at some point deletes *some* old rows (but not all - in fact, > only a small portion). > > In this case, the next INSERT once VACUUM has run is likely to stick a > "new" row somewhere very "far back" in the table, since there is now free > space there. This more or less completely ruins the BRIN index usability, > as the "old" blocks will now contain a single row from a "new" series.
Yeah. When we initially discussed BRIN, there was a mention of allowing a BRIN index to guide new tuple location -- something like auto-clustering, if you will. We haven't discussed the exact details but I think something along those lines is worth considering. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers