PostgreSQL / Өргөтгөл (UUID, PostGIS гэх мэт)

Өргөтгөл (UUID, PostGIS гэх мэт)

PostgreSQL-н хамгийн хүчирхэг давуу талуудын нэг бол extension тогтолцоо юм. Extension нь PostgreSQL-н цөм функцлагийг нэмэлт өгөгдлийн төрөл, функц, оператораар өргөтгөдөг. Нэг командаар суулгаж, тэр даруй ашиглаж болно — ямар ч гуравдагч сервис, тусдаа суулгалт шаардахгүй.

Extension суулгах ба удирдах

sql

-- Боломжтой extension-уудын жагсаалт
SELECT name, default_version, comment
FROM pg_available_extensions
ORDER BY name;

-- Extension идэвхжүүлэх
CREATE EXTENSION IF NOT EXISTS extension_нэр;

-- Идэвхжсэн extension-уудыг харах
SELECT extname, extversion
FROM pg_extension;

-- Extension шинэчлэх
ALTER EXTENSION extension_нэр UPDATE;

-- Extension устгах
DROP EXTENSION IF EXISTS extension_нэр;

Extension нь өгөгдлийн сан тус бүрт тусад нь идэвхжүүлдэг. Нэг серверийн нэг өгөгдлийн санд идэвхжүүлсэн нь нөгөө өгөгдлийн санд нөлөөлөхгүй.

uuid-ossp ба pgcrypto — UUID үүсгэх

SERIAL оронд UUID ашиглах нь distributed системд давхардал үүсэхгүй байх баталгаа болдог:

sql

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- UUID үүсгэх янз бүрийн арга
SELECT UUID_GENERATE_V4();          -- uuid-ossp: санамсаргүй UUID
SELECT GEN_RANDOM_UUID();           -- pgcrypto: санамсаргүй UUID (PostgreSQL 13+)

-- UUID-г primary key болгон ашиглах
CREATE TABLE users (
    id         UUID PRIMARY KEY DEFAULT GEN_RANDOM_UUID(),
    username   TEXT NOT NULL UNIQUE,
    email      TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO users (username, email)
VALUES ('bold_mn', 'bold@example.mn');

SELECT id, username FROM users;
-- id: a3f7c2d1-8b4e-4f9a-b2c5-1e7d6f8a9b0c (жишээ)

pgcrypto нь мөн нууц үг хэшлэхэд хэрэгтэй:

sql

-- Нууц үг хэшлэх ба шалгах
INSERT INTO users (username, email, password_hash)
VALUES ('sarnai', 'sarnai@mn', CRYPT('нууцүг123', GEN_SALT('bf', 10)));

-- Нэвтрэх үед шалгах
SELECT username FROM users
WHERE username = 'sarnai'
  AND password_hash = CRYPT('нууцүг123', password_hash);

pg_trgm — fuzzy хайлт

pg_trgm нь үгийн алдаатай (postgresq гэж бичсэн ч postgresql олох) болон хэсэгчилсэн тохирлыг дэмждэг хайлт юм:

sql

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE TABLE products (
    id   SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

INSERT INTO products (name) VALUES
    ('PostgreSQL өгөгдлийн сан'),
    ('MySQL өгөгдлийн сан'),
    ('MongoDB NoSQL'),
    ('Redis cache');

-- GIN индекс тавих
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);

-- Хэсэгчилсэн тохирол хайх
SELECT name, SIMILARITY(name, 'postgres') AS оноо
FROM products
WHERE name % 'postgres'          -- % оператор: тохирол байна уу?
ORDER BY оноо DESC;

-- LIKE-аас хурдан альтернатив
SELECT name FROM products
WHERE name ILIKE '%өгөгдлийн%';  -- Индексийг ашиглана

tablefunc — crosstab (pivot)

tablefunc нь мөрүүдийг багана болгон эргүүлдэг CROSSTAB функц санал болгодог:

sql

CREATE EXTENSION IF NOT EXISTS tablefunc;

-- Жишээ: сар тус бүрийн борлуулалт
CREATE TABLE monthly_sales (
    product TEXT,
    month   TEXT,
    amount  NUMERIC
);

INSERT INTO monthly_sales VALUES
    ('Утас', '2024-01', 5000000), ('Утас', '2024-02', 6200000),
    ('Чихэвч', '2024-01', 800000), ('Чихэвч', '2024-02', 950000);

-- Мөрийг баганад эргүүлэх
SELECT * FROM CROSSTAB(
    'SELECT product, month, amount FROM monthly_sales ORDER BY 1,2',
    'SELECT DISTINCT month FROM monthly_sales ORDER BY 1'
) AS ct (product TEXT, "2024-01" NUMERIC, "2024-02" NUMERIC);

PostGIS — газарзүйн өгөгдөл

PostGIS нь PostgreSQL-д газарзүйн координат, геометр, хайлт нэмдэг хамгийн алдартай extension юм. Supabase дээр ч өгөгөлмжтэй байдаг:

sql

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE locations (
    id       SERIAL PRIMARY KEY,
    name     TEXT NOT NULL,
    position GEOGRAPHY(POINT, 4326)   -- WGS84 координатын систем
);

-- Уланбаатарын алдартай газрууд
INSERT INTO locations (name, position) VALUES
    ('Сүхбаатарын талбай', ST_MAKEPOINT(106.9057, 47.9077)),
    ('Их дэлгүүр',         ST_MAKEPOINT(106.9022, 47.9070)),
    ('Зайсан толгой',      ST_MAKEPOINT(106.9315, 47.8704));

-- Хоёр цэгийн хоорондох зай (метрээр)
SELECT
    a.name AS эхлэл,
    b.name AS очих,
    ROUND(ST_DISTANCE(a.position, b.position)::NUMERIC) AS зай_метр
FROM locations a, locations b
WHERE a.name = 'Сүхбаатарын талбай'
  AND b.name = 'Зайсан толгой';

Дараагийн хичээлд:

Сурсан бүх мэдлэгээ нэгтгэн Node.js application-аас PostgreSQL-тай холбогдож бодит асуулга ажиллуулах аргыг сурна — pg library ашиглан connection pool, prepared statement, transaction гүйцэтгэнэ.

← Өмнөх хичээлBackup ба сэргээлт Дараах хичээл →Node.js + PostgreSQL