PostgreSQL: جداول PostgreSQL المقسمة ووراثة الجداول

-- الجدول الأم: مفتاح التقسيم ف��ط
CREATE TABLE orders (
    id          BIGINT GENERATED ALWAYS AS IDENTITY,
    user_id     BIGINT NOT NULL,
    order_date  DATE NOT NULL,
    total_amount NUMERIC(12,2),
    status      TEXT DEFAULT 'pending'
) PARTITION BY RANGE (order_date);

-- أقسام شهرية لعام 2024
CREATE TABLE orders_2024_01 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE orders_2024_02 PARTITION OF orders
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

CREATE TABLE orders_2024_03 PARTITION OF orders
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

-- القسم الافتراضي يلتقط الصفوف خارج النطاق
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

CREATE TABLE

-- توليد أقسام شهرية لسنة معينة
CREATE OR REPLACE FUNCTION create_monthly_partitions(
    p_parent  REGCLASS,
    p_year    INT
) RETURNS VOID AS $$
DECLARE
    m      INT;
    p_name TEXT;
    s_date TEXT;
    e_date TEXT;
BEGIN
    FOR m IN 1..12 LOOP
        p_name := format('%s_%s_%02s', p_parent::text, p_year, m);
        s_date := format('%s-%02s-01', p_year, m);
        e_date := format('%s-%02s-01', p_year, m + 1);
        EXECUTE format(
            'CREATE TABLE IF NOT EXISTS %I PARTITION OF %s
             FOR VALUES FROM (%L) TO (%L)',
            p_name, p_parent::text, s_date, e_date
        );
    END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT create_monthly_partitions('orders', 2025);

CREATE TABLE

INSERT INTO orders (user_id, order_date, total_amount, status)
VALUES
    (1001, '2024-01-15', 299.99, 'completed'),
    (1002, '2024-02-20', 159.50, 'shipped'),
    (1003, '2024-03-10', 89.00,  'pending');

-- استعلام مع تقليم الأقسام
SELECT * FROM orders
WHERE order_date BETWEEN '2024-02-01' AND '2024-02-29';

  id  | user_id | order_date | total_amount | status
------+---------+------------+--------------+--------
 1002 |    1002 | 2024-02-20 |       159.50 | shipped
(1 row)

CREATE TABLE users_by_region (
    id       BIGINT GENERATED ALWAYS AS IDENTITY,
    name     TEXT NOT NULL,
    region   TEXT NOT NULL,
    email    TEXT
) PARTITION BY LIST (region);

CREATE TABLE users_north_america PARTITION OF users_by_region
    FOR VALUES IN ('US', 'CA', 'MX');

CREATE TABLE users_europe PARTITION OF users_by_region
    FOR VALUES IN ('UK', 'DE', 'FR', 'ES');

CREATE TABLE users_asia PARTITION OF users_by_region
    FOR VALUES IN ('CN', 'JP', 'KR', 'SG');

CREATE TABLE users_other PARTITION OF users_by_region DEFAULT;

CREATE TABLE

CREATE TABLE access_logs (
    id         BIGINT GENERATED ALWAYS AS IDENTITY,
    user_id    BIGINT,
    action     TEXT,
    log_time   TIMESTAMPTZ DEFAULT now()
) PARTITION BY HASH (user_id);

-- إنشاء 8 أقسام تجزئة
CREATE TABLE access_logs_p0 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 0);
CREATE TABLE access_logs_p1 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 1);
CREATE TABLE access_logs_p2 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 2);
CREATE TABLE access_logs_p3 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 3);
CREATE TABLE access_logs_p4 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 4);
CREATE TABLE access_logs_p5 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 5);
CREATE TABLE access_logs_p6 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 6);
CREATE TABLE access_logs_p7 PARTITION OF access_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 7);

CREATE TABLE

CREATE TABLE order_details (
    id          BIGINT,
    order_id    BIGINT,
    order_date  DATE NOT NULL,
    region      TEXT NOT NULL,
    product_id  INT,
    quantity    INT
) PARTITION BY RANGE (order_date);

-- المستوى الأول: حسب الشهر
CREATE TABLE order_details_2024q1 PARTITION OF order_details
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01')
    PARTITION BY LIST (region);

-- المستوى الثاني: حسب المنطقة داخل كل نطاق شهر
CREATE TABLE order_details_2024q1_na PARTITION OF order_details_2024q1
    FOR VALUES IN ('US', 'CA', 'MX');

CREATE TABLE order_details_2024q1_eu PARTITION OF order_details_2024q1
    FOR VALUES IN ('UK', 'DE', 'FR');

CREATE TABLE

-- تمكين تقليم الأقسام (ON افتراضيًا)
SET enable_partition_pruning = on;

-- تحقق من الأقسام التي تم مسحها
EXPLAIN (COSTS OFF) SELECT * FROM orders
WHERE order_date = '2024-02-15';

Append
  -> Seq Scan on orders_2024_02
       Filter: (order_date = '2024-02-15'::date)

-- فصل قسم يناير 2023 (بدون فقدان بيانات)
ALTER TABLE orders DETACH PARTITION orders_2023_01;

-- الآن هو جدول مستقل، يمكن نقله إلى تخزين أرخص
ALTER TABLE orders_2023_01 SET TABLESPACE archive_tbs;

-- أو تصدير وإسقاط
COPY orders_2023_01 TO '/archive/orders_2023_01.csv';
DROP TABLE orders_2023_01;

-- SQL statement executed successfully

-- إنشاء جدول جديد أولاً
CREATE TABLE orders_2025_01 (LIKE orders INCLUDING DEFAULTS);

