Officiële PostgreSQL-extensiemodules

ServBay is een krachtige lokale webontwikkelomgeving die verschillende ontwikkelaarstools integreert, waaronder de veelzijdige PostgreSQL-database. De kracht van PostgreSQL zit niet alleen in de kernfunctionaliteit, maar ook in het uitgebreide ecosysteem van officiële extensies. ServBay wordt standaard geleverd met de meeste officiële PostgreSQL-extensiemodules geïnstalleerd, waardoor ontwikkelaars deze modules eenvoudig lokaal kunnen activeren en gebruiken. Dit vergroot de functionaliteit van de database aanzienlijk en voorziet in uiteenlopende en complexe toepassingsbehoeften.

In dit artikel bespreken we een aantal veelgebruikte officiële PostgreSQL-extensiemodules en leggen we uit hoe je deze in ServBay installeert en gebruikt.

Overzicht van veelgebruikte extensiemodules

PostgreSQL biedt een breed scala aan officiële extensies, variërend van prestatiemonitoring tot datatypes en full-text search. Hieronder vind je enkele modules die ontwikkelaars frequent inzetten in hun dagelijkse werk:

1. pg_stat_statements

• Functionaliteit: Verzamelt en registreert statistieken van alle op de server uitgevoerde SQL-statements, zoals het aantal aanroepen, totale uitvoeringstijd, aantal verwerkte rijen, enzovoorts.
• Toepassing: Helpt databasebeheerders en ontwikkelaars bij het analyseren van queryprestaties, het identificeren van trage queries en het optimaliseren van knelpunten.
• Installatieopdracht:

  CREATE EXTENSION pg_stat_statements;

  Let op: Na installatie moet je doorgaans ook in postgresql.conf de optie shared_preload_libraries = 'pg_stat_statements' instellen en de PostgreSQL-dienst herstarten om volledige functionaliteit te verkrijgen. ServBay maakt het eenvoudig om configuraties aan te passen.

2. pg_trgm

• Functionaliteit: Biedt functies en indexen voor tekstanalyse op basis van trigrammen (drielettercombinaties).
• Toepassing: Voor snelle fuzzy matching, gelijkeniszoeken en full-text search. Ideaal voor het werken met typfouten of variaties in tekst.
• Installatieopdracht:

  CREATE EXTENSION pg_trgm;

3. hstore

• Functionaliteit: Biedt een datatype om sleutel-waardeparens in één PostgreSQL-veld op te slaan.
• Toepassing: Perfect voor het opslaan van dynamische attributen, ongestructureerde of semi-gestructureerde data zoals configuraties, metadata of gebruikersvoorkeuren zonder een rigide databasetabelstructuur te definiëren.
• Installatieopdracht:

  CREATE EXTENSION hstore;

4. citext

• Functionaliteit: Levert een datatype voor tekst waarbij hoofdlettergevoeligheid wordt genegeerd.
• Toepassing: Voor toepassingen waarbij tekstvergelijkingen ongevoelig voor hoofdletters moeten zijn, zoals bij gebruikersnamen, e-mailadressen of tags. Dit voorkomt overmatig gebruik van de LOWER() functie in queries en vereenvoudigt de applicatielogica.
• Installatieopdracht:

  CREATE EXTENSION citext;

5. uuid-ossp

