پروژه‌ی پایانی: آماده‌سازی دیتابیس کارخانه‌ی فرش برای تولید

از طرح فصل ۳ تا سروری که می‌شود به آن تکیه کرد

در این پروژه دیتابیس کارخانه‌ی فرش را که در فصل ۳ طراحی کردیم، برای سرور واقعی آماده می‌کنیم: سروری با ۸ هسته، ۳۲ گیگابایت RAM و SSD که برنامه‌ی جنگوی ثبت سفارش، داشبورد سالن بافندگی و گزارش‌های مالی به آن وصل‌اند. هر گام را اجرا کنید و نتیجه را با کوئری بررسی کنید.

گام ۱: تنظیمات کلیدی

# /etc/postgresql/17/main/conf.d/tuning.conf
shared_buffers = 8GB               # حدود ۲۵٪ RAM؛ نیاز به restart
effective_cache_size = 24GB        # تخمین cache کل (پستگرس + سیستم‌عامل)؛ فقط راهنمای برنامه‌ریز
work_mem = 32MB                    # برای هر Sort/Hash در هر کوئری، نه برای هر اتصال
maintenance_work_mem = 1GB         # VACUUM، CREATE INDEX، restore
random_page_cost = 1.1             # SSD؛ پیش‌فرض 4 برای دیسک چرخان است
effective_io_concurrency = 200
max_connections = 100              # پشت PgBouncer
max_wal_size = 8GB
checkpoint_completion_target = 0.9
wal_compression = lz4

log_min_duration_statement = 500ms
log_lock_waits = on
log_autovacuum_min_duration = 10s
log_line_prefix = '%m [%p] %q%u@%d '
shared_preload_libraries = 'pg_stat_statements'

idle_in_transaction_session_timeout = 5min
autovacuum_vacuum_cost_limit = 2000

حساب سرانگشتی work_mem: ۱۰۰ اتصال × چند گره‌ی Sort در هر کوئری × 32MB می‌تواند از RAM بیشتر شود. مقدار سراسری را محتاط بگیرید و برای نقش گزارش‌گیر بیشتر کنید: ALTER ROLE bi_reader SET work_mem = '256MB';

گام ۲: نقش‌ها و امنیت

سه نقش گروهی فصل ۷ (owner، rw، ro) با DEFAULT PRIVILEGES؛ برنامه فقط با app_user، migration فقط با migrator. pg_hba فقط hostssl با scram-sha-256 از زیرشبکه‌ی سرور برنامه.

گام ۳: ایندکس‌ها بر اساس کوئری‌های واقعی

CREATE INDEX CONCURRENTLY orders_open_due_idx ON factory.orders (due_date)
  WHERE status IN ('confirmed', 'weaving', 'finishing');
CREATE INDEX CONCURRENTLY qc_defects_gin ON factory.qc_inspections USING gin (defects jsonb_path_ops);
CREATE INDEX CONCURRENTLY customers_name_trgm ON factory.customers
  USING gin (factory.fa_normalize(full_name) gin_trgm_ops);

-- بعد از یک هفته کار واقعی:
SELECT left(query, 70), calls, round(mean_exec_time::numeric, 1) AS mean_ms
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

گام ۴: نگهداری و تاریخچه

trigger updated_at و audit_log روی orders و order_items؛ جدول لاگ حسگرها partition ماهانه‌ی شمسی؛ autovacuum تهاجمی برای جدول موجودی نخ. صف کارهای پس‌زمینه (صدور فاکتور PDF، پیامک ارسال سفارش) با جدول jobs و SKIP LOCKED.

گام ۵: بکاپ و بازیابی

گام ۶: پایش

-- نسبت cache hit (باید بالای ۹۹٪ باشد)
SELECT round(100.0 * sum(blks_hit) / nullif(sum(blks_hit) + sum(blks_read), 0), 2) AS hit_pct
FROM pg_stat_database;

-- سن xid، اتصال‌ها بر اساس وضعیت، slotهای غیرفعال
SELECT max(age(datfrozenxid)) FROM pg_database;
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
SELECT slot_name, active, wal_status FROM pg_replication_slots WHERE NOT active;

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

• shared_buffers بیش از ۴۰٪ RAM معمولاً بهتر نمی‌کند، چون پستگرس به cache سیستم‌عامل هم تکیه دارد و داده دو بار cache می‌شود.
• با shared_buffers چند گیگابایتی، huge_pages = try و تنظیم vm.nr_hugepages در لینوکس سربار مدیریت حافظه را محسوس کم می‌کند.
• فایل‌های conf.d در اوبونتو بعد از postgresql.conf خوانده می‌شوند؛ تنظیمات خودتان را آن‌جا بگذارید تا ارتقای بسته فایل اصلی را بی‌دردسر عوض کند.
• ابزار pg_test_fsync سرعت واقعی fsync دیسک را می‌سنجد؛ روی بعضی سرورهای مجازی ارزان، همین عدد سقف تراکنش در ثانیه‌ی شماست.
• عدد بالای num_requested در نمای pg_stat_checkpointer (نسخه‌ی 17؛ در 16 ستون checkpoints_req در pg_stat_bgwriter) یعنی max_wal_size کوچک است و checkpointها زودتر از موعد اجرا می‌شوند.