PostgreSQL অ্যান্টিপ্যাটার্নস: রেজিস্ট্রি নেভিগেট করা

আজ SQL এ কোন জটিল কেস এবং পরিশীলিত অ্যালগরিদম থাকবে না। সবকিছু খুব সহজ হবে, ক্যাপ্টেন স্পষ্ট স্তরে - আসুন এটি করি ইভেন্ট রেজিস্ট্রি দেখা সময় অনুসারে সাজানো।

অর্থাৎ ডাটাবেসে একটি চিহ্ন রয়েছে events, এবং তার একটি ক্ষেত্র আছে ts - ঠিক যে সময়ে আমরা এই রেকর্ডগুলিকে সুশৃঙ্খলভাবে প্রদর্শন করতে চাই:

CREATE TABLE events(
  id
    serial
      PRIMARY KEY
, ts
    timestamp
, data
    json
);

CREATE INDEX ON events(ts DESC);

এটা স্পষ্ট যে আমাদের সেখানে এক ডজন রেকর্ড থাকবে না, তাই আমাদের কিছু ফর্মের প্রয়োজন হবে পৃষ্ঠা নেভিগেশন.

#0 "আমি আমার মায়ের পোগ্রোমিস্ট"

cur.execute("SELECT * FROM events;")
rows = cur.fetchall();
rows.sort(key=lambda row: row.ts, reverse=True);
limit = 26
print(rows[offset:offset+limit]);

এটি প্রায় একটি রসিকতা নয় - এটি বিরল, তবে বন্যের মধ্যে পাওয়া যায়। কখনও কখনও, ORM-এর সাথে কাজ করার পরে, SQL এর সাথে "সরাসরি" কাজে স্যুইচ করা কঠিন হতে পারে।

তবে আসুন আরও সাধারণ এবং কম স্পষ্ট সমস্যার দিকে এগিয়ে যাই।

#1 অফসেট

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - записей на странице, $1 - начало страницы

26 নম্বরটি কোথা থেকে এসেছে? এটি একটি স্ক্রীন পূরণ করার জন্য এন্ট্রির আনুমানিক সংখ্যা। আরও স্পষ্টভাবে, 25টি প্রদর্শিত রেকর্ড, প্লাস 1, ইঙ্গিত দেয় যে নমুনায় আরও কিছু আছে এবং এটি এগিয়ে যাওয়ার অর্থবোধ করে।

অবশ্যই, এই মানটি অনুরোধের মূল অংশে "সেলাই" করা যাবে না, তবে একটি প্যারামিটারের মাধ্যমে পাস করা যাবে। কিন্তু এই ক্ষেত্রে, PostgreSQL সময়সূচী তুলনামূলকভাবে কম রেকর্ড থাকা উচিত এমন জ্ঞানের উপর নির্ভর করতে সক্ষম হবে না - এবং সহজেই একটি অকার্যকর পরিকল্পনা বেছে নেবে।

এবং অ্যাপ্লিকেশন ইন্টারফেসে থাকাকালীন, রেজিস্ট্রি দেখার ভিজ্যুয়াল "পৃষ্ঠাগুলির মধ্যে স্যুইচিং" হিসাবে প্রয়োগ করা হয়, দীর্ঘ সময়ের জন্য কেউ সন্দেহজনক কিছু লক্ষ্য করে না। ঠিক সেই মুহূর্ত পর্যন্ত যখন, সুবিধার জন্য সংগ্রামে, UI/UX ইন্টারফেসটিকে "অন্তহীন স্ক্রোল"-এ রিমেক করার সিদ্ধান্ত নেয় - অর্থাৎ, সমস্ত রেজিস্ট্রি এন্ট্রি একটি একক তালিকায় আঁকা হয় যা ব্যবহারকারী উপরে এবং নীচে স্ক্রোল করতে পারে।

এবং তাই, পরবর্তী পরীক্ষার সময়, আপনি ধরা পড়েন রেকর্ডের নকল রেজিস্ট্রিতে কেন, কারণ টেবিলের একটি স্বাভাবিক সূচক আছে (ts), আপনার প্রশ্নের উপর নির্ভর করে?

ঠিক কারণ আপনি এটি বিবেচনায় নেননি ts একটি অনন্য কী নয় এই টেবিলে আসলে, এবং এর মান অনন্য নয়, বাস্তব অবস্থার যেকোনো "সময়" এর মতো - তাই, একই কী মান বাছাই করার কাঠামোর মধ্যে একটি ভিন্ন চূড়ান্ত আদেশের কারণে দুটি সংলগ্ন প্রশ্নের একই রেকর্ড সহজেই পৃষ্ঠা থেকে পৃষ্ঠায় "জাম্প" করে৷

আসলে, এখানে একটি দ্বিতীয় সমস্যা লুকিয়ে আছে, যা লক্ষ্য করা অনেক বেশি কঠিন - কিছু এন্ট্রি দেখানো হবে না মোটেও! সর্বোপরি, "ডুপ্লিকেট" রেকর্ডগুলি অন্য কারও জায়গা নিয়েছে। সুন্দর ছবিসহ বিস্তারিত ব্যাখ্যা পাওয়া যাবে এখানে পড়ুন.

সূচক প্রসারিত

