Components d'un SGBD

Visió general de l'arquitectura

Un Sistema Gestor de Bases de Dades (SGBD) modern és un programari extremament complex que integra desenes de subsistemes especialitzats. Malgrat la diversitat de motors (PostgreSQL, MySQL, SQL Server,...), tots comparteixen una arquitectura conceptual similar organitzada en capes.

flowchart TD
    subgraph Clients["Capa de clients"]
        APP["Aplicació web/mòbil"]
        CLI["Client CLI\n(psql, mysql, sqlcmd)"]
        GUI["Eina gràfica\n(DBeaver, pgAdmin)"]
    end

    subgraph Interface["Capa d'interfície"]
        CONN["Gestor de connexions\n(pool, autenticació)"]
        PROT["Protocol de xarxa\n(TCP/IP)"]
    end

    subgraph Processing["Capa de processament"]
        PARSE["Parser SQL\n(anàlisi sintàctica)"]
        SEM["Analitzador semàntic\n(resolució de noms)"]
        OPT["Optimitzador de consultes\n(plans d'execució)"]
        EXEC["Executor"]
    end

    subgraph Storage["Capa d'emmagatzematge"]
        BUFMGR["Gestor de buffer\n(cache de pàgines)"]
        TXMGR["Gestor de transaccions\n(MVCC / bloquejos)"]
        STOREMGR["Motor d'emmagatzematge\n(pàgines, registres)"]
        WAL["WAL / Redo Log"]
        CATALOG["Catàleg del sistema\n(metadades)"]
    end

    subgraph Physical["Capa física"]
        DATA["Fitxers de dades"]
        LOG["Fitxers de log"]
        IDX["Fitxers d'índex"]
    end

    Clients --> Interface --> Processing --> Storage --> Physical

    style APP fill:#2563EB,stroke:#1d4ed8,color:#fff
    style CLI fill:#2563EB,stroke:#1d4ed8,color:#fff
    style GUI fill:#2563EB,stroke:#1d4ed8,color:#fff
    style CONN fill:#2563EB,stroke:#1d4ed8,color:#fff
    style PROT fill:#2563EB,stroke:#1d4ed8,color:#fff
    style PARSE fill:#2563EB,stroke:#1d4ed8,color:#fff
    style SEM fill:#2563EB,stroke:#1d4ed8,color:#fff
    style OPT fill:#2563EB,stroke:#1d4ed8,color:#fff
    style EXEC fill:#2563EB,stroke:#1d4ed8,color:#fff
    style BUFMGR fill:#16A34A,stroke:#15803d,color:#fff
    style TXMGR fill:#16A34A,stroke:#15803d,color:#fff
    style STOREMGR fill:#16A34A,stroke:#15803d,color:#fff
    style WAL fill:#16A34A,stroke:#15803d,color:#fff
    style CATALOG fill:#7C3AED,stroke:#6d28d9,color:#fff
    style DATA fill:#1e293b,stroke:#475569,color:#fff
    style LOG fill:#1e293b,stroke:#475569,color:#fff
    style IDX fill:#1e293b,stroke:#475569,color:#fff
    style Clients fill:#1e3a8a,stroke:#3b82f6,color:#fff
    style Interface fill:#1e3a8a,stroke:#3b82f6,color:#fff
    style Processing fill:#1e3a8a,stroke:#3b82f6,color:#fff
    style Storage fill:#14532d,stroke:#22c55e,color:#fff
    style Physical fill:#0f172a,stroke:#475569,color:#fff

Cicle de vida d'una consulta SQL

Cada vegada que executem un SELECT, la consulta recorre totes les capes de l'arquitectura. El diagrama següent mostra el camí complet per a SELECT * FROM clients WHERE email = 'joan@example.com':

