PR507402 — Optimització de BD relacional a gran escala amb Docker

Objectius

• Muntar un entorn PostgreSQL 17 complet amb Docker Compose i carregar un dataset sintètic de 10 milions de registres.
• Mesurar i documentar el rendiment inicial de consultes reals amb EXPLAIN ANALYZE sobre una taula sense optimitzar.
• Crear índexs adequats (B-Tree, parcials, funcionals, compostos) per a un conjunt de consultes donades i quantificar la millora.
• Implementar particionament per rang sobre la taula de vendes i verificar el partition pruning amb el planificador.
• Configurar PgBouncer com a connection pool i comparar el rendiment amb i sense pooling amb pgbench.

Prerequisits

• Docker Desktop instal·lat i en execució (versió 24 o superior).
• DBeaver Community o qualsevol client SQL gràfic.
• Python 3.10 o superior (per al script de generació de dades).
• Coneixements de SQL i de lectura de plans EXPLAIN ANALYZE (vegeu el tema Optimització de queries d'aquest bloc).

Nomenclatura obligatòria

Substituïu nom_cognom per el vostre nom i primer cognom en minúscules sense accents ni espais, per exemple joan_puig. Tots els recursos Docker i SQL han d'usar la vostra nomenclatura. Les entregues amb nomenclatura incorrecta no es valoraran.

---

Part 1: Configuració de l'entorn

1.1 Estructura de directoris

Creeu l'estructura de directoris següent al vostre espai de treball:

pr507402-nom_cognom/
├── docker-compose.yml
├── init/
│   └── 01_schema.sql
├── scripts/
│   └── genera_dades.py
└── informe/
    └── informe_pr507402.md

1.2 Docker Compose amb PostgreSQL 17 i pgAdmin

# docker-compose.yml
services:
  postgres-nom-cognom:
    image: postgres:17
    container_name: postgres-nom-cognom
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: bigdata2026
      POSTGRES_DB: bigdata_nom_cognom
    ports:
      - "5433:5432"
    volumes:
      - pgdata_nom_cognom:/var/lib/postgresql/data
      - ./init:/docker-entrypoint-initdb.d
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres -d bigdata_nom_cognom"]
      interval: 10s
      timeout: 5s
      retries: 5

  pgadmin-nom-cognom:
    image: dpage/pgadmin4:latest
    container_name: pgadmin-nom-cognom
    environment:
      PGADMIN_DEFAULT_EMAIL: nom.cognom@sapalomera.cat
      PGADMIN_DEFAULT_PASSWORD: admin2026
    ports:
      - "5050:80"
    depends_on:
      postgres-nom-cognom:
        condition: service_healthy

volumes:
  pgdata_nom_cognom:

1.3 Esquema inicial de la taula de vendes

-- init/01_schema.sql
-- S'executa automàticament en el primer arrencament del contenidor

CREATE TABLE vendes (
    id             bigserial    PRIMARY KEY,
    data_venda     date         NOT NULL,
    client_id      integer      NOT NULL,
    producte_id    integer      NOT NULL,
    quantitat      integer      NOT NULL CHECK (quantitat > 0),
    preu_unitari   numeric(10, 2) NOT NULL CHECK (preu_unitari > 0),
    pais           text         NOT NULL,
    estat_enviament text        NOT NULL DEFAULT 'pendent'
        CHECK (estat_enviament IN ('pendent', 'enviat', 'entregat', 'retornat'))
);

-- Extensió per a estadístiques de consultes
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Aixequeu l'entorn:

docker compose up -d
docker compose logs -f postgres-nom-cognom
# Espereu fins a veure: "database system is ready to accept connections"

1.4 Generació del dataset sintètic (10 milions de registres)

El script Python següent insereix 10 milions de files en lots de 50.000 per minimitzar l'ús de memòria. En un ordinador modern tarda entre 5 i 15 minuts.

# scripts/genera_dades.py
import random
import psycopg2
from datetime import date, timedelta

CONN_PARAMS = {
    "host": "localhost",
    "port": 5433,
    "dbname": "bigdata_nom_cognom",
    "user": "postgres",
    "password": "bigdata2026",
}

PAISOS = ["ES", "FR", "DE", "IT", "PT", "NL", "BE", "AT", "PL", "GB"]
ESTATS = ["pendent", "enviat", "entregat", "retornat"]
PESOS_ESTAT = [0.10, 0.20, 0.60, 0.10]

DATA_INICI = date(2021, 1, 1)
DATA_FI = date(2025, 12, 31)
DIES_TOTAL = (DATA_FI - DATA_INICI).days

TOTAL_FILES = 10_000_000
MIDA_LOT = 50_000

def data_aleatoria():
    return DATA_INICI + timedelta(days=random.randint(0, DIES_TOTAL))

def genera_lot(mida):
    return [
        (
            data_aleatoria(),
            random.randint(1, 500_000),
            random.randint(1, 10_000),
            random.randint(1, 100),
            round(random.uniform(0.99, 999.99), 2),
            random.choice(PAISOS),
            random.choices(ESTATS, weights=PESOS_ESTAT)[0],
        )
        for _ in range(mida)
    ]

def main():
    conn = psycopg2.connect(**CONN_PARAMS)
    cur = conn.cursor()

    inserts = 0
    while inserts < TOTAL_FILES:
        mida = min(MIDA_LOT, TOTAL_FILES - inserts)
        lot = genera_lot(mida)
        cur.executemany(
            """INSERT INTO vendes
               (data_venda, client_id, producte_id, quantitat, preu_unitari, pais, estat_enviament)
               VALUES (%s, %s, %s, %s, %s, %s, %s)""",
            lot,
        )
        conn.commit()
        inserts += mida
        print(f"  Inserits: {inserts:,} / {TOTAL_FILES:,}", end="\r")

    cur.close()
    conn.close()
    print(f"\nFet. {TOTAL_FILES:,} registres inserits.")

if __name__ == "__main__":
    main()

pip install psycopg2-binary
python scripts/genera_dades.py

Verifiqueu el resultat:

SELECT COUNT(*) FROM vendes;
-- Resultat esperat: 10000000

SELECT pg_size_pretty(pg_total_relation_size('vendes')) AS mida_taula;

---

Part 2: Anàlisi sense optimitzar

Abans de crear cap índex, executeu les cinc consultes següents, anoteu el temps real (Execution Time) de cadascuna i feu una captura de pantalla del pla complet.

-- Habilitar temporitzador
\timing on

-- Q1: Vendes d'un client concret
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM vendes WHERE client_id = 42000;

-- Q2: Vendes d'Espanya en un rang de dates
EXPLAIN (ANALYZE, BUFFERS)
SELECT pais, COUNT(*), SUM(quantitat * preu_unitari) AS facturacio
FROM vendes
WHERE pais = 'ES' AND data_venda BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY pais;

-- Q3: Les 20 vendes més cares del dia d'avui (simula data fixa)
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM vendes
WHERE data_venda = '2025-06-15'
ORDER BY preu_unitari DESC
LIMIT 20;

-- Q4: Vendes pendents de França
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, data_venda, client_id, preu_unitari
FROM vendes
WHERE pais = 'FR' AND estat_enviament = 'pendent'
ORDER BY data_venda;

-- Q5: Cerca insensible a majúscules per país
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM vendes WHERE lower(pais) = 'de';

Per a cada consulta, ompliu la taula de l'informe:

Consulta	Node principal	Execution Time (sense índex)	Execution Time (amb índex)	Millora
Q1
Q2
Q3
Q4
Q5

Seq Scan vs Index Scan

En aquesta fase, totes les consultes haurien de mostrar Seq Scan al pla. Fixeu-vos en el camp Rows Removed by Filter: un valor molt alt indica que s'estan llegint moltes files per descartar-les, cosa que és precisament el problema que els índexs solucionen.

---

Part 3: Creació d'índexs i particionament

3.1 Creació dels índexs

Creeu els índexs adequats per a cadascuna de les cinc consultes anteriors. Justifiqueu cada decisió al vostre informe.

-- Índex per a Q1: B-Tree sobre client_id
CREATE INDEX idx_vendes_client_id ON vendes (client_id);

-- Índex per a Q2: compost per a la combinació pais + data_venda
CREATE INDEX idx_vendes_pais_data ON vendes (pais, data_venda);

-- Índex per a Q3: B-Tree sobre data_venda (cobreix el filtre i permet l'ordenació)
CREATE INDEX idx_vendes_data ON vendes (data_venda);

-- Índex per a Q4: parcial per a vendes pendents (subconjunt petit i molt consultat)
CREATE INDEX idx_vendes_fr_pendents ON vendes (data_venda)
WHERE pais = 'FR' AND estat_enviament = 'pendent';

-- Índex per a Q5: funcional per a cerques insensibles a majúscules
CREATE INDEX idx_vendes_pais_lower ON vendes (lower(pais));

Torneu a executar les cinc consultes i anoteu els nous temps a la taula comparativa.

3.2 Particionament per any

Creeu una versió particionada de la taula de vendes. Com que no podem convertir directament una taula existent, cal crear-ne una de nova, migrar les dades i substituir l'original.

-- Pas 1: Crear la taula particionada
CREATE TABLE vendes_part (
    id              bigserial,
    data_venda      date          NOT NULL,
    client_id       integer       NOT NULL,
    producte_id     integer       NOT NULL,
    quantitat       integer       NOT NULL CHECK (quantitat > 0),
    preu_unitari    numeric(10,2) NOT NULL CHECK (preu_unitari > 0),
    pais            text          NOT NULL,
    estat_enviament text          NOT NULL DEFAULT 'pendent'
        CHECK (estat_enviament IN ('pendent', 'enviat', 'entregat', 'retornat')),
    PRIMARY KEY (id, data_venda)
) PARTITION BY RANGE (data_venda);

-- Pas 2: Crear les particions anuals (2021-2025)
CREATE TABLE vendes_part_2021 PARTITION OF vendes_part
    FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');
CREATE TABLE vendes_part_2022 PARTITION OF vendes_part
    FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');
CREATE TABLE vendes_part_2023 PARTITION OF vendes_part
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE vendes_part_2024 PARTITION OF vendes_part
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
CREATE TABLE vendes_part_2025 PARTITION OF vendes_part
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- Pas 3: Migrar les dades (pot tardar uns minuts)
INSERT INTO vendes_part SELECT * FROM vendes;

-- Pas 4: Recrear els índexs sobre la taula particionada
CREATE INDEX idx_vp_client_id ON vendes_part (client_id);
CREATE INDEX idx_vp_pais_data ON vendes_part (pais, data_venda);

-- Pas 5: Verificar el partition pruning
EXPLAIN (ANALYZE)
SELECT COUNT(*) FROM vendes_part
WHERE data_venda BETWEEN '2024-01-01' AND '2024-12-31';
-- El pla ha d'accedir NOMÉS a vendes_part_2024

Comproveu quines particions apareixen al pla:

-- Llistar les particions i la seva mida
SELECT
    child.relname                                         AS particio,
    pg_size_pretty(pg_relation_size(child.oid))          AS mida
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class child  ON pg_inherits.inhrelid  = child.oid
WHERE parent.relname = 'vendes_part'
ORDER BY child.relname;

---

Part 4: Connection pooling amb PgBouncer

4.1 Afegir PgBouncer al Docker Compose

Afegiu el servei PgBouncer al docker-compose.yml existent:

  pgbouncer-nom-cognom:
    image: bitnami/pgbouncer:latest
    container_name: pgbouncer-nom-cognom
    environment:
      POSTGRESQL_HOST: postgres-nom-cognom
      POSTGRESQL_PORT: 5432
      POSTGRESQL_DATABASE: bigdata_nom_cognom
      POSTGRESQL_USERNAME: postgres
      POSTGRESQL_PASSWORD: bigdata2026
      PGBOUNCER_POOL_MODE: transaction
      PGBOUNCER_MAX_CLIENT_CONN: 500
      PGBOUNCER_DEFAULT_POOL_SIZE: 20
      PGBOUNCER_MIN_POOL_SIZE: 5
    ports:
      - "6432:6432"
    depends_on:
      postgres-nom-cognom:
        condition: service_healthy

docker compose up -d pgbouncer-nom-cognom

4.2 Benchmark amb pgbench

pgbench és l'eina de benchmark inclosa amb PostgreSQL. La instal·lareu dins del contenidor:

# Inicialitzar pgbench a la base de dades (crea les seves pròpies taules de prova)
docker exec postgres-nom-cognom pgbench \
  -U postgres -d bigdata_nom_cognom --initialize --scale=10

# Benchmark SENSE pooling (directe a PostgreSQL, port 5433)
docker exec postgres-nom-cognom pgbench \
  -U postgres -d bigdata_nom_cognom \
  -h localhost -p 5432 \
  --client=50 --jobs=4 --time=30 \
  --report-per-command \
  > resultats_sense_pool.txt

# Benchmark AMB pooling (a través de PgBouncer, port 6432)
docker exec pgbouncer-nom-cognom pgbench \
  -U postgres -d bigdata_nom_cognom \
  -h pgbouncer-nom-cognom -p 6432 \
  --client=50 --jobs=4 --time=30 \
  --report-per-command \
  > resultats_amb_pool.txt

Interpretació dels resultats de pgbench

Els indicadors clau són tps (transaccions per segon) i latency average (latència mitjana en ms). Compareu els dos fitxers de resultats i incloeu la comparativa a l'informe.

4.3 Monitoratge de PgBouncer

Mentre el benchmark s'executa, obriu una connexió al pseudo-port de PgBouncer i observeu l'estat del pool:

docker exec -it pgbouncer-nom-cognom \
  psql -U postgres -p 6432 pgbouncer

SHOW POOLS;
-- Observeu: cl_active (clients actius), sv_active (connexions reals actives a PG)
-- sv_active hauria de mantenir-se proper a default_pool_size (20)
-- cl_active pot arribar fins a max_client_conn (500)

SHOW STATS;
-- total_xact_count: transaccions totals processades
-- avg_xact_time: temps mig de transacció en microsegons

---

Lliurament

El lliurament és un fitxer .zip amb el nom PR507402_nom_cognom.zip que conté:

PR507402_nom_cognom/
├── docker-compose.yml            (versió final amb PgBouncer)
├── init/
│   └── 01_schema.sql
├── scripts/
│   └── genera_dades.py
├── sql/
│   ├── part2_analisi_inicial.sql  (les 5 consultes amb EXPLAIN ANALYZE)
│   ├── part3_indexos.sql          (CREATE INDEX amb comentaris justificatius)
│   └── part3_particionament.sql   (DDL complet de vendes_part)
├── resultats/
│   ├── resultats_sense_pool.txt
│   └── resultats_amb_pool.txt
└── informe/
    └── informe_pr507402.md

L'informe ha d'incloure:

• Taula comparativa de temps d'execució (Part 2 vs Part 3) per a les 5 consultes.
• Captures de pantalla dels plans EXPLAIN ANALYZE abans i després de cada índex.
• Captura del pla de la consulta sobre vendes_part mostrant el partition pruning.
• Taula comparativa de pgbench (tps i latència amb i sense PgBouncer).
• Reflexió final (mínim 150 paraules): quines optimitzacions han tingut més impacte i per quina raó.

Data límit

Consulteu el calendari d'aula per a la data de lliurament. Les entregues fora de termini sense justificació aprovada per l'instructor no es valoraran.