扩展参考

扩展管理

-- Install an extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- List installed extensions
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE installed_version IS NOT NULL;

-- Update an extension
ALTER EXTENSION pg_stat_statements UPDATE;

-- Remove an extension
DROP EXTENSION IF EXISTS pg_trgm;

-- Extensions available (not yet installed)
SELECT name FROM pg_available_extensions
WHERE installed_version IS NULL
ORDER BY name;

pg_stat_statements

跟踪所有 SQL 语句的执行统计信息，用于性能分析。

-- Enable in postgresql.conf:
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.track = all

CREATE EXTENSION pg_stat_statements;

-- Top 10 slowest queries
SELECT
  query,
  calls,
  total_exec_time / calls AS avg_ms,
  rows / calls AS avg_rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Reset statistics
SELECT pg_stat_statements_reset();

uuid-ossp & pgcrypto

-- uuid-ossp: generate UUIDs
CREATE EXTENSION "uuid-ossp";
SELECT uuid_generate_v4();   -- random UUID
SELECT uuid_generate_v1();   -- time-based UUID

-- Use as default column value
CREATE TABLE sessions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id INT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- pgcrypto: hashing and encryption
CREATE EXTENSION pgcrypto;

-- Password hashing (bcrypt)
SELECT crypt('mypassword', gen_salt('bf', 10));
SELECT crypt('mypassword', stored_hash) = stored_hash AS valid
FROM users WHERE id = 1;

-- Random bytes / UUID via pgcrypto
SELECT encode(gen_random_bytes(16), 'hex');
SELECT gen_random_uuid();

pg_trgm

基于三元组的相似度搜索——非常适合模糊匹配和 LIKE 加速。

CREATE EXTENSION pg_trgm;

-- Similarity score (0–1)
SELECT similarity('postgres', 'postgresql');

-- GIN index for LIKE / ILIKE
CREATE INDEX idx_products_name_trgm
  ON products USING GIN (name gin_trgm_ops);

-- Fast LIKE query using the index
SELECT * FROM products WHERE name ILIKE '%postgr%';

-- Find similar strings
SELECT name, similarity(name, 'posgres') AS sim
FROM products
WHERE name % 'posgres'       -- % operator: similarity > threshold
ORDER BY sim DESC
LIMIT 5;

-- Adjust similarity threshold (default 0.3)
SET pg_trgm.similarity_threshold = 0.4;

PostGIS 与 TimescaleDB 要点

-- PostGIS: geographic/spatial types
CREATE EXTENSION postgis;

CREATE TABLE locations (
  id    SERIAL PRIMARY KEY,
  name  TEXT,
  geom  GEOMETRY(Point, 4326)
);
INSERT INTO locations (name, geom)
  VALUES ('Office', ST_SetSRID(ST_MakePoint(116.4, 39.9), 4326));

-- Distance query (meters)
SELECT name,
  ST_DistanceSphere(geom, ST_MakePoint(116.4, 39.9)::geography) AS meters
FROM locations
ORDER BY meters;

-- GiST spatial index
CREATE INDEX idx_locations_geom ON locations USING GIST (geom);

-- TimescaleDB: time-series extension
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Convert regular table to hypertable
SELECT create_hypertable('metrics', 'time');

-- Continuous aggregate (materialized view refreshed automatically)
CREATE MATERIALIZED VIEW metrics_hourly
WITH (timescaledb.continuous) AS
  SELECT time_bucket('1 hour', time) AS bucket,
         avg(value) AS avg_val
  FROM metrics GROUP BY bucket;