Official PostgreSQL Extension Modules

ServBay is a powerful local web development environment that integrates a variety of developer tools, including a feature-rich PostgreSQL database. The strength of PostgreSQL lies not only in its core capabilities but also in its extensive ecosystem of official extensions. ServBay comes pre-installed with most of PostgreSQL’s official extension modules, allowing developers to easily enable and use these modules in their local environment, significantly enhancing database functionality to meet complex application requirements.

This article will introduce some commonly used official PostgreSQL extension modules and guide you on how to install and use them within ServBay.

Overview of Popular Extension Modules

PostgreSQL offers numerous official extension modules covering a range of features, from performance monitoring to data types and full-text search. Below are some frequently used extensions that developers often need in their daily work:

1. pg_stat_statements

• Functionality: Collects and records statistics for all SQL statements executed on the server, such as number of calls, total execution time, rows processed, and more.
• Use Cases: Helps database administrators and developers analyze query performance, identify slow queries, and optimize bottlenecks.
• Installation Command:

  CREATE EXTENSION pg_stat_statements;

  Note: After installation, you will typically need to configure shared_preload_libraries = 'pg_stat_statements' in the postgresql.conf file and restart the PostgreSQL service to fully enable and collect data. ServBay provides a convenient way to modify these settings.

2. pg_trgm

• Functionality: Offers trigram-based text similarity measurement functions and index support.
• Use Cases: Ideal for implementing efficient fuzzy matching, similarity search, and full-text search features, especially for handling typos or variants.
• Installation Command:

  CREATE EXTENSION pg_trgm;

3. hstore

• Functionality: Provides a data type for storing sets of key-value pairs within a single PostgreSQL value.
• Use Cases: Suitable for scenarios that require storing dynamic attributes, unstructured, or semi-structured data, such as configuration data, metadata, or user preferences, without the need for a strictly defined table structure.
• Installation Command:

  CREATE EXTENSION hstore;

4. citext

• Functionality: Provides a case-insensitive text string data type.
• Use Cases: Used when case-insensitive comparison is desired, for example, when storing and comparing usernames, email addresses, or tags. This avoids the need to frequently use the LOWER() function in queries and simplifies application logic.
• Installation Command:

  CREATE EXTENSION citext;

5. uuid-ossp

• Functionality: Supplies functions for generating universally unique identifiers (UUIDs), supporting multiple standard UUID versions (such as version 1, 3, 4, and 5).
• Use Cases: Used for generating globally unique identifiers, commonly for primary keys, session IDs, and unique tokens in distributed systems.
• Installation Command:

  CREATE EXTENSION "uuid-ossp";

  Note: The extension name contains a hyphen, so you must enclose it in double quotes in the CREATE EXTENSION command.

6. intarray

• Functionality: Provides additional functions and operators for integer arrays.
• Use Cases: Useful when you need to perform set operations (such as intersection and union), containment checks, sorting, and complex manipulations on integer arrays directly at the database level.
• Installation Command:

  CREATE EXTENSION intarray;

7. btree_gin

• Functionality: Adds GIN (Generalized Inverted Index) support for B-tree data types such as integers, text, dates, and more.
• Use Cases: GIN indexes are commonly used for indexing multivalued data types like arrays, hstore, and tsvector. With btree_gin, you can use GIN indexes on B-tree indexable elements within these composite types to boost query efficiency.
• Installation Command:

  CREATE EXTENSION btree_gin;

8. btree_gist

• Functionality: Enables GiST (Generalized Search Tree) index support for B-tree data types.
• Use Cases: GiST is a versatile index structure. btree_gist allows GiST indexes to be used on B-tree data, which is particularly useful for supporting multiple query types (such as range and containment queries), or for creating indexes on multiple columns.
• Installation Command:

  CREATE EXTENSION btree_gist;

9. cube

• Functionality: Provides a data type for representing multidimensional cubes or points, along with related operators and functions.
• Use Cases: Particularly useful for working with multidimensional data, such as simple geospatial data (2D or 3D points), data warehousing for multidimensional analytics, or scientific computation.
• Installation Command:

  CREATE EXTENSION cube;

10. fuzzystrmatch

• Functionality: Offers functions to determine similarity and distance between strings, such as Levenshtein distance, Soundex, Metaphone, etc.
• Use Cases: Used for fuzzy string matching, spell checking, data cleansing, and deduplication.
• Installation Command:

  CREATE EXTENSION fuzzystrmatch;

11. plpgsql

• Functionality: This is PostgreSQL’s procedural language, allowing users to write stored procedures, trigger functions, and anonymous code blocks.
• Use Cases: Used to implement complex business logic, automate tasks, and handle data processing workflows directly on the database server side. plpgsql is installed by default in PostgreSQL; you typically do not need to install it manually, but you can confirm its presence or recreate it using CREATE EXTENSION.
• Installation Command:

  CREATE EXTENSION plpgsql;