flowchart TD
    CLI["Client\n(app / psql)"]

    subgraph Interface["Capa d'interficie"]
        CONN["Gestor de connexions\nAutentica i assigna worker"]
    end

    subgraph Processing["Capa de processament"]
        PARSE["Parser SQL\nAnalisi sintactica"]
        SEM["Analitzador semantic\nResol noms i privilegis"]
        OPT["Optimitzador\nTria el pla mes eficient"]
        EXEC["Executor\nSeq Scan / Index Scan / Join..."]
    end

    subgraph Storage["Capa d'emmagatzematge"]
        BUF["Buffer Pool (RAM)\nCache de pagines"]
        DISK["Disc\nFitxers de dades"]
    end

    CLI -->|"1. Envia consulta SQL"| CONN
    CONN -->|"2. Consulta validada"| PARSE
    PARSE -->|"3. Arbre sintactic"| SEM
    SEM -->|"4. Arbre semantic"| OPT
    OPT -->|"5. Pla d'execucio"| EXEC
    EXEC -->|"6. Demana pagines"| BUF
    BUF -->|"HIT: retorna des de RAM"| EXEC
    BUF -->|"MISS: llegeix del disc"| DISK
    DISK -->|"Pagina carregada"| BUF
    EXEC -->|"7. Files resultat"| CLI

    style CLI fill:#4f46e5,color:#fff,stroke:#3730a3
    style CONN fill:#0891b2,color:#fff,stroke:#0e7490
    style PARSE fill:#059669,color:#fff,stroke:#047857
    style SEM fill:#059669,color:#fff,stroke:#047857
    style OPT fill:#d97706,color:#fff,stroke:#b45309
    style EXEC fill:#d97706,color:#fff,stroke:#b45309
    style BUF fill:#7c3aed,color:#fff,stroke:#6d28d9
    style DISK fill:#374151,color:#fff,stroke:#1f2937

On passa la major part del temps?

En un sistema ben configurat, el Buffer Pool absorbeix la gran majoria de les peticions (hit ratio > 95%). Quan el rendiment és dolent, l'EXPLAIN ANALYZE mostra quants "Buffer Hits" vs "Buffer Misses" hi ha hagut, i si el problema és la mida del buffer pool o la manca d'índexs.

Parser i analitzador semàntic

La primera fase del processament d'una consulta SQL és l'anàlisi sintàctica (parsing): el SGBD comprova que la sentència SQL està ben formada d'acord amb la gramàtica del llenguatge. Si hi ha un error de sintaxi (paraula reservada mal escrita, parèntesi no tancat), s'interromp el processament i es retorna un error al client.

A continuació, l'analitzador semàntic verifica que la consulta té sentit en el context de l'esquema actual: comprova que les taules i columnes referenciades existeixen, que els tipus de dades són compatibles i que l'usuari té els privilegis necessaris per executar l'operació.

Exemple d'error sintàctic:

SELEC * FORM clients;  -- Error: paraules clau mal escrites

Exemple d'error semàntic:

SELECT nom_inexistent FROM clients;  -- Error: la columna no existeix

Optimitzador de consultes

L'optimitzador és un dels components més sofisticats d'un SGBD. La seva tasca és trobar el pla d'execució més eficient per a una consulta donada. Per a una consulta SQL, poden existir desenes o fins i tot centenars de plans d'execució equivalents (que produiran el mateix resultat), però amb costos molt diferents.

L'optimitzador basa les seves decisions en:

Estadístiques del catàleg: Quantes files té cada taula, quina és la distribució dels valors en cada columna, quins índexs existeixen.
Estimació de costos: El cost de cada operació (escaneig seqüencial, escaneig d'índex, hash join, nested loop join, sort, etc.) en termes de I/O de disc i CPU.
Regles heurístiques: Per exemple, aplicar els filtres WHERE el més aviat possible per reduir el nombre de files a processar.

flowchart LR
    SQL["Consulta SQL"] --> PARSE["Parser"]
    PARSE --> TREE["Arbre sintàctic"]
    TREE --> OPT["Optimitzador"]
    STATS["Estadístiques\ndel catàleg"] --> OPT
    OPT --> PLAN["Pla d'execució\n(òptim)"]
    PLAN --> EXEC["Executor"]
    EXEC --> RESULT["Resultat"]

Per veure el pla d'execució d'una consulta podeu usar EXPLAIN:

PostgreSQLMySQL / MariaDBSQL ServerOracle

EXPLAIN ANALYZE SELECT * FROM clients WHERE email = 'joan@example.com';

EXPLAIN SELECT * FROM clients WHERE email = 'joan@example.com';

SET STATISTICS IO ON;
SELECT * FROM clients WHERE email = 'joan@example.com';

EXPLAIN PLAN FOR
SELECT * FROM clients WHERE email = 'joan@example.com';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

L'optimitzador no és perfecte

L'optimitzador pren decisions basades en estadístiques. Si les estadístiques estan desactualitzades (per exemple, si una taula ha crescut molt recentment), pot triar un pla subòptim. Per això és important executar ANALYZE (PostgreSQL), ANALYZE TABLE (MySQL) o EXEC DBMS_STATS.GATHER_TABLE_STATS('esquema','taula') (Oracle) periòdicament.

Executor

L'executor (o motor d'execució) és el component que porta a terme el pla d'execució triat per l'optimitzador. Opera sobre un conjunt d'operadors físics:

