Install and use extensions

This page documents how to install and use PostgreSQL extensions that are tested to work with YSQL. Note that since YugabyteDB’s storage architecture is not the same as that of native PostgreSQL, PostgreSQL extensions, especially those that interact with the storage layer, are not expected to work as-is on YugabyteDB. We intend to incrementally develop support for as many extensions as possible.

Pre-bundled extensions

These are extensions that are included in the standard YugabyteDB distribution and can be enabled in YSQL by running the CREATE EXTENSION statement.

fuzzystrmatch

The fuzzystrmatch extension provides several functions to determine similarities and distance between strings.

For more information, refer to fuzzystrmatch in the PostgreSQL docs.

fuzzystrmatch example

 levenshtein | metaphone
-------------+-----------
           2 | YKBT
(1 row)

pgcrypto

The pgcrypto extension provides various cryptographic functions.

For more information, refer to pgcrypto in the PostgreSQL docs.

pgcrypto example

                  id                  | content |                   digest
--------------------------------------+---------+--------------------------------------------
 b8f2e2f7-0b8d-4d26-8902-fa4f5277869d | abc     | \xa9993e364706816aba3e25717850c26c9cd0d89d
(1 row)

pg_stat_statements

The pg_stat_statements extension module is installed by default, but must be enabled before the pg_stat_statements view can be queried.

To get the output of pg_stat_statements in JSON format, visit https://<yb-tserver-ip>:13000/statements in your web browser, where <yb-tserver-ip> is the IP address of any YB-TServer node in your cluster.

For more information, refer to pg_stat_statements in the PostgreSQL documentation.

Server Programming Interface (spi) module

The spi module lets you use the Server Programming Interface (SPI) to create user-defined functions and stored procedures in C, and to run YSQL queries directly against YugabyteDB. In YugabyteDB, the following four (of the five — timetravel is not currently supported) extensions provided in the spi module can be used:

• autoinc functions autoincrement fields.
• insert_username functions track who changed a table.
• moddatetime functions track last modification times.
• refint functions implement referential integrity.

For more information, refer to spi module in the PostgreSQL docs.

spi example

1. Set up a table with triggers for tracking modification time and user (role). Connect with ysqlsh and run the following commands:

   CREATE EXTENSION insert_username;
   CREATE EXTENSION moddatetime;
   
   CREATE TABLE spi_test (
     id int primary key,
     content text,
     username text not null,
     moddate timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL
   );
   
   CREATE TRIGGER insert_usernames
     BEFORE INSERT OR UPDATE ON spi_test
     FOR EACH ROW
     EXECUTE PROCEDURE insert_username (username);
   
   CREATE TRIGGER update_moddatetime
     BEFORE UPDATE ON spi_test
     FOR EACH ROW
     EXECUTE PROCEDURE moddatetime (moddate);
   

2. Insert some rows. Each insert should add the current role as username and the current timestamp as moddate.

   SET ROLE yugabyte;
   INSERT INTO spi_test VALUES(1, 'desc1');
   
   SET ROLE postgres;
   INSERT INTO spi_test VALUES(2, 'desc2');
   INSERT INTO spi_test VALUES(3, 'desc3');
   
   SET ROLE yugabyte;
   INSERT INTO spi_test VALUES(4, 'desc4');
   
   SELECT * FROM spi_test ORDER BY id;
   

    id | content | username |          moddate
   ----+---------+----------+----------------------------
     1 | desc1   | yugabyte | 2019-09-13 16:55:53.969907
     2 | desc2   | postgres | 2019-09-13 16:55:53.983306
     3 | desc3   | postgres | 2019-09-13 16:55:53.98658
     4 | desc4   | yugabyte | 2019-09-13 16:55:53.991315
   (4 rows)
   

   The yugabyte and (for compatibility) postgres YSQL users are created by default.

3. Update some rows. This should update both username and moddate accordingly.

   UPDATE spi_test SET content = 'desc1_updated' WHERE id = 1;
   UPDATE spi_test SET content = 'desc3_updated' WHERE id = 3;
   
   SELECT * FROM spi_test ORDER BY id;
   

   id |    content    | username |          moddate
   ----+---------------+----------+----------------------------
     1 | desc1_updated | yugabyte | 2019-09-13 16:56:27.623513
     2 | desc2         | postgres | 2019-09-13 16:55:53.983306
     3 | desc3_updated | yugabyte | 2019-09-13 16:56:27.634099
     4 | desc4         | yugabyte | 2019-09-13 16:55:53.991315
   (4 rows)
   