12. tablefunc

• Functionality: Provides functions for manipulating and reshaping entire tables, the most famous being the crosstab function.
• Use Cases: Used for pivot table queries, turning unique values from one column into multiple columns, which is often required for reporting and data analysis.
• Installation Command:

  CREATE EXTENSION tablefunc;

13. dblink

• Functionality: Allows you to connect to another PostgreSQL database and execute queries from within your local database session.
• Use Cases: Ideal for simple data queries or migrations between different databases, without needing to handle cross-database connection logic in your application layer.
• Installation Command:

  CREATE EXTENSION dblink;

14. postgres_fdw

• Functionality: Provides a foreign data wrapper, allowing you to access and query tables from remote PostgreSQL databases as if they were local tables.
• Use Cases: Facilitates cross-database data integration and federated queries. More powerful and flexible than dblink, it supports more complex query optimizations.
• Installation Command:

  CREATE EXTENSION postgres_fdw;

15. pgcrypto

• Functionality: Offers various cryptographic and decryption functions, including hashing, symmetric encryption, and public key encryption.
• Use Cases: Used to implement data encryption at the database layer, store hashed passwords, generate or verify digital signatures, and other security-related features.
• Installation Command:

  CREATE EXTENSION pgcrypto;

These are just some of the official PostgreSQL extension modules included in ServBay. The goal of ServBay is to provide almost every official extension from the PostgreSQL community to cater to diverse developer needs.

Installing and Using PostgreSQL Extensions in ServBay

Installing and using PostgreSQL extensions in the ServBay environment is straightforward. Extension modules are installed in a specific database, which means you need to enable the desired extension in every database where you want to use it.

Prerequisites:

• Ensure ServBay is running and the PostgreSQL database service has started.

Steps:

1. Connect to Your Target PostgreSQL Database: Open your terminal application (such as Terminal or iTerm2 on macOS) and connect to the database where you wish to install the extension using the psql command line tool. By default, ServBay uses postgres as the PostgreSQL username and the default database name is also postgres.

   psql -U your_username -d your_database

   For example, to connect to the default postgres database:

   psql -U postgres -d postgres

   If prompted for a password, enter the root/postgres user password that you set for PostgreSQL in ServBay.

2. Install the Extension Module: Once connected, you’ll be in the psql command-line interface. Run the CREATE EXTENSION command to install the required extension module. Replace module_name with the name of the extension you wish to install.

   CREATE EXTENSION module_name;

   For example, to install the pg_trgm extension:

   CREATE EXTENSION pg_trgm;

3. Verify Installation: After installation, you can run the \dx command to list all installed extensions in the current database and confirm that your target extension has been added successfully.

   \dx

Example: Enabling the pg_trgm Extension

Below is a complete example of connecting to the postgres database via the psql command line and enabling the pg_trgm extension:

# Run the following command in the terminal to connect to the database
psql -U postgres -d postgres

# After connecting, you’ll see the psql prompt
psql (16.3 (ServBay))
Type "help" for help.

postgres=# -- View installed extensions (the initial list may include only plpgsql and pgcrypto by default)
postgres=# \dx
                  List of installed extensions
   Name   | Version |   Schema   |         Description          
----------+---------+------------+------------------------------
 pgcrypto | 1.3     | public     | cryptographic functions
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

postgres=# -- Install the pg_trgm extension
postgres=# CREATE EXTENSION pg_trgm;
CREATE EXTENSION

postgres=# -- Check the installed extensions again to confirm pg_trgm is added
postgres=# \dx
                                    List of installed extensions
   Name   | Version |   Schema   |                            Description                            
----------+---------+------------+-------------------------------------------------------------------
 pg_trgm  | 1.6     | public     | text similarity measurement and index searching based on trigrams
 pgcrypto | 1.3     | public     | cryptographic functions
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
(3 rows)

postgres=# -- Installation successful. Now you can use functions and operators provided by pg_trgm in this database.

Listing Available PostgreSQL Extensions in ServBay

Before installing, it’s helpful to be aware of all PostgreSQL extension modules packaged within ServBay. This can help you discover additional features that may benefit your projects.

After connecting to any PostgreSQL database, you can run the following SQL query to get a complete list of available extensions:

SELECT * FROM pg_available_extensions ORDER BY name;

Running this query will return a table listing all extension modules supported by the PostgreSQL version integrated in ServBay, including their names, default versions, whether they’re installed in the current database, and their descriptions.

A typical result (the list may be long and specifics will depend on the ServBay and PostgreSQL versions) might look like:

             name             | default_version | installed_version |                                                       comment                                                       
