PostgreSQL Extensions — Database

Apa itu PostgreSQL Extensions? Extensions menambahkan fitur baru ke PostgreSQL tanpa harus menulis ulang database engine. Install extension untuk mendapatkan…

Extension adalah paket fitur tambahan untuk PostgreSQL: fungsi, tipe data, operator dan index baru, dipasang dengan satu perintah CREATE EXTENSION. Mirip aplikasi di HP. HP-nya tetap sama, tapi setelah kamu pasang aplikasi peta, HP-nya bisa menunjukkan jalan. Ada dua syarat yang juga mirip: aplikasinya harus tersedia di toko (paketnya terpasang di server), dan dipasang di tiap HP sendiri-sendiri (di tiap database).

🧪 Tebakan cepat: di database Kedai Senja yang baru, kamu menjalankan SELECT similarity('Budi', 'Buddi'); untuk mengukur seberapa mirip dua nama. Hasilnya angka, atau error? Tebak dulu, jawabannya ada di bagian "Memasang extension".

Memasang extension

similarity() bukan fungsi bawaan PostgreSQL. Di database yang baru, jawabannya:

ERROR:  function similarity(unknown, unknown) does not exist
LINE 1: SELECT similarity('Budi', 'Buddi');
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Error, karena fungsinya milik extension pg_trgm yang belum dipasang. Itu jawaban tebakannya. Setelah dipasang, query yang sama menjawab 0.5714286.

CREATE EXTENSION IF NOT EXISTS pg_trgm;    -- pasang di database ini

SELECT extname, extversion FROM pg_extension;   -- yang sudah terpasang (di psql: \dx)
SELECT name FROM pg_available_extensions;       -- yang bisa dipasang di server ini

DROP EXTENSION pg_trgm;                    -- lepas lagi

pg_trgm: pencarian yang tahan salah ketik

pg_trgm memecah teks jadi trigram: potongan 3 huruf berurutan. Tiap kata dikecilkan, diberi 2 spasi di depan dan 1 di belakang, lalu dipotong per 3 huruf:

Teks Trigram
Budi " b", " bu", bud, udi, "di "
Buddi " b", " bu", bud, udd, ddi, "di "

Yang sama ada 4, gabungan keduanya ada 7, jadi similarity('Budi', 'Buddi') = 4 / 7 = 0.5714286. Operator % bernilai benar kalau kemiripannya melewati batas pg_trgm.similarity_threshold, yang default-nya 0.3:

SELECT name, similarity(name, 'Buddi') AS sim
FROM customers
WHERE name % 'Buddi'
ORDER BY sim DESC;
-- Budi | 0.5714286   (Andi cuma 0.1, Siti 0)

CREATE INDEX customers_name_trgm ON customers USING GIN (name gin_trgm_ops);

Index GIN dengan gin_trgm_ops membuat %, dan juga LIKE '%kata%', bisa memakai index.

PostGIS: data lokasi

PostGIS menambah tipe dan fungsi geospasial: titik, garis, area, dan jarak di permukaan bumi. Kedai Senja mencari cabang dalam radius 3 km dari pembeli di Monas:

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE branches (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  location geography(Point, 4326)
);

SELECT name, round(ST_Distance(location, ST_MakePoint(106.8272, -6.1754)::geography)) AS meter
FROM branches
WHERE ST_DWithin(location, ST_MakePoint(106.8272, -6.1754)::geography, 3000)
ORDER BY meter;
-- Kedai Senja Sabang  | 954
-- Kedai Senja Menteng | 2339

UUID dan pgcrypto

Sejak PostgreSQL 13, gen_random_uuid() sudah bawaan, tanpa extension. Sebelum itu, UUID dibuat lewat extension pgcrypto (yang dulu menyediakan gen_random_uuid()) atau uuid-ossp (uuid_generate_v4(), dan versi UUID lainnya). Kode lama dan migration lama masih sering memakai uuid_generate_v4(), jadi uuid-ossp masih sering ketemu.

pgcrypto juga menyediakan fungsi kriptografi: hash dan enkripsi.

CREATE EXTENSION IF NOT EXISTS pgcrypto;
SELECT encode(digest('SENJA2026', 'sha256'), 'hex');
-- ffe3542ca0d6ddae0e5bbc13db29fc47d6f61c4def985507cfe57dd8d54f31fa

Untuk password pengguna, hash-nya tetap di aplikasi (Hash::make() di Laravel), supaya password asli gak pernah terkirim di dalam query dan tercatat di log database.

pg_stat_statements: query mana yang lambat

pg_stat_statements mencatat statistik setiap query: berapa kali dijalankan dan berapa lama rata-ratanya. Bedanya dengan extension lain, dia harus dimuat saat server menyala:

-- 1. di postgresql.conf, lalu restart server:
--    shared_preload_libraries = 'pg_stat_statements'
-- 2. di database-nya:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT calls, round(mean_exec_time::numeric, 1) AS ms, query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 3;

Lupa langkah pertama, query-nya ditolak: ERROR: pg_stat_statements must be loaded via "shared_preload_libraries".

Memilih extension

Kebutuhan Extension
fuzzy search, typo pg_trgm
geolokasi, maps postgis
enkripsi, hash pgcrypto
uuid uuid-ossp
statistik query pg_stat_statements
lainnya, misalnya backup harian tidak dikenal, itu bukan tugas extension

⚠️ Jebakan yang sering ketemu

TL;DR: Extension menambah fitur ke PostgreSQL dan dipasang per database dengan CREATE EXTENSION IF NOT EXISTS ...;. Sebelum dipasang, fungsinya gak ada: similarity() menjawab error. pg_trgm mengukur kemiripan teks lewat trigram (Budi dan Buddi mirip 0.5714286), jadi pencarian tahan salah ketik. PostGIS menambah data lokasi (geography, ST_DWithin, ST_MakePoint(bujur, lintang)). pgcrypto untuk hash dan enkripsi, uuid-ossp untuk uuid_generate_v4() (sejak PostgreSQL 13, gen_random_uuid() sudah bawaan). pg_stat_statements mencatat query yang lambat, dan harus dimuat lewat shared_preload_libraries lalu restart.

Yang akan kamu pelajari