EnglishEnglish العربيةالعربية PortuguêsPortuguês Japanese日本語
404 Not Found

404 Not Found

nginx

Comprehensive Project: Building an E-commerce Database System from Scratch

1. What You'll Learn

2. The Story

Bob and Alice are launching a cross-border e-commerce platform for the Middle East. The requirements are complex: Arabic full-text search, JSONB dynamic product attributes, orders partitioned by month, row-level security to isolate tenants, and AI product recommendations. They solve it all with PostgreSQL in one place—tsvector for Arabic search, jsonb for flexible SKUs, declarative partitioning for hundreds of millions of orders, pgvector for semantic recommendations, and postgres_fdw to migrate data from the legacy PG. A single PG database replaces MySQL + Elasticsearch + Redis + a recommendation engine.

3. Concept: Requirements Analysis and Architecture Design

(1) Business Module Breakdown

Module Core tables Key features
User system users, user_addresses RLS row-level security, bcrypt passwords
Categories & products categories, products JSONB dynamic attributes, full-text search
Orders & order items orders, order_items Monthly RANGE partitioning
Shopping cart cart_items High-concurrency UPSERT
Payments payments Enum types, audit trail
Shipping tracking shipments, shipment_events Time-series data, JSONB events
Reviews & ratings reviews Star aggregation, GIN index
Data reporting mv_daily_sales, etc. Materialized views, scheduled refresh

(2) Complete ER Diagram

