public inbox for [email protected]  

help / color / mirror / Atom feed

efficiency random values / sequential ID values in indexes
2+ messages / 2 participants
[nested] [flat]

* efficiency random values / sequential ID values in indexes
@ 2024-04-15 10:04  Sanjay Minni <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: Sanjay Minni @ 2024-04-15 10:04 UTC (permalink / raw)
  To: [email protected]

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


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: efficiency random values / sequential ID values in indexes
@ 2024-04-15 12:48  Ron Johnson <[email protected]>
  parent: Sanjay Minni <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: Ron Johnson @ 2024-04-15 12:48 UTC (permalink / raw)
  To: pgsql-generallists.postgresql.org <[email protected]>

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


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2024-04-15 12:48 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-04-15 10:04 efficiency random values / sequential ID values in indexes Sanjay Minni <[email protected]>
2024-04-15 12:48 ` Ron Johnson <[email protected]>

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