একজন ধূর্ত বিকাশকারী বোঝেন যে সূচক কীটিকে অনন্য করতে হবে, এবং সবচেয়ে সহজ উপায় হল এটিকে একটি স্পষ্টভাবে অনন্য ক্ষেত্র দিয়ে প্রসারিত করা, যার জন্য PK উপযুক্ত:

CREATE UNIQUE INDEX ON events(ts DESC, id DESC);

এবং অনুরোধটি পরিবর্তন করে:

SELECT
  ...
ORDER BY
  ts DESC, id DESC
LIMIT 26 OFFSET $1;

#2 "কার্সার" এ স্যুইচ করুন

কিছু সময় পরে, একজন DBA আপনার কাছে আসে এবং আপনার অনুরোধে "সন্তুষ্ট" হয় তারা তাদের অফসেট নিয়ম দিয়ে সার্ভারটিকে নরকের মতো লোড করে, এবং সাধারণভাবে, এটিতে স্যুইচ করার সময় দেখানো শেষ মান থেকে নেভিগেশন. আপনার প্রশ্ন আবার পরিবর্তিত হয়:

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- последние полученные на предыдущем шаге значения
ORDER BY
  ts DESC, id DESC
LIMIT 26;

এটা না আসা পর্যন্ত আপনি স্বস্তির নিঃশ্বাস ফেলেছেন...

#3। সূচী পরিষ্কার করা

কারণ একদিন তোমার ডিবিএ পড়ে অকার্যকর সূচক খোঁজার বিষয়ে নিবন্ধ এবং যে উপলব্ধি "সর্বশেষ নয়" টাইমস্ট্যাম্প ভাল নয়. এবং আমি আবার আপনার কাছে এসেছি - এখন এই চিন্তাভাবনা নিয়ে যে সেই সূচকটি এখনও ফিরে আসা উচিত (ts DESC).

কিন্তু পৃষ্ঠাগুলির মধ্যে "জাম্পিং" রেকর্ডগুলির প্রাথমিক সমস্যাটির সাথে কী করবেন?.. এবং সবকিছুই সহজ - আপনাকে অনির্দিষ্ট সংখ্যক রেকর্ড সহ ব্লক নির্বাচন করতে হবে!

সাধারণভাবে, কে আমাদেরকে "ঠিক 26" নয়, কিন্তু "26 এর কম নয়" পড়তে নিষেধ করে? উদাহরণস্বরূপ, যাতে পরবর্তী ব্লকে রয়েছে স্পষ্টতই ভিন্ন অর্থ সহ রেকর্ড ts - তাহলে ব্লকগুলির মধ্যে "জাম্পিং" রেকর্ডগুলির সাথে কোনও সমস্যা হবে না!

এটি কীভাবে অর্জন করা যায় তা এখানে:

SELECT
  ...
WHERE
  ts < $1 AND
  ts >= coalesce((
    SELECT
      ts
    FROM
      events
    WHERE
      ts < $1
    ORDER BY
      ts DESC
    LIMIT 1 OFFSET 25
  ), '-infinity')
ORDER BY
  ts DESC;

এখানে কি হচ্ছে?

1. আমরা 25 ধাপ "নিচে" রেকর্ড করি এবং "সীমানা" মান পাই ts.
2. যদি সেখানে ইতিমধ্যে কিছু না থাকে, তাহলে NULL মান প্রতিস্থাপন করুন -infinity.
3. আমরা প্রাপ্ত মানের মধ্যে মানগুলির সম্পূর্ণ সেগমেন্ট বিয়োগ করি ts এবং ইন্টারফেস থেকে পাস করা $1 প্যারামিটার (আগের "শেষ" রেন্ডার করা মান)।
4. যদি একটি ব্লক 26টির কম রেকর্ডের সাথে ফেরত দেওয়া হয়, তবে এটি শেষটি।

বা একই ছবি:

কারণ এখন আমাদের আছে নমুনার কোনো নির্দিষ্ট "শুরু" নেই, তাহলে কিছুই আমাদের এই অনুরোধটিকে বিপরীত দিকে "প্রসারিত" করতে এবং "রেফারেন্স পয়েন্ট" থেকে ডাটা ব্লকের গতিশীল লোডিং বাস্তবায়ন করতে বাধা দেয় না - নীচে এবং উপরে উভয় দিকে।

মন্তব্য

1. হ্যাঁ, এই ক্ষেত্রে আমরা সূচকটি দুবার অ্যাক্সেস করি, তবে সবকিছুই "শুদ্ধভাবে সূচক দ্বারা"। অতএব, একটি subquery শুধুমাত্র ফলাফল হবে একটি অতিরিক্ত সূচক শুধুমাত্র স্ক্যান.
2. এটি বেশ স্পষ্ট যে এই কৌশলটি শুধুমাত্র তখনই ব্যবহার করা যেতে পারে যখন আপনার মান থাকে ts শুধুমাত্র সুযোগ দ্বারা অতিক্রম করতে পারেন, এবং তার মধ্যে কিছু. যদি আপনার সাধারণ কেস "00:00:00.000 এ এক মিলিয়ন রেকর্ড" হয় তবে আপনার এটি করা উচিত নয়। আমি বলতে চাচ্ছি, আপনার এমন ঘটনা ঘটতে দেওয়া উচিত নয়। কিন্তু যদি এটি ঘটে তবে একটি বর্ধিত সূচক সহ বিকল্পটি ব্যবহার করুন।

উত্স: www.habr.com