Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vBzDB-007cbM-VH for pgsql-hackers@arkaria.postgresql.org; Thu, 23 Oct 2025 17:34:53 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1vBzDA-00AVpH-R7 for pgsql-hackers@arkaria.postgresql.org; Thu, 23 Oct 2025 17:34:51 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vBzD9-00AVo5-Pm for pgsql-hackers@lists.postgresql.org; Thu, 23 Oct 2025 17:34:51 +0000 Received: from sonic310-21.consmr.mail.gq1.yahoo.com ([98.137.69.147]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vBzD6-003O9N-29 for pgsql-hackers@postgresql.org; Thu, 23 Oct 2025 17:34:49 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com.au; s=s2048; t=1761240885; bh=M5r/4448vqmW7Klb78KrM9FuxO8w15Xzj0oYfY3jcq8=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=qRyuDqyo9k28eW8OIEZnYbT3cVzLaLVe8ht70Ekht4HK2NWGDjGYEmjBaPDiqtF+mQA1DckLOH0PeDyMbjc5er19qWFuYpOvL1z3S7nT4w15ycWMvfswpgZkKmDnjRYDqH7hgouovgeWw9zFi2xvvj6uBguU20qbS2gYVzrKkt7pJdxHidNQtkb2OsP6A2sV3jhspBSpbGDXV1t8xp25VDmu7OL29AJWNmWo8sKHMN9htRKljWWkWfzbYRiFCouiSHMkVMl508xzJCQNPD8LEYPoZNI6Q2iEBuAIexLJmyAToZOgNcKet+xvLUJ0Hai/WWX9GrlTn0czrQCK2w87Aw== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1761240885; bh=pJ9s2dK/VH9lGFri2Lzv75zq84VYfMiHlwthd9Pz6Gl=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=Emlnfg9Cn35Fh+VuJqpgnbkrSmPFddRqaosiBHvIu5Hxks/8z6tjfl5OWg9OpbKekyRdPowTA+eqgtnUWmUlxK/NrYTL6CtK/oXtM8NLklWW68YBKjeetUjADelPX53npmcPzq4KDkO22aaNHGQDYANfnIwr77ChDm4h+jGI5/Dms0L9u8rpluz4vs7Nm1i3frYS5YiKgImOe8ITHdrY3F12VAc/cdMS9a9l4oOhx9fUseU4ApNgNpW5JQPPEy1vhVGYBEDGeOes1Y0laDI5pF7tyv0u9Xt8/Yk9hon2VYvqHJ3ZYeRvd1QpEA72RIgOK0BIR8CjjCkEVQ5z3MBQqw== X-YMail-OSG: tl8kIRMVM1nLjbAloaBbxPjAKEfVX_ZKtvUp0tLI5pWhp6NGBBKGqE.L5eVqyyi 4aycpdIl5GVJ3P54cRnlxGst0iUM9kIVb6U7ffFcgnKS1mSNZIMFPOKD5SmAggmA1tauVq33aXqY ylMwPxxNO3.hccsVjBH7tmFbVXLZ.6wdUBY_M59WIXN3brdqrSD9bPl0rpwwDeAQ9QnQFB7Ayuic .Dty6ZWA3FOb8xuaCOosTDXeNrCEF7QJf.yPnWZPKicQGWJUyBNVDUH2vEW4MimTPlbc2Sway2AF rlLqpHg8LXF0CUJ_1e4xibIlIoo.NLIindDMvvjIcpziLPRt1Nj5xwCoA53IWmxEnOsBr0JElXcx yU4wlnnGZlS3w3CT.7tCiFHtTvgD56gFZNDIIVUtRSVagD.0EGcnhoeINDRM0CWQYXKGUYFl9qlN h5NL3V5w_T7xOjXVZxedCi_7qebmTdpTEx.hyPAjWQ2XQ0LE.dfdhbr8R6R1IEbqothbwZPNR51Y 40fTzqa5Y7UstVin.GReLP5zMr4sSKhY5gciwPfsMcNZDeKyptMNzDsDEazadETuIblBP7JFkY36 UD2pCMwl8qgMqwe6dUrpJSCJ947grdMjPmQ.6_0yXa_3HEGmtb9aPZMPc0sWBBvgihqm6VXiHdfg hHVOR9IqAoM7dPipfZZbejxUxWWNvgg0x436sraN._uhJH4UG5p3e5D0R0SdH5Wl4kD0fUw6Gu2y PvTNevMd9TjuQ1Mz00w21GNzklDUgB_PnJF1PyfXPMD0xogw5_hoSZEnZmr8pQVaEEeZR4KAzG5C eh_216x8cQ87tiwrshVxY1vwjt5ryguJFYOXrDDXqa95FQp_SR_mMT7_5NIxLcsoczJ053KLv5Ki d2KffpRqoB28ilOHsr5lOPwhYw9wQ4pt_KWUjTMefYKynxG8Q8MbTlx_z_uYgRED_rD6RntfYncz YvjnRBZeoo_3GtFYKypggjcDRshioI393jo8.AJXZPeCk6HW.YdcjKukUFMXpMqA4.SwYkyzwJju cmO7MvWd4AMPT6DgfiLu0SNsepVNBqsw3_7gnAZdzemh_tAR0Re8Jh_fCWE7Y0v4f1SNk.1h6mjX eK39ljpjHwsQXVkHcVTnJHVzGeKjbdcP0N6V9a2f_xbtaidmcey8gSjP.oHI4m.BR6n7yFbI_MBG CE4xXyIYc6C_nussFJvYpY3wIIBMCBvCXF9OIAL66OcS9D84FehHhc8gNuX03c.U6MvTEiVz2XB1 MNgyGzo9fmJmFZU5WgZBh1CWPQgbJxePIEu9N.q1zyIJX0qjTgLzaPEUlaEeaNG72S8SMlRaNf8F sQWjYa.Gb4NSa5tmEFV4mN7TAHA_3wIzsIYveY.Xtnfuv2ydeynKeEL4wtSBXAWKlE4xpDzBJkY_ NFleBkHA4_JPD5RVrA86fLT7pFhiojPYdyYuh3UUAIK845Ld3RkCUhDURYiSDGNfvMs5E9qeiDiU hobcoxgz5fAsb9OMBuFUT9YsPbzMdHTBguAaFyyklRwLLFOnNp_pWh1ooUTouexxoWGoTDKWdjQL ZHhJcULaXevRkKb_TjsGBbi_mnLWLbMDm.9Rh7Yjlb9eCGHQd1aqj8Bc2MXRy045ISqGLkiM9vVv J385AO2om5EmBWEAI3sqPU3VQCvGnfrzN75ERd.vexxkfEb4hjX_6ZNTtMtdWCMqJxTKDBvc7eut Btmnt3dk3oRUN7ezF9zIycTQZGMSKg69eMJWlYhCPr3omBGihDlZr2hAEdC5OYxoO.Sm1ECMAhEp Evbc5EkJ5G5bu1eMEGRz5FIMTt5o8dBxhvvMxj3jGxE1VqnwKJDIzlg21s8Fp2bl.HILAgSFI2sX SS1Vv39xY6ZbnPQgawftVtwZ4fhDdkzOKQxQmKHIyrTA51xmNwtKJVE7xbuWQVGcN7kmjb.e0X.H eDijztCwM.HHFnrrRJGrkkQxkE8cAGeE_la8rZsj4VvSYD3xslRZVsCsjoHF.YQLRk20nIS9DFdN 7YkqIoHL0jo7l1g6oYLJXYhYrL5G19OoxPDSGsoNh7eMFASzB_bPfRMU6IhOWg6wLAxS9LnSkV7b k.JzPXVgI15hCLuC9JRB0SBBjDBTw98LwlYOd6kwm_nRWT9WM.k99bLCgE1.gXVhnJLN3lUOagL4 qfQO2SuX8xKyuSEelw1_9CsonJjgKCoB1N6Du_Yd6byDnHfo1idOrveDfq2LS8T2U_B4ILmniZ1N khw_DZFUORC3uppvdMt8FjNTVr9S.P2m0df3n4x8GNWnv076UKJv6Z0C2ePVCojYT2YgCfOGjoo0 xFj0W6mCM6sTTe5N6npOcsBYtZfAU9Du67ALRJGunfE.nvMs- X-Sonic-MF: X-Sonic-ID: 831e2b4e-5955-49b5-85c3-7aa5bbc4a57e Received: from sonic.gate.mail.ne1.yahoo.com by sonic310.consmr.mail.gq1.yahoo.com with HTTP; Thu, 23 Oct 2025 17:34:45 +0000 Date: Thu, 23 Oct 2025 17:34:13 +0000 (UTC) From: Sergey Prokhorenko To: Andrey Borodin Cc: pgsql-hackers Message-ID: <1895971769.8343.1761240853939@mail.yahoo.com> In-Reply-To: <18022523-0F8F-4C07-AFF5-57DC9086D78E@yandex-team.ru> References: <1791665551.452444.1761209220211.ref@mail.yahoo.com> <1791665551.452444.1761209220211@mail.yahoo.com> <18022523-0F8F-4C07-AFF5-57DC9086D78E@yandex-team.ru> Subject: Re: Add uuid_to_base32hex() and base32hex_to_uuid() built-in functions MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_8342_1735761053.1761240853938" X-Mailer: WebService/1.1.24652 YMailNorrin Content-Length: 6518 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_8342_1735761053.1761240853938 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable >> The value of converting uuid to base32 is not obvious though, so I>> wou= ld recommend explaining it in more detail. >=C2=A0Yes, and maybe some examples of other systems that adopted this form= at would be handy too. DNSSEC (https://en.wikipedia.org/wiki/Domain_Name_System_Security_Extension= s) many encoders and decoders >=C2=A0Sergey, can you, please, extend reasoning why this particular format= is prominent? RFC 4648 describes a bunch of formats. >=C2=A0Best regards, Andrey Borodin. Base32hex:1. Preserves sort order (unlike base64)2. Compact3. Standardized = and therefore implemented consistently everywhere4. Implemented in many pro= gramming languages' standard libraries5. Does not require specifying charac= ter case during dictation6. Has simple and high-performance encoding and de= coding algorithms (necessary for system integration using JSON) The only compact text encoding eliminates the problem of incompatibility. T= he authors and contributors of RFC 9562 were categorically against having m= ultiple encodings for UUIDs. They wanted to have only one compact, sort-ord= er-preserving text encoding. For compatibility, they added the canonical UU= ID format. Due to time constraints, the compact encoding was not included i= n RFC 9562. In databases, UUIDs should preferably be stored in binary format (the UUID = type in PostgreSQL) according to RFC 9562. Intermediate formats (bytea) reduce performance, which is the very reason w= e even abandoned the more compact base36 encoding. =20 ------=_Part_8342_1735761053.1761240853938 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
>> The value of converting uuid to base32 is not obvious though, so I
>> would recommend explaining it in more detail.

> Yes, and maybe some examples of= other systems that adopted this format would be handy too.

= DNSSEC (https://en.wikipedia.org/wiki/Domain_Name_System_Security_Extensions<= /a>)

> Sergey, c= an you, please, extend reasoning why this particular format is prominent? R= FC 4648 describes a bunch of formats.


> Best regards, Andrey Borodin.


<= div style=3D"font-family: Helvetica Neue, Helvetica, Arial, sans-serif; fon= t-size: 13px;">
Base32hex:
1. Preserves sort order (unlike ba= se64)
2. Compact
3. Standardized and therefore implemen= ted consistently everywhere
4. Implemented in many programming la= nguages' standard libraries
5. Does not require specifying charac= ter case during dictation
6. Has simple and high-performance enco= ding and decoding algorithms (necessary for system integration using JSON)<= /div>

The only compact text encoding eliminates the prob= lem of incompatibility. The authors and contributors of RFC 9562 were categ= orically against having multiple encodings for UUIDs. They wanted to have o= nly one compact, sort-order-preserving text encoding. For compatibility, th= ey added the canonical UUID format. Due to time constraints, the compact en= coding was not included in RFC 9562.

In databases,= UUIDs should preferably be stored in binary format (the UUID type in Postg= reSQL) according to RFC 9562.

Intermediate formats= (bytea) reduce performance, which is the very reason we even abandoned the= more compact base36 encoding.





------=_Part_8342_1735761053.1761240853938--