جدیدترین رکورد (از بسیاری موارد) را با PostgreSQL انتخاب کنید

داده های سری زمانی تقریباً در هر برنامه امروز همه گیر است. یکی از متداول ترین برنامه های نمایش داده شده در داده های سری زمانی ، یافتن جدیدترین مقدار برای یک دستگاه یا مورد خاص است.

در این پست وبلاگ ، ما پنج روش برای دسترسی به جدیدترین ارزش در PostgreSQL را کشف خواهیم کرد. هر گزینه مزایا و معایب خود را دارد که در مورد آنها بحث خواهیم کرد.

توجه: در طول این پست ، اشاراتی به "دستگاه" یا "کامیون" به سادگی متقاضیان هر آنچه برنامه شما در حال ذخیره داده های سری زمانی است ، خواه سنسور کیفیت هوا ، هواپیما ، ماشین ، بازدید از وب سایت یا چیز دیگری باشد. همانطور که می خوانید ، به جای داده های خاص که ما به عنوان نمونه از آن استفاده می کنیم ، روی مفهوم هر گزینه تمرکز کنید.

مشکل

دانستن نحوه پرس و جو از جدیدترین زمان و داده ها برای دستگاه در مجموعه داده های بزرگ و سری زمانی ، اغلب برای بسیاری از توسعه دهندگان برنامه یک چالش است. ما داده ها را مطالعه می کنیم ، طرح مناسب را تعیین می کنیم و شاخص هایی را ایجاد می کنیم که باید به سرعت نمایش داده شود.

هنگامی که نمایش داده ها به همان اندازه که انتظار داریم نیست ، به راحتی قابل سردرگمی است زیرا قرار است شاخص های PostgreSQL به شما در بازگشت سریع پرس و جوهای شما کمک کنند - درست است؟

در بیشتر موارد ، پاسخ به آن کاملاً "درست" است. با استفاده از شاخص مناسب ، PostgreSQL به طور معمول در بازیابی داده ها برای پرس و جو شما بسیار کارآمد است. همیشه تفاوت های ظریف وجود دارد که ما در این پست وقت لازم را نداریم (شاخص های زیادی را ایجاد نکنید ، مطمئن شوید که آمار به روز نگه داشته می شود و غیره) ، اما به طور کلی ، شاخص مناسب به طرز چشمگیری خواهد بودعملکرد پرس و جو یک پایگاه داده SQL را بهبود بخشید ، PostgreSQL گنجانده شده است.

قبل از اینکه به نحوه یافتن کارآمد سوابق خاص در یک پایگاه داده سری بزرگ با استفاده از ایندکس ها بپردازیم ، می خواهم مطمئن شوم که ما در مورد همان چیز صحبت می کنیم. برای مدت زمان این پست ، تمام منابع به شاخص ها به طور خاص به معنای شاخص B-Tree هستند. اینها رایج ترین شاخصی است که توسط تمام بانکهای اطلاعاتی اصلی OLTP پشتیبانی می شود و در یافتن ردیف های خاص داده ها در جداول بزرگ و کوچک بسیار مناسب هستند. PostgreSQL در واقع از بسیاری از انواع مختلف شاخص پشتیبانی می کند که می تواند به انواع مختلف نمایش داده ها و داده ها (از جمله داده های محور Timestamp) کمک کند ، اما از اینجا به بعد ، ما فقط در مورد شاخص های درخت B صحبت می کنیم.

تأثیر فهرست ها

در کانال انجمن TimescaleDB Slack ما و در سایر انجمن های توسعه دهنده مانند StackOverflow (مثال)، توسعه دهندگان اغلب تعجب می کنند که چرا جستجو برای آخرین مقدار در PostgreSQL کند است، حتی زمانی که به نظر می رسد نمایه درستی برای «سریع» کردن عبارت وجود دارد؟

پاسخ به آن در نحوه عملکرد برنامه ریز پرس و جو PostgreSQL نهفته است. همانطور که در زیر به آن خواهیم پرداخت، همیشه از شاخص دقیقاً همانطور که انتظار دارید استفاده نمی کند. برای اینکه نشان دهیم PostgreSQL چگونه ممکن است از یک شاخص در جدول سری های زمانی بزرگ استفاده کند، اجازه دهید مرحله را با مجموعه ای از داده های ساختگی تنظیم کنیم.

