From: | Sanjay Minni <sanjay(dot)minni(at)gmail(dot)com> |
---|---|
To: | Ron Johnson <ronljohnsonjr(at)gmail(dot)com> |
Cc: | "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: efficiency random values / sequential ID values in indexes |
Date: | 2024-04-16 13:10:46 |
Message-ID: | CAMpxBonx6cTY6ZGoxarXVkq=ex26HO3n7r6HyMsaSDrSTJUUsg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Thanks
On Mon, Apr 15, 2024 at 6:19 PM Ron Johnson <ronljohnsonjr(at)gmail(dot)com> wrote:
> On Mon, Apr 15, 2024 at 6:05 AM Sanjay Minni <sanjay(dot)minni(at)gmail(dot)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...
>
From | Date | Subject | |
---|---|---|---|
Next Message | Gus Spier | 2024-04-16 15:22:17 | AWS RDS Postgres and the DBA: Which/how many aws permissions/access do we really need? |
Previous Message | Laurenz Albe | 2024-04-16 11:28:51 | Re: Assistance needed for the query execution in non-public schema |