‏راهنمای بقای پستگرس برای استارتاپ‌ها

بلانژه می‌گه این متن اولش یه سند داخلی برای مهندس‌های Hatchet بوده: خلاصهٔ دو سال جنگیدن با پستگرس، چون مستندات رسمی موقع بحران انقدر جامع و مفصله که سخت می‌شه بهش تکیه کرد. فرضش اینه که خواننده SQL بلده و تقریباً می‌دونه ایندکس چیه. یه تذکر هم می‌ده: خیلی از این بهینه‌سازی‌ها با ORM شدنی نیست مگه بتونید از لایهٔ انتزاع رد شید و SQL خام بنویسید.

تو بخش پایه، قاعده‌های اسکیمایش ساده‌ست: همیشه کلید اصلی، ستون‌های identity یا UUID داخلی، همیشه timestamptz و احتیاط با کلید خارجی روی جدول‌های پرحجم. مدل ذهنی‌اش برای خوندن هم دوحالته‌ست: یا پستگرس با ایندکس سریع به ردیف می‌رسه، یا کل جدول رو seq scan می‌کنه. اولین کوئری کند معمولاً همون کوئری لیستیه، و درمانش ایندکس مرکبه — با این قاعده که ستون‌های ORDER BY آخر ایندکس بیان و با ترتیب کوئری هم‌راستا باشن.

سمت نوشتن، دو اصل حاکمه: تراکنش‌ها کوتاه بمونن و فقط ردیف‌هایی که لازمه قفل شن. نویسنده تأکید می‌کنه ساخت ایندکس روی جدول بزرگ حتماً باید با CREATE INDEX CONCURRENTLY انجام شه، وگرنه کل درج‌ها و به‌روزرسانی‌ها قفل می‌شن. مهاجرت‌ها هم بهتره افزایشی باشن و داخل تراکنش اجرا شن؛ هر چیزی که ALTER TABLE صدا می‌زنه یه نگاه دوم می‌خواد. کانکشن‌ها هم گرون‌ان، پس pooler بیرونی مثل pgbouncer یا دست‌کم یه pool درون‌حافظه‌ای لازمه.

بخش میانی دربارهٔ برنامه‌ریز کوئریه؛ به تعبیر نویسنده نشت‌کننده‌ترین انتزاع پستگرس که فقط بر اساس آمار جدول‌ها تصمیم می‌گیره، برای همین ANALYZE نامرتب یکی از دلایل رایج رفتار عجیب کوئریه. ابزار عیب‌یابی هم EXPLAIN ANALYZE ست. برای نوشتن حجم بالا، دسته‌ای‌کردن کوئری‌ها به گفتهٔ او حدود ده برابر توان عبوری رو بالا برده. و اگه autovacuum عقب بمونه، اول bloat می‌یاد و در بدترین حالت transaction id wraparound که یعنی داون‌تایم طولانی.

سه قابلیت پیشرفته هم معرفی می‌شن: FOR UPDATE SKIP LOCKED که پایهٔ صف کارِ خود Hatchet روی پستگرسه، پارتیشن‌بندی که هر پارتیشن رو مستقل autovacuum می‌کنه و حذف دادهٔ قدیمی رو به drop یه پارتیشن تقلیل می‌ده، و برای جابه‌جایی جدول‌های خیلی بزرگ، ترکیب تریگر و بک‌فیل دسته‌ای بیرون از تراکنش با تکیه بر قید یکتایی کلید اصلی تا نوشتن تکراری پیش نیاد.

نکات کلیدی:

• ستون‌های ORDER BY رو آخر ایندکس مرکب و هم‌راستا با کوئری بذارید
• ایندکس روی جدول بزرگ فقط با CREATE INDEX CONCURRENTLY
• تراکنش کوتاه، قفل کم، و کانکشن بلندعمر پشت یه pooler
• کوئری کند رو با EXPLAIN ANALYZE و آمار به‌روز جدول عیب‌یابی کنید
• autovacuum رو قبل از bloat تنظیم کنید، نه بعدش