-- إضافة قيد تحقق للتحقق (يسرع ATTACH)
ALTER TABLE orders_2025_01
    ADD CONSTRAINT orders_2025_01_check
    CHECK (order_date >= '2025-01-01' AND order_date < '2025-02-01');

-- إرفاق بالأم (يأخذ قفلاً حصريًا لفترة وجيزة)
ALTER TABLE orders ATTACH PARTITION orders_2025_01
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE

-- فصل دون منع القراءات المتزامنة
ALTER TABLE orders DETACH PARTITION orders_2023_01 CONCURRENTLY;

-- SQL statement executed successfully

-- الفهرس على الأم ينتشر إلى جميع الأقسام
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- كل قسم يحصل على فهرسه الخاص
\d orders_2024_01

Indexes:
    "orders_2024_01_user_id_idx" btree (user_id)

-- هذا يفشل: فريد بدون مفتاح تقسيم
CREATE UNIQUE INDEX idx_orders_id ON orders (id);
-- ERROR: unique constraint must contain partition key

-- هذا يعمل: فريد مع مفتاح تقسيم
CREATE UNIQUE INDEX idx_orders_id_date ON orders (id, order_date);

CREATE TABLE

-- الجدول الأم
CREATE TABLE people (
    id    SERIAL PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT
);

-- الجدول الابن يرث جميع الأعمدة + يضيف إضافات
CREATE TABLE employees (
    salary    NUMERIC(10,2),
    dept      TEXT,
    hire_date DATE
) INHERITS (people);

-- إضافة مفتاح أساسي للابن
ALTER TABLE employees ADD PRIMARY KEY (id);

-- استعلام الأم + جميع الأبناء
SELECT * FROM people;

-- استعلام الأم فقط (بدون أبناء)
SELECT * FROM ONLY people;

-- تحقق من أي جدول يأتي كل صف
SELECT tableoid::regclass, * FROM people;

-- تحليل قسم محدد
ANALYZE orders_2024_02;

-- تحليل جميع الأقسام عبر الأم
ANALYZE orders;

-- التحقق من إحصائيات الأقسام
SELECT relname, n_live_tup, last_analyze
FROM pg_stat_user_tables
WHERE relname LIKE 'orders_%'
ORDER BY relname;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- ربط على مستوى الأقسام (PG 12+)
SET enable_partitionwise_join = on;

SELECT o.id, o.total_amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-03-31';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- جدول تدقيق أساسي
CREATE TABLE audit_log (
    id         BIGINT GENERATED ALWAYS AS IDENTITY,
    table_name TEXT NOT NULL,
    action     TEXT NOT NULL,
    changed_at TIMESTAMPTZ DEFAULT now()
) PARTITION BY RANGE (changed_at);

-- أقسام شهرية، كل منها موروث بجداول نوعية محددة
CREATE TABLE audit_log_2024_01 PARTITION OF audit_log
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- أعمدة إضافية لنوع تدقيق محدد عبر الوراثة
CREATE TABLE audit_log_user_changes (
    old_email TEXT,
    new_email TEXT
) INHERITS (audit_log_2024_01);

CREATE TABLE

-- إعداد تقسيم شهري كامل لطلبات التجارة الإلكترونية
CREATE TABLE orders (
    id           BIGINT GENERATED ALWAYS AS IDENTITY,
    user_id      BIGINT NOT NULL,
    order_date   DATE NOT NULL,
    total_amount NUMERIC(12,2) DEFAULT 0,
    status       TEXT DEFAULT 'pending',
    created_at   TIMESTAMPTZ DEFAULT now()
) PARTITION BY RANGE (order_date);

-- إنشاء أقسام للربع الأول 2024
CREATE TABLE orders_2024_01 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE orders_2024_02 PARTITION OF orders
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE orders_2024_03 PARTITION OF orders
    FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

-- الفهرس الفريد يجب أن يتضمن مفتاح التقسيم
CREATE UNIQUE INDEX idx_orders_id_date ON orders (id, order_date);
CREATE INDEX idx_orders_user_date ON orders (user_id, order_date);

-- إدراج بيانات اختبار
INSERT INTO orders (user_id, order_date, total_amount, status) VALUES
    (1001, '2024-01-05', 299.99, 'completed'),
    (1001, '2024-02-14', 159.50, 'shipped'),
    (1002, '2024-01-20', 450.00, 'completed'),
    (1002, '2024-03-01', 89.00,  'pending'),
    (1003, '2024-02-28', 1200.00,'completed');

-- التحقق من تقليم الأقسام
EXPLAIN (COSTS OFF) SELECT * FROM orders
    WHERE order_date BETWEEN '2024-02-01' AND '2024-02-28';

-- فصل قسم قديم للأرشفة
ALTER TABLE orders DETACH PARTITION orders_2024_01;
COPY orders_2024_01 TO '/archive/orders_2024_01.csv';

-- إرفاق قسم جديد للربع التالي
CREATE TABLE orders_2024_04 (LIKE orders INCLUDING DEFAULTS);
ALTER TABLE orders_2024_04
    ADD CONSTRAINT chk_2024_04
    CHECK (order_date >= '2024-04-01' AND order_date < '2024-05-01');
ALTER TABLE orders ATTACH PARTITION orders_2024_04
    FOR VALUES FROM ('2024-04-01') TO ('2024-05-01');

-- مراقبة أحجام الأقسا��
SELECT relname,
       pg_size_pretty(pg_total_relation_size(oid)) AS size,
       reltuples::bigint AS row_estimate
FROM pg_class
WHERE relname LIKE 'orders_2024%' ORDER BY relname;