Re: pages_in_range for BRIN index

Greetings,

* David Harrigan (dharrigan@gmail.com) wrote:
> Thank you kindly for replying. Very insightful. Thank you! The type of
> query is mostly "give me all the rows between now and 1 week ago",
> i.e., 7 days worth of data. In some cases that may be extended to 2
> weeks or 1 month ago, but generally it's around that time range (it is
> filtered on another few attributes, otherwise it would be a massive
> amount of data - however it is mostly time constrained). Would
> classify as a "bulk" query (not quite sure what you mean in that
> regard).

Yes, that'd qualify as 'bulk'.

If you really want to work on optimizing this (and, to be clear, I don't
know that you really need to- the BRIN is likely to be pretty small
with just the defaults), you could look at how many rows you typically
have on a page, and then how many pages account for a week or so of
data, and then maybe set your pages_in_range to, say, a fourth of that?

That'd minimize the size of the BRIN while avoiding having queries using
it for a single week on average, hopefully, only end up scanning up to a
fourth of a week or so of pages that weren't actually relevant to the
query.

> Perhaps I will go with the default and see how that works out!

Definitely a reasonable approach too. :)

> I haven't thought about partitioning. I'll have to read up on that
> (generally, why would I partition?)

Partitioning is breaking up very large tables (hundreds of millions of
rows) into multiple tables, to make certain operations easier,
particularly ones around data maintenance.  Examples include: easier to
expire out old data (just drop the partition instead of having to do a
big DELETE query...), takes less time to VACUUM or reindex a partition
than it does a huge table, pg_dump can export the data in parallel, etc.

Once a partition is known to be 'complete' and you're not adding any
more rows to it you could also VACUUM FREEZE it, assuming you expect it
to be around long enough for transaction wraparound to be a possibility,
so you don't have to wonder when that's going to happen or such.

Thanks,

Stephen