Seq Scan: Llegeix totes les files d'una taula seqüencialment. Adequat quan s'espera llegir un gran percentatge de les files.
índex Scan: Usa un índex B-tree per localitzar les files de forma eficient. Adequat quan s'espera llegir un petit percentatge de les files.
Hash Join: Per a joins entre dues taules, construeix una taula hash amb la taula més petita i l'usa per trobar les coincidències amb la taula gran.
Nested Loop Join: Per a cada fila de la taula exterior, busca les files coincidents a la taula interior (eficient si la taula interior té un índex).
Sort: Ordena les files, necessari per a ORDER BY o per a certs algoritmes de join.
Aggregate: Calcula funcions d'agregació (COUNT, SUM, AVG, etc.).

Gestor de buffer (Buffer Pool / Buffer Cache)

El gestor de buffer manté a la memòria RAM les pàgines de disc que s'han llegit recentment o que s'escriuen freqüentment. L'objectiu és minimitzar el nombre d'accessos al disc, que són ordres de magnitud més lents que els accessos a la RAM.

flowchart LR
    EXEC["Executor"] --> BUF["Buffer Pool\n(RAM)"]
    BUF -->|"pàgina en cache: hit"| EXEC
    BUF -->|"pàgina no en cache: miss"| DISK["Disc\n(fitxers de dades)"]
    DISK -->|"llegir pàgina"| BUF

Conceptes clau:

Buffer hit: La pàgina sol·licitada ja està a la cache → molt ràpid.
Buffer miss: La pàgina no està a la cache i s'ha de llegir del disc → lent.
Hit ratio: Percentatge de peticions que es resolen amb un buffer hit. Un bon hit ratio seria >95%.
Dirty page: Una pàgina que ha estat modificada a la cache però encara no s'ha escrit al disc.
Algoritme LRU: Quan la cache està plena i cal carregar una nova pàgina, s'expulsa la pàgina Least Recently Used (la que fa més temps que no s'ha accedit).

La mida del buffer pool és un dels paràmetres de configuració més importants d'un SGBD:

PostgreSQL: shared_buffers (recomanat: 25% de la RAM del servidor)
MySQL/InnoDB: innodb_buffer_pool_size (recomanat: 70-80% de la RAM dedicada)
SQL Server: max server memory

Pàgines de dades i registres

Quan un SGBD emmagatzema dades al disc, no ho fa registre a registre ni byte a byte. La unitat mínima de lectura i escriptura al disc és la pàgina de dades (també anomenada bloc). Tota la comunicació entre el disc i la memòria RAM es fa en unitats de pàgines senceres.

Per què pàgines?

Llegir un sol byte del disc té un cost pràcticament igual que llegir 8 KB, perquè el temps dominant és el posicionament del capçal (en discs mecànics) o la latència d'accés (en SSD). Per això, els SGBD llegeixen i escriuen blocs de mida fixa: les pàgines.

Mida de pàgina per SGBD

SGBD	Mida de pàgina per defecte	Configurable?
PostgreSQL	8 KB	Només en compilació (`--with-blocksize`)
MySQL / InnoDB	16 KB	Sí (`innodb_page_size`: 4K, 8K, 16K, 32K, 64K)
SQL Server	8 KB	No (fix)
Oracle	8 KB	Sí (`DB_BLOCK_SIZE`: 2K, 4K, 8K, 16K, 32K)
SQLite	4 KB	Sí (`PRAGMA page_size`: 512 a 65536 bytes)

Estructura interna d'una pàgina

Cada pàgina de dades té una estructura interna ben definida. Prenent com a referència PostgreSQL (8 KB = 8.192 bytes), una pàgina conté:

📄 Pàgina de dades — Estructura interna (PostgreSQL · 8 KB)

