English 中文 العربية 日本語 Português

PostgreSQL: Projeto Abrangente

Última atualização: 2026-08-26

1. O que Você Aprenderá

2. A História

Bob e Alice estão lançando uma plataforma de e-commerce transfronteiriço para o Oriente Médio. Os requisitos são complexos: busca textual em árabe, atributos dinâmicos de produtos em JSONB, pedidos particionados por mês, segurança em nível de linha para isolar tenants e recomendações de produtos por IA. Eles resolvem tudo com PostgreSQL em um só lugar — tsvector para busca em árabe, jsonb para SKUs flexíveis, particionamento declarativo para centenas de milhões de pedidos, pgvector para recomendações semânticas e postgres_fdw para migrar dados do PG legado. Um único banco PG substitui MySQL + Elasticsearch + Redis + um motor de recomendação.

3. Conceito: Análise de Requisitos e Design de Arquitetura

(1) Detalhamento dos Módulos de Negócio

Módulo Tabelas principais Funcionalidades-chave
Sistema de usuários users, user_addresses RLS segurança em nível de linha, senhas bcrypt
Categorias e produtos categories, products Atributos dinâmicos JSONB, busca textual
Pedidos e itens orders, order_items Particionamento RANGE mensal
Carrinho de compras cart_items UPSERT de alta concorrência
Pagamentos payments Tipos enum, trilha de auditoria
Rastreamento de envio shipments, shipment_events Dados de série temporal, eventos JSONB
Avaliações e classificações reviews Agregação de estrelas, índice GIN
Relatórios de dados mv_daily_sales, etc. Materialized views, atualização programada

(2) Diagrama ER Completo

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) Relatório de Seleção PostgreSQL vs MySQL

Dimensão PostgreSQL MySQL
Atributos dinâmicos JSONB jsonb nativo + índice GIN + operadores Tipo JSON, mas indexação fraca
Busca textual tsvector/tsquery nativo, multilíngue Sem suporte nativo, precisa de Elasticsearch
Busca vetorial Extensão pgvector nativa Precisa de um serviço externo
Particionamento RANGE/LIST/HASH declarativo 8.0+ suporta, mas mais fraco
Segurança em nível de linha Políticas RLS Não suportado
Materialized views Suporte nativo + atualização programada Não suportado
Ecossistema de extensões Rico (PostGIS/pgcrypto/FDW) Menos plugins
Consultas complexas Funções de janela/CTE/LATERAL 8.0+ suporta gradualmente
Maturidade operacional Alta, autovacuum/PITR Alta, replicação master-slave madura
Atividade da comunidade Crescimento mais rápido Maior base de usuários

Conclusão: este projeto precisa de atributos dinâmicos JSONB, busca textual, busca vetorial, RLS, particionamento e materialized views — o PostgreSQL suporta todos nativamente, enquanto o MySQL precisaria de 4+ componentes de middleware externos, então PostgreSQL é a escolha.

4. Operação: Módulo 1 — Sistema de Usuários

(1) Tabelas de Usuários e Endereços

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
);

▶ Exemplo: RLS Segurança em Nível de Linha para Isolamento de Tenants

SQL 
-- Habilitar RLS na tabela users
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Isolamento de tenants: usuários só podem ver do mesmo tenant
CREATE POLICY tenant_isolation ON users
    USING (tenant_id = current_setting('app.tenant_id')::bigint);

-- Admin pode ver tudo
CREATE POLICY admin_all_access ON users
    USING (role = 'admin');

-- Definir contexto de tenant por sessão
SET app.tenant_id = '1';
SELECT * FROM users;

Saída:

TEXT 📖 Somente leitura 
CREATE TABLE

▶ Exemplo: Registro com Hash de Senha

SQL 
CREATE EXTENSION IF NOT EXISTS pgcrypto;

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

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

Saída:

TEXT 📖 Somente leitura 
INSERT 0 1

5. Operação: Módulo 2 — Categorias e Produtos

▶ Exemplo: Árvore de Categorias Autorreferenciada

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);

-- Consulta recursiva: árvore de categorias
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;

Saída:

TEXT 📖 Somente leitura 
INSERT 0 1

▶ Exemplo: Tabela de Produtos com Atributos Dinâmicos JSONB

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)
);

Saída:

TEXT 📖 Somente leitura 
CREATE TABLE

▶ Exemplo: Consulta de Atributos JSONB

SQL 
-- Inserir com atributos dinâmicos
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);

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

-- Índice GIN para consultas de contenção JSONB
CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

Saída:

TEXT 📖 Somente leitura 
INSERT 0 1

▶ Exemplo: Busca Textual (incluindo Árabe)

SQL 
-- Índice GIN para busca textual
CREATE INDEX idx_products_search ON products USING GIN (search_vector);

-- Buscar em inglês ou árabe
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;

-- Busca em árabe
SELECT name, name_ar
FROM products, plainto_tsquery('simple', 'آيفون') q
WHERE search_vector @@ q;

Saída:

TEXT 📖 Somente leitura 
CREATE TABLE

▶ Exemplo: Recomendação de Produtos Similares com pgvector

SQL 
-- Índice HNSW para busca vetorial
CREATE INDEX idx_products_embedding ON products
    USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- Encontrar produtos similares
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';

Saída:

TEXT 📖 Somente leitura 
CREATE TABLE

6. Operação: Módulo 3 — Pedidos e Itens do Pedido

▶ Exemplo: Tabela de Pedidos Particionada por RANGE Mensal

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);

Saída:

TEXT 📖 Somente leitura 
CREATE TABLE

▶ Exemplo: Criação de Pedido e Cálculo do Valor

SQL 
-- Criar pedido
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';

-- Atualizar total do pedido
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';

Saída:

TEXT 📖 Somente leitura 
  result  
----------
   42.50
(1 row)

7. Operação: Módulo 4 — Carrinho de Compras

▶ Exemplo: UPSERT no Carrinho de Compras

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)
);

-- Adicionar ou atualizar item do carrinho (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;

-- Ver carrinho com detalhes dos produtos
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;

Saída:

TEXT 📖 Somente leitura 
INSERT 0 1

8. Operação: Módulo 5 — Pagamentos

▶ Exemplo: Tabela de Pagamentos e Tipos Enum

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)
);

-- Registrar pagamento bem-sucedido
INSERT INTO payments (order_id, order_date, method, amount, status, paid_at)
VALUES (1, '2024-01-15', 'credit_card', 2398.00, 'completed', now());

-- Atualizar status do pedido após pagamento
UPDATE orders SET status = 'paid'
WHERE id = 1 AND order_date = '2024-01-15';

Saída:

TEXT 📖 Somente leitura 
INSERT 0 1

9. Operação: Módulo 6 — Rastreamento de Envio

▶ Exemplo: Tabelas de Envio e Eventos JSONB

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()
);

-- Rastrear envio com eventos
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);

-- Timeline do envio
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;

Saída:

TEXT 📖 Somente leitura 
INSERT 0 1

10. Operação: Módulo 7 — Avaliações e Classificações

▶ Exemplo: Tabela de Avaliações e Agregação de Estrelas

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');

-- Resumo de avaliação do produto com função de janela
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;

Saída:

TEXT 📖 Somente leitura 
 count 
-------
     5
(1 row)

11. Operação: Módulo 8 — Relatórios de Dados

▶ Exemplo: Materialized View de Vendas Diárias

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);

-- Atualizar diariamente (pode ser programado com pg_cron)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;

-- Dias com maior receita
SELECT order_date, daily_revenue, order_count
FROM mv_daily_sales
ORDER BY daily_revenue DESC
LIMIT 10;

Saída:

TEXT 📖 Somente leitura 
 count 
-------
     5
(1 row)

▶ Exemplo: Materialized View de Vendas por Categoria

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;

Saída:

TEXT 📖 Somente leitura 
  result  
----------
   42.50
(1 row)

12. Operação: Stored Procedures e Automação

▶ Exemplo: Stored Procedure de Criação de Pedido

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;
;

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

Saída:

TEXT 📖 Somente leitura 
  result  
----------
   42.50
(1 row)

▶ Exemplo: Stored Procedure para Manutenção Automática de Partições

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;

    -- Desanexar partições com mais de 2 anos
    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;

Saída:

TEXT 📖 Somente leitura 
CREATE TABLE

13. Operação: Migração de Dados FDW e Backup PITR

▶ Exemplo: Migrando do PG Legado com 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;

-- Migrar usuários com re-hash de senha
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;

Saída:

TEXT 📖 Somente leitura 
INSERT 0 1

▶ Exemplo: Estratégia de Backup PITR

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

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

# Restaurar para um ponto no tempo
pg_restore --target-time='2024-03-15 14:30:00' -d shop_db /backup/base

Saída:

TEXT 📖 Somente leitura 
# command executed successfully
Estratégia Frequência Retenção Tempo de recuperação
pg_basebackup completo Diário 7 dias 30-60 min
Arquivamento WAL Contínuo 7 dias Qualquer ponto no tempo
Backup lógico pg_dump Semanal 4 semanas 1-4 horas

