Originally from:[tweet](https://twitter.com/samokhvalov/status/1730107298369171943),[LinkedIn post]().

Ok, you've asked – here it is, a draft recipe to use UUIDv7 and partitioning (we'll

use[@TimescaleDB](https://twitter.com/TimescaleDB)). It's not super elegant, might be not the best, and requires some

effort to reach efficient plans (with partition pruning involved). If you have an alternative or improvement ideas in

mind – let me know.

We'll take the function by[@DanielVerite](https://twitter.com/DanielVerite) to generate UUIDv7 as basis:

create or replacefunctionuuid_generate_v7() returns uuid

as $$

select encode(

set_bit(

set_bit(

overlay(

uuid_send(gen_random_uuid())

placingsubstring(int8send(floor(extract(epochfrom clock_timestamp())*1000)::bigint)from3)

from1 for6

),

52,1

),

53,1

),

'hex')::uuid;

$$ language SQL volatile;

Next, we'll create two functions:

1.`ts_to_uuid_v7` – generate UUIDv7 based on any arbitrary`timestamptz` value, and

2.`uuid_v7_to_ts` – extract`timestamptz` from the existing UUIDv7 value.

Note that this approach is not what the authors of revised RFC4122 (that will likely be finalized soon) would encourage;

see[this discussion and the words](https://postgresql.org/message-id/flat/C80B8FDB-8D9E-48A2-82A2-48863987A1B1%40yandex-team.ru#074a05d31c9ce38bee2f8c8097877485)

by

[@x4mmmmmm](https://twitter.com/x4mmmmmm):

Anyway, let's just do it:

create extension pgcrypto;

create or replacefunctionts_to_uuid_v7(timestamptz) returns uuid

as $$

select encode(

set_bit(

set_bit(

overlay(

uuid_send(gen_random_uuid())

placingsubstring(int8send(floor(extract(epochfrom $1)*1000)::bigint)from3)

from1 for6

),

52,1

),

53,1

),

'hex')::uuid;

$$ language SQL volatile;

create or replacefunctionuuid_v7_to_ts(uuid_v7 uuid) returnstimestamptz

as $$

to_timestamp(

(

'x'||substring(

encode(uuid_send(uuid_v7),'hex')

from1 for12

)

)::bit(48)::bigint/1000.0

)::timestamptz;

$$ language sql;

Checking the functions:

test=# select now(), ts_to_uuid_v7(now() - interval '1y');

now | ts_to_uuid_v7

2023-11-3005:36:32.205093+00 | 0184c709-63cd-7bd1-99c3-a4773ab1e697

(1 row)

test=# select uuid_v7_to_ts('0184c709-63cd-7bd1-99c3-a4773ab1e697');

uuid_v7_to_ts

2022-11-3005:36:32.205+00

(1 row)

Pretending that we haven't noticed the loss of microseconds, we continue.

Create a table, where we'll store ID as UUID, but additionally have a`timestamptz` column – this column will be used as

partitioning key when we convert the table to partitioned table ("hypertable" in TimescaleDB's terminology):

createtablemy_table (

id uuidnot null

default'00000000-0000-0000-0000-000000000000'::uuid,

payloadtext,

uuid_tstimestamptznot null default clock_timestamp()-- or now(), depending on goals

);

The default value`00000000-...00` for`id` is "fake" – it will always be replaced in trigger, based on the timestamp:

create or replacefunctiont_update_uuid() returns trigger

as $$

ifnew.id isnullornew.id='00000000-0000-0000-0000-000000000000'::uuid then

new.id := ts_to_uuid_v7(new.uuid_ts);

end if;

return new;

end;

$$ language plpgsql;

before insertorupdateon my_table

for each row execute function t_update_uuid();

Now, use TimescaleDB partitioning:

create extension timescaledb;

select create_hypertable(

relation :='my_table',

time_column_name :='uuid_ts',

chunk_time_interval :='1 minute'::interval

);

And now insert some test data – some rows for the "past" and some "current" rows:

insert into my_table(payload, uuid_ts)

select random()::text, ts

from generate_series(

timestamptz'2000-01-01 00:01:00',

timestamptz'2000-01-01 00:05:00',

interval'5 second'

)as ts;

insert into my_table(payload)

select random()::text

from generate_series(1,10000);

vacuum analyze my_table;

Checking the structure of`my_table` in psql using`\d+` we now see that multiple partitions ("chunks") were created by

TimescaleDB:

test=# \d+ my_table

...

Child tables:_timescaledb_internal._hyper_2_3_chunk,

_timescaledb_internal._hyper_2_4_chunk,

_timescaledb_internal._hyper_2_5_chunk,

_timescaledb_internal._hyper_2_6_chunk,

_timescaledb_internal._hyper_2_7_chunk,

_timescaledb_internal._hyper_2_8_chunk,

_timescaledb_internal._hyper_2_9_chunk

Now we just need to remember that`uuid_ts` should always participate in queries, to let planner deal with as few

partitions as possible – but knowing the`id` values, we can always reconstruct the`uuid_ts` values, using

`uuid_v7_to_ts()`. Note that I first disabled`seqscan` as the table`my_table` has too few rows, otherwise PostgreSQL

may decide on preferring`seqscan` over index scan:

test# set enable_seqscan = off;

test=# explain select * from my_table where uuid_ts = uuid_v7_to_ts('00dc6ad0-9660-7b92-a95e-1d7afdaae659');

QUERY PLAN

Append (cost=0.14..8.16 rows=1 width=41)

-> Index Scan using _hyper_5_11_chunk_my_table_uuid_ts_idxon _hyper_5_11_chunk (cost=0.14..8.15 rows=1 width=41)

Index Cond: (uuid_ts='2000-01-01 00:01:00+00'::timestamp with time zone)

(3 rows)

test=# explain select * from my_table

where uuid_ts>= uuid_v7_to_ts('018c1ecb-d3b7-75b1-add9-62878b5152c7')

order by uuid_tsdesclimit10;

QUERY PLAN

Limit (cost=0.29..1.17 rows=10 width=41)

-> Custom Scan (ChunkAppend)on my_table (cost=0.29..11.49 rows=126 width=41)

Order:my_table.uuid_tsDESC

-> Index Scan using _hyper_5_16_chunk_my_table_uuid_ts_idxon _hyper_5_16_chunk (cost=0.29..11.49 rows=126 width=41)

Index Cond: (uuid_ts>='2023-11-30 05:55:23.703+00'::timestamp with time zone)

(5 rows)

– partition pruning in play, although it will require certain effort to have it in various queries. But it works.

Also read the following comment by[@jamessewell](https://twitter.com/jamessewell), originaly posted

[here](https://twitter.com/jamessewell/status/1730125437903450129):

Originally from:[tweet](https://twitter.com/samokhvalov/status/1730609033860858080),[LinkedIn post]().

In Postgres, all tables have hidden, system columns;`ctid` being one of them. Reading it, we can see physical

location of the tuple (tuple = row physical version), the page number and offset inside it:

nik=# create table t0 as select 1 as id;

nik=# select ctid, id from t0;

ctid | id

(0,1) |1

(1 row)

👉 page 0, position 1.

A single PostgreSQL page, which is 8 KiB by default, and can be checked by looking at`block_size`:

nik=# show block_size;

block_size

(1 row)

How many tuples can fit into a single page? Let's see:

nik=# create table t0 as select i

from generate_series(1,1000)as i;

nik=# select count(*)

from t0

where (ctid::text::point)[0]=0;

count

(1 row)

nik=# select pg_column_size(i) from t0 limit 1;

pg_column_size

(1 row)

👉 If we use 4-byte numbers, then it's 226 tuples. Here I used`(ctid::text::point)[0]` to convert`ctid` value to

"point" to get the first its component, then (the page number).

If we use 2-byte numbers or, say, 1-byte`boolean` values (yes, boolean needs 1 byte, not 1 bit), the number is the

same:

nik=# drop table t0;

nik=# create table t0 as select true

from generate_series(1,1000)as i;

nik=# select count(*)

from t0

where (ctid::text::point)[0]=0;

count

(1 row)

Why 226 again? The thing is that, the size of the value doesn't matter here; it just needs to be less or equal to 8

bytes. For every row, alignment padding adds zeroes, so we'll always have 8 bytes for each row. Math:


👉 What we have counted here:

1. A single 24-byte page header (`PageHeaderData`).

2. N pointers to each tuple – 4 bytes each (`ItemIdData`).

3. N tuple headers – 23 bytes each, padded to 24 (`HeapTupleHeaderData`).

4. N tuple values – if <= 8 bytes, then it's padded to 8 bytes.

Source code defining the

structures (for[PG16](https://github.com/postgres/postgres/blob/REL_16_STABLE/src/include/storage/bufpage.h)).

**Can we fit even MORE tuples?**

The answer is YES. Postgres allows tables without columns (!) In this case, the math is:


Let's see (note the empty column list in the`SELECT` clause):

nik=# create table t0 as select

from generate_series(1,1000)as i;

nik=# select count(*)

from t0

where (ctid::text::point)[0]=0;

count

(1 row)