Visió general Una pàgina de dades de 8 KB (8.192 bytes) es divideix en 5 zones. Els Item Pointers creixen de dalt cap avall ▼ i les Tuples creixen de baix cap amunt ▲. Quan es troben, la pàgina està plena.

📋 Capçalera (PageHeader)24 bytes

LSN · checksum · flags · lower · upper · special

👆 Item Pointers (Line Pointers)4 bytes cadascun

Pointer 1 → (offset, len, flags) ↓ Tuple 1
Pointer 2 → (offset, len, flags) ↓ Tuple 2
Pointer 3 → (offset, len, flags) ↓ Tuple 3
▼ creixen cap avall

▒ Espai lliure (Free Space) ▒

📦 Tuples (registres)variable

▲ creixen cap amunt
Tuple 3: capçalera (23 B) + dades
Tuple 2: capçalera (23 B) + dades
Tuple 1: capçalera (23 B) + dades

Espai especial0 bytes (heap page)

Detall Cada Item Pointer (4 bytes) conté l'offset i la longitud de la tupla a la que apunta. La capçalera de la pàgina indica on acaben els pointers (pd_lower) i on comencen les tuples (pd_upper).

Camp	Mida	Funció
pd_lsn	8 bytes	Log Sequence Number (WAL)
pd_checksum	2 bytes	Verificació d'integritat
pd_flags	2 bytes	Flags de la pàgina
pd_lower	2 bytes	Final dels item pointers ▼
pd_upper	2 bytes	Inici de les tuples ▲
pd_special	2 bytes	Inici de l'espai especial

pd_lower = 24 + (N × 4) → final dels item pointers pd_upper = inici de la última tupla inserida Quan pd_lower ≥ pd_upper → pàgina plena!

Components principals:

Capçalera (PageHeader): 24 bytes amb metadades de la pàgina: LSN (Log Sequence Number per al WAL), checksum, flags, i els punters lower (final dels item pointers) i upper (inici de les tuples).
Item Pointers (Line Pointers): Un array de punters de 4 bytes cadascun. Cada punter indica l'offset i longitud d'una tupla dins la pàgina. Creixen de dalt cap avall.
Espai lliure (Free Space): La zona entre els item pointers i les tuples. A mesura que s'insereixen registres, aquest espai es redueix.
Tuples (registres): Les dades reals dels registres. Cada tupla té la seva pròpia capçalera (23 bytes a PostgreSQL) seguida dels valors dels camps. Creixen de baix cap amunt.
Espai especial: Reservat per a índexs (B-tree, GiST...); en pàgines de dades normals (heap) ocupa 0 bytes.

Per què creixen en direccions oposades?

Els item pointers creixen de dalt cap avall i les tuples de baix cap amunt. Quan les dues zones es troben, la pàgina està plena. Això permet afegir tant punters com tuples sense necessitat de moure dades existents.

Estructura d'una tupla (registre)

Cada tupla dins d'una pàgina conté, a més de les dades de l'usuari, una capçalera amb informació de control:

📦 Estructura d'una tupla (registre)

Visió general Cada tupla conté 3 zones: una capçalera (23 bytes, control MVCC), un null bitmap opcional, i les dades de l'usuari (els valors dels camps).

Capçalera (HeapTupleHeader)23 bytes

t_xmin

4 B · TX creació

t_xmax

4 B · TX eliminació

t_cid

4 B · cmd ID

t_ctid

6 B · (pàg, item)

t_infomask · t_infomask2 · t_hoff

2+2+1 bytes · flags i offset dades

🔳 Null Bitmapopcional · variable

Dades de l'usuari~79 bytes (variable)

1

id · int4

Maria

nom · varchar

Puig

cognom · varchar

Girona

ciutat · varchar

maria@mail.cat

email · varchar

Overhead Cada tupla ocupa com a mínim 23 bytes de capçalera + 4 bytes d'item pointer = 27 bytes d'overhead. Per a files molt petites, l'overhead pot ser la major part de l'espai!

Exemple de taula	Dades útils	Overhead	% Overhead
clients (5 columnes)	79 bytes	27 bytes	25%
relació M:N (2 int)	8 bytes	27 bytes	77%!
log amb text llarg	500 bytes	27 bytes	5%

⚠ Taules amb files petites → overhead elevat ✓ Taules amb files grans → overhead negligible