برای این پرسش های مثال، اجازه دهید وانمود کنیم که برنامه ما در حال ردیابی ناوگان حمل و نقل است، با حسگرهایی که داده ها را چند بار در دقیقه گزارش می دهند تا زمانی که کامیون یک اتصال سلولی داشته باشد. گاهی اوقات کامیون سیگنال را از دست می دهد که باعث می شود اطلاعات چند ساعت یا چند روز بعد ارسال شود. اگرچه برنامه مطمئناً درگیرتر خواهد بود و طرحی پیچیده تر برای ردیابی داده های مربوط به سری های زمانی و کسب وکار دارد، اجازه دهید روی دو تا از جدول ها تمرکز کنیم.

کامیون

این جدول هر کامیونی را که بخشی از ناوگان است ردیابی می کند. حتی برای یک شرکت بسیار بزرگ، این جدول معمولاً شامل چند ده هزار ردیف است.

برای پرسش های زیر، وانمود می کنیم که این جدول دارد~10000 کامیون که اکثر آنها در حال حاضر فعال هستند و اطلاعات را چند بار در دقیقه ثبت می کنند.

کامیون خوانی

جدول خواندن همه داده هایی را که از هر کامیون در طول زمان تحویل داده می شود ذخیره می کند. داده ها معمولاً چند بار در دقیقه به ترتیب زمانی ارسال می شوند، اگرچه داده های قدیمی تر می توانند زمانی که کامیون ها ارتباط خود را با سرویس سلولی قطع کنند یا فرستنده ها خراب شوند، به دست می آیند. برای این مثال، ما یک طرح جدول با فرمت گسترده و تنها چند ستون داده برای ساده نگه داشتن کارها نشان خواهیم داد. بسیاری از برنامه های IoT انواع مختلفی از نقاط داده را برای هر مجموعه خواندن ذخیره می کنند.

هنگامی که یک Hypertable TimescaleDB ایجاد می کنید، یک نمایه در ستون timestamp به طور خودکار ایجاد می شود، مگر اینکه به طور خاص به تابع ()create_hypertable بگویید که این کار را نکند. برای جدول truck_reading، شاخص پیش فرض باید شبیه به موارد زیر باشد:

CREATE INDEX ix_ts ON truck_reading (ts DESC);

این شاخص (یا حداقل یک نمایه ترکیبی که ابتدا از ستون زمان استفاده می کند) حتی برای ابتدایی ترین پرس و جوها که در آن زمان درگیر است، ضروری است و به شدت برای مدیریت تکه های hypertable توصیه می شود. پرس و جوهایی که به تنهایی شامل زمان هستند مانند MIN(ts) یا MAX(ts) به راحتی می توانند از این شاخص ارضا شوند.

با این حال، اگر بخواهیم حداقل یا حداکثر خواندن را برای یک کامیون خاص بدانیم، PostgreSQL راهی برای یافتن سریع آن اطلاعات نخواهد داشت. جستار زیر را در نظر بگیرید که آخرین قرائت های یک کامیون خاص را جستجو می کند:

اگر جدول truck_reading فقط دارای شاخص زمان پیش فرض (ix_ts بالا) بود، PostgreSQL هیچ روش کارآمدی برای دریافت جدیدترین ردیف داده ها برای این کامیون خاص ندارد. درعوض، باید شروع به خواندن ایندکس از ابتدا کند (جدیدترین مُهر زمانی ابتدا بر اساس ترتیب فهرست است) و هر ردیف را بررسی کنید تا ببینید آیا حاوی 1234 به عنوان truck_id است.

اگر این کامیون اخیراً گزارش داده بود، PostgreSQL باید حداکثر چند هزار ردیف را بخواند و پرس و جو همچنان "سریع" بود. اگر کامیون ظرف چند ساعت یا چند روز داده ها را ضبط نمی کرد، PostgreSQL ممکن بود مجبور شود صدها هزار یا میلیون ها ردیف داده را بخواند، قبل از اینکه ردیفی را پیدا کند که در آن truck_id = 1234 باشد.

