Re: UUID v1 optimizations...

On Sun, May 26, 2019 at 7:38 PM Ancoron Luciferis <
ancoron(dot)luciferis(at)googlemail(dot)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.