Date: Tue, 30 Jan 2024 00:27:21 +0000 (UTC)
From: Sergey Prokhorenko <sergeyprokhorenko@yahoo.com.au>
To: "Andrey M. Borodin" <x4mmm@yandex-team.ru>
Cc: pgsql-hackers mailing list <pgsql-hackers@postgresql.org>,
	Aleksander Alekseev <aleksander@timescale.com>,
	=?UTF-8?Q?Przemys=C5=82aw_Sztoch?= <przemyslaw@sztoch.pl>,
	Nikolay Samokhvalov <nik@postgres.ai>,
	"David G. Johnston" <david.g.johnston@gmail.com>,
	Jelte Fennema-Nio <postgres@jeltef.nl>,
	Nick Babadzhanian <pgnickb@gmail.com>,
	Mat Arye <mat@timescaledb.com>,
	Peter Eisentraut <peter.eisentraut@enterprisedb.com>,
	Tom Lane <tgl@sss.pgh.pa.us>, Daniel Gustafsson <daniel@yesql.se>,
	Matthias van de Meent <boekewurm+postgres@gmail.com>,
	Nikolay Samokhvalov <samokhvalov@gmail.com>,
	"Kyzer Davis (kydavis)" <kydavis@cisco.com>,
	Andres Freund <andres@anarazel.de>,
	"brad@peabody.io" <brad@peabody.io>, Kirk Wolak <wolakk@gmail.com>
Message-ID: <1945125834.2044089.1706574441164@mail.yahoo.com>
In-Reply-To: <5932FFF8-4E5F-44DC-8FEA-22E8302759C7@yandex-team.ru>
References: 
 <CAAhFRxitJv=yoGnXUgeLB_O+M7J2BJAmb5jqAT9gZ3bij3uLDA@mail.gmail.com>
 <CAGECzQQ=38bVUR=LZ6vmBCEjaDfOOoQa+ygFJ1mCG_H2jsC90Q@mail.gmail.com>
 <1003561938.6914482.1704452267331@mail.yahoo.com>
 <80E8F728-FC9C-461E-9B61-332E3AAEC066@yandex-team.ru>
 <CAJ7c6TPcYQHWYa97MiETT6O8=3Yq=r0CMtyu0y3DEYmOcmnmPA@mail.gmail.com>
 <67eb8a02-f35c-af10-1615-afae0f9f7548@sztoch.pl>
 <CAJ7c6TPZZg8uM+2=Hj_s-gcvYCfYo=8ABZPTBvvh=mkCdRot9Q@mail.gmail.com>
 <8A52AED1-2CA6-403C-8B5B-D24EA8D58616@yandex-team.ru>
 <9CE1E513-B11A-4C59-980E-D61EC56716E7@yandex-team.ru>
 <CAKFQuwai1N+Tm-P91zqnJEmT11kD7RqJLJrrhWFSxS1pRSR47w@mail.gmail.com>
 <B4034B63-6C3F-46B1-A26F-23B4B8CC72D6@yandex-team.ru>
 <0A0D7507-C434-46AC-BCC4-1166C5A2742A@yandex-team.ru>
 <CAM527d__3qQxvpUC_5tWW5TeodE=bivYT3eNDpjrVAk3-uU0TA@mail.gmail.com>
 <1459150585.359059.1706132985954@mail.yahoo.com>
 <CAM527d8QbYa9CfsUezn_owUP8M0JWGYiosMEOkQqD6=PsncEnA@mail.gmail.com>
 <00B784AB-5B52-4CC7-8A0D-B1C0C53B52DC@yandex-team.ru>
 <f0c8f561-9f68-0634-b815-33a629 c352ec@sztoch.pl>
 <CAJ7c6TPX5xJSr17806LOGcvuM5kk3jEtRoHSmZkLuG52zYoZuA@mail.gmail.com>
 <1014147035.642629.1706202245918@mail.yahoo.com>
 <5932FFF8-4E5F-44DC-8FEA-22E8302759C7@yandex-team.ru>
Subject: Re: UUID v7
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_2044088_24612575.1706574441162"
Content-Length: 9135
Archived-At: 
 <https://www.postgresql.org/message-id/1945125834.2044089.1706574441164%40mail.yahoo.com>
Precedence: bulk

------=_Part_2044088_24612575.1706574441162
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Andrey,
 I understand and agree with your goals.=C2=A0But instead of dangerous univ=
