الـ Partitioning والـ VACUUM والمراقبة: Postgres على المدى الطويل

DROP TABLE IF EXISTS events CASCADE;
CREATE TABLE events (
  id         bigint GENERATED ALWAYS AS IDENTITY,
  created_at timestamptz NOT NULL,
  kind       text NOT NULL,
  PRIMARY KEY (id, created_at)  -- الـ PK لازم فيه عمود الـ partition
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_08 PARTITION OF events FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');
CREATE TABLE events_2026_09 PARTITION OF events FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
CREATE TABLE events_2026_10 PARTITION OF events FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
CREATE INDEX ON events (kind, created_at); -- بيتعمل على كل partition لوحده

INSERT INTO events (created_at, kind)
SELECT timestamptz '2026-08-01' + i * interval '5 minutes', CASE WHEN i % 3 = 0 THEN 'login' ELSE 'view' END
FROM generate_series(0, 26000) i;
ANALYZE events;

SELECT tableoid::regclass AS partition, count(*) FROM events GROUP BY 1 ORDER BY 1;

-- partition pruning: Postgres بيقرا الـ partition المطلوب بس
EXPLAIN (COSTS OFF) SELECT count(*) FROM events WHERE created_at >= '2026-09-10' AND created_at < '2026-09-11';

-- retention: مسح شهر كامل = DROP (فوري)، مش DELETE لملايين الصفوف
ALTER TABLE events DETACH PARTITION events_2026_08;
DROP TABLE events_2026_08;

-- مفيش partition للشهر ده
INSERT INTO events (created_at, kind) VALUES ('2027-01-15', 'view');

$ psql -q -f schema.sql
   partition    | count 
----------------+-------
 events_2026_08 |  8928
 events_2026_09 |  8640
 events_2026_10 |  8433
(3 rows)

                                                                         QUERY PLAN                                                                         
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate
   ->  Seq Scan on events_2026_09 events
         Filter: ((created_at >= '2026-09-10 00:00:00+00'::timestamp with time zone) AND (created_at < '2026-09-11 00:00:00+00'::timestamp with time zone))
(3 rows)

psql:schema.sql:29: ERROR:  no partition of relation "events" found for row
DETAIL:  Partition key of the failing row contains (created_at) = (2027-01-15 00:00:00+00).

import pg from "pg";

const url = process.env.DATABASE_URL;
const a = new pg.Client(url);
const b = new pg.Client(url);
await a.connect();
await b.connect();

await a.query(`DROP TABLE IF EXISTS counters;
  CREATE TABLE counters (id int PRIMARY KEY, n int) WITH (autovacuum_enabled = false);
  INSERT INTO counters SELECT i, 0 FROM generate_series(1, 10000) i;`);

// الإحصائيات بتتكتب كل شوية: بنجبرها تتكتب دلوقتي علشان نشوفها
const stats = async () => {
  await a.query("SELECT pg_stat_force_next_flush(), pg_stat_clear_snapshot()");
  return (await a.query(`SELECT n_dead_tup::int AS dead, pg_size_pretty(pg_relation_size('counters')) AS size
                         FROM pg_stat_user_tables WHERE relname = 'counters'`)).rows[0];
};

// كل UPDATE بيعمل نسخة جديدة، والقديمة بتبقى dead tuple
for (let i = 0; i < 5; i++) await a.query("UPDATE counters SET n = n + 1");
console.log("بعد 5 updates:", await stats());

// transaction مفتوحة من زمان في connection تانية
await b.query("BEGIN ISOLATION LEVEL REPEATABLE READ");
await b.query("SELECT 1 FROM counters LIMIT 1");
await a.query("UPDATE counters SET n = n + 1");
await a.query("VACUUM counters");
console.log("vacuum والـ transaction مفتوحة:", await stats());

await b.query("COMMIT");
await a.query("VACUUM counters");
console.log("vacuum بعد ما قفلت:", await stats());
await a.query("VACUUM FULL counters");
console.log("VACUUM FULL:", await stats());
await a.end();
await b.end();

$ node main.js
بعد 5 updates: { dead: 49805, size: '2120 kB' }
vacuum والـ transaction مفتوحة: { dead: 19935, size: '2472 kB' }
vacuum بعد ما قفلت: { dead: 0, size: '2472 kB' }
VACUUM FULL: { dead: 0, size: '360 kB' }

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT pg_stat_statements_reset() IS NOT NULL AS reset;

-- شوية شغل
DROP TABLE IF EXISTS t; CREATE TABLE t AS SELECT i, md5(i::text) AS h FROM generate_series(1, 200000) i;
SELECT count(*) FROM t WHERE h LIKE 'abc%';
SELECT count(*) FROM t WHERE h LIKE 'abc%';
SELECT count(*) FROM t WHERE i = 42;

-- أكتر queries بتاخد وقت إجمالي
SELECT calls, rows, left(query, 45) AS query
FROM pg_stat_statements WHERE query ILIKE '%from t%'
ORDER BY total_exec_time DESC LIMIT 3;

-- نسبة القراية من الـ memory (cache hit)
SELECT round(100.0 * sum(heap_blks_hit) / nullif(sum(heap_blks_hit + heap_blks_read), 0)) > 50 AS mostly_cached
FROM pg_statio_user_tables;

-- أكبر الجداول
SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total
FROM pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 2;

$ psql -q -f schema.sql
 reset 
-------
 t
(1 row)

 count 
-------
    42
(1 row)

 count 
-------
    42
(1 row)

 count 
-------
     1
(1 row)

 calls | rows |                 query                  
-------+------+----------------------------------------
     2 |    2 | SELECT count(*) FROM t WHERE h LIKE $1
     1 |    1 | SELECT count(*) FROM t WHERE i = $1
(2 rows)

 mostly_cached 
---------------
 t
(1 row)

    relname     |  total  
----------------+---------
 t              | 14 MB
 events_2026_09 | 1048 kB
(2 rows)

DROP TABLE IF EXISTS metrics CASCADE;
CREATE TABLE metrics (at timestamptz NOT NULL, name text NOT NULL, value float8 NOT NULL)
  PARTITION BY RANGE (at);


CREATE OR REPLACE FUNCTION ensure_month(d date) RETURNS text LANGUAGE plpgsql AS $$
DECLARE
  name  text := 'metrics_' || to_char(d, 'YYYY_MM');
  start date := date_trunc('month', d);
BEGIN
  IF to_regclass(name) IS NOT NULL THEN
    RETURN name || ' موجود';
  END IF;
  EXECUTE format('CREATE TABLE %I PARTITION OF metrics FOR VALUES FROM (%L) TO (%L)',
                 name, start, start + interval '1 month');
  RETURN name || ' اتعمل';
END $$;

CREATE OR REPLACE FUNCTION drop_before(d date) RETURNS SETOF text LANGUAGE plpgsql AS $$
DECLARE part text;
BEGIN
  FOR part IN
    SELECT c.relname FROM pg_inherits i JOIN pg_class c ON c.oid = i.inhrelid
    WHERE i.inhparent = 'metrics'::regclass ORDER BY 1
  LOOP
    IF to_date(right(part, 7), 'YYYY_MM') < date_trunc('month', d) THEN
      EXECUTE format('ALTER TABLE metrics DETACH PARTITION %I', part);
      EXECUTE format('DROP TABLE %I', part);
      RETURN NEXT part;
    END IF;
  END LOOP;
END $$;

SELECT ensure_month(d) FROM unnest(ARRAY['2026-09-15', '2026-10-01', '2026-10-20', '2026-11-01']::date[]) d;
SELECT drop_before('2026-10-01');
SELECT c.relname FROM pg_inherits i JOIN pg_class c ON c.oid = i.inhrelid
WHERE i.inhparent = 'metrics'::regclass ORDER BY 1;

$ psql -q -f schema.sql
     ensure_month      
-----------------------
 metrics_2026_09 اتعمل
 metrics_2026_10 اتعمل
 metrics_2026_10 موجود
 metrics_2026_11 اتعمل
(4 rows)

   drop_before   
-----------------
 metrics_2026_09
(1 row)

     relname     
-----------------
 metrics_2026_10
 metrics_2026_11
(2 rows)