tablefunc

The tablefunc extension provides several table functions. For example, normal_rand() creates values, picked using a pseudorandom generator, from an ideal normal distribution. You specify how many values you want, and the mean and standard deviation of the ideal distribution. You use it in the same way that you use generate_series()

tablefunc example

Test it as follows:

You'll see results similar to the following:

 k  |    v     
----+----------
  1 |   988.53
  2 |  1005.18
  3 |  1014.30
  4 |  1000.92
  5 |   999.51
  6 |  1000.94
  7 |  1007.45
  8 |   991.22
  9 |   987.95
 10 |   996.57
(10 rows)

Every time you repeat the test, you'll see different generated values for v. The section Analyzing a normal distribution with percent_rank(), cume_dist() and ntile(), within the major documentation section Window functions, uses normal_rand(). It populates a table with a large number, say 100,000, rows and displays the outcome as a histogram that clearly shows the familiar bell-curve shape.

These other functions are brought by tablefunc: connectby(); and crosstab() and crosstabN().

The connectby() function displays a hierarchy of the kind that you see in an "employees" table with a reflexive foreign key constraint where "manager_id" refers to "employee_id". Each next deeper level in the tree is indented from its parent following the well-known pattern.

The crosstab()and crosstabN() functions produce “pivot” displays. The "N" in crosstabN() indicates the fact that a few, crosstab1(), crosstab2(), crosstab3(), are provided natively by the extension and that you can follow documented steps to create more.

For more information, refer to tablefunc in the PostgreSQL docs.

Extensions requiring installation

Other extensions have to be installed manually before they can be enabled with the CREATE EXTENSION statement.

Typically, extensions need three types of files:

• Shared library files (<name>.so)
• SQL files (<name>--<version>.sql)
• Control files (<name>.control)

To install an extension, you need to copy these files into the respective directories of your YugabyteDB installation.

Shared library files will be in the pkglibdir directory, while SQL and control files should be in the extension subdirectory of the libdir directory.

To find these directories on your local installation, you can use the YugabyteDB pg_config executable. First, alias it to yb_pg_config by replacing <yugabyte-path> with the path to your YugabyteDB installation in the following command and then run the command.

List existing shared libraries with:

List SQL and control files for already-installed extensions with:

To get these files for your target extension, build the extension from scratch following the extension's build instructions.

Alternatively, if you already have PostgreSQL (ideally version 11.2 for best YSQL compatibility) with that extension installed, you can find these files as follows:

Copy the files to the YugabyteDB installation, and restart the cluster (or the respective node in a multi-node install). Finally, connect to the cluster with ysqlsh and run the CREATE EXTENSION statement to create the extension.

Only some extensions are currently supported. If you encounter any issues when installing or using a particular extension, file a GitHub issue in the yugabyte/yugabyte-db repository.

PostGIS

PostGIS is a spatial database extender for PostgreSQL-compatible object-relational databases. The simplest way to set it up locally is to install it together with regular PostgreSQL.

YSQL does not yet support GiST indexes. This is tracked in GitHub issue #1337.

macOS

There are two ways to install PostGIS on macOS:

• Download and install Postgres.app

• Or, install with Homebrew:

  $ brew install postgres postgis
  

Ubuntu

Add the PostgreSQL APT sources. Then, use apt to install:

CentOS

Get the YUM repository from the PostgreSQL website. Then, use yum or dnf to install:

Post-install

Now, follow the preceding instructions to copy the needed files into your YugabyteDB installation, and then create the extension.

On Linux systems, PostGIS libraries have dependencies that must also be installed. Use the extensions option to the post-install tool, available in YugabyteDB 2.3.2 and later.

Then, create the extension:

This might take a couple of minutes.

postgis example

1. Get a sample PostGIS dataset:

   $ wget -O edmonton.zip "https://data.edmonton.ca/api/geospatial/jfvj-x253?method=export&format=Shapefile" && unzip edmonton.zip
   

