[آموزش] پارتیشن بندی جداول در PostgreSQL

[rezaonline.net]

پارتیشن بندی اساسا در دیتاهای بزرگ سبب افزایش سرعت (قابل ملموس) دستورات SELECT میشود.
و همانطور که از اسمش پیداست براین اساس هست که رکوردهای یک جدول را به چندین پارت کوچکتر تقسیم میکند و براساس نوع تقسیم بندی فقط در یک یا چند پارت کوچکتر عملیات جستجو را انجام میدهد که این نتیجه خوبی در دیتاهای بارکورد بالا خواهد داشت ، دیتابیس هایی چون MySQL (https://dev.mysql.com/doc/refman/5.7/en/partitioning-key.html) و MSSQL (http://www.sqlshack.com/database-table-partitioning-sql-server/)چنین قابلیتی رو دارا هستند که بسادگی پارتیشن بندی در آنها ایجاد کرد اما برای PostgreSQL (http://www.postgresql.org/docs/current/static/ddl-partitioning.html)کمی داستان متفاوت است. در حقیقت پارتیشن بندی در PostgreSQL با قابلیت وراثت جداول از یکدیگر قابل پیاده سازی میباشد به این ترتیب که یک جدول مادر ساخته میشود و جدولهای فرزند متعدد (که بعنوان پارت های جدا هستند) از جدول مادر ارث بری میکنند و وظیفه ذخیره داده ها را برعهده دارند .


فرض کنید ما تیبلی برای ذخیره سازی مطالب وبلاگ در نظر میگیرم

CREATE TABLE "post" (
"id" serial NOT NULL,
"title" character varying(255) NOT NULL,
"user_id" integer NOT NULL,
"story" text NOT NULL,
"status" smallint NOT NULL DEFAULT '1'
);

فرض کنیم قرار است نهایتا 3000 رکورد در این تیبل ذخیره شود ، خب ما میخواهیم این تیبل را به سه پارت 1000 رکوردی تقسیم کنیم براساس کلید اصلی !

ابتدا سه جدول میسازیم که از جدول مادر ارث بری کنند و در هر جدول حوزه مجاز id را با دستور check مشخص میکنیم .

CREATE TABLE POST1000
(
PRIMARY KEY (id) ,
CHECK(id>0 AND id<=1000)
) INHERITS (post);

CREATE TABLE POST2000
(
PRIMARY KEY (id) ,
CHECK(id>1000 AND id<=2000)
) INHERITS (post);

CREATE TABLE POST3000
(
PRIMARY KEY (id) ,
CHECK(id>2000 AND id<=3000)
) INHERITS (post);

سپس در جدولهای فرزند برای کلید id ، ایندکس تعریف میکنیم

CREATE INDEX "post1000_id" ON "post1000" ("id");
CREATE INDEX "post2000_id" ON "post2000" ("id");
CREATE INDEX "post3000_id" ON "post3000" ("id");

حالا باید یک تریگر بنویسیم که در زمان insert کردن به جدول post ، مقادیر را به جدول های فرزند منتقل کند .

CREATE OR REPLACE FUNCTION post_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF (NEW.id>0 and NEW.id<=1000) THEN
INSERT INTO post1000 VALUES (NEW.*);
ELSIF (NEW.id>1000 and NEW.id<=2000) THEN
INSERT INTO post2000 VALUES (NEW.*);
ELSIF (NEW.id>2000 and NEW.id<=3000) THEN
INSERT INTO post3000 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'id is not valid for insert in partition table!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_post_trigger
BEFORE INSERT ON post
FOR EACH ROW EXECUTE PROCEDURE post_trigger();

حالا تعدادی رکورد مختلف اضافه میکنیم .

INSERT INTO "post" ("id", "title", "user_id", "story", "status")
VALUES
('1', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('2', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('3', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('1000', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('1001', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('1002', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('1999', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('2000', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('2001', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('2002', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('2003', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('2999', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('2998', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('3000', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ,
('999', 'عنوان مطلب', '1', 'توضیحات مطلب', '1') ;

دقت کنید که پارامتر constraint_exclusion در فایل کانفیگ دیتابیس postgresql.conf برابر با ON باشد یا با دستور زیر اعمالش کنید

SET constraint_exclusion = on;

اکنون نتیجه کاملا گویاست !
http://blog.rezaonline.net/static/file/postgresql_part.png

ین آموزش روی رکوردهای پایین انجام شده ، دقت کنید که بستگی به دیتاهای شما دارد ، ترجیحا از پارتهای زیادی استفاده نکنید چون هر چند پارتیشن بندی سرعت خواندن را افزایش میدهد سرعت insert,update را کند میکند (البته نه به همان نسبت )

پس در استفاده از این قابلیت زیاده روی نکنید :)
با تشکر ، رضا شیخله

لینک منبع : http://www.rezaonline.net/blog/howto-create-postgresql-table-partitioning-with-inherits.html (http://www.rezaonline.net/blog/howto-create-postgresql-table-partitioning-with-inherits.html)