برای نشان دادن این، ما یک مجموعه داده نمونه ایجاد کردیم~20 میلیون ردیف داده (1 هفته برای 10000 کامیون) و سپس 12 ساعت اخیر برای truck_id = 1234 حذف شد.

در خروجی EXPLAIN زیر، می توانیم ببینیم که PostgreSQL باید کل ایندکس را اسکن کند و بیش از 1. 53 میلیون ردیف را که با «truck_id» مورد جستجوی ما مطابقت ندارند، فیلتر کند. حتی نگران کننده تر مقدار داده ای است که PostgreSQL برای بازیابی صحیح یک ردیف از داده هایی که درخواست می کردیم پردازش می کرد -~184 مگابایت داده!(23168 بافر x 8 کیلوبایت در هر بافر)

اگر برنامه شما مجبور باشد برای هر پرس و جو آنقدر کار انجام دهد، با افزایش داده ها به سرعت در ساده ترین پرس و جوها با تنگنا مواجه می شود.

بنابراین، ضروری است که شاخص(های) صحیحی برای الگوهای پرس و جوی معمولی برنامه خود داشته باشیم.

در این مثال (و در بسیاری از برنامه های کاربردی واقعی)، حداقل باید یک شاخص دیگر ایجاد کنیم که شامل truck_id و ts باشد. این امر باعث می شود که پرسش های مربوط به یک کامیون خاص بر اساس زمان بسیار کارآمدتر جستجو شوند. یک شاخص نمونه به این صورت خواهد بود:

با ایجاد این شاخص، PostgreSQL می تواند آخرین رکورد یک کامیون خاص را خیلی سریع پیدا کند، خواه داده های چند ثانیه یا چند هفته پیش را گزارش کرده باشد.

با همان مجموعه داده مانند بالا ، همان پرس و جو که یک DataPoint را برای Truck_ID = 1234 از 12 ساعت پیش برمی گرداند ، فقط 40 کیلوبایت داده می خواند! یعنی~4600x داده های کمتری که باید خوانده شوند زیرا ما شاخص مناسب را ایجاد کردیم ، به ذکر زمان اجرای زیر میلی ثانیه! این موز است!

برای روشن شدن ، هر دو پرس و جو از یک فهرست برای جستجوی ردیف استفاده کردند. تفاوت در نحوه استفاده از شاخص ها برای یافتن داده های مورد نظر ما است.

اولین پرس و جو مجبور به فیلتر کردن Tuple بود زیرا فقط زمان بندی بخشی از شاخص بود. فیلتر بعد از خواندن Tuple از دیسک انجام می شود ، این بدان معناست که کار بسیار بیشتری انجام می شود و فقط سعی می کنید داده های صحیح را پیدا کنید.

در مقابل ، پرس و جو دوم از هر دو بخش از شاخص (Truck_ID و Ts) به عنوان بخشی از شرایط شاخص استفاده کرد. این بدان معنی است که فقط ردیف هایی که با محدودیت مطابقت دارند از دیسک خوانده می شوند. در این حالت ، این تعداد بسیار کمی است و پرس و جو بسیار سریعتر است!

متأسفانه ، حتی با وجود هر دو این شاخص های هدفمند ، چند نمایش داده شده SQL سری زمانی مشترک وجود دارد که انجام نمی شود و همچنین بیشتر توسعه دهندگان انتظار دارند.

بیایید در مورد چرا این موضوع صحبت کنیم.

نمایش داده شد

نمایش داده شدگان باز به دنبال نقاط داده منحصر به فرد (اول ، آخرین ، جدیدترین) بدون مشخص کردن محدوده زمانی خاص یا محدودیت دستگاه (در مثال ما). این نوع نمایش داده ها برنامه ریز را با گزینه های اندک ترک می کند ، بنابراین فرض می کند که باید در زمان برنامه ریزی ، کل شاخص را اسکن کند. این ممکن است درست نباشد ، اما PostgreSQL نمی تواند قبل از اجرای پرس و جو بداند و به دنبال داده ها شروع می کند.

