Thread: pages_in_range for BRIN index

Greetings,

* David Harrigan (dharrigan@gmail.com) wrote:
> I'm having difficulty in trying to understand the value I should use for
> `pages_in_range` when creating a BRIN index. I have data coming in, nearly
> 3-4
> rows per second with an ever increasing `created` timestamp. Perfect for a
> BRIN index to save on space :-).

Yup, that does sound like the right use-case.

> I gathered some data (at time of query) on the table I'm interested in:
>
> Size (bytes): 16130588672
> Pages: 1969066
> Rows: 48215073
> Rows/Page: 24

Not quite there yet, but hopefully you're thinking about partitioning
too.

> At the moment, the column I'm indexing on, created, has a BTREE index on it,
> and it's over 2GB in size!

Well, ok, sure, 2G isn't *that* big tho. :)

> What would I need to do to go about choosing an appropriate value for
> pages_in_range?

Ok, so, this really depends on what queries you're running and how many
rows you want to have to scan for them.  If you're mostly doing 'bulk'
kinds of queries that hit a lot of rows, then having a larger value is
probably fine since you won't be going through that many rows that you
have to throw away.  On the other hand, if the queries that are using
this index are just getting back a couple of rows as a result, then you
probably want a smaller value, to make those queries be fast.

Of course, the tradeoff here is in the size of the index- a smaller
number means a larger index, and a larger number means a smaller index.

If you're not sure though- try the default..?

> Lastly, is it necessary to run a `vacuum analyse freeze` before/after
> index creation?

Nope.

Thanks,

Stephen

On 06/06/2020 08:35:56, Stephen Frost wrote:

> Greetings,
>
> * David Harrigan (dharrigan@gmail.com) wrote:
> > I'm having difficulty in trying to understand the value I should use for
> > `pages_in_range` when creating a BRIN index. I have data coming in, nearly
> > 3-4
> > rows per second with an ever increasing `created` timestamp. Perfect for a
> > BRIN index to save on space :-).
>
> Yup, that does sound like the right use-case.
>
> > I gathered some data (at time of query) on the table I'm interested in:
> >
> > Size (bytes): 16130588672
> > Pages: 1969066
> > Rows: 48215073
> > Rows/Page: 24
>
> Not quite there yet, but hopefully you're thinking about partitioning
> too.
>
> > At the moment, the column I'm indexing on, created, has a BTREE index on it,
> > and it's over 2GB in size!
>
> Well, ok, sure, 2G isn't *that* big tho. :)
>
> > What would I need to do to go about choosing an appropriate value for
> > pages_in_range?
>
> Ok, so, this really depends on what queries you're running and how many
> rows you want to have to scan for them.  If you're mostly doing 'bulk'
> kinds of queries that hit a lot of rows, then having a larger value is
> probably fine since you won't be going through that many rows that you
> have to throw away.  On the other hand, if the queries that are using
> this index are just getting back a couple of rows as a result, then you
> probably want a smaller value, to make those queries be fast.
>
> Of course, the tradeoff here is in the size of the index- a smaller
> number means a larger index, and a larger number means a smaller index.
>
> If you're not sure though- try the default..?
>
> > Lastly, is it necessary to run a `vacuum analyse freeze` before/after
> > index creation?
>
> Nope.
>
> Thanks,
>
> Stephen

Hi!

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).

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

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

Thanks again!

-=david=-

I prefer encrypted and signed messages.
GPG Fingerprint: 110AF423364754E2880FADAD1C5285BFB20A22F9

No trees were harmed in the sending of this message, however, a number
of electrons were inconvenienced.

This email is sent without prejudice.

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

On 07/06/2020 15:28:24, Stephen Frost wrote:

> 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

Hi Stephen,

Thanks once again for your reply. Extremely helpful! I'm going to read
up more on partitioning and consider my options with regard the BRIN
index. Exciting! :-)

-=david=-

I prefer encrypted and signed messages.
GPG Fingerprint: 110AF423364754E2880FADAD1C5285BFB20A22F9

No trees were harmed in the sending of this message, however, a number
of electrons were inconvenienced.

This email is sent without prejudice.