• Functionaliteit: Biedt functies voor het genereren van universeel unieke identificaties (UUID's), ondersteunt verscheidene standaarden (zoals versie 1, 3, 4 en 5).
• Toepassing: Voor het genereren van wereldwijde unieke sleutels, veel gebruikt als primaire sleutels, sessie-ID’s of unieke aanduidingen in gedistribueerde systemen.
• Installatieopdracht:

  CREATE EXTENSION "uuid-ossp";

  Let op: De extensienaam bevat een koppelteken en moet daarom tussen dubbele aanhalingstekens bij CREATE EXTENSION.

6. intarray

• Functionaliteit: Biedt extra functies en operatoren voor het werken met integer-arrays.
• Toepassing: Handig wanneer je op database-niveau complexe operaties wil doen op integer-arrays, zoals doorsnedes, unies, contains-queries of sorteren.
• Installatieopdracht:

  CREATE EXTENSION intarray;

7. btree_gin

• Functionaliteit: Biedt GIN-indexondersteuning voor B-tree datatypes zoals integer, tekst en datum.
• Toepassing: GIN-indexen zijn vaak geschikt voor multi-value types zoals arrays, hstore of tsvector. btree_gin maakt het mogelijk om ook B-tree-indexeerbare attributen in zulke structuren met GIN te indexeren, wat de zoekprestaties verbetert.
• Installatieopdracht:

  CREATE EXTENSION btree_gin;

8. btree_gist

• Functionaliteit: Biedt GiST-indexondersteuning voor B-tree datatypes.
• Toepassing: GiST (‘Generalized Search Tree’) is een flexibel indexmechanisme. Met btree_gist kun je B-tree-indexeerbare data via GiST indexeren, handig voor range- en contains-queries of het creëren van samengestelde indexen.
• Installatieopdracht:

  CREATE EXTENSION btree_gist;

9. cube

• Functionaliteit: Voegt een datatype toe voor het representeren van meer-dimensionale kubussen of punten, samen met bijbehorende operatoren en functies.
• Toepassing: Voor toepassingen die werken met multidimensionale data, zoals eenvoudige geografische gegevens (2D/3D coördinaten), datawarehousing of wetenschappelijke analyses.
• Installatieopdracht:

  CREATE EXTENSION cube;

10. fuzzystrmatch

• Functionaliteit: Biedt functies om de gelijkenis en verschillen tussen strings te bepalen, zoals Levenshtein-afstand, Soundex, Metaphone enzovoorts.
• Toepassing: Ideaal voor fuzzy string matching, spellingscontrole, data cleaning en het opsporen van doublures.
• Installatieopdracht:

  CREATE EXTENSION fuzzystrmatch;

11. plpgsql

• Functionaliteit: Dit is de procedurele programmeertaal van PostgreSQL, waarmee je stored procedures, trigger-functies en anonieme codeblokken kunt schrijven.
• Toepassing: Voor het implementeren van complexe bedrijfslogica, automatisering en dataverwerkingsprocessen aan de serverzijde. plpgsql is meestal standaard geïnstalleerd, maar je kunt via CREATE EXTENSION expliciet bevestigen of opnieuw installeren.
• Installatieopdracht:

  CREATE EXTENSION plpgsql;

12. tablefunc

• Functionaliteit: Biedt functies voor het manipuleren en transformeren van volledige tabellen, waaronder de bekende crosstab-functie.
• Toepassing: Voor het uitvoeren van draaitabelqueries (ook pivot tables genoemd), waarbij unieke waarden uit één kolom als kolommen worden weergegeven; handig voor rapportages en analyses.
• Installatieopdracht:

  CREATE EXTENSION tablefunc;

13. dblink

• Functionaliteit: Maakt het mogelijk om vanuit één PostgreSQL-database verbinding te maken met en queries uit te voeren op een andere PostgreSQL-database.
• Toepassing: Ideaal voor eenvoudige dataquery’s of -migraties tussen verschillende databases, zonder complexe cross-database logica aan applicatiezijde.
• Installatieopdracht:

  CREATE EXTENSION dblink;

14. postgres_fdw

• Functionaliteit: Biedt een Foreign Data Wrapper, zodat tabellen uit een externe PostgreSQL-database toegankelijk zijn alsof ze lokaal zijn.
• Toepassing: Vergemakkelijkt data-integratie tussen databases en federatieve queries, krachtiger en flexibeler dan dblink, met betere ondersteuning voor geoptimaliseerde queries.
• Installatieopdracht:

  CREATE EXTENSION postgres_fdw;

15. pgcrypto

• Functionaliteit: Biedt een reeks encryptie- en decryptiefuncties, waaronder hashing, symmetrische en asymmetrische encryptie.
• Toepassing: Voor het versleutelen van data op databaseniveau, wachtwoord hashing, genereren of verifiëren van digitale handtekeningen en andere securitytoepassingen.
• Installatieopdracht:

  CREATE EXTENSION pgcrypto;

Dit is slechts een deel van de officiële PostgreSQL-extensies die in ServBay beschikbaar zijn. ServBay streeft ernaar om vrijwel alle populaire extensies uit de PostgreSQL-community te integreren en zo aan uiteenlopende ontwikkelbehoeften tegemoet te komen.

PostgreSQL-extensiemodules installeren en gebruiken in ServBay

Het installeren en gebruiken van PostgreSQL-extensies in ServBay is uiterst eenvoudig. Let er wel op dat extensies op het niveau van individuele databases geïnstalleerd worden. Dit houdt in dat je een extensie afzonderlijk moet activeren in elke database waar je deze wil gebruiken.

Vereisten:

• Zorg dat ServBay actief is en de PostgreSQL-databaseservice draait.

Stappenplan:

1. Verbind met de gewenste PostgreSQL-database: Open je terminal-applicatie (zoals Terminal of iTerm2 op macOS) en gebruik het psql commandline-tool om verbinding te maken met de database waarin je de extensie wilt installeren. Standaard is de PostgreSQL-gebruikersnaam en database in ServBay postgres.

   psql -U jouw_gebruikersnaam -d jouw_database

   Bijvoorbeeld, verbinding maken met de standaard postgres database:

   psql -U postgres -d postgres

   Indien er om een wachtwoord wordt gevraagd, voer dan het root/postgres-wachtwoord in dat je in ServBay aan PostgreSQL hebt toegekend.

2. Installeer de extensiemodule: Na een succesvolle verbinding kom je in de psql commandlineomgeving. Voer het CREATE EXTENSION-commando uit om de gewenste extensie te installeren. Vervang module_name door de naam van jouw extensie.

   CREATE EXTENSION module_name;

   Bijvoorbeeld, om de pg_trgm extensie te installeren:

   CREATE EXTENSION pg_trgm;

3. Bevestig de installatie: Na installatie kun je met het \dx-commando de lijst van alle geïnstalleerde extensies in de huidige database tonen en zo zien of jouw extensie succesvol is toegevoegd.

   \dx

Voorbeeld: De pg_trgm extensie activeren

Hieronder volgt een volledig voorbeeld voor het verbinden met de postgres database en het activeren van de pg_trgm extensie via de psql-interface:

# Voer in de terminal het volgende in om met de database te verbinden
psql -U postgres -d postgres

# Na succesvolle verbinding verschijnt de psql-prompt
psql (16.3 (ServBay))
Type "help" for help.

postgres=# -- Bekijk de momenteel geïnstalleerde extensies (meestal enkel plpgsql en pgcrypto als standaard)
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=# -- Installeer de pg_trgm extensie
postgres=# CREATE EXTENSION pg_trgm;
CREATE EXTENSION

postgres=# -- Controleer nogmaals de lijst met geïnstalleerde extensies, pg_trgm is nu toegevoegd
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=# -- Installatie geslaagd, je kunt nu de functies en operatoren van pg_trgm inzetten in deze database

Lijst van beschikbare extensies in PostgreSQL binnen ServBay opvragen

Voorafgaand aan de installatie kan het nuttig zijn om te weten welke extensies standaard beschikbaar zijn in de PostgreSQL-versie van ServBay. Zo ontdek je snel welke tools je extra mogelijkheden geven.

Na verbinding met eender welke PostgreSQL-database kun je de volgende SQL-query uitvoeren voor een volledig overzicht van beschikbare extensies:

SELECT * FROM pg_available_extensions ORDER BY name;

Deze query levert een tabel met de naam, standaardversie, geïnstalleerde versie en een beschrijving van iedere extensie die ondersteund wordt door de geïntegreerde PostgreSQL-versie binnen ServBay.

Een voorbeeldresultaat (de feitelijke lijst kan variëren, afhankelijk van de PostgreSQL-versie van ServBay):

             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)

Let op: bovenstaande lijst dient als voorbeeld! De feitelijke lijst en versienummers kunnen anders zijn, afhankelijk van updates binnen ServBay of wijzigingen in PostgreSQL.

Samenvatting

Met ServBay heb je direct de beschikking over een rijk aanbod aan officiële PostgreSQL-extensies. Dit vereenvoudigt het gebruik van geavanceerde databasediensten in je lokale ontwikkelomgeving enorm. Ontwikkelaars hoeven niets handmatig te compileren of te installeren – enkel een eenvoudig SQL-commando is voldoende om de gewenste module te activeren. Zo kun je je volledig richten op applicatieontwikkeling en je productiviteit verhogen. Dankzij deze extensies bouw je moeiteloos krachtige functies in je project, zoals prestatieanalyse, full-text search en databeveiliging.