UUID Functions.sgml
application/octet-stream
<sect1 id="functions-uuid">
<title>UUID Functions</title>
<indexterm zone="datatype-uuid">
<primary>UUID</primary>
<secondary>generating</secondary>
</indexterm>
<indexterm>
<primary>gen_random_uuid</primary>
</indexterm>
<indexterm>
<primary>uuidv4</primary>
</indexterm>
<indexterm>
<primary>uuidv7</primary>
</indexterm>
<indexterm>
<primary>uuid_extract_timestamp</primary>
</indexterm>
<indexterm>
<primary>uuid_extract_version</primary>
</indexterm>
<para>
<productname>PostgreSQL</productname> includes several functions to generate
UUIDs (Universally Unique IDentifiers) as specified in
<ulink url="https://datatracker.ietf.org/doc/html/rfc9562">RFC 9562</ulink>.
This section describes those included in the core distribution.
</para>
<para>
The <xref linkend="uuid-ossp"/> module provides additional functions that
implement other standard algorithms for generating UUIDs.
</para>
<para>
<productname>PostgreSQL</productname> also provides the usual comparison
operators shown in <xref linkend="functions-comparison-op-table"/> for
UUIDs.
</para>
<sect2 id="functions-generating-uuidv7">
<title>Generating Version 7 UUIDs</title>
<synopsis>
<function>uuidv7</function> (<optional> <parameter>offset</parameter>
<type>interval</type> </optional>) <returnvalue>uuid</returnvalue>
</synopsis>
<para>
Function <function>uuidv7()</function> is designed to be the default choice
for generation of primary keys instead of integer data types, backed by a
sequence generator.
</para>
<para>
The function returns a version 7 UUID, which includes a UNIX timestamp with
millisecond precision, a 12-bit sub-millisecond timestamp, and a random
component. This function can accept optional <parameter>offset</parameter>
parameter of type <type>interval</type> which is added to the internal
timestamp.
</para>
<para>
Monotonically increasing identifiers are generated even if the system clock
jumps backward, or access to the system clock is unavailable, or UUIDs are
generated at very high frequency, as the internal timestamp functions as a
counter to maintain order.
</para>
<para>
If using the <parameter>offset</parameter> parameter results in timestamp
overflow or a negative timestamp, an adjusted timestamp value is
automatically used. The timestamp behaves like a ring buffer: when the
maximum value is exceeded, it wraps around to the minimum value. Similarly,
if the absolute value of the negative <parameter>offset</parameter> exceeds
the time elapsed since 00:00:00 UTC on 1 January, 1970, the timestamp wraps
around to the maximum value.
</para>
</sect2>
<sect2 id="functions-generating-uuidv4">
<title>Generating Version 4 UUIDs</title>
<synopsis>
<function>gen_random_uuid</function> () <returnvalue>uuid</returnvalue>
<function>uuidv4</function> () <returnvalue>uuid</returnvalue>
</synopsis>
<para>
These functions return a version 4 (random) UUID.
</para>
<para>
They are not recommended for generation of primary keys.
</para>
</sect2>
<sect2 id="functions-extracting-data-from-uuid">
<title>Extracting Data from UUIDs</title>
<para>
There are also two functions to extract data from UUIDs:
</para>
<synopsis>
<function>uuid_extract_timestamp</function> (uuid) <returnvalue>timestamp with
time zone</returnvalue>
</synopsis>
<para>
This function extracts a <type>timestamp with time zone</type> from UUID
version 1 and 7. For other versions, this function returns null. Note that
the extracted timestamp is not necessarily exactly equal to the time the
UUID was generated; this depends on the implementation that generated the
UUID.
</para>
<synopsis>
<function>uuid_extract_version</function> (uuid)
<returnvalue>smallint</returnvalue>
</synopsis>
<para>
This function extracts the version from a UUID of the variant described by
<ulink url="https://datatracker.ietf.org/doc/html/rfc9562">RFC 9562</ulink>.
For other variants, this function returns null. For example, for a UUID
generated by <function>gen_random_uuid</function>, this function will
return 4.
</para>
</sect2>
<sect2 id="functions-uuid-type-choice">
<title>Deciding Whether and Which UUID to Use</title>
<para>
UUIDs serve as unique identifiers. Alternatives include integer data types
backed by a sequence generator. When choosing between them for primary keys,
consider the following information.
</para>
<informaltable>
<tgroup cols="5">
<thead>
<row>
<entry>Disadvantages or limitations of identifier types</entry>
<entry>uuidv4()</entry>
<entry>uuidv7()</entry>
<entry>uuidv7(<parameter>offset</parameter>)</entry>
<entry><type>identity</type> or <type>bigserial</type></entry>
</row>
</thead>
<tbody>
<row>
<entry>Need to generate new primary keys when merging data</entry>
<entry>NO</entry>
<entry>NO</entry>
<entry>NO</entry>
<entry>YES</entry>
</row>
<row>
<entry>Need for synchronization in distributed generation across
multiple processes (microservices)</entry>
<entry>NO</entry>
<entry>NO</entry>
<entry>NO</entry>
<entry>YES</entry>
</row>
<row>
<entry>Lock contention arises when multiple processes (microservices)
write to the same table simultaneously</entry>
<entry>NO</entry>
<entry>YES</entry>
<entry>NO (with several offsets)</entry>
<entry>YES</entry>
</row>
<row>
<entry>Absence of identifier locality reduces performance and increases
index size</entry>
<entry>YES</entry>
<entry>NO</entry>
<entry>NO</entry>
<entry>NO</entry>
</row>
<row>
<entry>Absence of identifier locality results in inefficient
partitioning</entry>
<entry>YES</entry>
<entry>NO</entry>
<entry>NO</entry>
<entry>NO</entry>
</row>
<row>
<entry>The order of creation of records is unknown for logging systems,
time-series databases, debugging, and auditing</entry>
<entry>YES</entry>
<entry>NO</entry>
<entry>NO (with nondecreasing offsets)</entry>
<entry>NO</entry>
</row>
<row>
<entry>The number of records in the table is disclosed</entry>
<entry>NO</entry>
<entry>NO</entry>
<entry>NO</entry>
<entry>YES</entry>
</row>
<row>
<entry>The creation date and time of the record are disclosed</entry>
<entry>NO</entry>
<entry>YES</entry>
<entry>NO</entry>
<entry>NO</entry>
</row>
<row>
<entry>Takes up more memory and disk space</entry>
<entry>YES</entry>
<entry>YES</entry>
<entry>YES</entry>
<entry>NO</entry>
</row>
<row>
<entry>Ambiguous full-text search for documents, files, and web pages
by identifiers</entry>
<entry>NO</entry>
<entry>NO</entry>
<entry>NO</entry>
<entry>YES</entry>
</row>
</tbody>
</tgroup>
</informaltable>
<para>
When generating identifiers simultaneously in several client sessions, the
<function>uuidv7()</function> function does not guarantee monotonicity,
although monotonicity is usually preserved in such a situation.
</para>
<para>
In real-world scenarios, the performance of keys generated by
<function>uuidv7()</function> function is nearly equivalent to that of
<type>identity</type> or <type>bigserial</type> type, significantly
outperforming <function>uuidv4()</function>.
</para>
<para>
It is advisable to assess the performance of keys generated by different
methods using the <xref linkend="pgbench"/> benchmarking utility, along
with custom scenarios and script files tailored to your specific
requirements.
</para>
</sect2>
</sect1>