Date: Sun, 7 Jun 2020 21:02:15 +0100
From: David Harrigan <dharrigan@gmail.com>
To: Stephen Frost <sfrost@snowman.net>
Cc: pgsql-novice@lists.postgresql.org
Subject: Re: pages_in_range for BRIN index
Message-ID: <20200607200215.kajtlbpbgeymk4bz@daffy>
References: 
 <CAKdGhePbMSiQ_qo718oSZTu=j1yU3ne=6kYfmCDOUwzp3+TnZQ@mail.gmail.com>
 <20200606123556.GV6680@tamriel.snowman.net>
 <20200606132720.ji66sn6fc2zraovg@frodo.localdomain>
 <20200607192824.GW6680@tamriel.snowman.net>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="5lovzq6nnlsrktun"
Content-Disposition: inline
In-Reply-To: <20200607192824.GW6680@tamriel.snowman.net>
Precedence: bulk


--5lovzq6nnlsrktun
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline

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.

--5lovzq6nnlsrktun
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQEzBAABCgAdFiEEEQr0IzZHVOKID62tHFKFv7IKIvkFAl7dR8EACgkQHFKFv7IK
IvkEuQf/U58aNE3rQrAm1M+sH+3Pi4CTEbyn/Pxz5NbjdHlDa4hL+vugNUsjWDNj
2hlO7q9oRHDSNGy5sCT6wwWtWiuL0+cLt3jLn4sg7ppynuip1BDxo3RzX6Pzuqbe
Qxe9005Y771Xnq6Kqkdo+Hwm7xMpeNlBb469eUZy7Gv3hidDxKndZK/z1IzRMNic
yGwdT74gQxlgYc1HiQsX57POjZUhbF/F8HmpSCM7v7F6nnX69/dlJghFTPES5KJQ
Ui5RLZ3nemWK5mvSi3cWd3h8ldjrM+X6br4wTgXBY4V2+DH8DqBktKaUOEoS6MoQ
hO76hHX/ZIPC4KAR2LTH+Z62eOcVkA==
=+ChN
-----END PGP SIGNATURE-----

--5lovzq6nnlsrktun--