این امر به ویژه در هنگام تقسیم جداول دشوار است زیرا شاخص های واقعی به طور مستقل با هر پارتیشن جدول ذخیره می شوند. بنابراین ، هیچ شاخص جهانی برای کل جدول وجود ندارد که مشخص کند آیا یک کامیون خاص (در مورد ما) در یک پارتیشن وجود دارد. یک بار دیگر ، هنگامی که برنامه ریز PostgreSQL در مرحله برنامه ریزی اطلاعات کافی ندارد ، فرض می شود که هر پارتیشن نیاز به پرس و جو دارد ، به طور معمول باعث افزایش زمان برنامه ریزی می شود.

یک پرس و جو مانند موارد زیر را در نظر بگیرید ، که از اولین خواندن برای یک Truck_ID خاص درخواست می کند:

با دو شاخص موجود در محل ((TS DESC) و (TRUCK_ID ، TS DESC)) ، احساس می شود که این باید یک پرس و جو سریع باشد. اما از آنجا که فشار خون بالا به موقع تقسیم می شود ، برنامه ریز در ابتدا فرض می کند که باید هر تکه را اسکن کند. اگر پارتیشن های زیادی داشته باشید ، زمان برنامه ریزی بیشتر طول خواهد کشید.

اگر جدول Truck_reading به طور فعال داده های جدید را دریافت کند ، اجرای پرس و جو همچنان "سریع" خواهد بود زیرا احتمالاً جواب در قسمت اول یافت می شود و به سرعت باز می گردد. اما اگر Truck_ID = 1234 هرگز اطلاعاتی را گزارش نکرده باشد یا هفته ها آفلاین باشد ، PostgreSQL باید هر دو برنامه ریزی کند و سپس شاخص هر قطعه را اسکن کند. این پرس و جو از شاخص کامپوزیت در هر پارتیشن استفاده می کند تا به سرعت مشخص شود که هیچ سوابق برای کامیون وجود ندارد ، اما هنوز هم باید برای برنامه ریزی و اجرای پرس و جو وقت بگذارد.

درعوض ، ما می خواهیم در هر زمان ممکن از انجام کارهای غیر ضروری خودداری کنیم و از پتانسیل این ضد الگوی پرس و جو جلوگیری کنیم.

نمایش داده شدگان در سایت بالا:

بسیاری از نمایش داده ها همچنین می توانند با افزایش کاردینال بودن تأثیر منفی بگذارند و با رشد حجم داده ها آهسته تر می شوند و موارد فردی بیشتر ردیابی می شوند. گزینه های 1-4 در زیر نمونه های خوبی از نمایش داده شد که در مجموعه داده های کوچک و متوسط عملکرد خوبی دارند ، اما با افزایش حجم و کاردینال بودن ، اغلب کندتر می شوند.

این نمایش داده ها سعی می کنند از طریق جدول سری زمانی توسط Truck_ID "گام" کنند و از شاخص های موجود در فشار خون بالا استفاده کنند. با این حال ، هرچه موارد بیشتری نیاز به پرس و جو داشته باشند ، تکرار اغلب کندتر می شود زیرا این شاخص خیلی بزرگ است تا بتواند در حافظه مناسب باشد و باعث می شود PostgreSQL به طور مکرر داده ها را به و از دیسک مبادله کند.

با درک اینکه این دو نوع نمایش داده شده ممکن است در هر شرایطی نیز انجام ندهند ، بیایید پنج روش مختلف را برای به دست آوردن جدیدترین رکورد برای هر مورد در جدول سری زمانی خود بررسی کنیم. در بیشتر شرایط ، حداقل یکی از این گزینه ها برای داده های شما به خوبی کار خواهد کرد.

توسعه! = تولید

یک کلمه سریع هشدار در هنگام پرش به نمونه های SQL در زیر.

همیشه خوب است به یاد داشته باشید که پایگاه داده توسعه شما بعید است که به عنوان پایگاه داده تولید شما از همان حجم ، کاردینالیت و توان معامله برخوردار باشد. هر یک از نمایش داده های نمونه ای که در زیر نشان می دهیم ممکن است در یک پایگاه داده کوچکتر و کمتر فعال ، فقط به خوبی عمل کند ، فقط برای عملکرد ضعیف تر از حد انتظار در تولید.

همیشه بهتر است در محیطی که ممکن است به تولید شبیه باشد ، آزمایش کنید. نحوه انجام این کار فراتر از محدوده این پست است ، اما چند گزینه می تواند باشد:

• با استفاده از فورک پایگاه داده یک کلیک با نمونه Cloud TimeScale خود استفاده کنید تا به راحتی یک نسخه از تولید را برای آزمایش و یادگیری تهیه کنید. استفاده از داده ها تا حد امکان معمولاً ترجیح داده می شود!
• از پایگاه داده تولیدی خود نسخه پشتیبان تهیه کرده و در یک مکان تایید شده بازیابی کنید و داده ها را ناشناس کنید، با حفظ مشخصات و آمار ردیف مشابه. پس از هر گونه تغییر داده، جدول را همیشه آنالیز کنید.
• استفاده مجدد از طرح خود و تولید تعداد زیادی داده نمونه با حجم بالا و کاردینالیتی بالا با ()gene_series را در نظر بگیرید (احتمالاً با استفاده از برخی از ایده های سری ما در مورد تولید داده های نمونه واقعی تر در داخل PostgreSQL).

هر روشی را که انتخاب می کنید، همیشه به یاد داشته باشید که پایگاه داده با 1 میلیون ردیف داده سری زمانی برای 100 مورد، بسیار متفاوت از پایگاه داده ای با 10 میلیارد ردیف داده سری زمانی برای 10000 مورد که هر چند ثانیه گزارش می شود، عمل می کند.

اکنون که درباره چگونگی کمک نمایه ها به ما در یافتن داده ها بحث کرده ایم و برخی از الگوهای پرس و جو را که می توانند کندتر از حد معمول هستند، بررسی کرده ایم، وقت آن است که مقداری SQL بنویسیم و در مورد زمان مناسب استفاده از هر گزینه صحبت کنیم.

گزینه 1: ساده لوح GROUP BY

SQL یک زبان قدرتمند است. متأسفانه، هر پایگاه داده ای که اجازه می دهد پرس و جوها در SQL نوشته شوند، اغلب عملکردهای کمی متفاوت برای انجام کارهای مشابه دارند، یا به سادگی از استانداردهای SQL پشتیبانی نمی کنند که در غیر این صورت، درخواست های «آخرین نقطه» کارآمد را مانند آنچه در مورد آن بحث کردیم، پشتیبانی نمی کند.

با این حال، تقریباً در هر پایگاه داده ای که SQL یک زبان پرس و جو پشتیبانی می شود، می توانید این پرس و جو را برای دریافت آخرین زمانی که یک کامیون داده ها را ثبت کرده است، اجرا کنید. در بیشتر موارد، این کار روی مجموعه داده های بزرگ به خوبی انجام نمی شود زیرا بند GROUP BY از استفاده از ایندکس ها جلوگیری می کند.

از آنجایی که شاخص ها در PostgreSQL استفاده نمی شوند، این رویکرد برای مجموعه داده های با حجم بالا/کاردینالیته بالا توصیه نمی شود. اما، حتی اگر کارآمد نباشد، نتیجه مورد انتظار شما را خواهد گرفت.

اگر سؤالی مانند این دارید، در نظر بگیرید که چگونه یکی از گزینه های دیگر فهرست شده در زیر ممکن است بهتر با الگوی درخواست شما مطابقت داشته باشد.

گزینه 2: پیوستن جانبی

یکی از ساده ترین توصیه هایی که برای هر توسعه دهنده پایگاه داده PostgreSQL وجود دارد، یادگیری نحوه استفاده از LATERAL JOIN است. در برخی دیگر از موتورهای پایگاه داده (مانند SQL Server) این دستورات APPLY نامیده می شوند، اما اساساً همان کار را انجام می دهند - پرس و جو داخلی را برای هر ردیف تولید شده توسط پرس و جو بیرونی اجرا کنید. از آنجا که یک JOIN است، پرس و جو داخلی می تواند از مقادیر پرس و جو بیرونی استفاده کند.(در حالی که این شبیه به یک پرس و جو فرعی مرتبط است، یک چیز نیست.)

پیوندهای جانبی گزینه خوبی هستند که شما به عنوان توسعه دهنده یا مدیر ، تقریباً می دانید که پرس و جو بیرونی چند ردیف باز خواهد گشت. برای چند صد یا چند هزار ردیف ، این الگوی احتمالاً تا زمانی که شاخص صحیح وجود داشته باشد ، رکورد "اخیر" شما را بازگرداند.

نکته مناسب در مورد یک پرس و جو جانبی جانبی این است که می توان فیلتر اضافی را در پرس و جو بیرونی برای شناسایی موارد خاص برای بازیابی داده ها برای استفاده کرد. در بیشتر موارد ، داده های تجاری رابطه ای (کامیون) یک جدول کوچکتر با زمان پرس و جو سریعتر خواهد بود. صفحه بندی همچنین می تواند در جدول کوچکتر کارآمدتر اعمال شود (یعنی جبران 500 حد 100) که باعث می شود کل کار را که پرس و جو داخلی نیاز به انجام آن دارد ، کاهش دهد.

متأسفانه ، یک نکته منفی از یک درخواست پیوستن جانبی این است که می تواند مستعد مسئله کاردینال بودن بالا باشد که ما در بالا حداقل از دو طریق در مورد آن صحبت کردیم.

اول ، اگر پرس و جو بیرونی موارد بیشتری را نسبت به جدول داخلی بازگرداند ، این پرس و جو بر روی جدول داخلی که کارهای بیشتری از حد لازم دارد ، حلقه می کند. به عنوان مثال ، اگر جدول کامیون 10،000 مدخل برای کامیون ها داشته باشد اما فقط 1000 نفر از آنها تا به حال خوانش را گزارش کرده بودند ، پرس و جو از پرس و جو داخلی 10 برابر بیشتر از آنچه لازم بود ، حلقه می شود.

دوم ، حتی اگر کاردینال بودن پرس و جو داخلی و بیرونی به طور کلی مطابقت داشته باشد ، اگر این کاردینال بودن زیاد باشد یا جدول روی پرس و جو داخلی بسیار بزرگ باشد ، یک پرس و جو جانبی جانبی با گذشت زمان کند می شود زیرا حافظه یا I/O به یک عامل محدود کننده تبدیل می شود. واددر مقطعی ، ممکن است شما نیاز به گزینه 5 در زیر به عنوان یک راه حل نهایی داشته باشید.

گزینه 3: TimeScaledB Skipscan

سلب مسئولیت: این روش فقط در هنگام نصب پسوند TimescaledB کار می کند. اگر هنوز از آن استفاده نمی کنید ، می توانید اطلاعات بیشتری را در صفحه اسناد ما پیدا کنید.

پیوندهای جانبی ابزاری عالی برای کار در هنگام کار با نمایش داده های تکراری است. با این حال ، همانطور که ما فقط بحث کردیم ، آنها همیشه بهترین انتخاب نیستند که در هنگام تکرار موارد پرس و جو بیرونی باعث شود که پرس و جو درونی اغلب اجرا شود و به دنبال داده هایی که وجود ندارد.

This is when it can be advantageous to use the reading table itself to get the distinct items and related data. In particular, this is helpful when we want to query trucks that have reported data within a period of time, for example, the last 24 hours. While we could add a filter to the ier query above ( WHERE ts>اکنون () - فاصله '24 ساعت ') ، ما هنوز باید بیش از هر کامیون_ید تکرار کنیم ، که برخی از آنها ممکن است در 24 ساعت گذشته داده ها را گزارش نکرده باشد.

از آنجا که ما قبلاً شاخص IX_TRUCK_ID_TS را در بالا ایجاد کردیم که توسط Truck_ID و TS DESC سفارش داده شده است ، یک رویکرد مشترک که بسیاری از توسعه دهندگان PostgreSQL سعی می کنند استفاده از یک پرس و جو با PostgreSQL باشد.

