ليه PostgreSQL؟ وإيه اللي ينتقل من SQL Server؟
PostgreSQL relational database مفتوحة المصدر، قوية في SQL standards، constraints، transactions، extensibility، و JSONB. لو فاهم relational modeling و joins و indexes في SQL Server، جزء كبير من المفاهيم ينتقل، لكن syntax والـ types والـ execution details مختلفة. لا تفترض إن SQL متطابق بين المحركين: راجع identity columns، date/time، pagination، upsert، JSON، locking، functions و query planner.
PostgreSQL يستخدم process-based architecture: اتصال العميل يتعامل عادة مع backend process، وفيه shared buffers و WAL و background processes. كمطور application، المهم تفهم connection pooling، transactions، WAL durability، vacuum/autovacuum، وقيود الاتصالات. لا تفتح connection لكل thread بلا إدارة؛ استخدم Npgsql pooling أو pool خارجي حسب deployment.
Tables و Data Types و Constraints
اختيار type قرار correctness قبل ما يكون storage optimization. استخدم integer/bigint للمعرفات والأعداد الصحيحة، numeric(precision, scale) للأموال والحسابات الدقيقة، timestamptz للحظة زمنية مطلقة، و date لتاريخ business بلا وقت. في PostgreSQL، timestamp with time zone يخزن لحظة زمنية ويعرضها وفق timezone الجلسة؛ لا يخزن اسم المنطقة الزمنية الأصلي. خزن zone ID منفصلًا لو business يحتاجها.
CREATE TABLE customers (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email varchar(320) NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT uq_customers_email UNIQUE (email)
);
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
total numeric(12,2) NOT NULL CHECK (total >= 0),
status text NOT NULL CHECK (status IN ('Pending','Paid','Cancelled')),
created_at timestamptz NOT NULL DEFAULT now()
);NOT NULL و CHECK و UNIQUE و FOREIGN KEY هي خط دفاع في database؛ لا تكتفِ بـ validation في API لأن concurrent requests أو scripts أو services أخرى قد تكتب مباشرة. اختَر ON DELETE behavior حسب business retention: حذف customer لا ينبغي بالضرورة حذف invoices أو orders التاريخية.
PostgreSQL يدعم enums كنوع database، لكن تغييره في schema له lifecycle مختلف عن lookup table أو text + CHECK. قيّم سهولة migrations وتوافق التطبيق قبل الاختيار. وفرق بين json و jsonb: JSON يحفظ النص، JSONB يخزن تمثيلًا ثنائيًا قابلًا للفهرسة والبحث بكفاءة أعلى في كثير من الاستخدامات.
SQL و JOINs و Upsert
الـ joins والـ grouping والـ subqueries نفس المفاهيم relational، لكن راجع أسماء functions و operators. PostgreSQL يدعم RETURNING لإرجاع الصفوف المتأثرة من INSERT/UPDATE/DELETE، ويدعم INSERT ... ON CONFLICT كآلية upsert ذرية مرتبطة بقيد أو index فريد.
INSERT INTO customers (email)
VALUES (@email)
ON CONFLICT (email)
DO UPDATE SET email = EXCLUDED.email
RETURNING id, email, created_at;لا تستخدم upsert لإخفاء business conflict. لو duplicate email يجب أن يرجع conflict للعميل، استخدم INSERT عادي وتعامل مع unique violation. أما لو المطلوب create-or-update فعلًا، ON CONFLICT يمنع race condition التي تحصل مع SELECT ثم INSERT منفصلين.
SELECT id, customer_id, total, created_at
FROM orders
WHERE status = @status
ORDER BY created_at DESC, id DESC
LIMIT @page_size OFFSET @offset;
UPDATE orders
SET status = 'Paid'
WHERE id = @id AND status = 'Pending'
RETURNING id, status;الـ OFFSET pagination تصبح أبطأ مع offsets كبيرة. استخدم keyset: WHERE (created_at, id) < (@last_created_at, @last_id) مع نفس ترتيب DESC. راجع ترتيب nulls و collation لو query تعتمد على text sorting.
Transactions و ACID و Isolation
PostgreSQL يستخدم MVCC (Multi-Version Concurrency Control): readers يرون snapshot متسقة بدل أن تمنع كل قراءة كل كتابة. التحديثات تنشئ row versions جديدة، والصفوف القديمة تحتاج vacuum عندما لا تعود snapshots تحتاجها. ده يقلل reader-writer blocking، لكنه لا يلغي locks أو deadlocks ولا يجعل كل transaction آمنة منطقيًا.
مستويات العزل الأساسية: Read Committed هو الافتراضي، وكل statement ترى snapshot عند بدايتها؛ Repeatable Read تحافظ على snapshot transaction-wide وتمنع non-repeatable reads، وقد تظهر serialization-style conflicts؛ Serializable يحاول ضمان نتيجة مكافئة لتنفيذ serial وقد يرفض transaction ويطلب retry. retry يجب أن يعيد transaction كاملة وبمنطق idempotent.
BEGIN;
UPDATE inventory
SET available = available - @quantity
WHERE product_id = @product_id
AND available >= @quantity;
-- Application checks affected row count.
-- If 0 rows: insufficient stock or product missing.
COMMIT;الـ update الشرطي أفضل من read balance ثم update منفصل من غير حماية. لو business operation تحتاج عدة rows، استخدم transaction مع isolation/locking مناسب. تجنب transaction طويلة أثناء انتظار HTTP provider؛ اعمل workflow/hold أو outbox حسب السيناريو.
Locks و Deadlocks و SKIP LOCKED
MVCC يقلل تعارض reader/writer لكنه لا يلغي row/table locks. UPDATE/DELETE يأخذ row-level locks، و DDL قد يأخذ locks قوية. deadlock يحصل عندما transactions تنتظر locks بترتيب دائري؛ PostgreSQL يكتشف cycle وي abort واحدة. قلل مدة transaction، احصل على rows بترتيب ثابت، لا تنتظر network داخل transaction، وتعامل مع deadlock retry محدودًا للعملية كاملة.
SELECT ... FOR UPDATE يقفل rows حتى نهاية transaction، مناسب لحالات claim/modify عندما conditional update وحده لا يكفي. SKIP LOCKED مفيد لعدة workers يسحبون jobs من queue table دون انتظار نفس row، لكنه لا يضمن FIFO صارمة وقد يتخطى rows مقفولة.
BEGIN;
WITH next_jobs AS
(
SELECT id
FROM background_jobs
WHERE status = 'Pending'
ORDER BY created_at, id
FOR UPDATE SKIP LOCKED
LIMIT 20
)
UPDATE background_jobs AS j
SET status = 'Processing', locked_at = now()
FROM next_jobs
WHERE j.id = next_jobs.id
RETURNING j.*;
COMMIT;Indexes و EXPLAIN ANALYZE
B-tree هو النوع الافتراضي الشائع ويدعم equality/range/order patterns. PostgreSQL يوفر أيضًا GIN للـ arrays/JSONB/full-text، GiST لأنواع بحث مكانية/مدى معينة، BRIN للجداول الضخمة المرتبة طبيعيًا تقريبًا. لا تختار نوع index بالاسم؛ اختاره من operators المستخدمة في query وحجم البيانات.
CREATE INDEX ix_orders_customer_created
ON orders (customer_id, created_at DESC, id DESC);
CREATE INDEX ix_orders_pending_created
ON orders (created_at DESC)
WHERE status = 'Pending';
CREATE INDEX ix_customers_lower_email
ON customers (lower(email));ترتيب composite index مهم: leading columns تؤثر على الاستفادة. Partial index يخزن subset عندما query predicate يطابق شرطه، و expression index يخدم تعبيرًا مثل lower(email). راجع حجم index وكلفة writes؛ كل index إضافي يحتاج صيانة ويزيد تكلفة INSERT/UPDATE/DELETE.
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 30;قارن estimated rows بـ actual rows، وراقب loops، shared hit/read blocks، sort method، sequential/index scans، ووقت كل node. EXPLAIN ANALYZE ينفذ query فعلًا؛ لا تشغله على UPDATE/DELETE production بلا فهم أو داخل transaction مناسبة. بعد تغييرات كبيرة في البيانات راجع statistics و autovacuum.
PostgreSQL Performance Checklist
- EXPLAIN ANALYZE مع BUFFERS على query ممثلة.
- قارن estimated/actual rows و loops و sort/hash spills.
- راجع index prefix و selectivity و partial/expression index.
- راجع bloat/autovacuum/statistics و long-running transactions.
- راقب connection pool و active sessions و locks.
- افحص replication lag و WAL growth عند read replicas.
- قارن query latency مع application serialization/network overhead.
Index Types و JSONB و EXPLAIN ANALYZE
B-tree هو الاختيار العام للمقارنات والترتيب، بينما GIN مناسب لأنواع مثل arrays و JSONB و full-text search، و GiST يخدم أنواعًا من range/geometric/search problems. اختيار index يعتمد على operator class والـ predicate الفعلي، وليس على اسم العمود فقط. Partial index يفهرس subset من الصفوف ويكون فعالًا عندما يتكرر predicate ثابت مثل WHERE is_active.
JSONB مناسب لبيانات شبه مرنة، لكنه لا يعني أن كل schema يجب أن يصبح document داخل عمود واحد. الحقول التي تدخل في joins أو constraints أو تقارير كثيرة قد تستحق أعمدة relational صريحة. استخدم GIN operator class المناسب لنمط الاستعلام، ثم قِس write amplification وحجم index.
CREATE INDEX ix_events_payload_gin
ON events USING GIN (payload jsonb_path_ops);
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, payload
FROM events
WHERE payload @> '{"kind":"payment"}'::jsonb;
-- Partial index for frequently queried active rows:
CREATE INDEX ix_orders_open_created
ON orders (created_at DESC)
WHERE status = 'open';EXPLAIN ANALYZE ينفذ الاستعلام بالفعل؛ لا تشغله على تعديل بيانات أو query مكلفة في production بلا احتياط. افحص actual rows و loops و buffers، ولا تعتمد على cost رقمًا بالمللي ثانية. تأكد أن query predicate يطابق شروط partial index منطقيًا.
JSONB و Full-Text Search
JSONB مفيد لبيانات شبه منظمة تختلف بين records، لكنه لا يعني تحويل كل schema إلى JSON. الحقول الأساسية المستخدمة في joins/constraints/reporting غالبًا تستحق أعمدة typed. ضع JSONB للخصائص المرنة أو metadata، مع schema validation في التطبيق أو database عند الحاجة.
CREATE TABLE products (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
attributes jsonb NOT NULL DEFAULT '{}'::jsonb
);
CREATE INDEX ix_products_attributes_gin
ON products USING GIN (attributes);
SELECT id, name
FROM products
WHERE attributes @> '{"color":"blue"}'::jsonb;GIN index قد يسرع containment queries، لكن اختيار operator class مهم. اختبر query الفعلية بـ EXPLAIN ANALYZE. Full-text search يستخدم tsvector لتمثيل document terms و tsquery للبحث، مع language configuration للـ stemming/stop words.
ALTER TABLE articles
ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''))
) STORED;
CREATE INDEX ix_articles_search
ON articles USING GIN (search_vector);
SELECT id, title
FROM articles
WHERE search_vector @@ plainto_tsquery('english', @query);Npgsql والاتصال من .NET
Npgsql هو ADO.NET provider لـ PostgreSQL، و Npgsql.EntityFrameworkCore.PostgreSQL هو EF Core provider. استخدم version متوافقًا مع EF Core major version. Npgsql يدعم pooling و async و parameters و COPY protocol لبعض عمليات bulk. لا تخلط بين package Npgsql منخفض المستوى و EF provider.
using Npgsql;
await using var dataSource = NpgsqlDataSource.Create(connectionString);
await using var command = dataSource.CreateCommand(
"SELECT id, email FROM customers WHERE email = $1");
command.Parameters.AddWithValue(email);
await using var reader = await command.ExecuteReaderAsync(ct);
while (await reader.ReadAsync(ct))
{
var id = reader.GetInt64(0);
var address = reader.GetString(1);
}في Npgsql الحديث، NpgsqlDataSource نقطة مركزية لإدارة إعدادات الاتصال والـ pool. لا تضع connection string في source code؛ استخدم secret/config provider. في production اضبط SSL/TLS، timeouts، pool sizing، command timeout، logging مع إخفاء parameters الحساسة.
builder.Services.AddDbContext<AppDbContext>(options =>
options.UseNpgsql(
builder.Configuration.GetConnectionString("Postgres"),
npgsql => npgsql.EnableRetryOnFailure()));Execution retry strategy لا تجعل أي transaction تلقائيًا آمنة لإعادة التنفيذ. لو فتحت transaction يدويًا مع retry strategy، استخدم execution strategy بالشكل الموصى به من provider وأعد العملية كوحدة واحدة، مع idempotency حيث يلزم.
Migrations و Schema Evolution
في production، migration لازم تراعي backward compatibility مع التطبيق أثناء rolling deployment. pattern expand/contract: أضف nullable column/dual-write، deploy code يدعم القديم والجديد، backfill batches، ثم اجعل column required أو احذف القديم في release لاحق. لا تعمل rename/drop column في نفس release قبل أن كل instances تتوقف عن استخدامها.
راجع lock impact لكل ALTER TABLE؛ بعض العمليات تحتاج table rewrite أو lock قوي. استخدم CREATE INDEX CONCURRENTLY عندما مناسب، مع العلم أنه لا يعمل داخل transaction block بالطريقة المعتادة. migration failure قد تترك index invalid أو partial state؛ جهز recovery instructions.
Npgsql Pooling و Batching و Type Mapping
NpgsqlDataSource نقطة مركزية للـ pooling/configuration. لا تنشئ DataSource لكل request. اضبط Maximum Pool Size/Timeout/Command Timeout وفق workload، وراقب pool wait. استخدم parameters typed عند ambiguous types، وتجنب إرسال huge arrays/JSON payload بلا قياس. Npgsql يدعم batching و COPY لبعض bulk workloads؛ COPY غالبًا أسرع للتحميل الكبير لكنه يتطلب validation/staging strategy.
EF Core provider version يجب يتوافق مع EF Core major version. عند mapping DateTime/DateTimeOffset راجع UTC/timestamptz semantics، وعند mapping enum/jsonb راجع provider configuration. لا تفترض أن SQL Server provider translation هي نفسها PostgreSQL provider؛ integration tests على PostgreSQL الفعلي مهمة.
pgvector و Vector Search Awareness
pgvector extension تضيف vector type وعمليات similarity داخل PostgreSQL. ممكن تكون مناسبة لو التطبيق يحتاج relational data و embeddings في نفس المكان، مع ACL joins و transactions. لكنها لا تجعل PostgreSQL تلقائيًا أفضل vector database لكل workload؛ قارن حجم vectors، recall/latency، indexing method، update rate، filtering، replication و operational constraints.
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE document_chunks (
id bigserial PRIMARY KEY,
tenant_id uuid NOT NULL,
content text NOT NULL,
embedding vector(1536) NOT NULL
);
CREATE INDEX ix_chunks_embedding
ON document_chunks USING hnsw (embedding vector_cosine_ops);
SELECT id, content
FROM document_chunks
WHERE tenant_id = @tenant_id
ORDER BY embedding <=> @query_embedding
LIMIT 8;أهم نقطة أمنية: tenant filter جزء من query وليس خطوة لاحقة بعد retrieval. راجع query plan مع filters؛ vector index و tenant filter قد يتفاعلان بطرق تؤثر على الأداء. سجّل model/dimension/version للـ embedding، ولا تخلط vectors بأبعاد أو نماذج مختلفة.
Hybrid search جوه PostgreSQL: full-text + pgvector + RRF
لو بياناتك أصلًا في PostgreSQL، تقدر تعمل hybrid retrieval من غير search engine منفصل: keyword ranking بـ tsvector/ts_rank_cd و semantic ranking بـ pgvector، وتدمج الترتيبين بـ Reciprocal Rank Fusion (RRF): كل مستند بياخد 1/(k + rank) من كل قائمة (k≈60) وبتجمعهم. RRF بيدمج ترتيب مش scores، فمش محتاج normalization لمقاييس مختلفة.
WITH kw AS (
SELECT id, row_number() OVER (ORDER BY ts_rank_cd(tsv, q) DESC) AS r
FROM document_chunks, websearch_to_tsquery('simple', @query_text) q
WHERE tenant_id = @tenant_id AND tsv @@ q
ORDER BY ts_rank_cd(tsv, q) DESC LIMIT 50
),
vec AS (
SELECT id, row_number() OVER (ORDER BY embedding <=> @query_embedding) AS r
FROM document_chunks
WHERE tenant_id = @tenant_id
ORDER BY embedding <=> @query_embedding LIMIT 50
)
SELECT c.id, c.content,
COALESCE(1.0 / (60 + kw.r), 0) + COALESCE(1.0 / (60 + vec.r), 0) AS rrf
FROM document_chunks c
LEFT JOIN kw ON kw.id = c.id
LEFT JOIN vec ON vec.id = c.id
WHERE kw.id IS NOT NULL OR vec.id IS NOT NULL
ORDER BY rrf DESC
LIMIT 8;
-- tsv is a generated column: to_tsvector('simple', normalized_content), indexed with GINالـ 'simple' configuration ما بيعملش stemming — مناسب للعربي بعد normalization يدوي، لأن PostgreSQL ما فيهوش Arabic stemmer مدمج. الـ keyword half بيلقط الأرقام والأكواد وأسماء المنتجات اللي الـ embeddings بتضيعها. المقارنة مع Elasticsearch/OpenSearch في درس Elasticsearch & OpenSearch.
Backup و Vacuum و PostgreSQL مقابل SQL Server
Backup strategy تعتمد على RPO/RTO: logical backups عبر pg_dump مناسبة لنسخ database/schema/data، و physical base backups مع WAL archiving تدعم point-in-time recovery. اختبار restore أهم من وجود backup file؛ نفّذ restore drills وتحقق من زمن الاستعادة والاتساق.
Autovacuum مهم لاسترجاع المساحة القابلة لإعادة الاستخدام وتحديث visibility map/statistics. لا توقفه كحل لمشكلة أداء قبل تشخيص bloat والـ transaction age. Long-running transactions قد تمنع تنظيف row versions القديمة. راقب connection count، locks، slow queries، replication lag، disk/WAL growth و autovacuum activity.
SQL Server و PostgreSQL كلاهما relational engines قوية. الاختيار يتأثر بالخبرة، licensing/hosting، ecosystem، SQL features، operational tooling، existing estate، workload و provider support. لا توجد قاعدة أن واحدًا أسرع دائمًا؛ benchmark على workload ممثل هو الفيصل.
VACUUM و Autovacuum و WAL
UPDATE/DELETE في MVCC يتركان dead tuples حتى يمكن تنظيفها بعد انتهاء snapshots التي تحتاج النسخ القديمة. VACUUM يجعل المساحة قابلة لإعادة الاستخدام ويحدث visibility map، بينما VACUUM FULL يعيد كتابة table ويحتاج lock أقوى ومساحة إضافية. Autovacuum يعمل تلقائيًا وفق thresholds/scales؛ لا توقفه بلا فهم.
WAL (Write-Ahead Log) يسجل تغييرات قبل كتابة data pages لضمان recovery. WAL archiving/replication slots قد تحتفظ بملفات كثيرة لو consumer متأخر أو slot abandoned. راقب disk usage و replication lag و oldest transaction age. long-running transaction قد تمنع vacuum من تنظيف tuples وتسبب bloat.
PostgreSQL MVCC و VACUUM و Index Maintenance
PostgreSQL MVCC يحتفظ بنسخ row versions حتى transactions القديمة لا تعود تحتاجها؛ UPDATE/DELETE ينشئان dead tuples بدل overwrite فوري في كل الحالات. VACUUM يسمح بإعادة استخدام المساحة داخل relation ويحافظ على visibility map؛ autovacuum مهم جدًا لمنع table bloat و transaction ID wraparound. long-running transactions قد تمنع cleanup وتسبب bloat.
راقب pg_stat_user_tables و dead tuples و autovacuum activity و long transactions. لا تشغل VACUUM FULL عشوائيًا في production؛ قد يحتاج lock قويًا ويعيد كتابة table. Indexes لها أنواع: B-tree للترتيب والمساواة/range، GIN لـ JSONB/arrays/full-text، GiST لأنواع spatial/range، و BRIN لبيانات كبيرة مرتبة طبيعيًا. اختر وفق operator class/query shape.
MVCC و VACUUM و Transaction ID
PostgreSQL يستخدم MVCC: UPDATE لا يعدّل tuple القديمة في مكانها ببساطة؛ ينشئ نسخة row جديدة ويحدد صلاحية النسخ حسب transaction visibility. ده يسمح للقراء والكتّاب بالتعايش بدرجة جيدة، لكنه ينتج dead tuples تحتاج vacuuming. VACUUM يعلّم المساحة قابلة لإعادة الاستخدام داخل relation، بينما VACUUM FULL يعيد كتابة الجدول ويحتاج lock أقوى ومساحة إضافية.
Autovacuum جزء أساسي من التشغيل، وليس مهمة تنظيف اختيارية. راقب dead tuples، transaction age، vacuum/analyze activity، وحجم الجداول. إهمال vacuum قد يؤدي إلى bloat ومشاكل transaction ID wraparound. لا تطفئ autovacuum كحل سريع لمشكلة performance دون خطة تشغيلية.
-- Operational inspection examples:
SELECT relname, n_live_tup, n_dead_tup,
last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;
SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;هذه views مؤشرات وليست قياسًا دقيقًا لحظيًا لكل tuple. اقرأها مع حجم الجدول ومعدل التحديث وسجلات autovacuum. ضبط thresholds يعتمد على حجم الجدول ونمط workload، لذلك لا تنسخ إعدادًا واحدًا لكل قواعد البيانات.
اختبر فهمك
ما الذي يميز MVCC؟
متى تستخدم JSONB بدل أعمدة relational؟
هل EXPLAIN ANALYZE مجرد تقدير؟
لماذا لا يكفي SELECT ثم INSERT لمنع duplicate email؟
المراجع والقراءة الإضافية
الدرس بيشرح الفكرة نفسها؛ الروابط دي للتحقق والتعمق والرجوع لتفاصيل الـ API. (محتاجة إنترنت.)
التوثيق الرسمي — Official documentation
مصادر تعليمية ومتقدمة — Third-party & advanced reading
خريطة التغطية: 18 عنصر من الـ Roadmap ومكان شرحه في الدرس
خلصت الدرس وحليت الأسئلة؟ علّم عليه عشان يتحسب في تقدّمك.