Subject: efficiency random values / sequential ID values in indexes

From: Sanjay Minni <[email-masked]>
Date: Mon, 15 Apr 2024 15:34:21 +0530

Hi

Is there any appreciable difference in using random values or sequential
values in indexes

in a multi tenanted application there is a choice that the single field
ID's value is totally random / UUID or the numbers are created with a
prefix of the tenant. Since all access will be within a tenant only, will
it make any performance difference between using purely random values vs
<tenant no prefix part>+<random value>.

thanks
Sanjay

---

From: Ron Johnson <[email-masked]>
Date: Mon, 15 Apr 2024 08:48:58 -0400

> <tenant no prefix part>+<random value>.

Two benefits of <tenant no prefix part>+<random value>:

1. In a non-partitioned table, it gives your index "locality of data": all
of customer X's record pointers are in *This* subtree.  Makes buffers more
efficient when a customer runs reports.  Bonus points if you then regularly
CLUSTER using that table.
2. Makes table partitioning by <tenant prefix> much easier.  That also
enhances locality of data.

Just make sure that the field ID is BIGINT...