اگر این کار را بدون نصب TimescaleDB امتحان کنید، عملکرد خوبی نخواهد داشت - حتی اگر نمایه ای داریم که به نظر می رسد داده ها را به درستی مرتب می کند و «پرش» از آن آسان است! این به این دلیل است که از PostgreSQL 14، هیچ ویژگی در مرحله اجرای پرس و جو وجود ندارد که بتواند شاخص را برای یافتن هر نمونه منحصر به فرد یک کلید خاص "راه برود". در عوض، PostgreSQL اساساً تمام داده ها را می خواند، آن ها را بر اساس ستون های ON گروه بندی می کند، و سپس تمام ردیف ها را به جز ردیف اول (بر اساس ترتیب) فیلتر می کند.

با این حال، با نصب پسوند TimescaleDB (نسخه 2. 3 یا بالاتر)، پرس و جو DISTINCT ON تا زمانی که فهرست صحیح وجود داشته باشد و به همان پرس و جو سفارش داده شود، بسیار کارآمدتر کار می کند. این به این دلیل است که پسوند TimescaleDB یک گره پرس و جو جدیدی به نام "SkipScan" اضافه می کند که به محض یافتن یک کلید دیگر شروع به اسکن کردن شاخص با مقدار کلید بعدی می کند. یکی از بهترین بخش های (SkipScan) این است که روی هر جدول PostgreSQL با شاخص B-tree کار می کند. لازم نیست که یک Hypertable TimescaleDB باشد!

چند تفاوت در نحوه استفاده از ایندکس وجود دارد که همه آنها در پست وبلاگ لینک شده در بالا توضیح داده شده است.

گزینه 4: Scan Index Loose

اگر برنامه افزودنی TimescaleDB را نصب نکنید (یا نتوانید)، هنوز راهی برای پرس و جو از جدول truck_reading وجود دارد تا مُهر زمانی آخرین خوانده شده را برای هر truck_id به طور موثر برگردانید.

در ویکی PostgreSQL صفحه ای اختصاص داده شده به پویش فهرست سست وجود دارد. این روشی را برای استفاده از پرس و جوهای CTE بازگشتی برای انجام کارهایی که گره TimescaleDB (SkipScan) انجام می دهد نشان می دهد. نوشتن آن تقریباً ساده نیست و بازگرداندن چندین ردیف دشوارتر است (مشابه پرس و جوی DISTINCT نیست)، اما راهی برای استفاده مؤثرتر از ایندکس برای بازیابی یک ردیف برای هر مورد ارائه می دهد.

بزرگترین ایراد این رویکرد این است که بازگرداندن چندین ستون داده با CTE بازگشتی بسیار دشوارتر است (و در بیشتر موارد برگرداندن چندین ردیف به سادگی غیرممکن است). بنابراین در حالی که برخی از توسعه دهندگان از این به عنوان پرس و جوی Skip Scan یاد می کنند، به راحتی به شما اجازه نمی دهد که تمام داده های ردیف را برای یک جدول با حجم بالا مانند گره پرس و جو (SkipScan) که TimescaleDB ارائه می دهد، بازیابی کنید.

گزینه 5: جدول ثبت و ماشه

بعضی اوقات ، به ویژه با مجموعه داده های بزرگ و بالا ، گزینه های فوق برای عملیات روزانه به اندازه کافی کارآمد نیستند. پرس و جو برای آخرین خواندن همه موارد ، یا دستگاه هایی که در 24 ساعت گذشته ارزش آن را گزارش نکرده اند ، با افزایش حجم داده و کاردینال بودن ، انتظارات شما را برآورده نمی کنند.

در این حالت ، ممکن است گزینه بهتری برای حفظ جدول باشد که آخرین قرائت ها را برای هر دستگاه ذخیره کند ، زیرا در جدول سری زمانی خام قرار داده شده است تا برنامه شما بتواند از مجموعه داده های بسیار کوچکتر برای جدیدترین مقادیر پرس و جو کند. برای ردیابی و به روزرسانی جدول ورود به سیستم ، ما یک محرک پایگاه داده در جدول داده های خام (Hyper) ایجاد خواهیم کرد.

"یک دقیقه صبر کنید! آیا شما فقط گفتید که ما می خواهیم یک ماشه پایگاه داده ایجاد کنیم؟ آیا همه نمی گویند شما هرگز نباید از آنها استفاده کنید؟"

