public inbox for [email protected]  

help / color / mirror / Atom feed

From: Sanjay Minni <[email protected]>
To: Ron Johnson <[email protected]>
Cc: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: efficiency random values / sequential ID values in indexes
Date: Tue, 16 Apr 2024 18:40:46 +0530
Message-ID: <CAMpxBonx6cTY6ZGoxarXVkq=ex26HO3n7r6HyMsaSDrSTJUUsg@mail.gmail.com> (raw)
In-Reply-To: <CANzqJaD170-1zKXm3M3TwVSPEPeDKh3FuvRcc-ZJV3H2Aa-Ggw@mail.gmail.com>
References: <CAMpxBonz65-cqa_OUTvDYA9omPSMEAgqd6SrNDhWPzpOaJ5_rg@mail.gmail.com>
	<CANzqJaD170-1zKXm3M3TwVSPEPeDKh3FuvRcc-ZJV3H2Aa-Ggw@mail.gmail.com>

Thanks

On Mon, Apr 15, 2024 at 6:19 PM Ron Johnson <[email protected]> wrote:

> On Mon, Apr 15, 2024 at 6:05 AM Sanjay Minni <[email protected]>
> 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...
>

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: efficiency random values / sequential ID values in indexes
  In-Reply-To: <CAMpxBonx6cTY6ZGoxarXVkq=ex26HO3n7r6HyMsaSDrSTJUUsg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox