🏺معماری پایگاه داده – تاریخچه بر ایالت

پایگاه داده باید چه چیزی را ذخیره کند؟ دو روش کلی برای تفکر در مورد آن وجود دارد:

1. یک پایگاه داده باید حالت را ذخیره کند
2. یک پایگاه داده باید تاریخچه را ذخیره کند

مهندسان تمایل دارند که پایگاه داده خود را به عنوان یک ذخیره‌کننده در نظر بگیرند حالت. این رویکرد واضح تر است و کمی ساده تر است.

اما به نظر می رسد که با پایگاه داده خود به عنوان یک ذخیره از تاریخ در عوض می تواند برنامه های شما را بسیار انعطاف پذیرتر و انعطاف پذیرتر کند. بیایید نگاهی بیندازیم …

تفاوت در چیست؟

اجازه دهید وانمود کنیم که یک بازی وب ساده داریم و می‌خواهیم تابلوی امتیازات را به کاربران خود نشان دهیم. هر مسابقه یک یا چند بازیکن دارد و هر بازیکن صفر یا بیشتر امتیاز کسب می کند. می‌خواهیم رتبه‌بندی بازیکنان را بر اساس تعداد امتیازی که در مجموع کسب کرده‌اند نشان دهیم:

اگر پایگاه داده را به عنوان یک فروشگاه دولتی در نظر بگیرید

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

CREATE TABLE leaderboard (
  username VARCHAR UNIQUE NOT NULL PRIMARY KEY,
  points INTEGER NOT NULL
);

CREATE INDEX points_idx ON leaderboard (points DESC);

آنها هر زمان که بازیکنی امتیاز کسب کند شروع به به روز رسانی این جدول جدید می کنند و یک پرس و جو ساده روی آن انجام می دهند تا 20 بازیکن برتر را بدست آورند:

SELECT *
FROM leaderboard
ORDER BY points DESC
LIMIT 20;

اگر پایگاه داده را به عنوان یک فروشگاه تاریخ در نظر بگیرید

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

CREATE TABLE "match" (
  match_id INTEGER UNIQUE NOT NULL PRIMARY KEY,
  start_time TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  end_time TIMESTAMP WITHOUT TIME ZONE NOT NULL,
  map_name VARCHAR NOT NULL,
  game_mode VARCHAR NOT NULL
);

CREATE TABLE match_player (
  match_id INTEGER NOT NULL,
  username VARCHAR NOT NULL,
  points INTEGER NOT NULL,
  FOREIGN KEY (match_id) REFERENCES "match" (match_id)
);

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

SELECT username, SUM(points)
FROM match_player
GROUP BY username
ORDER BY SUM(points) DESC
LIMIT 20;

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

شاید متوجه شده باشید که کوئری تابلوی امتیازات آنها حتی از آن استفاده نمی کند match جدول (فقط استفاده می کند match_player). با وجود عدم نیاز game_mode و اطلاعات دیگر در آنجا بلافاصله، این مهندس تصمیم گرفت که بخش مهمی از تاریخچه برنامه است، و بنابراین باید ذخیره شود.

برای اینکه پرس و جو کمی آسان تر شود، این مهندس ممکن است تصمیم بگیرد که یک leaderboard view، که در اصل یک پرس و جو ذخیره شده است که می تواند مانند یک جدول پرس و جو شود:

CREATE VIEW leaderboard AS (
  SELECT username, SUM(points)
  FROM match_player
  GROUP BY username
  ORDER BY SUM(points) DESC
  LIMIT 20
);

SELECT * FROM leaderboard;

اشکالات و ویژگی های جدید در راه است

حشره – یک روز متوجه می شوید که یک باگ در بازی شما مورد سوء استفاده قرار می گیرد و برخی از کاربران توانسته اند امتیازهای ناعادلانه ای کسب کنند. شما به تاریخچه git خود نگاه می‌کنید و تشخیص می‌دهید که اشکال در 4 آوریل معرفی شده است. شما به عقب در گزارش‌های خود نگاه می‌کنید و برخی از شناسه‌های مطابقت را پیدا می‌کنید که مطمئناً از باگ مورد سوء استفاده قرار گرفته است، و همچنین تعیین می‌کنید که اگر کسی توانسته است بیش از 50 امتیاز کسب کند. آنها باید از امتیازات یک مسابقه استفاده کرده باشند.

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

برای مهندس که تاریخچه مسابقات را ذخیره کرده است، وضعیت آسان تر است. آن‌ها می‌توانند مسابقات باگ شده و هر اتفاقی که پس از ۴ آوریل رخ داده است را حذف کنند و بازیکنی بیش از ۵۰ امتیاز کسب کند. مشکل حل شد.

ویژگی جدید – یک روز تصمیم می گیرید که می خواهید تابلوهای امتیازات ماهانه ایجاد کنید. اگر با معماری فروشگاه دولتی رفته‌اید، باید حداقل یک جدول کاملاً جدید ایجاد کنید، و نمی‌توانید برای ماه‌هایی که قبلاً گذشته است، تابلوهای امتیازات را نشان دهید. اگر معماری فروشگاه تاریخ را دنبال کردید، در حال حاضر تمام داده‌هایی را که به صورت ماسبق نیاز دارید در اختیار دارید، و فقط باید یک پرس و جو جدید بنویسید.

ما هرگز نمی‌توانیم بدانیم که آینده چه خواهد بود، اما همانطور که می‌بینیم، مهندسی که پایگاه داده خود را به عنوان یک فروشگاه تاریخ در نظر می‌گیرد، برای رویارویی با هر نیازی که سر راهش قرار می‌گیرد، بسیار مجهزتر است!

کارایی

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

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

نماهای مادی شده

برخی از پایگاه‌های داده، مانند PostgreSQL، از مفهومی به نام نماهای تحقق یافته پشتیبانی می‌کنند.

نمای مادی شده جدولی است که محتوای آن بر اساس یک پرس و جو محاسبه می شود. اگر با نماهای معمولی (غیر مادی) آشنا هستید، تفاوت اصلی این است که نمای مادی شده روی دیسک ذخیره می شود. محتویات یک نمای واقعی فقط در صورت درخواست، از طریق اجرای برنامه، مجدداً محاسبه می شود REFRESH MATERIALIZED VIEW بیانیه.

در مثال تابلوی امتیازات، مهندسی که معماری فروشگاه تاریخ را دنبال کرده است، می‌تواند یک نمای مادی شده دقیقاً مانند نمای معمولی ایجاد کند، فقط با اضافه کردن MATERIALIZED کلمه کلیدی:

CREATE MATERIALIZED VIEW leaderboard AS (
  SELECT username, SUM(points)
  FROM match_player
  GROUP BY username
  ORDER BY SUM(points) DESC
  LIMIT 20
);

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

برای محاسبه مجدد تابلوی امتیازات هر ساعت، می توانید از آن استفاده کنید pg_cron برای راه اندازی یک کار cron در پایگاه داده:

CREATE EXTENSION IF NOT EXISTS pg_cron;

SELECT cron.schedule(
  'leaderboard-refresh',
  '0 * * * *',
  'REFRESH MATERIALIZED VIEW CONCURRENTLY leaderboard'
);

(نصب PostgreSQL شما ممکن است قبلاً نصب شده باشد pg_cron موجود است، اما اگر نه، باید آن را نصب کنید)

سپس تابلوی امتیازات شما به طور خودکار هر ساعت به روز می شود.

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

علیرغم این کاستی ها، دیدگاه مادی اغلب یک راه حل عالی است. اما اگر به داده‌های کاملاً به‌روز نیاز دارید، یا حجم عظیمی از داده‌ها برای جمع‌آوری دارید، تکنیک بعدی ممکن است به شما کمک کند…

نگهداری از نمای افزایشی (IVM)

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

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

راه ساده برای انجام آن ایجاد یک است leaderboard جدول، سپس هر زمان که یک رکورد جدید را وارد کنید match_player جدول، شما همچنین کدی برای به روز رسانی در آن دارید leaderboard جدول برای به روز رسانی امتیاز شرکت کنندگان.

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

CREATE OR REPLACE FUNCTION update_leaderboard_function()
RETURNS TRIGGER AS $$
BEGIN
  IF (TG_OP = 'INSERT')
  THEN
    -- If a new match record is being inserted, we
    -- do an upsert on the leaderboard table to increase
    -- the points of the leaderboard record corresponding
    -- to that use username in the new match record.
    -- IMPORTANT: The leaderboard table must have a unique
    -- index on the username column
    INSERT INTO leaderboard (username, points) 
    VALUES (NEW.username, NEW.points)
    ON CONFLICT (username)
    DO UPDATE SET points = leaderboard.points + NEW.points;
  ELSEIF (TG_OP = 'DELETE')
  THEN
    -- If a match record is being deleted, we update
    -- the leaderboard table and decrease the leaderboard
    -- points of the user whose match record was deleted.
    UPDATE leaderboard
    SET points = leaderboard.points - OLD.points
    WHERE leaderboard.username = OLD.username;
  ELSEIF (TG_OP = 'UPDATE')
  THEN
    -- If a match record is being updated, we calculate
    -- the difference in points between the new record
    -- and the old record and add that to the points of
    -- the user whose match record was update (the difference
    -- might be negative, in which case the user's position
    -- on the leaderboard would go down)
    UPDATE leaderboard
    SET points = leaderboard.points + (NEW.points - OLD.points)
    WHERE leaderboard.username = OLD.username;
  END IF;

  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER update_leaderboard_trigger
AFTER INSERT OR UPDATE OR DELETE ON match_player
FOR EACH ROW
EXECUTE FUNCTION update_leaderboard_function();

اگر قبلاً با تریگرها کار نکرده اید، ممکن است بزرگ و ترسناک به نظر برسد، اما کاری که انجام می دهد بسیار ساده است:

1. اگر شما INSERT به درون match_player جدول، امتیاز به جدول اضافه می کند leaderboard جدول.
2. اگر شما DELETE از match_player جدول، امتیاز را از جدول کسر می کند leaderboard جدول.
3. اگر شما UPDATE را match_player جدول، تغییر در نقاط را محاسبه می کند (از آنجایی که تابع به هر دو دسترسی دارد NEW و OLD نسخه ردیف) و تفاوت را در مورد اعمال می کند leaderboard جدول.

بنابراین این ماشه به طور خودکار تابلوی امتیازات شما را در زمان واقعی به روز نگه می دارد! (تا زمانی که این کار را نکنید TRUNCATE آن، حداقل).

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

نتیجه

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

شما چی فکر میکنید؟ آیا با هر دوی این معماری ها آشنایی دارید؟ آیا مورد سوم، چهارم یا پنجمی وجود دارد که باید در مورد آن صحبت کنیم؟

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