DEV Community

Franck Pachot for YugabyteDB

Posted on


Install extensions from PGDG repo to YugabyteDB - example with sequential_uuids

In the previous post, I installed a PostgreSQL extension to YugabyteDB by building it. Some extensions are available in the PostgreSQL Global Development Group YUM repository. Here is how to get it from there into a YugabyteDB.

I'm using YugabyteDB 2.17, which is compatible with PostgreSQL 11.2, on Centos7. Here are the available .rpm:

Example with sequential_uuids

Vlad Mihalcea asked if we have a Time-Sorted Unique Identifier in YugabyteDB. I answered that we probably don't need it but that there is probably a PostgreSQL extension that works with YugabyteDB:

And, yes, there is, available from the YUM repository. I'll take this as an example.

I do all that in a temporary directory to avoid overwriting existing files

Download the RPM

I can download the .rpm from the online repo:

yum install -y wget
wget -c

Enter fullscreen mode Exit fullscreen mode

or though YUM after installing the repo:

yum install -y
yum install --downloadonly -y --downloaddir=$PWD sequential_uuids_11

Enter fullscreen mode Exit fullscreen mode


If downloaded directly, I can check the dependencies with rpm -qRp:
Image description

If downloaded with yum, the dependencies have been downloaded. I can also I can get them with yum -q deplist and they were displayed when downloading:

Image description

Note that I don't need the postgresql11 ones because I'll use YugabyteDB instead.

Extract the extension to YugabyteDB

My YugabyteDB installation dir is /home/yugabyte and the extension files (share/extensions/*.{control,sql} and libs/*.so) must go into /home/yugabyte/postgres.

Because the RPM installs it in /usr/pgsql-11, I create a symbolic link in my temporary directory and them with a relative path:

mkdir -p ./usr &&
ln -Ts /home/yugabyte/postgres ./usr/pgsql-11 &&
rpm2cpio ./sequential_uuids_11-*.rpm | 
 cpio -idmv --no-absolute-filenames

Enter fullscreen mode Exit fullscreen mode

Using -v, it displays the extracted files:

Image description

Thanks to the symbolic link, they are in the YugabyteDB postgres directory.

The temporary directory can be removed.

Testing the extension

You must install the extension on all nodes of your YugabyteDB server.

Then, when connected to any node, load the extension and play with it:

yugabyte=# create extension sequential_uuids;

yugabyte=# create table demo ( id uuid, primary key(id asc) );

yugabyte=# insert into demo
             select uuid_time_nextval(5,x'ffff'::int)
             from generate_series(1,5)
            returning *;

yugabyte=# \watch
Thu 22 Dec 2022 08:24:15 AM UTC (every 2s)

(5 rows)

Thu 22 Dec 2022 08:24:17 AM UTC (every 2s)

(5 rows)

Thu 22 Dec 2022 08:24:19 AM UTC (every 2s)

(5 rows)

Thu 22 Dec 2022 08:24:21 AM UTC (every 2s)

(5 rows)

Thu 22 Dec 2022 08:24:23 AM UTC (every 2s)
Enter fullscreen mode Exit fullscreen mode

You see that the first 4 bytes (because 0xffff, the default, and the maximum) are the same within a 5 second interval (the first parameter which I've set to 5 instead of the default 60 seconds) with the others random.

Note that I have defined the primary key sharding as ASC rather than the default HASH because, if the goal is to colocate the rows inserted at the same time, you don't want to apply hash sharding.

I also verify that from concurrent sessions the prefix is the same:

for i in {1..10}
 ysqlsh -tAq -h $(hostname) -c 'insert into demo select uuid_time_nextval() returning *,now()'&
Enter fullscreen mode Exit fullscreen mode

Image description

Validating the compatibility with PostgreSQL

The best way to compare the behavior in YugabyteDB is to run the PostgreSQL regression test and compare with the expected output:

yum install -y git
git clone

ysqlsh -h $(hostname) --echo-all --quiet         \
 --file sequential-uuids/test/sql/uuids.sql      |
  sdiff sequential-uuids/test/expected/uuids.out -

Enter fullscreen mode Exit fullscreen mode

The only difference is because the regression test has no ORDER BY and the order of rows from a hash partitioned table (the default) in a distributed database is different than from the single-node heap table:
Image description
But the result is the same.

The full PostgreSQL-compatibility of the extension is confirmed by the regression tests.

About Sequential UUID in YugabyteDB

⚠️ You must think about the consequence in a Distributed SQL database before using a time-based UUID. Thanks to the LSM-Tree storage, YugabyteDB doesn't have the problems that sequential_uuids tries to solve (WAL write amplification, B-Tree fragmentation and clustering factor). If you want a UUID, then the one from pgcrypto (already installed in YugabyteDB) gen_random_uuid() is probably the right one.

In addition to that, having concurrent sessions touching the same key range will create a hotspot on one tablet. However, if there are not too many concurrent sessions, and the load is distributed with other tables, then maybe this UUID can benefit from colocation in DocDB and filesystem caches. But a cached sequence may be better as it keeps rows together from the same session, but distributes those from concurrent sessions.

Anyway, I've run the above for a while, with more rows inserted (from generate_series(1,100000)) and checked that tablet auto-split occurs:
Image description
As you see, one is still at Split Depth 1 because no Sequential UUID went into this range, and another has been split 7 times. So YugabyteDB still balanced the storage.

Summary on the extension installation

If you think a PostgreSQL extension is necessary for your application, you can use this example to test it. The same can be used to deploy it. But to be sure that it is supported, do not hesitate to open a git issue of ask in the slack channel.

Top comments (0)

Timeless DEV post...

Git Concepts I Wish I Knew Years Ago

The most used technology by developers is not Javascript.

It's not Python or HTML.

It hardly even gets mentioned in interviews or listed as a pre-requisite for jobs.

I'm talking about Git and version control of course.

One does not simply learn git