El pes de les capçaleres

Cada tupla ocupa com a mínim 23 bytes de capçalera + 4 bytes d'item pointer = 27 bytes d'overhead. Per a una taula amb files molt petites (p.ex. una taula de relació molts-a-molts amb dos enters de 4 bytes), l'overhead pot suposar el 75% de l'espai! Això és important a l'hora de planificar l'emmagatzematge.

Identificador de tupla: TID (ctid)

Cada fila en una taula PostgreSQL té un TID (Tuple Identifier) format per dues parts:

Número de pàgina (dins del fitxer de la taula)
Número d'item (posició dins de la pàgina)

    TID = (pàgina, item)  →  Exemple: (0, 1) = pàgina 0, item 1

📍 Identificador de tupla: TID (ctid)

Organització Cada fila en PostgreSQL té un TID format per dues parts: (pàgina, item).
L'identificador apunta directament a quin bloc físic s'ha d'anar a buscar l'item.

Fitxer de la taula "clients"

Pàgina 0 (8 KB)

Item 1 (0,1): Maria Puig, Girona
Item 2 (0,2): Joan Mas, Barcelona
...
Item 45 (0,45): Pere Roca, Blanes

Pàgina 1 (8 KB)

Item 1 (1,1): Laia Font, Lloret
Item 2 (1,2): Marc Vila, Figueres
...

Pàgina 2 (8 KB)...

Inspecció A PostgreSQL es pot consultar directament la pseudo-columna ctid a qualsevol taula per veure la distribució real i el bloc des d'on s'estan llegint els registres.

Consulta SQL	Resultat
`SELECT ctid, nom, ciutat FROM clients LIMIT 5;`	(0,1) \| Maria \| Girona (0,2) \| Joan \| Barcelona (0,3) \| Anna \| Tarragona (0,4) \| Pere \| Blanes (0,5) \| Laura \| Blanes

Visualització i càlcul de capacitat

La següent representació explora l'anatomia pràctica d'una pàgina de dades prenent la taula clients real com a cas d'estudi.

📊 Anatomia pràctica i Càlculs (Taula Clients)

Distribució Real Anem a calcular què passa realment en una pàgina si introduïm files d'uns ~102 bytes a la taula clients. La tupla conté els elements bàsics previament definits: id, nom, cognom, ciutat, email més la capçalera de registre obligatòria de PostgreSQL.

Usat: 552 B

Lliure: 7.636 bytes (93.2%)

Capçalera (24B) Item Ptrs (20B) Tuples (~508B) Lliure (7.636B)

Càlcul d'espai Quantes tuples ens caben tenint en compte l'overhead d'item pointer de 4 bytes per cadascuna? Aquest càlcul determina quantes pàgines llegirem pel total de files!

Concepte	Mida calculada	Explicació matemàtica
Espai total útil	8.168 B	8.192 bytes de pàgina - 24 bytes de capçalera = 8.168 útils
Pes per tupla	~106 B / fila	~102 (dades + capçalera tupla) + 4 (item pointer associat)
Capacitat per pàgina	~77 files	8.168 bytes / 106 bytes per fila ≈ 77 files màxim per pàgina
Acumulat (15.420 f)	200 pàgines	15.420 files totals / 77 files/pàgina = 200 pàgines (1,6 MB)

Relació amb el Buffer Pool L'emmagatzematge defineix com el disc funciona, però la memòria agilitza el procés: quan l'executor demana per exemple el ctid=(0,3) sempre carrega o demana la pàgina 0 sencera alhora, cap registre es carrega de forma solitaria!

Buffer Hit

Temps estimat: µs (en memòria)

vs

Buffer Miss

Temps estimat: ms (al disc)

Lleugeresa. Un cop processat a memòria RAM inicialment, l'accés a les tuples subsegüents a la mateixa pàgina ve "gratis" al nivell d'I/O.

Com es distribueixen les taules en pàgines

📂 Com es distribueix una Taula en Pàgines

Mapatge a Disc Una taula amb 15.420 files no es desa amb cap ordre "especial" inicialment per defecte ("Heap Table"). Realment s'ha introduït en un fitxer al disc estructurat i trossejat per pàgines contigües de 8 KB cadascuna de manera successiva.

Pàgina 0

77 tuples
8 KB

Pàgina 1

77 tuples
8 KB

Pàgina 2

77 tuples
8 KB

···

Pàgina 199

38 tuples
8 KB

      ↓ Fitxer físic base/16384/24601
200 pàgines × 8 KB = 1,6 MB
    

Seq Scan vs Index Scan: impacte en pàgines

Ara que entenem les pàgines, podem veure per què els índexos són tan importants:

⚡ Seq Scan vs Index Scan — Impacte en pàgines

Seq Scan Sense índex, l'executor ha de llegir totes les pàgines de la taula per trobar les files que compleixen el filtre WHERE ciutat = 'Blanes'. La majoria de pàgines no contenen files de Blanes, però s'han de llegir igualment.

▮▮▮ Llegeix TOTES les 200 pàgines (1,6 MB) ▮▮▮

⚠ 200 pàgines × 8 KB = 1,6 MB d'I/O de disc ⚠ La majoria de pàgines llegides no contenen files útils

Index Scan Amb l'índex B-tree idx_clients_ciutat, l'executor consulta l'índex per saber exactament quines pàgines contenen files amb ciutat = 'Blanes' i salta la resta.

12 pàg.

188 pàgines saltades

✓ Només ~12 pàgines × 8 KB = 96 KB d'I/O ✓ L'índex apunta directament a les pàgines amb files de Blanes

Comparació L'índex redueix l'I/O de disc 17 vegades. Aquesta és la raó per la qual l'optimitzador tria l'Index Scan quan hi ha un índex adequat.

Mètode	Pàgines llegides	I/O de disc	Temps estimat
Seq Scan	200 (100%)	1,6 MB	~50 ms
Index Scan	~12 (6%)	96 KB	~3 ms

1,6 MB

Seq Scan

→

96 KB

Index Scan

＝

17× menys I/O

Reducció

La clau del rendiment

Un Seq Scan llegeix totes les pàgines de la taula, una per una. Un Index Scan usa l'índex B-tree per anar directament a les pàgines que contenen les files desitjades. Ara entenem per què l'optimitzador de la secció anterior triava l'Index Scan: 17 vegades menys I/O de disc.

Consulta pràctica: inspeccionar pàgines reals

PostgreSQL ofereix l'extensió pageinspect per examinar l'estructura interna de les pàgines:

Instal·lar l'extensióVeure capçalera de pàginaVeure item pointersQuantes tuples per pàgina?

CREATE EXTENSION IF NOT EXISTS pageinspect;

-- Capçalera de la pàgina 0 de la taula 'clients'
SELECT * FROM page_header(get_raw_page('clients', 0));

    lsn     | checksum | flags | lower | upper | special | pagesize | version
------------+----------+-------+-------+-------+---------+----------+---------
 0/1A3F5E0  |   0xA7B3 |     0 |   344 |  1280 |    8192 |     8192 |       4

-- Item pointers de la pàgina 0
SELECT lp, lp_off, lp_len, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('clients', 0))
LIMIT 5;

 lp | lp_off | lp_len | t_xmin | t_xmax | t_ctid
----+--------+--------+--------+--------+--------
  1 |   8090 |    102 |   1038 |      0 | (0,1)
  2 |   7988 |     98 |   1039 |      0 | (0,2)
  3 |   7882 |    106 |   1040 |      0 | (0,3)
  4 |   7782 |    100 |   1041 |      0 | (0,4)
  5 |   7680 |    102 |   1042 |      0 | (0,5)

-- Comptador de tuples per pàgina
SELECT
    blkno AS pagina,
    count(*) AS tuples
FROM (
    SELECT (ctid::text::point)[0]::int AS blkno
    FROM clients
) sub
GROUP BY blkno
ORDER BY blkno
LIMIT 5;

 pagina | tuples
--------+--------
      0 |     77
      1 |     77
      2 |     77
      3 |     77
      4 |     64

AC0372/01/07 — Miniactivitat

RA1 · CA1.5

Connecteu-vos a un contenidor PostgreSQL, creeu una taula senzilla amb almenys 4 columnes i inseriu 200 registres. Després:

Consulteu el ctid de les primeres i últimes files. Quantes pàgines ha ocupat la taula?
Calculeu manualment quantes tuples per pàgina haurien de cabre (usant la mida de les columnes) i compareu-ho amb el resultat real.
Extra: Instal·leu pageinspect i examineu la capçalera de la primera pàgina. Quins valors tenen lower i upper?

Gestor de transaccions

El gestor de transaccions garanteix les propietats ACID de les transaccions. Usa dues tecnologies principals:

MVCC (Multi-Version Concurrency Control)

PostgreSQL i MySQL/InnoDB usen MVCC: en lloc de bloquejar les files quan s'actualitzen, es creen noves versions de cada fila. Una transacció de lectura veu un "snapshot" consistent de les dades en el moment en què va iniciar, independent de les modificacions que estan fent altres transaccions concurrents.

Avantatge: Les lectures no bloquegen les escriptures i viceversa, millorant molt la concurrència.

2PL (Two-Phase Locking)

Alguns motors (o parts d'ells) usen bloquejos (locks): per llegir o modificar un recurs, una transacció ha d'adquirir un bloqueig sobre ell. Altres transaccions que intentin accedir al mateix recurs han d'esperar.

Tipus de bloquejos:

Shared lock (S): Per a lectures. Múltiples transaccions poden tenir un shared lock simultàniament.
Exclusive lock (X): Per a escriptures. Només una transacció pot tenir un exclusive lock i no es pot tenir si ja hi ha un shared lock.

Deadlock

Un deadlock es produeix quan dues transaccions es bloquegen mutuament: la transacció A espera que la B alliberi un recurs, i la B espera que l'A n'alliberi un altre. Els SGBD detecten i resolen els deadlocks automàticament fent un ROLLBACK d'una de les transaccions, però la seva aparició freqüent indica un problema de disseny en les transaccions de l'aplicació.

WAL (Write-Ahead Log)

El WAL (Write-Ahead Log), també conegut com a redo log (MySQL/InnoDB) o transaction log (SQL Server), és un fitxer especial on el SGBD anota tots els canvis abans d'aplicar-los als fitxers de dades.

sequenceDiagram
    participant TX as Transaccio
    participant WAL as WAL (disc)
    participant BUF as Buffer Pool (RAM)
    participant DATA as Fitxer de dades (disc)

    TX->>WAL: 1. Escriu el canvi al WAL (COMMIT)
    WAL-->>TX: 2. Confirma que està escrit (durable)
    TX-->>TX: 3. Retorna OK al client
    BUF->>DATA: 4. Aplica el canvi al fitxer de dades (més tard)

Gràcies al WAL, si el servidor falla després del pas 3 però abans del pas 4, en reiniciar el SGBD pot llegir el WAL i reaplicar els canvis pendents. Això garanteix la durabilitat de les transaccions confirmades.

El WAL també s'usa per a la replicació: les rèpliques llegeixen el WAL del servidor primari per mantenir-se sincronitzades.

Catàleg del sistema

El catàleg del sistema (o diccionari de dades) és un conjunt de taules internes del SGBD que emmagatzemen les metadades de tots els objectes de la base de dades: taules, columnes, tipus de dades, índexs, vistes, usuaris, privilegis, estadístiques, etc.

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Consultar taules del cataleg
SELECT tablename, tableowner, hasindexes
FROM pg_tables
WHERE schemaname = 'public';

-- Consultar columnes d'una taula
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'clients';

-- Estadístiques de l'optimitzador
SELECT relname, n_live_tup, last_analyze
FROM pg_stat_user_tables;

-- Mostrar taules de la BD actual
SHOW TABLES;

-- Mostrar estructura d'una taula
DESCRIBE clients;

-- Informació detallada del catàleg
SELECT table_name, table_rows, data_length
FROM information_schema.tables
WHERE table_schema = 'gbd_practica';

-- Consultar taules de l'usuari
SELECT name, type_desc, create_date
FROM sys.objects
WHERE type = 'U';

-- Consultar columnes d'una taula
SELECT c.name, t.name AS data_type, c.is_nullable
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('clients');

-- Consultar taules de l'usuari actual
SELECT table_name, num_rows, last_analyzed
FROM user_tables;

-- Consultar columnes d'una taula
SELECT column_name, data_type, nullable
FROM user_tab_columns
WHERE table_name = 'CLIENTS';

-- Estadístiques de l'optimitzador
SELECT table_name, num_rows, blocks, last_analyzed
FROM user_tab_statistics
WHERE table_name = 'CLIENTS';

AC0372/01/06 — Miniactivitat

RA1 · CA1.5

Connecteu-vos a un contenidor PostgreSQL i executeu les consultes del catàleg de l'exemple. Quantes taules internes del catàleg (pg_catalog) podeu identificar? Cerqueu a la documentació oficial per a què serveix la taula pg_stat_user_tables i quina informació us pot ser útil com a DBA.

Simulació: del SQL al resultat

La simulació següent mostra el recorregut complet d'una consulta SQL a través dels components interns d'un SGBD. Utilitza els botons per avançar pas a pas o prem Auto per veure-ho automàticament.

🗄️ Simulació — Cicle de vida d'una consulta SQL (PostgreSQL)

1. Entrada L'usuari envia una consulta SQL al SGBD a través d'un client. El motor la rep com una seqüència de text plana sobre la connexió TCP/IP.

      SELECT nom, cognom

      FROM clients

      WHERE ciutat = 'Blanes'

      ORDER BY cognom;

L'usuari envia una simple cadena de caràcters per la xarxa sense sentit lògic previ.

2. Lexer (Tokenització) Llegeix la cadena i la separa en tokens atorgant-los categories (paraules claus, identificadors, operadors o variables literals).

      K: SELECT
      ID: nom
      PUNC: ,
      ID: cognom
      K: FROM
      ID: clients
      K: WHERE
      ID: ciutat
      OP: =
      LIT: 'Blanes'
      K: ORDER BY
      ID: cognom
    

3. Parser (Arbre) La llista de tokens es comprova d'acord a la gramàtica (sintaxi) creant l'Arbre de Sintaxi Abstracta. Si falla (ex: "SELEC"), es llança l'error fins al client.

      [SELECT_STMT]

      ├─ TARGET_LIST → nom, cognom

      ├─ FROM_CLAUSE → clients

      ├─ WHERE_CLAUSE

      │  └─ OP_EQUAL

      │     ├─ COLUMN_REF → ciutat

      │     └─ STRING_CONST → 'Blanes'

      └─ SORT_CLAUSE → cognom ASC

4. Analitzador Semàntic Ara l'Arbre verifica que cadascun dels objectes descrits representi alguna entitat real i existent consultant directament al Catàleg del Sistema.

Objectiu detectat	Resolució (Catàleg del sistema)
ID `clients`	Existeix com a Taula base (pg_class) ✓
Col `nom, cognom, ciutat`	Són columnes VARCHAR de la taula clients ✓
Operador `= 'Blanes'`	Tipus correctes (VARCHAR compatible amb string genèric) ✓
Privilegis	L'usuari execució té garantits permisos de SELECT ✓

5. Optimitzador Es plantegen i avaluen plans per determinar quina operació física farà menys I/O i consum de CPU basejant-se en mides i l'estructura local que ha deixat l'analitzador.

Nom del Pla i Condició	Cost I/O Estimat	Estat
Seq Scan: Llegeix les 15.420 files directament senceres demanant a sistema arxius sencers	~850.0	Descartat
Index Scan (escollit): Sap que hi ha `idx_clients_ciutat`; accés filtrat i precís a memòria	~48.0	MÉS EFICIENT

6. Pla Execució (EXPLAIN) Es converteix en format d'operadors d'arbre. De baix cap a dalt s'aniran alimentant els resultats entre ells. Es pot veure al PG Admin.

      > EXPLAIN SELECT nom, ...;

      Sort  (cost=72.45..74.57 rows=847 width=100)

        Sort Key: cognom

        ->  Index Scan using idx_clients_ciutat on clients ...

            Index Cond: (ciutat = 'Blanes')

7. Executor (Motor) Demana directament al Buffer Pool les pàgines. Processa l'arbre creat a la Fase 6 per portar les tuples del disc, treuren el "projection" de columnes i reordenant (quicksort).

Index B-Tree Localitza 847 files Buffer Heap Fetch: Recull valors (RAM) Projection Sort: Ordena i retalla en memòria

8. Resultat Final Totes les tuples empaquetades i processades segons l protocol retornen al client demanant resultats en pocs milisegons d'eficiència.

 nom   | cognom
-------+--------
 Laura | Amat
 Jordi | Coll
 Maria | Ferrer
 ...
(847 rows)
Time: 12.421 ms
    