SQL — اسنیپت‌های منتخب

یه کارگاه PostgreSQL از زبون کسی که واقعاً باهاش کار کرده: هر کوئری از یه نشونه‌ی واقعی شروع میشه — گزارش خالی، جوین کند، ایندکسی که هیچ کاری نمی‌کنه — و خودِ کوئری دقیق رو همراه با خروجی psql و توضیحش نشونت میده. پر از دام‌هایی که حلشون کرده (NOT IN/NULL، non-sargable، OFFSET عمیق) و همون کوئری‌های تشخیصی روی کاتالوگ‌های سیستمی که DBAها واقعاً کنار دستشون نگه می‌دارن.

19 اسنیپت منتخب

• →دامِ قاب پیش‌فرض توی FIRST_VALUE / LAST_VALUEاگه قاب پیش‌فرض (تا CURRENT ROW) رو عوض نکنی، LAST_VALUE همون ردیف جاری رو برمی‌گردونه. راه‌حلش اینه: قاب رو تا UNBOUNDED FOLLOWING گسترش بدی.
• →تشخیص حلقه توی گراف: بند CYCLEیه حلقه توی داده‌ها (A والد B، B والد A) باعث میشه CTE بازگشتی بی‌پایان بچرخه. بند CYCLE (توی PostgreSQL 14 به بعد) بازگشت رو متوقف می‌کنه و ردیف‌های مشکل‌دار رو علامت می‌زنه.
• →ادغام بازه‌های تاریخیِ هم‌پوشاندوره‌های هم‌پوشان رو به بازه‌های پیوسته تبدیل می‌کنی: هر وقت شروع یه دوره از بیشینهٔ پایان‌های قبلی جلوتر بزنه یه گروه جدید شناسایی میشه، بعد بر اساس گروه تجمیع می‌کنی.
• →ایندکس‌گذاری روی jsonb: GIN و عملگر containment یعنی @>یه ایندکس GIN از نوع jsonb_path_ops کوئری‌های containment (@>) رو روی حجم بالای سندها به‌شدت سریع می‌کنه. استفادهٔ واقعیش رو با EXPLAIN چک کن.
• →پیدا کردن کلیدهای خارجیِ بدون ایندکسPostgreSQL برای FKها خودکار ایندکس نمی‌سازه: بعدش هر DELETE روی جدول والد یه seq scan روی فرزند راه می‌ندازه. این کوئریِ کاتالوگ، FKهایی رو لیست می‌کنه که ستون اولشون تو هیچ ایندکسی نیست.
• →ضدالگو: NOT IN در مواجهه با NULLکافیه یه NULL تو زیرکوئری باشه تا NOT IN بی‌سروصدا صفر ردیف برگردونه (منطق سه‌ارزشیِ SQL). NOT EXISTS در برابر NULL امنه و معمولاً پلن بهتری هم می‌گیره.
• →ضدالگو: تابع روی ستون ایندکس‌شده (غیرِ sargable)تا DATE_TRUNC یا EXTRACT رو روی ستون بذاری، دیگه نمی‌تونی از ایندکس استفاده کنی. اگه فیلتر رو به‌صورت بازهٔ مقادیر بازنویسی کنی sargable میشه و ایندکس B-tree دوباره به‌کار میاد.
• →ضدالگو: OFFSETِ عمیق — برو سراغ keysetOFFSET 20000 تو هر صفحه ۲۰ هزار ردیف رو می‌خونه و دور می‌ریزه. صفحه‌بندیِ keyset (یا seek) از آخرین عنصری که دیدی ادامه میده: تو هر عمقی هزینه‌ش ثابته.
• →آخرین ردیفِ هر کلید تو یه گذر با DISTINCT ONمخصوص PostgreSQLه: DISTINCT ON اولین ردیفِ هر گروه رو طبق ORDER BY نگه می‌داره — با ایندکس مناسب، اغلب از ROW_NUMBER سریع‌تره.
• →صف کارِ هم‌روند: FOR UPDATE SKIP LOCKEDهر worker با پریدن از رو ردیف‌هایی که بقیه قفل کردن، یه دسته job رزرو می‌کنه: یه صف کاریِ قابل‌اتکا تو SQLِ خالص، بدون هیچ brokerِ بیرونی.
• →قیف تبدیل: نرخ هر مرحله تو یک کوئریبا FILTER کاربرهای یکتا رو تو هر مرحله می‌شمره، بعد با LATERAL VALUES قیف رو به ردیف باز می‌کنه تا هم نرخ کل و هم نرخ مرحله‌به‌مرحله در بیاد.
• →توالی‌های نزدیک به سرریز (overflow از نوع int4)اندازه می‌گیره که هر sequence چند درصد ظرفیتش نسبت به max_value مصرف شده: سرریز یه sequence از نوع int4 یه‌شبه همهٔ درج‌ها رو متوقف می‌کنه.
• →قیدهای NOT VALID و triggerهای غیرفعال: نگهبان‌های خوابیدهفهرست می‌کنه قیدهای CHECK/FK رو که با NOT VALID ساخته شدن (و هیچ‌وقت دوباره اعتبارسنجی نشدن) و triggerهای غیرفعال رو: قواعد کسب‌وکاری که دیتابیس دیگه اعمالشون نمی‌کنه.
• →آبشار MRR: حرکت‌های new، expansion، contraction و churn تو یک گزارشدرآمد تکرارشونده رو ماه‌به‌ماه به چهار حرکت می‌شکنه، از طریق یه FULL JOIN بین MRR هر مشتری و ماه قبلش — گزارش استاندارد هر SaaS، تو SQL خالص.
• →امتیازدهی کامل RFM: تازگی، فراوانی و مبلغ تو پنجک‌هایه زنجیره با دو CTE: اول سه محور RFM رو برای هر مشتری تجمیع می‌کنه، با NTILE به پنجک‌ها امتیاز میده، بعد یه بخش‌بندی خوانا (قهرمان، VIP که باید دوباره فعال بشه، ازدست‌رفته) آمادهٔ CRM می‌سازه.
• →کشف تقلب: تراکنش‌های چندکشوریِ نزدیک‌به‌هم روی یک کارتیک پنجرهٔ LAG به ازای هر کارت می‌سازه تا هر تراکنش رو با قبلی مقایسه کنه: عوض‌شدن کشور در کمتر از یک ساعت از نظر فیزیکی ممکن نیست — همون نشونهٔ کلاسیک کارت لورفته.
• →کشف الگوی N+1 در pg_stat_statementsکوئری‌هایی که زمان واحدشون ناچیزه ولی صدها هزار بار صدا زده می‌شن، لو می‌دن که یک ORM افتاده تو حلقهٔ N+1: تک‌تک نامرئی‌ان، ولی روی هم غالب.
• →همبستگی فیزیکی ستون‌ها: نامزدهای BRIN و CLUSTERpg_stats.correlation رو برای ستون‌های ایندکس‌شده می‌خونه: نزدیک ۱ که باشه، یک BRIN ریز کافیه؛ نزدیک ۰ روی ستونی که مدام مبنای مرتب‌سازیه، یک CLUSTER جدول رو بازچینی می‌کنه و I/O رو چند برابر کم می‌کنه.
• →بادکردگی (bloat) واقعی ایندکس‌های B-tree با pgstatindexتراکم صفحه‌های برگ هر ایندکس رو دقیق (نه تخمینی) اندازه می‌گیره: زیر ۵۰٪ تراکم یعنی ایندکس الکی دو برابر شده و حقشه یک REINDEX CONCURRENTLY بخوره.