توسعه نرمافزار
راهنمای کارایی پستگرسکیوال
بیش از آنچه دوست دارم اعتراف کنم، ساعتها به کوئریهای کند پستگرس در تولید خیره ماندهام. در ابتدا غریزهام این بود که مسئله را با سختافزار حل کنم — اینستنس بزرگتر، رم بیشتر، دیسک سریعتر. گاهی کمک میکرد، اما بردهای واقعی تقریباً همیشه از فهمیدن آنچه پستگرس واقعاً انجام میداد میآمدند. این راهنما همه چیزی است که آرزو میکردم قبل از اولین مانور اضطراری تولیدم میدانستم.
همیشه با EXPLAIN ANALYZE شروع کنید
قبل از دست زدن به ایندکس یا بازنویسی کوئری، EXPLAIN (ANALYZE, BUFFERS) را اجرا کنید. این پلن اجراییای که پستگرس انتخاب کرده، زمان هر مرحله و میزان دادهای که از دیسک در برابر کش خوانده را نشانتان میدهد.
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2025-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 20;دنبال پویشهای ترتیبی روی جدولهای بزرگ، حلقههای تودرتو با تعداد ردیف بالا و عملیات مرتبسازی که به دیسک سرریز میکنند بگردید. اینها مظنونان شما هستند.
اگر EXPLAIN ANALYZE نگرفتهاید، دارید حدس میزنید. پستگرس دقیقاً میگوید زمان کجا میرود — به آن گوش دهید.
ایندکسها: اولین اهرم واقعی
ایندکسها مجانی نیستند — خواندن را سریع و نوشتن را کند میکنند — اما نبود ایندکس روی یک ستون فیلترشده یا joinشده رایجترین علت کوئری کندی است که میبینم.
کِی ایندکس اضافه کنیم
- ستونهایی در عبارتهای
WHEREکه بخش قابلتوجهی از ردیفها را فیلتر میکنند. - ستونهای کلید خارجی که در
JOINها استفاده میشوند. - ستونهایی در
ORDER BYوقتی نمیتوانید هزینه یک مرحله مرتبسازی را بدهید.
ایندکسهای مرکب و ترتیب ستون
یک ایندکس مرکب روی (status, created_at) به کوئریهایی که با status فیلتر و با تاریخ مرتب میکنند کمک میکند. ترتیب ستون مهم است: انتخابیترین ستون را اول بگذارید، یا با الگوی فیلتر-سپس-مرتبسازی کوئریتان هماهنگ کنید.
CREATE INDEX idx_orders_status_created
ON orders (status, created_at DESC)
WHERE status = 'pending';آن ایندکس جزئی — با عبارت WHERE — وقتی فقط سفارشهای pending را کوئری میکنید کوچکتر و سریعتر است.
الگوهای کوئری که آسیب میزنند
SELECT *
گرفتن همه ستونها پستگرس را مجبور میکند داده بیشتری از دیسک بخواند و بیشتری روی سیم بفرستد. فقط آنچه لازم دارید را انتخاب کنید، بهخصوص روی جدولهای پهن.
توابع روی ستونهای ایندکسشده
WHERE DATE(created_at) = '2025-06-01' استفاده از ایندکس را مانع میشود چون ستون را میپیچید. بهجایش از یک بازه استفاده کنید:
WHERE created_at >= '2025-06-01'
AND created_at < '2025-06-02'N+1 در سطح پایگاه داده
حتی با eager loading در ORM، برخی کوئریهای گزارشگیری باز هم در حلقه به پایگاه داده میزنند. جستوجوهایتان را دستهای کنید یا بهصورت یک JOIN تنها بازنویسی کنید.
اتصالسازی استخری و پیکربندی
پستگرس برای هر اتصال یک پردازه میسازد. زیر بار، سربار خود اتصالها میتواند لهتان کند. برای اپلیکیشنهای وب با اتصالهای کوتاهعمر فراوان از یک استخرکننده مثل PgBouncer در حالت تراکنشی استفاده کنید.
چند تنظیم که ارزش دانستن دارند (با احتیاط تنظیم کنید، بعد اندازه بگیرید):
| تنظیم | چه میکند | قاعده سرانگشتی |
|---|---|---|
shared_buffers | کش خود پستگرس | حدود ۲۵٪ رم روی سرور اختصاصی DB |
work_mem | حافظه هر عملیات مرتبسازی/هش | اگر مرتبسازی به دیسک سرریز میکند بالا ببرید |
effective_cache_size | راهنمایی به پلنر درباره کش سیستمعامل | حدود ۵۰ تا ۷۵٪ کل رم |
random_page_cost | فرض SSD در برابر HDD پلنر | روی SSD پایینتر (مثلاً ۱.۱) |
مانیتورینگ در تولید
نمیتوانید چیزی را که اندازه نمیگیرید بهینه کنید. pg_stat_statements را فعال کنید تا فراوانی و زمان کل کوئریها را ردیابی کنید. ابزارهایی مثل pgHero یا داشبوردهای سفارشی روی pg_stat_user_tables نفخ، ایندکسهای بلااستفاده و نسبتهای اصابت کش را نشان میدهند.
مراقب اینها باشید:
- نسبت اصابت کش زیر حدود ۹۹٪ روی یک سیستم گرمشده — شاید به رم بیشتر یا ایندکسهای بهتر نیاز دارید.
- جدولهایی با تعداد بالای پویش ترتیبی نسبت به پویشهای ایندکس.
- کوئریهای طولانیمدت که بقیه را مسدود میکنند —
pg_stat_activityرا چک کنید.
اشتباهات رایج
- ایندکسگذاری هر ستون. ایندکسهای بلااستفاده فضا هدر میدهند و نوشتن را کند میکنند. با
pg_stat_user_indexesبازبینی کنید. - هرگز اجرا نکردن VACUUM. تاپلهای مرده انباشته میشوند و جدولها را باد میکنند. autovacuum معمولاً این را مدیریت میکند، اما مراقب جدولهایی باشید که از قلم میاندازد.
- بهینهسازی قبل از اندازهگیری. کوئری کندی که فکر میکنید کند است شاید همانی نباشد که ۸۰٪ زمان DB شما را میخورد.
بهترین شیوهها
- همیشه قبل و بعد از یک تغییر EXPLAIN ANALYZE بگیرید.
- ایندکسها را بر اساس الگوهای واقعی کوئری از لاگها اضافه کنید، نه حدس.
- هرجا با الگوهای دسترسی هماهنگاند از ایندکسهای جزئی و مرکب استفاده کنید.
- اتصالها را استخری کنید؛ بدون استخر برای هر درخواست یک اتصال جدید باز نکنید.
- لاگ کوئریهای کند را هفتگی در تولید بازبینی کنید — الگوها سریع پدیدار میشوند.
جمعبندی
کارایی پستگرسکیوال یک مهارت است، نه قرعهکشی. با EXPLAIN ANALYZE اندازه بگیرید، عمدی ایندکس بزنید، از ضدالگوهای کوئری بپرهیزید، اتصالهایتان را استخری کنید و آنچه واقعاً در تولید اجرا میشود را مانیتور کنید. پایگاه داده اگر یاد بگیرید گوش دهید، میگوید چه چیزی غلط است. همین امروز روی کندترین کوئریتان EXPLAIN بگیرید — پاسخ احتمالاً همین حالا در پلن است.