14. Exemplo Abrangente

SQL 
-- Script completo de inicialização do banco de dados de e-commerce
-- Módulo 1: Sistema de usuários
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
);

-- Módulo 2: Categorias e Produtos
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);

-- Módulo 3: Pedidos (particionados mensalmente)
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)
);

-- Módulo 4: Carrinho
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)
);

-- Módulo 5: Pagamentos
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)
);

-- Módulo 6: Envio
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()
);

-- Módulo 7: Avaliações
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)
);

-- Módulo 8: Materialized views de relatórios
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);

❓ Perguntas Frequentes

P Por qual módulo devo começar a criar tabelas no projeto abrangente?
R Comece pelas tabelas base que não têm dependências de chave estrangeira (users, categories), depois crie as tabelas que as referenciam (products, orders) e por fim as tabelas que dependem de pedidos (payments, shipments). A ordem errada causará erros devido a restrições de chave estrangeira.
P Como lidar com referências de chave estrangeira em uma tabela particionada?
R A chave estrangeira de uma tabela particionada deve incluir a chave de particionamento. Quando order_items referencia orders, use a chave estrangeira composta (order_id, order_date), não apenas order_id.
P Qual é a diferença entre RLS e uma cláusula WHERE normal?
R RLS é uma política aplicada em nível de banco de dados que entra em vigor mesmo se a camada de aplicação esquecer de filtrar. Uma cláusula WHERE normal depende do código da aplicação e é fácil de ser omitida. Para cenários multi-tenant, RLS é obrigatória.
P Muitos atributos JSONB prejudicam o desempenho de consultas?
R O JSONB em si não afeta o limite de tamanho da linha, mas mais atributos significam uma linha maior e maior I/O. Crie um índice GIN em atributos consultados com frequência, ou use um índice de expressão para extrair campos quentes.
P Com que frequência uma materialized view deve ser atualizada?
R Depende de quão atualizados os dados precisam ser. Um relatório diário pode ser atualizado uma vez por dia; um dashboard em tempo real pode usar REFRESH CONCURRENTLY a cada 5–15 minutos para evitar o bloqueio da tabela.
P Como popular a coluna de embedding do pgvector com dados?
R O PostgreSQL em si não gera embeddings. A camada de aplicação chama um modelo de IA (ex: a API de embeddings da OpenAI) para obter o vetor e depois o escreve na coluna vector do PG. Isso pode ser sincronizado automaticamente com uma trigger ou código de aplicação.
P Este projeto pode substituir o Elasticsearch?
R Para escala média (milhões de documentos), sim. A busca textual do PG mais pg_trgm cobre cerca de 80% dos cenários. Mas em escala muito grande (centenas de milhões) ou quando se precisa de análises de agregação, o Elasticsearch ainda é o motor de busca dedicado.

📖 Resumo

📝 Exercícios

  1. ⭐ Seguindo o script do exemplo abrangente, crie o banco de dados de e-commerce completo na sua instância PG local, insira 10 linhas de teste e verifique a consulta básica de cada módulo (login de usuário, busca de produtos, criação de pedidos, UPSERT do carrinho).

  2. ⭐⭐ Estenda o projeto abrangente: (1) adicione um módulo de cupons (tabela coupons + regras JSONB + uma stored procedure para validá-los); (2) escreva uma função search_products(keyword TEXT, min_price NUMERIC, max_price NUMERIC, category_id INT) que combine busca textual + filtragem JSONB + faixa de preço; (3) crie um job programado com pg_cron para particionamento automático mensal.

  3. ⭐⭐⭐ Produza um relatório de otimização em nível de produção: (1) use pg_stat_statements para coletar as Top 10 consultas lentas e proponha planos de otimização; (2) desenhe uma estratégia de autovacuum para todas as tabelas particionadas; (3) configure backup PITR e teste recuperação point-in-time; (4) escreva um script completo de postgres_fdw para migrar dados de uma instância PG para outra; (5) use EXPLAIN ANALYZE para verificar se todas as consultas-chave usam o plano ótimo.

Web-Tutorial.com

Equipe Técnica Web-Tutorial

Uma plataforma de tutoriais mantida por diversos desenvolvedores. Cada tutorial é escrito e revisado por profissionais da área correspondente. Trabalhamos para manter nosso conteúdo preciso e confiável — se encontrar algum problema, avise-nos.

100%