PostgreSQL Extensions

Popular Extensions

CREATE EXTENSION pg_trgm;

CREATE EXTENSION postgis;

CREATE EXTENSION pgcrypto;

CREATE EXTENSION hstore;

🔹 Managing Extensions

Install, view, and remove extensions to customize database capabilities. Extension management is straightforward with SQL commands. Each extension adds specific functionality that integrates seamlessly with PostgreSQL's core features.

-- Create/install extension
CREATE EXTENSION pg_trgm;

-- Create in specific schema
CREATE EXTENSION pg_trgm SCHEMA extensions;

-- List all available extensions
SELECT * FROM pg_available_extensions;

-- List installed extensions
\dx

-- View extension details
\dx+ pg_trgm

-- Remove extension
DROP EXTENSION pg_trgm;

 Name    | Version | Schema | Description
---------+---------+--------+------------------
 pg_trgm | 1.6     | public | text similarity

🔹 pg_trgm - Text Search

The pg_trgm extension enables fuzzy text matching and similarity searches. It's perfect for autocomplete, typo-tolerant search, and finding similar strings. Trigram matching works by breaking text into three-character sequences for comparison.

-- Install extension
CREATE EXTENSION pg_trgm;

-- Find similar strings
SELECT similarity('PostgreSQL', 'Postgres') AS score;

-- Fuzzy search
SELECT * FROM products
WHERE name % 'laptp';  -- Finds "laptop"

-- Create trigram index for fast search
CREATE INDEX idx_products_name_trgm 
ON products USING gin(name gin_trgm_ops);

-- Search with threshold
SELECT * FROM products
WHERE similarity(name, 'phone') > 0.3;

 score
-------
 0.75

🔹 PostGIS - Geographic Data

PostGIS transforms PostgreSQL into a spatial database for geographic information systems. It supports geometry types, spatial indexes, and geographic calculations. Essential for mapping applications, location services, and spatial analysis.

-- Install PostGIS
CREATE EXTENSION postgis;

-- Create table with geography column
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    coordinates GEOGRAPHY(POINT, 4326)
);

-- Insert location data
INSERT INTO locations (name, coordinates)
VALUES ('New York', ST_GeogFromText('POINT(-74.006 40.7128)'));

-- Find distance between points
SELECT ST_Distance(
    ST_GeogFromText('POINT(-74.006 40.7128)'),
    ST_GeogFromText('POINT(-118.2437 34.0522)')
) / 1000 AS distance_km;

 distance_km
-------------
 3936.45

🔹 pgcrypto - Encryption

The pgcrypto extension provides cryptographic functions for data security. Use it to hash passwords, encrypt sensitive data, and generate secure random values. Essential for applications handling confidential information requiring strong encryption.

-- Install pgcrypto
CREATE EXTENSION pgcrypto;

-- Hash password
SELECT crypt('mypassword', gen_salt('bf'));

-- Verify password
SELECT crypt('mypassword', stored_hash) = stored_hash;

-- Generate UUID
SELECT gen_random_uuid();

-- Encrypt data
SELECT pgp_sym_encrypt('sensitive data', 'encryption_key');

-- Decrypt data
SELECT pgp_sym_decrypt(encrypted_column, 'encryption_key')
FROM secure_table;

 gen_random_uuid
----------------------------------
 a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

🔹 hstore - Key-Value Storage

The hstore extension adds key-value pair storage within PostgreSQL columns. It's ideal for flexible schemas, storing attributes that vary by row, or semi-structured data. Hstore provides efficient storage and querying of dynamic properties.

-- Install hstore
CREATE EXTENSION hstore;

-- Create table with hstore column
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    attributes hstore
);

-- Insert data with key-value pairs
INSERT INTO products (name, attributes)
VALUES ('Laptop', 'color=>silver, ram=>16GB, ssd=>512GB');

-- Query hstore data
SELECT name, attributes->'color' AS color
FROM products;

-- Search in hstore
SELECT * FROM products
WHERE attributes @> 'ram=>16GB';

 name   | color
--------+--------
 Laptop | silver

🔹 uuid-ossp - UUID Generation

The uuid-ossp extension generates universally unique identifiers for primary keys. UUIDs provide globally unique values without coordination between databases. Perfect for distributed systems, replication, and avoiding ID conflicts.

-- Install uuid-ossp
CREATE EXTENSION "uuid-ossp";

-- Generate UUID v4 (random)
SELECT uuid_generate_v4();

-- Create table with UUID primary key
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    username VARCHAR(50),
    email VARCHAR(100)
);

-- Insert with auto-generated UUID
INSERT INTO users (username, email)
VALUES ('john_doe', '[email protected]');

 uuid_generate_v4
----------------------------------
 f47ac10b-58cc-4372-a567-0e02b2c3d479

🔹 pg_stat_statements - Query Tracking

Track query execution statistics for performance analysis and optimization. This extension records metrics for all executed queries including execution time, call frequency, and resource usage. Essential for identifying slow queries and optimization opportunities.

-- Install extension
CREATE EXTENSION pg_stat_statements;

-- View query statistics
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Find slow queries
SELECT query, mean_exec_time
FROM pg_stat_statements
WHERE mean_exec_time > 1000  -- Over 1 second
ORDER BY mean_exec_time DESC;

-- Reset statistics
SELECT pg_stat_statements_reset();

 query              | calls | mean_exec_time
--------------------+-------+----------------
 SELECT * FROM...  | 1500  | 245.32