FeaturesPostgresql

Managing PostgreSQL Extensions on the ObjectRocket Service

By February 13, 2020 No Comments

The PostgreSQL extensions ecosystem is extremely robust and provides a wide array of additional capabilities to PostgreSQL. As we mentioned in our launch blog, we want to provide you with all of the tools you need to operate production databases on the ObjectRocket platform, so we’ve recently added the ability to use extensions. The extensions act as plug-ins, and provide extra functionality and features.

Viewing the Available Extensions

We include a number of extensions for our Postgres instances and we’ve tried to cover the most common and requested plug-ins available. To see the list of our supported plug-ins, you can connect to a running instance on our service and use the following queries: 

`SELECT * FROM pg_available_extensions;` will show a super list of all of the extensions in the postgres extensions directory.

`SHOW extwlist.extensions;` will show the allowed extensions on our service.

The two lists will differ slightly, because there are some extensions included with the Postgres packages by default that we don’t provide user access. If there is an extension you need that’s not in the whitelist or not available at all, you can always work with our support team to get it added to our platform.

To save you some time, here is the current list of both available and allowed extensions on the ObjectRocket service:

Name
Description
address_standardizer
Used to parse an address into constituent elements. Generally used to support geocoding address normalization.
address_standardizer_data_us
Address Standardizer US dataset example
amcheck
Functions for verifying relation integrity
autoinc
Functions for auto incrementing fields
bloom
Bloom access method - signature file based index
btree_gin
Support for indexing common datatypes in GIN
btree_gist
Support for indexing common datatypes in GiST
citext
Data type for case-insensitive character strings
cube
Data type for multidimensional cubes
dblink
Connect to other PostgreSQL databases from within a database
dict_int
Text search dictionary template for integers

dict_xsyn
Text search dictionary template for extended synonym processing

earthdistance
Calculate great-circle distances on the surface of the Earth

fuzzystrmatch

Determine similarities and distance between strings

hstore

Data type for storing sets of (key, value) pairs

insert_username

Functions for tracking who changed a table

intagg
Integer aggregator and enumerator (obsolete)

intarray
Functions, operators, and index support for 1-D arrays of integers

isn
Data types for international product numbering standards

lo
Large Object maintenance

ltree
Data type for hierarchical tree-like structures

moddatetime

Functions for tracking last modification time

pageinspect

Inspect the contents of database pages at a low level

pg_buffercache
Examine the shared buffer cache
pg_freespacemap
Examine the free space map (FSM)
pg_prewarm
Prewarm relation data
pg_repack
Reorganize tables in PostgreSQL databases with minimal locks
pg_stat_statements
Track execution statistics of all SQL statements executed
pg_trgm
Text similarity measurement and index searching based on trigrams
pg_visibility
Examine the visibility map (VM) and page-level visibility info
pgcrypto
Cryptographic functions
pgrowlocks
Show row-level locking information
pgstattuple
Show tuple-level statistics
plperl
PL/Perl procedural language
plpgsql
PL/pgSQL procedural language
postgis
PostGIS geometry, geography, and raster spatial types and functions
postgis_sfcgal
PostGIS SFCGAL functions
postgis_tiger_geocoder
PostGIS tiger geocoder and reverse geocoder
postgis_topology
PostGIS topology spatial types and functions
postgres_fdw
Foreign-data wrapper for remote PostgreSQL servers
refint
Functions for implementing referential integrity (obsolete)
seg
Data type for representing line segments or floating-point intervals
sslinfo
Information about SSL certificates
tablefunc
Functions that manipulate whole tables, including crosstab
tcn
Triggered change notifications
tsm_system_rows
TABLESAMPLE method which accepts number of rows as a limit
tsm_system_time
TABLESAMPLE method which accepts time in milliseconds as a limit
unaccent
Text search dictionary that removes accents
uuid-ossp
Generate universally unique identifiers (UUIDs)
xml2
XPath querying and XSLT

I started with the query approach, because we’ll regularly be adding extensions, as needed, so running the query will always provide the most up-to-date list.

Loading Extensions

Loading extensions on the service works no differently than it usually does in Postgres. You first issue the CREATE EXTENSION command in the database that you want to add the functionality to.

Let’s show a very simple example. The “uuid-ossp” extension provides utilities to manage UUIDs. Let’s try to use the “uuid_generate_v1()” function.

Image from Gyazo

As you can see, the function fails initially, because we have not yet loaded the extension. However, after loading the “uuid-ossp” extension, we try to use the same function and we see that it now returns a UUID for us. 

Once again, this is a very simple example and extensions can do much much more, but you can see the basic mechanics.

Be aware that extensions are only active/usable in the databases you load them in. In other words, every time you create a new database, you’ll need to run CREATE EXTENSION in that database. If you’d like an extension to be available in new databases automatically, you’ll need to either create a new database template and use that when creating new databases, or update the template1 database to include that extension (since by default all new databases are a copy of template1).

Enjoy the new extensions, and let us know if you have any requests for adding specific functionality.

Steve Croce

Steve Croce

Steve Croce is currently a Senior Product Manager and Head of User Experience at ObjectRocket. Today, Steve leads the UX/UI team through rebuilding out the platform’s user interface, scopes the company’s product and feature roadmap, and oversees the day to day development for ObjectRocket's Elasticsearch and PostgreSQL offerings. A product manager by day, he still likes to embrace his engineer roots by night and develop with Elasticsearch, SQL, Kubernetes, and web application stacks. He's spoken at KubeCon + CloudNativeCon, OpenStack summit, Percona Live, and various ObjectRocket events.