اکستنشن‌های مهم و partitioning اعلانی

پستگرس را با اکستنشن بزرگ کنید، نه با سرویس جدید

بخش بزرگی از قدرت پستگرس در اکستنشن‌هاست: نوع داده، ایندکس، تابع و حتی زبان جدید که مثل بخشی از هسته رفتار می‌کنند. بسیاری از آن‌ها همراه بسته‌ی postgresql-contrib (در نسخه‌های جدید PGDG داخل خود بسته‌ی اصلی) نصب‌اند و فقط باید در هر دیتابیس فعال شوند.

SELECT name, default_version, installed_version
FROM pg_available_extensions WHERE name IN ('pg_trgm', 'unaccent', 'postgis', 'vector');

CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public;
\dx

partitioning اعلانی

جدول لاگ حسگرهای دستگاه‌ها سالی چند صد میلیون ردیف می‌گیرد. اگر آن را بر اساس زمان به پارتیشن‌های ماهانه بشکنیم، کوئری‌های «این ماه» فقط یک پارتیشن را می‌خوانند (partition pruning) و پاک کردن داده‌ی یک سال پیش یک DROP لحظه‌ای است، نه DELETE میلیونی که bloat بسازد. مرز پارتیشن‌ها را می‌توان با ماه‌های شمسی گذاشت:

CREATE TABLE factory.loom_sensor_log (
  loom_id     int NOT NULL,
  recorded_at timestamptz NOT NULL,
  rpm         smallint,
  temp_c      numeric(4,1),
  PRIMARY KEY (loom_id, recorded_at)
) PARTITION BY RANGE (recorded_at);

-- فروردین و اردیبهشت ۱۴۰۵
CREATE TABLE factory.loom_sensor_log_1405_01 PARTITION OF factory.loom_sensor_log
  FOR VALUES FROM ('2026-03-21 00:00+03:30') TO ('2026-04-21 00:00+03:30');
CREATE TABLE factory.loom_sensor_log_1405_02 PARTITION OF factory.loom_sensor_log
  FOR VALUES FROM ('2026-04-21 00:00+03:30') TO ('2026-05-22 00:00+03:30');

CREATE INDEX ON factory.loom_sensor_log USING brin (recorded_at);

-- بایگانی یک ماه: جدا کردن بدون قفل سنگین (نسخه‌ی 14)
ALTER TABLE factory.loom_sensor_log DETACH PARTITION factory.loom_sensor_log_1405_01 CONCURRENTLY;

ایندکسی که روی والد بسازید خودکار روی همه‌ی پارتیشن‌ها، از جمله پارتیشن‌های آینده، ساخته می‌شود.

نکته‌هایی که کمتر کسی می‌داند

• کلید اصلی و UNIQUE روی جدول partition‌شده باید ستون پارتیشن را شامل شود؛ برای همین PRIMARY KEY بالا (loom_id, recorded_at) است، نه یک id تنها.
• پارتیشن DEFAULT (PARTITION OF ... DEFAULT) تله دارد: ساختن هر پارتیشن جدید باید کل آن را اسکن کند تا ردیفی از بازه‌ی جدید در آن نباشد، و وجودش DETACH ... CONCURRENTLY را غیرممکن می‌کند. به‌جایش پارتیشن‌های آینده را زودتر بسازید.
• pruning فقط وقتی کار می‌کند که شرط مستقیم روی ستون پارتیشن باشد؛ WHERE recorded_at::date = '2026-04-01' همه‌ی پارتیشن‌ها را می‌خواند.
• زیر چند ده میلیون ردیف، partitioning معمولاً فقط پیچیدگی اضافه می‌کند؛ ایندکس مناسب کافی است. برای ساخت خودکار پارتیشن‌های آینده از اکستنشن pg_partman کمک بگیرید.
• CREATE EXTENSION در 13 به بعد برای اکستنشن‌های «trusted» (مثل pg_trgm و unaccent) به superuser نیاز ندارد؛ مالک دیتابیس با مجوز CREATE کافی است.