Here's what I was thinking of regarding disk space: https://www.postgresql.org/docs/11/storage-page-layout.html
That's the kind of low-level detail I try *not* to worry about, but like to have in the back of my mind at least a little. If I read it correctly, a fixed-length field is going to be marked as present with a bit, and then inlined in the row storage without any extra cost for an address. So not much in the way of extra overhead. Spending the space on a field, reduces the compute needed to constantly perform extracts on the UUID field to access the same information. But that particular trade-off is an ancient discussion and judgement call, you know your requirements and constraints better than anyone else. So, I'll leave it at that. On Sun, May 26, 2019 at 8:24 PM Morris de Oryx <morrisdeo...@gmail.com> wrote: > On Sun, May 26, 2019 at 7:38 PM Ancoron Luciferis < > ancoron.lucife...@googlemail.com> wrote: > > The BRIN index is something I might need to test, though. >> > > Yes, check that out, it might give you some ideas. A B-tree (in whatever > variant) is *inherently *a large index type. They're ideal for finding > unique values quickly, not ideal for storing redundant values, and pretty > decent at finding ranges. A BRIN (Block Range Index), as implemented in > Postgres, is good for finding unique values and and ranges. But here's the > thing, a BRIN index takes some absurdly small % of the space of a B-tree. > You have to blink and check again to be sure you've figured it right. > > How can a BRIN index be so much smaller? By throwing virtually everything > out. A BRIN index doesn't store all of the values in a page, it stores the > min/max value and that's it. So it's a probabilistic index (of sorts.) Is > the value you're seeking on such and so page? The answer is "No" or > "Maybe." That's a fast test on a very cheap data structure. > > When the answer is "maybe", the full has to be loaded and scanned to > determine if a specific value is found. So, you have a very small index > structure, but have to do more sequential scanning to determine if a record > is indexed in that page or not. In the real world, this can work out to be > a high-performance structure at very low cost. But for it to work, your > records need to be physically ordered (CLUSTER) by the condition in that > index. And, going forward, you ought to be inserting in order > too.(More-or-less.) So, a BRIN index is a great option *if *you have an > insertion pattern that allows it to remain efficient, and if you're goal is > range searching without a heavy B-tree index to maintain. > > I have no clue how BRIN indexes and partitioning interact. > >