درسته. محرک ها غالباً در دنیای SQL رپ بد می گیرند و صادقانه بگویم که اغلب قابل توجیه است. به درستی و با اجرای صحیح ، محرک های پایگاه داده می توانند بسیار مفید باشند و تأثیر کمتری در عملکرد انتخاب داشته باشند. درج و به روزرسانی عملکرد تأثیر خواهد گذاشت زیرا هر معامله باید کارهای بیشتری انجام دهد. عملکرد عملکرد ممکن است یا ممکن است تأثیر شما را تحت تأثیر قرار دهد ، بنابراین آزمایش ضروری است.

SQL زیر نمونه ای از حداقل نحوه اجرای این نوع ورود به سیستم را ارائه می دهد. ملاحظات زیادی در مورد نحوه اجرای بهترین گزینه برای برنامه خاص شما وجود دارد. هر فرآیند جدیدی را که در پایگاه داده خود به پردازش داده ها تبلیغ می کنید ، کاملاً آزمایش کنید.

به طور خلاصه ، اسکریپت مثال زیر:

• یک جدول برای ذخیره جدیدترین داده ها ایجاد می کند. اگر فقط می خواهید آخرین زمان بندی خوانش های هر کامیون را ذخیره کنید ، این به راحتی می تواند مقادیر را در یک میدان جدید در جدول کامیون وارد کند
• alter fillfactor جدول را به 90 ٪ تغییر می دهد زیرا به روزرسانی سنگین خواهد شد
• یک عملکرد ماشه ایجاد می کند که اگر یک کامیون وجود نداشته باشد یا مقادیر را به روز کند ، یک ردیف را وارد می کند اگر یک ردیف برای آن کامیون در حال حاضر دارای ورودی در جدول باشد (درگیری)
• ماشه را روی داده های فشار خون فعال می کند

نکته اصلی این رویکرد فقط ردیابی آنچه لازم است ، کاهش میزان کار PostgreSQL به عنوان بخشی از معامله کلی که داده های خام را انجام می دهد انجام می دهد. اگر برنامه شما مقادیر 100000 دستگاه را در هر ثانیه به روز کند (و شما 50 ستون داده را ردیابی می کنید) ، ممکن است یک رویکرد محرک متفاوت لازم باشد. اگر این نوع حجم داده ای است که به طور مرتب مشاهده می کنید ، فرض می کنیم که شما یک PostgreSQL DBA با تجربه در تیم خود دارید تا به مدیریت و حفظ پایگاه داده برنامه خود کمک کند - و به شما کمک می کند تا تصمیم بگیرید که آیا رویکرد جدول ورود به سیستم با منابع سرور موجود کار خواهد کرد.

با استفاده از این قطعات ، جدول جدید شروع به دریافت ردیف های جدید از داده ها و به روزرسانی آخرین مقادیر به عنوان داده ها می کند. پرس و جو این جدول بسیار کارآمدتر از جستجوی صدها میلیون ردیف خواهد بود.

گزینه ها را مرور کنید

نتیجه

هر رویکردی که انجام دهید ، امیدوارم یکی از این گزینه ها به شما کمک کند تا مرحله بعدی را برای بهبود عملکرد برنامه خود بردارید.

اگر شما مایل به کشف TimeScaledB هستید یا می خواهید در مورد چگونگی درک سایر توسعه دهندگان چگونه به سرعت جدیدترین ارزش دستگاه ها را در پایگاه داده های سری بزرگ زمان پیدا کنید ، به جامعه Timescale بپیوندید! در مکالمه شرکت کنید و در جامعه پر جنب و جوش ما سؤال کنید یا پیشگام در انجمن جدید جامعه ما باشید ، خانه ای برای بحث های طولانی که دوام خواهد داشت. همچنین می توانید مستندات ما را بخوانید ، یا پروژه ها و سؤالات خود را برای ما timescaledb توییت کنید. تیم Timescale و کاربران آماده کمک و یادگیری هستند!

اگر هنوز TimeScaledB را امتحان نکرده اید ، ساده ترین راه برای شروع استفاده از یک پایگاه داده کاملاً مدیریت شده در Timescale Cloud است. در اینجا ثبت نام کنید - برای 30 روز 100 ٪ رایگان است ، کارت اعتباری لازم نیست.

ویدیو های آموزشی فارکس...