Re: UUID v1 optimizations...

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 <[email protected]>
wrote:

> On Sun, May 26, 2019 at 7:38 PM Ancoron Luciferis <
> [email protected]> 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.
>
>