public inbox for [email protected]  

help / color / mirror / Atom feed

From: Ron Johnson <[email protected]>
To: pgsql-generallists.postgresql.org <[email protected]>
Subject: Re: efficiency random values / sequential ID values in indexes
Date: Mon, 15 Apr 2024 08:48:58 -0400
Message-ID: <CANzqJaD170-1zKXm3M3TwVSPEPeDKh3FuvRcc-ZJV3H2Aa-Ggw@mail.gmail.com> (raw)
In-Reply-To: <CAMpxBonz65-cqa_OUTvDYA9omPSMEAgqd6SrNDhWPzpOaJ5_rg@mail.gmail.com>
References: <CAMpxBonz65-cqa_OUTvDYA9omPSMEAgqd6SrNDhWPzpOaJ5_rg@mail.gmail.com>

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

view thread (2+ messages)

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]
  Subject: Re: efficiency random values / sequential ID values in indexes
  In-Reply-To: <CANzqJaD170-1zKXm3M3TwVSPEPeDKh3FuvRcc-ZJV3H2Aa-Ggw@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