ersal=C2=A0functions, it is better to develop=C2=A0safe highly specialized =
functions that implement only these goals.
There should not be a function uuidv7(T) from an arbitrary timestamp, but t=
here should be a special function that implements your algorithm: uuidv8(no=
w() + '1 century' * random(0,10)).
I replaced 1 day with 1 century because the spread of 1 day is too small. O=
ver time, records will be inserted between existing records, which is undes=
irable.
Similarly, if we need to calculate the partition id, then we do not need to=
 use the uuid_extract_time() function to provide the extracted timestamp, t=
he accuracy of which cannot be guaranteed. Instead, we need to give exactly=
 the partition id, calculated using the uuidv7 timestamp. For example, part=
itions may have approximately a month interval between each other.
As for the documentation, it must be indicated that the UUIDv7 structure is=
 not timestamp + random, but timestamp + randomly seeded counter + random, =
like in all advanced implementations.

Sergey Prokhorenko
sergeyprokhorenko@yahoo.com.au
______________________________________________________________=20

    On Monday, 29 January 2024 at 09:32:54 pm GMT+3, Andrey M. Borodin <x4m=
mm@yandex-team.ru> wrote: =20
=20
=20

> On 25 Jan 2024, at 22:04, Sergey Prokhorenko <sergeyprokhorenko@yahoo.com=
.au> wrote:
>=20
> Aleksander,
>=20
> In this case the documentation must state that the functions uuid_extract=
_time() and uuidv7(T) are against the RFC requirements, and that developers=
 may use these functions with caution at their own risk, and these function=
s are not recommended for production environment.

Refining documentation is good. However, saying that these functions are no=
t recommended for production must be based on some real threats.

>=20
> The function uuidv7(T) is not better than uuid_extract_time(). Careless d=
evelopers may well pass any business date into this function: document date=
, registration date, payment date, reporting date, start date of the curren=
t month, data download date, and even a constant. This would be a profanati=
on of UUIDv7 with very negative consequences.

Even if the developer pass constant time to uuidv7(T) they will get what th=
ey asked for - unique identifier. Moreover - it still will be keeping local=
ity. There will be no negative consequences at all.
On the contrary, experienced developer can leverage parameter when data loc=
ality should be reduced. If you have serveral streams of data, you might wa=
nt to introduce some shift in reduce contention.
For example, you can generate uuidv7(now() + '1 day' * random(0,10)). This =
will split 1 contention point to 10 and increase ingestion performance 10x-=
fold.

> On 29 Jan 2024, at 18:58, Junwang Zhao <zhjwpku@gmail.com> wrote:
>=20
> If other timestamp sources or
> a custom timestamp epoch are required, UUIDv8 MUST be used.

Well, yeah. RFC says this... in 4 capital letters :) I believe it's kind of=
 a big deficiency that k-way sortable identifiers are not implementable on =
top of UUIDv7. Well, let's go without this function. UUIDv7 is still an imp=
rovement over previous versions.


Jelte, your documentation corrections looks good to me, I'll include them i=
n next version.

Thanks!


Best regards, Andrey Borodin. =20
------=_Part_2044088_24612575.1706574441162
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<html><head></head><body><div class=3D"ydp98e42e61yahoo-style-wrap" style=
=3D"font-family:Helvetica Neue, Helvetica, Arial, sans-serif;font-size:16px=
;"><div><div dir=3D"ltr" data-setdir=3D"false"><div><div>Andrey,</div><div>=
<br></div><div dir=3D"ltr" data-setdir=3D"false"> <span>I understand and ag=
ree with your goals.</span>&nbsp;But instead of dangerous  <span>universal&=
nbsp;</span>functions, it is better to develop&nbsp;<span>safe</span> highl=
y specialized functions that implement only these goals.</div><div><br></di=
v><div>There should not be a function uuidv7(T) from an arbitrary timestamp=
, but there should be a special function that implements your algorithm: uu=
idv8(now() + '1 century' * random(0,10)).</div><div><br></div><div>I replac=
ed 1 day with 1 century because the spread of 1 day is too small. Over time=
, records will be inserted between existing records, which is undesirable.<=
/div><div><br></div><div>Similarly, if we need to calculate the partition i=
d, then we do not need to use the uuid_extract_time() function to provide t=
he extracted timestamp, the accuracy of which cannot be guaranteed. Instead=
, we need to give exactly the partition id, calculated using the uuidv7 tim=
estamp. For example, partitions may have approximately a month interval bet=
ween each other.</div><div><br></div><div dir=3D"ltr" data-setdir=3D"false"=
>As for the documentation, it must be indicated that the UUIDv7 structure i=
s not timestamp + random, but timestamp + randomly seeded counter + random,=
 <span>like in all advanced implementations</span>.</div></div><br></div><d=