100% 
erDiagram
    USERS ||--o{ USER_ADDRESSES : "has"
    USERS ||--o{ ORDERS : "places"
    USERS ||--o{ CART_ITEMS : "has"
    USERS ||--o{ REVIEWS : "writes"
    CATEGORIES ||--o{ CATEGORIES : "parent"
    CATEGORIES ||--o{ PRODUCTS : "contains"
    PRODUCTS ||--o{ ORDER_ITEMS : "included_in"
    PRODUCTS ||--o{ CART_ITEMS : "added_to"
    PRODUCTS ||--o{ REVIEWS : "reviewed_in"
    ORDERS ||--o{ ORDER_ITEMS : "contains"
    ORDERS ||--o{ PAYMENTS : "paid_by"
    ORDERS ||--o{ SHIPMENTS : "shipped_via"
    SHIPMENTS ||--o{ SHIPMENT_EVENTS : "tracked_by"

    USERS {
        bigint id PK
        text email UK
        text password_hash
        text role
        timestamptz created_at
    }
    USER_ADDRESSES {
        bigint id PK
        bigint user_id FK
        text address_line
        text city
        text country
    }
    CATEGORIES {
        int id PK
        text name
        int parent_id FK
        int sort_order
    }
    PRODUCTS {
        bigint id PK
        text name
        text name_ar
        int category_id FK
        numeric price
        jsonb attributes
        tsvector search_vector
        vector embedding
    }
    ORDERS {
        bigint id PK
        bigint user_id FK
        date order_date
        numeric total_amount
        text status
    }
    ORDER_ITEMS {
        bigint id PK
        bigint order_id FK
        bigint product_id FK
        int quantity
        numeric unit_price
    }
    CART_ITEMS {
        bigint id PK
        bigint user_id FK
        bigint product_id FK
        int quantity
    }
    PAYMENTS {
        bigint id PK
        bigint order_id FK
        text method
        numeric amount
        text status
        timestamptz paid_at
    }
    SHIPMENTS {
        bigint id PK
        bigint order_id FK
        text carrier
        text tracking_code
        text status
    }
    SHIPMENT_EVENTS {
        bigint id PK
        bigint shipment_id FK
        text event_type
        jsonb metadata
        timestamptz event_time
    }
    REVIEWS {
        bigint id PK
        bigint user_id FK
        bigint product_id FK
        int rating
        text comment
    }

(3) PostgreSQL vs MySQL Selection Report

Dimension PostgreSQL MySQL
JSONB dynamic attributes Native jsonb + GIN index + operators JSON type, but weak indexing
Full-text search Built-in tsvector/tsquery, multi-language No native support, needs Elasticsearch
Vector search pgvector native extension Needs an external service
Partitioning Declarative RANGE/LIST/HASH 8.0+ supports, but weaker
Row-level security RLS policies Not supported
Materialized views Native support + scheduled refresh Not supported
Extension ecosystem Rich (PostGIS/pgcrypto/FDW) Fewer plugins
Complex queries Window functions/CTE/LATERAL 8.0+ gradually supports
Operational maturity High, autovacuum/PITR High, mature master-slave replication
Community activity Fastest-growing Largest user base

Conclusion: this project needs JSONB dynamic attributes, full-text search, vector search, RLS, partitioning, and materialized views—PostgreSQL supports all of them natively, while MySQL would need 4+ external middleware components, so PostgreSQL is the choice.

4. Operation: Module 1 — User System

(1) Users and Addresses Tables

SQL 
CREATE TABLE users (
    id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email         TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    role          TEXT NOT NULL DEFAULT 'customer'
                    CHECK (role IN ('customer','vendor','admin')),
    tenant_id     BIGINT DEFAULT 1,
    created_at    TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE user_addresses (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id      BIGINT NOT NULL REFERENCES users(id),
    address_line TEXT NOT NULL,
    city         TEXT NOT NULL,
    country      TEXT NOT NULL,
    is_default   BOOLEAN DEFAULT false
);

▶ Example: RLS Row-Level Security for Tenant Isolation

SQL 
-- Enable RLS on users table
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Tenant isolation: users can only see same tenant
CREATE POLICY tenant_isolation ON users
    USING (tenant_id = current_setting('app.tenant_id')::bigint);

-- Admin can see all
CREATE POLICY admin_all_access ON users
    USING (role = 'admin');

-- Set tenant context per session
SET app.tenant_id = '1';
SELECT * FROM users;

Output:

TEXT 
CREATE TABLE

▶ Example: Password Hash Registration

SQL 
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Register with bcrypt hash
INSERT INTO users (email, password_hash, role, tenant_id)
VALUES (
    'alice@example.com',
    crypt('SecurePass123', gen_salt('bf')),
    'customer',
    1
);

-- Verify login
SELECT id, role FROM users
WHERE email = 'alice@example.com'
  AND password_hash = crypt('SecurePass123', password_hash);

Output:

TEXT 
INSERT 0 1

5. Operation: Module 2 — Categories and Products

▶ Example: Self-Referencing Category Tree

SQL 
CREATE TABLE categories (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    name_ar     TEXT,
    parent_id   INT REFERENCES categories(id),
    sort_order  INT DEFAULT 0
);

INSERT INTO categories (name, name_ar, parent_id, sort_order) VALUES
    ('Electronics', 'إلكترونيات', NULL, 1),
    ('Phones', 'هواتف', 1, 1),
    ('Laptops', 'حاسبات', 1, 2),
    ('Clothing', 'ملابس', NULL, 2);

-- Recursive query: category tree
WITH RECURSIVE cat_tree AS (
    SELECT id, name, name_ar, parent_id, 0 AS level
    FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.name_ar, c.parent_id, ct.level + 1
    FROM categories c JOIN cat_tree ct ON c.parent_id = ct.id
)
SELECT repeat('  ', level) || name AS tree, name_ar
FROM cat_tree ORDER BY level, sort_order;

Output:

TEXT 
INSERT 0 1

▶ Example: Products Table with JSONB Dynamic Attributes

SQL 
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE products (
    id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name          TEXT NOT NULL,
    name_ar       TEXT,
    category_id   INT NOT NULL REFERENCES categories(id),
    price         NUMERIC(12,2) NOT NULL,
    attributes    JSONB DEFAULT '{}',
    search_vector TSVECTOR GENERATED ALWAYS AS (
        setweight(to_tsvector('simple', coalesce(name, '')), 'A') ||
        setweight(to_tsvector('simple', coalesce(name_ar, '')), 'B')
    ) STORED,
    embedding     vector(1536)
);

Output:

TEXT 
CREATE TABLE

▶ Example: JSONB Attribute Query

SQL 
-- Insert with dynamic attributes
INSERT INTO products (name, name_ar, category_id, price, attributes) VALUES
    ('iPhone 15 Pro', 'آيفون 15 برو', 2, 1199.00,
     '{"color": "titanium", "storage": "256GB", "5g": true}'::jsonb),
    ('MacBook Air M3', 'ماك بوك إير', 3, 1299.00,
     '{"color": "midnight", "ram": "16GB", "screen": "15 inch"}'::jsonb);

-- Find 5G phones under 1200 USD
SELECT name, price, attributes->>'storage' AS storage
FROM products
WHERE attributes @> '{"5g": true}'::jsonb
  AND price < 1200;

-- GIN index for JSONB containment queries
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

Output:

TEXT 
INSERT 0 1

▶ Example: Full-Text Search (including Arabic)

SQL 
-- GIN index for full-text search
CREATE INDEX idx_products_search ON products USING GIN (search_vector);

-- Search in English or Arabic
SELECT name, name_ar, ts_rank(search_vector, q) AS rank
FROM products, plainto_tsquery('simple', 'iphone') q
WHERE search_vector @@ q
ORDER BY rank DESC;

-- Arabic search
SELECT name, name_ar
FROM products, plainto_tsquery('simple', 'آيفون') q
WHERE search_vector @@ q;

Output:

TEXT 
CREATE TABLE

▶ Example: pgvector Similar-Product Recommendation

SQL 
-- HNSW index for vector search
CREATE INDEX idx_products_embedding ON products
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- Find similar products
SELECT p2.name, p2.price,
       p2.embedding <=> p1.embedding AS distance
FROM products p1
CROSS JOIN LATERAL (
    SELECT * FROM products
    WHERE id != p1.id
    ORDER BY embedding <=> p1.embedding
    LIMIT 3
) p2
WHERE p1.name = 'iPhone 15 Pro';

Output:

TEXT 
CREATE TABLE

6. Operation: Module 3 — Orders and Order Items

▶ Example: Monthly RANGE-Partitioned Orders Table

SQL 
CREATE TABLE orders (
    id           BIGINT GENERATED ALWAYS AS IDENTITY,
    user_id      BIGINT NOT NULL REFERENCES users(id),
    order_date   DATE NOT NULL DEFAULT current_date,
    total_amount NUMERIC(12,2) DEFAULT 0,
    status       TEXT DEFAULT 'pending'
                  CHECK (status IN ('pending','paid','shipped','completed','cancelled')),
    created_at   TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (order_date);

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 order_items (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id    BIGINT NOT NULL,
    order_date  DATE NOT NULL,
    product_id  BIGINT NOT NULL REFERENCES products(id),
    quantity    INT NOT NULL CHECK (quantity > 0),
    unit_price  NUMERIC(12,2) NOT NULL,
    FOREIGN KEY (order_id, order_date) REFERENCES orders(id, order_date)
);

CREATE INDEX idx_orders_user ON orders (user_id, order_date);
CREATE INDEX idx_order_items_order ON order_items (order_id, order_date);

Output:

TEXT 
CREATE TABLE

▶ Example: Order Creation and Amount Calculation

SQL 
-- Create order
WITH new_order AS (
    INSERT INTO orders (user_id, order_date, status)
    VALUES (1, '2024-01-15', 'pending')
    RETURNING id, order_date
)
INSERT INTO order_items (order_id, order_date, product_id, quantity, unit_price)
SELECT new_order.id, new_order.order_date, p.id, 2, p.price
FROM new_order, products p
WHERE p.name = 'iPhone 15 Pro';

-- Update order total
UPDATE orders o SET total_amount = (
    SELECT SUM(quantity * unit_price)
    FROM order_items oi
    WHERE oi.order_id = o.id AND oi.order_date = o.order_date
)
WHERE o.id = 1 AND o.order_date = '2024-01-15';

Output:

TEXT 
  result  
----------
   42.50
(1 row)

7. Operation: Module 4 — Shopping Cart

▶ Example: UPSERT Shopping Cart

SQL 
CREATE TABLE cart_items (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id    BIGINT NOT NULL REFERENCES users(id),
    product_id BIGINT NOT NULL REFERENCES products(id),
    quantity   INT NOT NULL DEFAULT 1 CHECK (quantity > 0),
    added_at   TIMESTAMPTZ DEFAULT now(),
    UNIQUE (user_id, product_id)
);

-- Add or update cart item (UPSERT)
INSERT INTO cart_items (user_id, product_id, quantity)
VALUES (1, 1, 1)
ON CONFLICT (user_id, product_id)
DO UPDATE SET quantity = cart_items.quantity + EXCLUDED.quantity;

-- View cart with product details
SELECT p.name, p.price, ci.quantity,
       p.price * ci.quantity AS line_total
FROM cart_items ci
JOIN products p ON ci.product_id = p.id
WHERE ci.user_id = 1;

Output:

TEXT 
INSERT 0 1

8. Operation: Module 5 — Payments

▶ Example: Payments Table and Enum Types

SQL 
CREATE TYPE payment_method AS ENUM ('credit_card', 'paypal', 'bank_transfer', 'cod');
CREATE TYPE payment_status AS ENUM ('pending', 'completed', 'failed', 'refunded');

CREATE TABLE payments (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id   BIGINT NOT NULL,
    order_date DATE NOT NULL,
    method     payment_method NOT NULL,
    amount     NUMERIC(12,2) NOT NULL,
    status     payment_status DEFAULT 'pending',
    paid_at    TIMESTAMPTZ,
    FOREIGN KEY (order_id, order_date) REFERENCES orders(id, order_date)
);

-- Record successful payment
INSERT INTO payments (order_id, order_date, method, amount, status, paid_at)
VALUES (1, '2024-01-15', 'credit_card', 2398.00, 'completed', now());

-- Update order status after payment
UPDATE orders SET status = 'paid'
WHERE id = 1 AND order_date = '2024-01-15';

Output:

TEXT 
INSERT 0 1

9. Operation: Module 6 — Shipping Tracking

▶ Example: Shipping Tables and JSONB Events

SQL 
CREATE TABLE shipments (
    id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id      BIGINT NOT NULL,
    order_date    DATE NOT NULL,
    carrier       TEXT NOT NULL,
    tracking_code TEXT NOT NULL UNIQUE,
    status        TEXT DEFAULT 'created'
                   CHECK (status IN ('created','in_transit','delivered','failed')),
    FOREIGN KEY (order_id, order_date) REFERENCES orders(id, order_date)
);

CREATE TABLE shipment_events (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    shipment_id  BIGINT NOT NULL REFERENCES shipments(id),
    event_type   TEXT NOT NULL,
    metadata     JSONB DEFAULT '{}',
    event_time   TIMESTAMPTZ DEFAULT now()
);

-- Track shipment with events
INSERT INTO shipments (order_id, order_date, carrier, tracking_code)
VALUES (1, '2024-01-15', 'DHL Express', 'DHL123456789');

INSERT INTO shipment_events (shipment_id, event_type, metadata) VALUES
    (1, 'picked_up',     '{"location": "Dubai Warehouse"}'::jsonb),
    (1, 'in_transit',    '{"location": "Bahrain Hub", "eta": "2024-01-18"}'::jsonb),
    (1, 'out_for_delivery', '{"location": "Riyadh"}'::jsonb);

-- Shipment timeline
SELECT se.event_time, se.event_type, se.metadata->>'location' AS location
FROM shipment_events se
WHERE se.shipment_id = 1
ORDER BY se.event_time;

Output:

TEXT 
INSERT 0 1

10. Operation: Module 7 — Reviews and Ratings

▶ Example: Reviews Table and Star Aggregation

SQL 
CREATE TABLE reviews (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id    BIGINT NOT NULL REFERENCES users(id),
    product_id BIGINT NOT NULL REFERENCES products(id),
    rating     INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment    TEXT,
    created_at TIMESTAMPTZ DEFAULT now(),
    UNIQUE (user_id, product_id)
);

INSERT INTO reviews (user_id, product_id, rating, comment) VALUES
    (1, 1, 5, 'Excellent phone, fast delivery'),
    (2, 1, 4, 'Good but expensive'),
    (3, 2, 5, 'Best laptop ever');

-- Product rating summary with window function
SELECT p.name,
       COUNT(r.id) AS review_count,
       AVG(r.rating)::numeric(3,2) AS avg_rating,
       COUNT(r.id) FILTER (WHERE r.rating = 5) AS five_star,
       COUNT(r.id) FILTER (WHERE r.rating = 4) AS four_star
FROM products p
LEFT JOIN reviews r ON r.product_id = p.id
GROUP BY p.id, p.name;

Output:

TEXT 
 count 
-------
     5
(1 row)

11. Operation: Module 8 — Data Reporting

▶ Example: Daily-Sales Materialized View

SQL 
CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT order_date,
       COUNT(DISTINCT user_id) AS unique_buyers,
       COUNT(*) AS order_count,
       SUM(total_amount) AS daily_revenue,
       AVG(total_amount)::numeric(12,2) AS avg_order_value
FROM orders
WHERE status = 'completed'
GROUP BY order_date
ORDER BY order_date;

CREATE UNIQUE INDEX idx_mv_daily_sales_date ON mv_daily_sales (order_date);

-- Refresh daily (can be scheduled with pg_cron)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;

-- Top revenue days
SELECT order_date, daily_revenue, order_count
FROM mv_daily_sales
ORDER BY daily_revenue DESC
LIMIT 10;

Output:

TEXT 
 count 
-------
     5
(1 row)

▶ Example: Category-Sales Materialized View

SQL 
CREATE MATERIALIZED VIEW mv_category_sales AS
SELECT c.name AS category,
       p.name AS product_name,
       SUM(oi.quantity) AS total_sold,
       SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN categories c ON p.category_id = c.id
JOIN orders o ON oi.order_id = o.id AND oi.order_date = o.order_date
WHERE o.status = 'completed'
GROUP BY c.name, p.name
ORDER BY revenue DESC;

REFRESH MATERIALIZED VIEW mv_category_sales;

Output:

TEXT 
  result  
----------
   42.50
(1 row)

12. Operation: Stored Procedures and Automation

▶ Example: Order-Creation Stored Procedure

SQL 
CREATE OR REPLACE PROCEDURE create_order(
    p_user_id    BIGINT,
    p_items      JSONB
)
LANGUAGE plpgsql AS 
DECLARE
    v_order_id   BIGINT;
    v_order_date DATE := current_date;
    v_item       JSONB;
BEGIN
    INSERT INTO orders (user_id, order_date, status)
    VALUES (p_user_id, v_order_date, 'pending')
    RETURNING id INTO v_order_id;

    FOR v_item IN SELECT * FROM jsonb_array_elements(p_items)
    LOOP
        INSERT INTO order_items (order_id, order_date, product_id, quantity, unit_price)
        VALUES (v_order_id, v_order_date,
                (v_item->>'product_id')::bigint,
                (v_item->>'quantity')::int,
                (SELECT price FROM products WHERE id = (v_item->>'product_id')::bigint));
    END LOOP;

    UPDATE orders SET total_amount = (
        SELECT SUM(quantity * unit_price) FROM order_items
        WHERE order_id = v_order_id AND order_date = v_order_date
    ) WHERE id = v_order_id AND order_date = v_order_date;

    COMMIT;
END;
;

-- Call procedure
CALL create_order(1, '[
    {"product_id": 1, "quantity": 1},
    {"product_id": 2, "quantity": 2}
]'::jsonb);

Output:

TEXT 
  result  
----------
   42.50
(1 row)

▶ Example: Stored Procedure for Automatic Partition Maintenance

SQL 
CREATE OR REPLACE FUNCTION maintain_order_partitions()
RETURNS VOID AS 
DECLARE
    v_next_month DATE;
    v_part_name  TEXT;
BEGIN
    v_next_month := date_trunc('month', current_date + interval '1 month')::date;
    v_part_name  := 'orders_' || to_char(v_next_month, 'YYYY_MM');

    IF NOT EXISTS (
        SELECT 1 FROM pg_class WHERE relname = v_part_name
    ) THEN
        EXECUTE format(
            'CREATE TABLE %I PARTITION OF orders
             FOR VALUES FROM (%L) TO (%L)',
            v_part_name,
            v_next_month,
            (v_next_month + interval '1 month')::date
        );
    END IF;

    -- Detach partitions older than 2 years
    FOR v_part_name IN
        SELECT relname FROM pg_class
        WHERE relname LIKE 'orders_20__%'
          AND relkind = 'r'
          AND relname < 'orders_' || to_char(current_date - interval '2 years', 'YYYY_MM')
    LOOP
        EXECUTE format('ALTER TABLE orders DETACH PARTITION %I', v_part_name);
    END LOOP;
END;
 LANGUAGE plpgsql;

Output:

TEXT 
CREATE TABLE

13. Operation: FDW Data Migration and PITR Backup

▶ Example: Migrating from the Legacy PG with postgres_fdw

SQL 
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER legacy_pg FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '10.0.1.50', port '5432', dbname 'legacy_shop');

CREATE USER MAPPING FOR current_user SERVER legacy_pg
    OPTIONS (user 'migrate_user', password 'secure_pass');

IMPORT FOREIGN SCHEMA public LIMIT TO (old_users, old_products)
    FROM SERVER legacy_pg INTO legacy;

-- Migrate users with password re-hash
INSERT INTO users (email, password_hash, role, tenant_id, created_at)
SELECT email, crypt(raw_password, gen_salt('bf')), 'customer', 1, created_at
FROM legacy.old_users
ON CONFLICT (email) DO NOTHING;

Output:

TEXT 
INSERT 0 1

▶ Example: PITR Backup Strategy

BASH 
# Base backup
pg_basebackup -D /backup/base -Ft -z -P

# Archive WAL (postgresql.conf)
wal_level = replica
archive_mode = on
archive_command = 'cp %p /backup/wal/%f'

# Restore to point in time
pg_restore --target-time='2024-03-15 14:30:00' -d shop_db /backup/base

Output:

TEXT 
# command executed successfully
Strategy Frequency Retention Recovery time
pg_basebackup full Daily 7 days 30-60 min
WAL archiving Continuous 7 days Any point in time
Logical backup pg_dump Weekly 4 weeks 1-4 hours

14. Comprehensive Example

SQL 
-- Complete e-commerce database initialization script
-- Module 1: User system
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT 'customer'
         CHECK (role IN ('customer','vendor','admin')),
    tenant_id BIGINT DEFAULT 1,
    created_at TIMESTAMPTZ DEFAULT now()
);
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON users
    USING (tenant_id = current_setting('app.tenant_id')::bigint);

CREATE TABLE user_addresses (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    address_line TEXT NOT NULL, city TEXT NOT NULL, country TEXT NOT NULL,
    is_default BOOLEAN DEFAULT false
);

-- Module 2: Categories & Products
CREATE TABLE categories (
    id SERIAL PRIMARY KEY, name TEXT NOT NULL,
    name_ar TEXT, parent_id INT REFERENCES categories(id),
    sort_order INT DEFAULT 0
);
CREATE TABLE products (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL, name_ar TEXT,
    category_id INT NOT NULL REFERENCES categories(id),
    price NUMERIC(12,2) NOT NULL,
    attributes JSONB DEFAULT '{}',
    search_vector TSVECTOR GENERATED ALWAYS AS (
        setweight(to_tsvector('simple', coalesce(name,'')), 'A') ||
        setweight(to_tsvector('simple', coalesce(name_ar,'')), 'B')
    ) STORED,
    embedding vector(1536)
);
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);
CREATE INDEX idx_products_search ON products USING GIN (search_vector);
CREATE INDEX idx_products_embedding ON products
    USING hnsw (embedding vector_cosine_ops) WITH (m=16, ef_construction=64);

-- Module 3: Orders (monthly partitioned)
CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    order_date DATE NOT NULL DEFAULT current_date,
    total_amount NUMERIC(12,2) DEFAULT 0,
    status TEXT DEFAULT 'pending'
           CHECK (status IN ('pending','paid','shipped','completed','cancelled')),
    created_at TIMESTAMPTZ DEFAULT now(),
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (order_date);
CREATE TABLE orders_2024_q1 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

CREATE TABLE order_items (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id BIGINT NOT NULL, order_date DATE NOT NULL,
    product_id BIGINT NOT NULL REFERENCES products(id),
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(12,2) NOT NULL,
    FOREIGN KEY (order_id, order_date) REFERENCES orders(id, order_date)
);

-- Module 4: Cart
CREATE TABLE cart_items (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    product_id BIGINT NOT NULL REFERENCES products(id),
    quantity INT NOT NULL DEFAULT 1, added_at TIMESTAMPTZ DEFAULT now(),
    UNIQUE (user_id, product_id)
);

-- Module 5: Payments
CREATE TYPE payment_method AS ENUM ('credit_card','paypal','bank_transfer','cod');
CREATE TYPE payment_status AS ENUM ('pending','completed','failed','refunded');
CREATE TABLE payments (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id BIGINT NOT NULL, order_date DATE NOT NULL,
    method payment_method NOT NULL, amount NUMERIC(12,2) NOT NULL,
    status payment_status DEFAULT 'pending', paid_at TIMESTAMPTZ,
    FOREIGN KEY (order_id, order_date) REFERENCES orders(id, order_date)
);

-- Module 6: Shipping
CREATE TABLE shipments (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id BIGINT NOT NULL, order_date DATE NOT NULL,
    carrier TEXT NOT NULL, tracking_code TEXT NOT NULL UNIQUE,
    status TEXT DEFAULT 'created'
           CHECK (status IN ('created','in_transit','delivered','failed')),
    FOREIGN KEY (order_id, order_date) REFERENCES orders(id, order_date)
);
CREATE TABLE shipment_events (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    shipment_id BIGINT NOT NULL REFERENCES shipments(id),
    event_type TEXT NOT NULL, metadata JSONB DEFAULT '{}',
    event_time TIMESTAMPTZ DEFAULT now()
);

-- Module 7: Reviews
CREATE TABLE reviews (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    product_id BIGINT NOT NULL REFERENCES products(id),
    rating INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment TEXT, created_at TIMESTAMPTZ DEFAULT now(),
    UNIQUE (user_id, product_id)
);

-- Module 8: Reporting materialized views
CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT order_date, COUNT(DISTINCT user_id) AS unique_buyers,
       COUNT(*) AS order_count, SUM(total_amount) AS daily_revenue
FROM orders WHERE status = 'completed'
GROUP BY order_date;
CREATE UNIQUE INDEX idx_mv_daily ON mv_daily_sales (order_date);

❓ FAQ

Q Which module should I start building tables from in the comprehensive project?
A Start with base tables that have no foreign-key dependencies (users, categories), then build the tables that reference them (products, orders), and finally the tables that depend on orders (payments, shipments). The wrong order will error out due to foreign-key constraints.
Q How do I handle foreign-key references on a partitioned table?
A A partitioned table's foreign key must include the partition key. When order_items references orders, use the composite foreign key (order_id, order_date), not just order_id.
Q What's the difference between RLS and a normal WHERE clause?
A RLS is a database-level enforced policy that takes effect even if the application layer forgets to filter. A normal WHERE clause relies on application code and is easy to miss. For multi-tenant scenarios, RLS is mandatory.
Q Do too many JSONB attributes hurt query performance?
A JSONB itself doesn't affect the row-size limit, but more attributes means a larger row and higher I/O. Build a GIN index on frequently queried attributes, or use an expression index to extract hot fields.
Q How often should a materialized view be refreshed?
A It depends on how fresh the data needs to be. A daily report can refresh once a day; a real-time dashboard can use REFRESH CONCURRENTLY every 5–15 minutes to avoid locking the table.
Q How do I populate the pgvector embedding column with data?
A PostgreSQL itself doesn't generate embeddings. The application layer calls an AI model (e.g., the OpenAI embeddings API) to get the vector, then writes it to PG's vector column. This can be synced automatically with a trigger or application code.
Q Can this project replace Elasticsearch?
A For medium scale (millions of documents), yes. PG full-text search plus pg_trgm covers about 80% of scenarios. But at very large scale (hundreds of millions) or when you need aggregation analytics, Elasticsearch is still the dedicated search engine.

📖 Summary

📝 Exercises

  1. ⭐ Following the comprehensive example script, create the complete e-commerce database on your local PG instance, insert 10 test rows, and verify the basic query for each module (user login, product search, order creation, cart UPSERT).

  2. ⭐⭐ Extend the comprehensive project: (1) add a coupon module (coupons table + JSONB rules + a stored procedure to validate them); (2) write a search_products(keyword TEXT, min_price NUMERIC, max_price NUMERIC, category_id INT) function that combines full-text search + JSONB filtering + price range; (3) create a pg_cron scheduled job for monthly automatic partitioning.

  3. ⭐⭐⭐ Produce a production-grade optimization report: (1) use pg_stat_statements to collect the Top 10 slow queries and propose optimization plans; (2) design an autovacuum strategy for all partitioned tables; (3) configure PITR backup and test point-in-time recovery; (4) write a complete postgres_fdw script to migrate data from one PG instance to another; (5) use EXPLAIN ANALYZE to verify all key queries take the optimal plan.

Web-Tutorial.com

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

100%

🙏 帮我们做得更好

我们是刚上线的编程教程站，几个人的小团队，精力有限。页面虽经检查，难免还有疏漏——链接失效、排版错乱、内容有误、语言生硬……

如果您发现了，麻烦告诉我们，我们会在收到反馈后第一时间进行修复，再次感谢您的光临 🙏