Обсуждение: efficiency random values / sequential ID values in indexes

Поиск
Список
Период
Сортировка

efficiency random values / sequential ID values in indexes

От
Sanjay Minni
Дата:
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

 
  

Re: efficiency random values / sequential ID values in indexes

От
Ron Johnson
Дата:
On Mon, Apr 15, 2024 at 6:05 AM Sanjay Minni <sanjay.minni@gmail.com> wrote:
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>.

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...

Re: efficiency random values / sequential ID values in indexes

От
Sanjay Minni
Дата:
Thanks

On Mon, Apr 15, 2024 at 6:19 PM Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Mon, Apr 15, 2024 at 6:05 AM Sanjay Minni <sanjay.minni@gmail.com> wrote:
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>.

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...