2. Extract the dataset using the shp2pgsql tool. This should come with your PostgreSQL installation — it is not yet packaged with YSQL.

   $ shp2pgsql geo_export_*.shp > edmonton.sql
   

3. Edit the generated edmonton.sql for YSQL compatibility.

   • First, inline the PRIMARY KEY declaration for gid as YSQL does not yet support adding primary key constraints after the table creation.
   • Additionally, for simplicity, change the table name (and references to it in the associated INSERT statements) to just geo_export (in other words, remove the UUID postfix).

   The edmonton.sql file should now start as follows:

   SET CLIENT_ENCODING TO UTF8;
   SET STANDARD_CONFORMING_STRINGS TO ON;
   BEGIN;
   CREATE TABLE "geo_export" (gid serial PRIMARY KEY,
     "area_km2" numeric,
     "name" varchar(254),
     "number" numeric);
   SELECT AddGeometryColumn('','geo_export','geom','0','MULTIPOLYGON',2);
   
   INSERT INTO "geo_export" ("area_km2","name","number",geom) VALUES ...
   

4. Load the sample data.

   $ ./bin/ysqlsh -a -f edmonton.sql
   

5. Run some sample queries. Connect with ysqlsh and run:

   SELECT name, area_km2, ST_Area(geom), ST_Area(geom)/area_km2 AS area_ratio FROM "geo_export" LIMIT 10;
   

               name            |     area_km2      |       st_area        |      area_ratio
   ----------------------------+-------------------+----------------------+----------------------
   River Valley Terwillegar   | 3.077820277027079 | 0.000416617423004673 | 0.000135361192501822
   Carleton Square Industrial | 0.410191631391664 | 5.56435079305678e-05 | 0.000135652469899947
   Cy Becker                  | 1.015144841249301 | 0.000137900847258255 | 0.000135843518732308
   Elsinore                   | 0.841471068786406 | 0.000114331091817771 |  0.00013587049639468
   McLeod                     | 0.966538217483227 | 0.000131230296771637 | 0.000135773520796051
   Gainer Industrial          | 0.342464541730177 | 4.63954326887451e-05 | 0.000135475142782225
   Coronet Industrial         | 1.606907195063447 | 0.000217576340986435 | 0.000135400688760899
   Marquis                    | 9.979100854886905 |  0.00135608901739072 | 0.000135892906295924
   South Terwillegar          | 1.742840325820606 | 0.000235695089933611 | 0.000135236192576985
   Carlisle                   | 0.961897333826841 | 0.000130580966739925 | 0.000135753538499185
   (10 rows)
   

   SELECT a.name, b.name FROM "geo_export" AS a, "geo_export" AS b
   WHERE ST_Intersects(a.geom, b.geom) AND a.name LIKE 'University of Alberta';
   

           name          |          name
   -----------------------+-------------------------
   University of Alberta | University of Alberta
   University of Alberta | McKernan
   University of Alberta | Belgravia
   University of Alberta | Garneau
   University of Alberta | River Valley Mayfair
   University of Alberta | River Valley Walterdale
   University of Alberta | Windsor Park
   (7 rows)
   

postgresql-hll (PostgreSQL extension for HyperLogLog)

The postgresql-hll module introduces the data type hll, which is a HyperLogLog data structure. HyperLogLog is a fixed-size, set-like structure used for distinct value counting with tunable precision.

First, install postgres-hll from source locally in a PostgreSQL instance. Ideally, use the same PostgreSQL version as that incorporated into YugabyteDB. You can see the PostgreSQL version incorporated in a YugabyteDB installation by using the following ysqlsh command:

Once you've installed the extension in PostgreSQL, copy the files to your YugabyteDB instance as follows:

postgresql-hll example

You can run a quick example for the postgresql-hll repository. Connect with ysqlsh and run the following:

uuid-ossp

The uuid-ossp extension provides functions to generate universally unique identifiers (UUIDs), and functions to produce certain special UUID constants.

The easiest way to install the extension is to copy the files from an existing PostgreSQL installation into Yugabyte, and then create the extension.

uuid-ossp example

Connect with ysqlsh and run: