Apa itu PostgreSQL Extensions?

Extensions menambahkan fitur baru ke PostgreSQL tanpa harus menulis ulang database engine. Install extension untuk mendapatkan kemampuan tambahan.

Extension Populer

1. pg_trgm (Trigram Matching)

Fuzzy search dan similarity matching. Cocok untuk typo-tolerant search.

CREATE EXTENSION pg_trgm;

-- Index untuk fuzzy search
CREATE INDEX idx_users_name_trgm ON users
USING GIN(name gin_trgm_ops);

-- Pencarian mirip (typo-tolerant)
SELECT name, similarity(name, 'Buddi') AS sim
FROM users
WHERE name % 'Buddi'  -- similarity > threshold
ORDER BY sim DESC;

2. uuid-ossp

CREATE EXTENSION "uuid-ossp";
-- Atau gunakan gen_random_uuid() bawaan PG 13+
SELECT uuid_generate_v4(); -- random UUID
SELECT gen_random_uuid();  -- native alternative

3. PostGIS (Geospatial)

CREATE EXTENSION postgis;

CREATE TABLE stores (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    location GEOGRAPHY(POINT, 4326)
);

-- Cari toko dalam radius 5km
SELECT name, ST_Distance(location, ST_MakePoint(106.8, -6.2)::geography) AS distance
FROM stores
WHERE ST_DWithin(location, ST_MakePoint(106.8, -6.2)::geography, 5000)
ORDER BY distance;

4. pgcrypto

CREATE EXTENSION pgcrypto;
-- Hash password di level database
INSERT INTO users (email, password_hash)
VALUES ('[email protected]', crypt('rahasia', gen_salt('bf')));

Cara Manage Extensions

-- Lihat extensions yang terinstall
SELECT * FROM pg_extension;

-- Install extension
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Hapus extension
DROP EXTENSION pg_trgm;