Official PostgreSQL Extension Modules

PostgreSQL is a powerful open-source object-relational database system with a wealth of extension modules to enhance its functionality. ServBay includes all the official extension modules provided by PostgreSQL, allowing users to easily leverage these modules to meet various complex application requirements. Below are some commonly used official PostgreSQL extension modules and a brief introduction to each.

Common Extension Modules

1. pg_stat_statements

Function: Collects and records execution statistics for SQL statements.

Use Case: Helps database administrators analyze and optimize query performance.

Installation Command:

CREATE EXTENSION pg_stat_statements;

2. pg_trgm

Function: Provides trigram-based string similarity search functionality.

Use Case: Used for fuzzy matching, full-text search, etc.

Installation Command:

CREATE EXTENSION pg_trgm;

3. hstore

Function: Provides key-value pair storage functionality.

Use Case: Suitable for storing dynamic attributes, such as configuration data, metadata, etc.

Installation Command:

CREATE EXTENSION hstore;

4. citext

Function: Provides case-insensitive text types.

Use Case: Used for scenarios requiring case-insensitive comparison, such as usernames, email addresses, etc.

Installation Command:

CREATE EXTENSION citext;

5. uuid-ossp

Function: Generates UUIDs (Universal Unique Identifiers).

Use Case: Used in scenarios requiring unique identifiers, such as primary keys, session IDs, etc.

Installation Command:

CREATE EXTENSION "uuid-ossp";

6. intarray

Function: Provides additional operations for integer arrays.

Use Case: Suitable for scenarios requiring complex operations on integer arrays.

Installation Command:

CREATE EXTENSION intarray;

7. btree_gin

Function: Provides B-tree operations support based on GIN indexes.

Use Case: Used in scenarios requiring efficient indexing and querying.

Installation Command:

CREATE EXTENSION btree_gin;

8. btree_gist

Function: Provides B-tree operations support based on GiST indexes.

Use Case: Used in scenarios requiring multidimensional indexing and complex queries.

Installation Command:

CREATE EXTENSION btree_gist;

9. cube

Function: Provides support for multidimensional data.

Use Case: Suitable for scenarios requiring multidimensional data handling, such as data warehousing, OLAP analysis, etc.

Installation Command:

CREATE EXTENSION cube;

10. fuzzystrmatch

Function: Provides string fuzzy matching functionality.

Use Case: Used for fuzzy search, data cleaning, etc.

Installation Command:

CREATE EXTENSION fuzzystrmatch;

11. plpgsql

Function: PL/pgSQL procedural language support.

Use Case: Used for writing stored procedures and triggers.

Installation Command:

CREATE EXTENSION plpgsql;

12. tablefunc

Function: Provides support for table functions.

Use Case: Used for cross-tab queries, data pivoting, etc.

Installation Command:

CREATE EXTENSION tablefunc;

13. dblink

Function: Provides cross-database connection functionality.

Use Case: Used for querying data from one database to another.

Installation Command:

CREATE EXTENSION dblink;

14. postgres_fdw

Function: Provides access to external PostgreSQL databases.

Use Case: Used for cross-database querying and data integration.

Installation Command:

CREATE EXTENSION postgres_fdw;

15. pgcrypto

Function: Provides encryption and decryption functionality.

Use Case: Used for data encryption, hashing, and other security needs.

Installation Command:

CREATE EXTENSION pgcrypto;

Installing and Using Extension Modules

In ServBay, you can follow these steps to install and use PostgreSQL's official extension modules:

1. Connect to the PostgreSQL database:

   psql -U your_username -d your_database

2. Install the extension module:

   CREATE EXTENSION module_name;

3. Verify the installation:

   \dx

Here is an example of enabling pg_trgm:

psql (16.3 (ServBay))
Type "help" for help.

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=# CREATE EXTENSION pg_trgm;
CREATE EXTENSION
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=#

Querying PostgreSQL Bundled Modules

In ServBay, you can use the following command to view the bundled PostgreSQL modules:

SELECT * FROM pg_available_extensions order by name;

A typical return would be:

             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)

Through these extension modules, you can enhance PostgreSQL functionality to meet various complex application requirements. ServBay includes all the official modules provided by PostgreSQL, allowing users to install and use them as needed.