------------------------------+-----------------+-------------------+---------------------------------------------------------------------------------------------------------------------
 address_standardizer         | 3.4.2           |                   | Used to parse an address into constituent elements. Generally used to support geocoding address normalization step.
 address_standardizer_data_us | 3.4.2           |                   | Address Standardizer US dataset example
 adminpack                    | 2.1             |                   | administrative functions for PostgreSQL
 amcheck                      | 1.3             |                   | functions for verifying relation integrity
 autoinc                      | 1.0             |                   | functions for autoincrementing fields
 bloom                        | 1.0             |                   | bloom access method - signature file based index
 btree_gin                    | 1.3             |                   | support for indexing common datatypes in GIN
 btree_gist                   | 1.7             |                   | support for indexing common datatypes in GiST
 citext                       | 1.6             |                   | data type for case-insensitive character strings
 cube                         | 1.5             |                   | data type for multidimensional cubes
 dblink                       | 1.2             |                   | connect to other PostgreSQL databases from within a database
 dict_int                     | 1.0             |                   | text search dictionary template for integers
 dict_xsyn                    | 1.0             |                   | text search dictionary template for extended synonym processing
 earthdistance                | 1.1             |                   | calculate great-circle distances on the surface of the Earth
 file_fdw                     | 1.0             |                   | foreign-data wrapper for flat file access
 fuzzystrmatch                | 1.2             |                   | determine similarities and distance between strings
 hstore                       | 1.8             |                   | data type for storing sets of (key, value) pairs
 insert_username              | 1.0             |                   | functions for tracking who changed a table
 intagg                       | 1.1             |                   | integer aggregator and enumerator (obsolete)
 intarray                     | 1.5             |                   | functions, operators, and index support for 1-D arrays of integers
 isn                          | 1.2             |                   | data types for international product numbering standards
 lo                           | 1.1             |                   | Large Object maintenance
 ltree                        | 1.2             |                   | data type for hierarchical tree-like structures
 moddatetime                  | 1.0             |                   | functions for tracking last modification time
 old_snapshot                 | 1.0             |                   | utilities in support of old_snapshot_threshold
 pageinspect                  | 1.12            |                   | inspect the contents of database pages at a low level
 pg_buffercache               | 1.4             |                   | examine the shared buffer cache
 pg_freespacemap              | 1.2             |                   | examine the free space map (FSM)
 pg_jieba                     | 1.1.1           |                   | a parser for full-text search of Chinese
 pg_prewarm                   | 1.2             |                   | prewarm relation data
 pg_stat_statements           | 1.10            |                   | track planning and execution statistics of all SQL statements executed
 pg_surgery                   | 1.0             |                   | extension to perform surgery on a damaged relation
 pg_trgm                      | 1.6             | 1.6               | text similarity measurement and index searching based on trigrams
 pg_visibility                | 1.2             |                   | examine the visibility map (VM) and page-level visibility info
 pg_walinspect                | 1.1             |                   | functions to inspect contents of PostgreSQL Write-Ahead Log
 pgcrypto                     | 1.3             | 1.3               | cryptographic functions
 pgrouting                    | 3.6.2           |                   | pgRouting Extension
 pgrowlocks                   | 1.2             |                   | show row-level locking information
 pgstattuple                  | 1.5             |                   | show tuple-level statistics
 plpgsql                      | 1.0             | 1.0               | PL/pgSQL procedural language
 postgis                      | 3.4.2           |                   | PostGIS geometry and geography spatial types and functions
 postgis_sfcgal               | 3.4.2           |                   | PostGIS SFCGAL functions
 postgis_tiger_geocoder       | 3.4.2             |                   | PostGIS tiger geocoder and reverse geocoder
 postgres_fdw                 | 1.1             |                   | foreign-data wrapper for remote PostgreSQL servers
 refint                       | 1.0             |                   | functions for implementing referential integrity (obsolete)
 seg                          | 1.4             |                   | data type for representing line segments or floating-point intervals
 sslinfo                      | 1.2             |                   | information about SSL certificates
 tablefunc                    | 1.0             |                   | functions that manipulate whole tables, including crosstab
 tcn                          | 1.0             |                   | Triggered change notifications
 tsm_system_rows              | 1.0             |                   | TABLESAMPLE method which accepts number of rows as a limit
 tsm_system_time              | 1.0             |                   | TABLESAMPLE method which accepts time in milliseconds as a limit
 unaccent                     | 1.1             |                   | text search dictionary that removes accents
 vector                       | 0.7.0           |                   | vector data type and ivfflat and hnsw access methods
 xml2                         | 1.1             |                   | XPath querying and XSLT
 zhparser                     | 2.2             |                   | a parser for full-text search of Chinese
(55 rows)

Note: The above list is illustrative; the actual list and version numbers you see in ServBay may vary based on updates to ServBay or PostgreSQL.

Summary

ServBay greatly simplifies taking advantage of powerful official PostgreSQL extensions within your local development environment by including a rich set of pre-installed modules. Developers don’t need to compile or install them manually—just enable the required extensions in your database via simple SQL commands, so you can focus on application development and boost productivity. With these extensions, adding advanced database features—such as performance analysis, full-text search, or data security—to your projects is a breeze.