iv><br></div><div class=3D"ydp98e42e61signature">Sergey Prokhorenko</div><d=
iv class=3D"ydp98e42e61signature"><br></div><div class=3D"ydp98e42e61signat=
ure">sergeyprokhorenko@yahoo.com.au</div></div><div class=3D"ydp98e42e61sig=
nature"><br></div><div class=3D"ydp98e42e61signature">_____________________=
_________________________________________</div>
        <div><br></div><div><br></div>
       =20
        </div><div id=3D"ydp710bf224yahoo_quoted_7340234694" class=3D"ydp71=
0bf224yahoo_quoted">
            <div style=3D"font-family:'Helvetica Neue', Helvetica, Arial, s=
ans-serif;font-size:13px;color:#26282a;">
               =20
                <div>
                    On Monday, 29 January 2024 at 09:32:54 pm GMT+3, Andrey=
 M. Borodin &lt;x4mmm@yandex-team.ru&gt; wrote:
                </div>
                <div><br></div>
                <div><br></div>
                <div><div dir=3D"ltr"><br clear=3D"none"><br clear=3D"none"=
>&gt; On 25 Jan 2024, at 22:04, Sergey Prokhorenko &lt;<a shape=3D"rect" hr=
ef=3D"mailto:sergeyprokhorenko@yahoo.com.au" rel=3D"nofollow" target=3D"_bl=
ank">sergeyprokhorenko@yahoo.com.au</a>&gt; wrote:<br clear=3D"none">&gt; <=
br clear=3D"none">&gt; Aleksander,<br clear=3D"none">&gt; <br clear=3D"none=
">&gt; In this case the documentation must state that the functions uuid_ex=
tract_time() and uuidv7(T) are against the RFC requirements, and that devel=
opers may use these functions with caution at their own risk, and these fun=
ctions are not recommended for production environment.<br clear=3D"none"><b=
r clear=3D"none">Refining documentation is good. However, saying that these=
 functions are not recommended for production must be based on some real th=
reats.<br clear=3D"none"><br clear=3D"none">&gt; <br clear=3D"none">&gt; Th=
e function uuidv7(T) is not better than uuid_extract_time(). Careless devel=
opers may well pass any business date into this function: document date, re=
gistration date, payment date, reporting date, start date of the current mo=
nth, data download date, and even a constant. This would be a profanation o=
f UUIDv7 with very negative consequences.<br clear=3D"none"><br clear=3D"no=
ne">Even if the developer pass constant time to uuidv7(T) they will get wha=
t they asked for - unique identifier. Moreover - it still will be keeping l=
ocality. There will be no negative consequences at all.<br clear=3D"none">O=
n the contrary, experienced developer can leverage parameter when data loca=
lity should be reduced. If you have serveral streams of data, you might wan=
t to introduce some shift in reduce contention.<br clear=3D"none">For examp=
le, you can generate uuidv7(now() + '1 day' * random(0,10)). This will spli=
t 1 contention point to 10 and increase ingestion performance 10x-fold.<br =
clear=3D"none"><br clear=3D"none">&gt; On 29 Jan 2024, at 18:58, Junwang Zh=
ao &lt;<a shape=3D"rect" href=3D"mailto:zhjwpku@gmail.com" rel=3D"nofollow"=
 target=3D"_blank">zhjwpku@gmail.com</a>&gt; wrote:<br clear=3D"none">&gt; =
<br clear=3D"none">&gt; If other timestamp sources or<br clear=3D"none">&gt=
; a custom timestamp epoch are required, UUIDv8 MUST be used.<br clear=3D"n=
one"><br clear=3D"none">Well, yeah. RFC says this... in 4 capital letters :=
) I believe it's kind of a big deficiency that k-way sortable identifiers a=
re not implementable on top of UUIDv7. Well, let's go without this function=
. UUIDv7 is still an improvement over previous versions.<br clear=3D"none">=
<br clear=3D"none"><br clear=3D"none">Jelte, your documentation corrections=
 looks good to me, I'll include them in next version.<br clear=3D"none"><br=
 clear=3D"none">Thanks!<br clear=3D"none"><br clear=3D"none"><br clear=3D"n=
one">Best regards, Andrey Borodin.</div></div>
            </div>
        </div></body></html>
------=_Part_2044088_24612575.1706574441162--