Arquitectura i Fonaments dels SGBD

Introducció

Entendre el funcionament intern d'un SGBD és imprescindible per a un DBA. Sense aquest coneixement, les decisions d'optimització, la tria de motors d'emmagatzematge o la configuració d'aïllament de transaccions es converteixen en actes cecs. En aquesta unitat aprofundim en l'arquitectura física i lògica dels SGBD, el cicle de vida d'una consulta, les propietats ACID, els nivells d'aïllament de transaccions i els principals motors d'emmagatzematge.

---

1. El model ANSI/SPARC en profunditat

Recordem que el model ANSI/SPARC defineix tres nivells d'abstracció: extern, conceptual i intern. Ara aprofundim en com cada motor implementa aquests nivells.

1.1 Nivell intern: l'emmagatzematge real

El nivell intern és on les dades viuen físicament al disc. Cada SGBD té la seva pròpia implementació, però totes comparteixen conceptes comuns:

Pàgines o blocs: La unitat mínima d'I/O. PostgreSQL usa pàgines de 8 KB per defecte. MySQL/InnoDB usa pàgines de 16 KB. SQL Server usa pàgines de 8 KB. Oracle usa blocs de 8 KB per defecte (configurable: 2 KB a 32 KB).

Fitxers de dades: Les taules s'emmagatzemen en fitxers al sistema de fitxers de l'OS. PostgreSQL usa un fitxer per relació (taula o índex). MySQL/InnoDB pot usar un únic ibdata1 (system tablespace) o fitxers per taula (.ibd). SQL Server usa fitxers .mdf (primari) i .ndf (secundaris). Oracle usa fitxers de dades (.dbf) organitzats en tablespaces.

Fitxers de log (WAL/Redo): Crítics per a la durabilitat. Registren totes les modificacions abans d'aplicar-les als fitxers de dades, permetent la recuperació davant fallades. PostgreSQL: WAL (Write-Ahead Log). MySQL: Binary Log + InnoDB Redo Log. SQL Server: Transaction Log (.ldf). Oracle: Redo Log Files (Online Redo Logs + Archived Redo Logs).

1.2 Nivell conceptual: l'esquema lògic

El nivell conceptual es materialitza en el catàleg del sistema (system catalog o data dictionary), que és un conjunt de taules internes on el SGBD emmagatzema la definició de tots els objectes:

SGBD	Catàleg del sistema
PostgreSQL	pg_catalog (taules pg_class, pg_attribute, pg_index...)
MySQL	information_schema + mysql (base de dades interna)
SQL Server	sys.* (vistes del sistema: sys.tables, sys.columns...)
Oracle	DBA_*, ALL_*, USER_* (vistes del diccionari de dades)

1.3 Nivell extern: vistes i seguretat

El nivell extern es implementa principalment mitjançant vistes (views), que presenten un subconjunt o transformació de les dades del nivell conceptual. Les vistes permeten:

• Ocultar columnes sensibles (contrasenyes, salaris, DNI).
• Simplificar consultes complexes per als usuaris.
• Implementar seguretat a nivell de fila (Row-Level Security).
• Proporcionar compatibilitat cap enrere quan l'esquema canvia.

---

2. Arquitectura física d'un SGBD

L'arquitectura física d'un SGBD modern s'organitza en diversos processos o fils que cooperen per processar les peticions dels clients.

flowchart TB
    Client["Client\n(aplicació, psql, mysql, sqlcmd)"]

    subgraph Capa_Xarxa["Capa de xarxa i autenticació"]
        Listen["Listener / Acceptor\n(port TCP)"]
        Auth["Autenticació\n(pg_hba.conf, my.cnf, login, sqlnet.ora)"]
    end

    subgraph Capa_SQL["Processament SQL"]
        Parser["Parser\n(anàlisi sintàctica)"]
        Analyzer["Analitzador semàntic\n(resolució de noms, tipus)"]
        Rewriter["Reescriptor\n(expansió de vistes, regles)"]
        Optimizer["Optimitzador\n(plans d'execució, cost-based)"]
        Executor["Executor\n(execució del pla)"]
    end

    subgraph Capa_Transaccions["Gestió de transaccions i concurrència"]
        TxMgr["Gestor de transaccions\n(BEGIN / COMMIT / ROLLBACK)"]
        LockMgr["Gestor de bloqueigs\n(locks, deadlock detection)"]
        MVCC["Control de concurrència\n(MVCC o 2PL)"]
    end

    subgraph Capa_Emmagatzematge["Capa d'emmagatzematge"]
        BufferPool["Buffer Pool / Shared Buffers\n(memòria cau de pàgines)"]
        StorageEng["Motor d'emmagatzematge\n(InnoDB, heap, etc.)"]
        WAL["Gestor de WAL/Redo Log\n(escriptura anticipada)"]
    end

    subgraph Disc["Emmagatzematge persistent"]
        DataFiles["Fitxers de dades"]
        LogFiles["Fitxers de log"]
        Indexes["Índexos"]
    end

    Client --> Listen
    Listen --> Auth
    Auth --> Parser
    Parser --> Analyzer
    Analyzer --> Rewriter
    Rewriter --> Optimizer
    Optimizer --> Executor
    Executor --> TxMgr
    TxMgr --> LockMgr
    TxMgr --> MVCC
    Executor --> BufferPool
    BufferPool --> StorageEng
    StorageEng --> WAL
    WAL --> Disc
    StorageEng --> Disc

    style Capa_Xarxa fill:#e3f2fd,stroke:#1565c0
    style Capa_SQL fill:#e8f5e9,stroke:#2e7d32
    style Capa_Transaccions fill:#fff8e1,stroke:#f57f17
    style Capa_Emmagatzematge fill:#fce4ec,stroke:#c62828
    style Disc fill:#f3e5f5,stroke:#6a1b9a

2.1 El Buffer Pool (memòria cau de pàgines)

El Buffer Pool (o Shared Buffer a PostgreSQL) és la memòria cau de pàgines de disc, i és probablement el paràmetre de configuració més important d'un SGBD. Quan el motor necessita llegir o modificar una pàgina de dades, primer comprova si ja es troba al buffer pool. Si hi és (buffer hit), evita l'I/O de disc, que és ordres de magnitud més lent que l'accés a RAM. Si no hi és (buffer miss), ha de llegir-la del disc i carregar-la al buffer pool.

El Buffer Hit Ratio (taxa d'encerts) hauria de ser >99% en un sistema ben configurat. Si és inferior, probablement cal augmentar la memòria assignada al buffer pool.

SGBD	Paràmetre de configuració	Valor recomanat
PostgreSQL	shared_buffers	25% de la RAM del servidor
MySQL/InnoDB	innodb_buffer_pool_size	70-80% de la RAM disponible
SQL Server	max server memory	RAM total - 4 GB (per a l'OS)
Oracle	SGA_TARGET o MEMORY_TARGET	70-80% de la RAM disponible

---

3. El cicle de vida d'una consulta SQL

Seguim el camí d'una consulta SQL des que l'usuari l'envia fins que obté el resultat.

3.1 Anàlisi sintàctica (Parsing)

El parser rep el text de la consulta SQL com una cadena de caràcters i la converteix en un arbre sintàctic (parse tree). Comprova que la consulta estigui ben formada sintàcticament (words-clau correctes, parèntesi balancejats, etc.), però no comprova si les taules o columnes existeixen realment.

3.2 Anàlisi semàntica

L'analitzador semàntic pren l'arbre sintàctic i consulta el catàleg del sistema per verificar que tots els objectes referenciats existeixen (taules, columnes, funcions), que els tipus de dades són compatibles i que l'usuari té els permisos necessaris.

3.3 Reescriptura

El reescriptor (query rewriter) transforma la consulta per expandir vistes, aplicar regles de seguretat (Row-Level Security) o simplificar expressions. Per exemple, si una consulta fa SELECT * FROM vista_vendes, el reescriptor la substitueix per la definició completa de la vista.

3.4 Optimització

L'optimitzador és el component més sofisticat d'un SGBD. La seva tasca és trobar el pla d'execució més eficient entre les (potencialment milers) alternatives possibles per executar una consulta. Utilitza estadístiques sobre la distribució de les dades (histogrames, cardinalitats) per estimar el cost de cada operació i triar el pla de menor cost.

El resultat és un pla d'execució que descriu quines operacions físiques s'han de fer i en quin ordre: quins índexos usar, com fer els joins (nested loop, hash join, merge join), en quin ordre accedir a les taules, etc.

3.5 Execució

L'executor pren el pla d'execució i l'executa pas a pas, interactuant amb el buffer pool per llegir i escriure pàgines, el gestor de transaccions per gestionar el context transaccional i el gestor de bloqueigs per garantir la concurrència segura.

---

4. Propietats ACID

Les propietats ACID (Atomicitat, Consistència, Aïllament, Durabilitat) defineixen el comportament que ha de tenir un SGBD transaccional. Són la garantia que ofereix el motor als desenvolupadors d'aplicacions.

4.1 Atomicitat (Atomicity)

Una transacció és una unitat indivisible de treball. O s'executen totes les operacions que la formen, o no s'executa cap. Si alguna operació falla a mig camí, el SGBD desfà automàticament totes les operacions anteriors de la mateixa transacció (rollback).

Exemple real: Una transferència bancària consisteix en dos passos: (1) restar 100€ del compte A i (2) sumar 100€ al compte B. Si el sistema cau entre els dos passos, la transacció es desfarà completament. No pot quedar en un estat en què s'ha restat del compte A però no s'ha sumat al B.

PostgreSQLMySQL / MariaDBSQL ServerOracle

BEGIN;
UPDATE comptes SET saldo = saldo - 100 WHERE id = 'A';
UPDATE comptes SET saldo = saldo + 100 WHERE id = 'B';
-- Si algun UPDATE falla, tot el bloc es desfarà automàticament
COMMIT;

START TRANSACTION;
UPDATE comptes SET saldo = saldo - 100 WHERE id = 'A';
UPDATE comptes SET saldo = saldo + 100 WHERE id = 'B';
COMMIT;
-- En cas d'error: ROLLBACK;

BEGIN TRANSACTION;
UPDATE comptes SET saldo = saldo - 100 WHERE id = 'A';
UPDATE comptes SET saldo = saldo + 100 WHERE id = 'B';
IF @@ERROR <> 0
    ROLLBACK TRANSACTION;
ELSE
    COMMIT TRANSACTION;

-- Oracle inicia les transaccions implícitament
UPDATE comptes SET saldo = saldo - 100 WHERE id = 'A';
UPDATE comptes SET saldo = saldo + 100 WHERE id = 'B';
COMMIT;
-- En cas d'error:
-- ROLLBACK;

4.2 Consistència (Consistency)

Una transacció porta la base de dades d'un estat vàlid a un altre estat vàlid, respectant totes les restriccions d'integritat definides: claus primàries, claus foranes, restriccions CHECK, restriccions UNIQUE, triggers, etc.

Exemple: Si una restricció de clau forana exigeix que comanda.client_id existeixi a la taula clients, un intent d'inserir una comanda amb un client_id inexistent serà rebutjat per mantenir la consistència.

4.3 Aïllament (Isolation)

Les transaccions concurrents s'executen de forma com si s'haguessin executat seqüencialment, sense interferències mútues. En pràctica, existeix un espectre de nivells d'aïllament (veure secció 5) que permeten escollir entre major rendiment o major consistència.

4.4 Durabilitat (Durability)

Un cop una transacció s'ha confirmat (COMMIT), les seves modificacions queden persistides de forma permanent, fins i tot si el sistema cau immediatament després. Això es garanteix escrivint al log de transaccions (WAL/Redo Log) abans de confirmar el commit.

Durabilitat i rendiment

La durabilitat té un cost: el SGBD ha d'esperar que l'escriptura al log es persisteixi al disc (fsync) abans de confirmar el commit. Alguns sistemes desactiven el fsync per guanyar rendiment, però sacrifiquen la durabilitat. Mai facis això en un sistema de producció amb dades crítics.

---

5. Nivells d'aïllament de transaccions

L'estàndard SQL:1992 defineix quatre nivells d'aïllament, que determinen quins fenòmens d'anomalia de concurrència poden ocórrer.

5.1 Anomalies de concurrència

Abans d'explicar els nivells, cal definir les anomalies que volem evitar:

• Lectura bruta (Dirty Read): Transacció A llegeix dades modificades per la transacció B que encara no s'ha confirmat. Si B fa rollback, A haurà llegit dades que mai van existir.
• Lectura no repetible (Non-Repeatable Read): Transacció A llegeix la mateixa fila dues vegades i obté valors diferents, perquè la transacció B va modificar i confirmar la fila entre les dues lectures d'A.
• Lectura fantasma (Phantom Read): Transacció A executa la mateixa consulta amb un WHERE dues vegades i obté conjunts de files diferent, perquè la transacció B ha inserit o eliminat files que compleixen el WHERE entre les dues execucions d'A.
• Anomalia de sèrie (Serialization Anomaly): El resultat de l'execució concurrent de múltiples transaccions és diferent de qualsevol ordre seqüencial possible.

5.2 La taula d'aïllaments

Nivell d'aïllament	Dirty Read	Non-Repeatable Read	Phantom Read	Cost rendiment
READ UNCOMMITTED	Possible	Possible	Possible	Molt baix
READ COMMITTED	Impossible	Possible	Possible	Baix
REPEATABLE READ	Impossible	Impossible	Possible*	Mitjà
SERIALIZABLE	Impossible	Impossible	Impossible	Alt

*PostgreSQL i MySQL amb InnoDB eviten els phantoms a REPEATABLE READ gràcies a MVCC/gap locks.

5.3 Com configurar el nivell d'aïllament

PostgreSQLMySQL / MariaDBSQL ServerOracle

-- Per a la sessió actual:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- o bé:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- PostgreSQL no suporta READ UNCOMMITTED (tracta com READ COMMITTED)

-- Per defecte de tot el servidor (postgresql.conf):
-- default_transaction_isolation = 'read committed'

-- Comprovació:
SHOW transaction_isolation;

-- Per a la sessió:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- Globalment (requereix SUPER):
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Comprovació:
SELECT @@transaction_isolation;
-- my.cnf:
-- transaction-isolation = READ-COMMITTED

-- Per a la sessió:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Nivell addicional de SQL Server:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
-- READ COMMITTED SNAPSHOT ISOLATION (RCSI):
ALTER DATABASE [nom_bd] SET READ_COMMITTED_SNAPSHOT ON;

-- Comprovació:
DBCC USEROPTIONS;

-- Oracle suporta nativament READ COMMITTED i SERIALIZABLE:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Oracle NO suporta READ UNCOMMITTED ni REPEATABLE READ com a
-- nivells explícits (usa MVCC per proporcionar consistència de lectura)

-- Per defecte: READ COMMITTED
-- Comprovació (via diccionari de dades):
SELECT sid, serial#, username, status
FROM v$session WHERE username IS NOT NULL;

Recomanació pràctica

La majoria d'aplicacions web funcionen correctament amb READ COMMITTED (el nivell per defecte de PostgreSQL, SQL Server i Oracle). MySQL/InnoDB usa REPEATABLE READ per defecte, que és més conservador. Usar SERIALIZABLE pot causar un augment significatiu de bloqueigs i penalitzar el rendiment; reserva'l per a operacions financeres crítics.

---

6. Mecanismes de control de concurrència

Els SGBD utilitzen dos grans paradigmes per implementar l'aïllament:

6.1 Bloqueig en dues fases (2PL — Two-Phase Locking)

El protocol 2PL divideix una transacció en dues fases:

1. Fase de creixement: La transacció adquireix bloqueigs (shared locks per a lectura, exclusive locks per a escriptura) però no en allibera cap.
2. Fase de decreixement: La transacció allibera bloqueigs però no en pot adquirir de nous.

El 2PL garanteix la serialitzabilitat, però pot provocar deadlocks (bloqueigs mortals) quan dues transaccions esperen mútuament que l'altra alliberi un bloqueig. Els SGBD detecten els deadlocks automàticament i eliminen (abort) una de les transaccions per trencar el cicle.

6.2 Control de concurrència multiversion (MVCC)

El MVCC (Multiversion Concurrency Control) és el mecanisme que usa PostgreSQL, Oracle i InnoDB (MySQL/MariaDB). En comptes de bloquejar les files per a lectura, el SGBD manté múltiples versions de cada fila, cadascuna etiquetada amb l'identificador de la transacció que la va crear. Els lectors veuen sempre una versió consistent de les dades sense bloquejar els escriptors, i els escriptors no bloquegen els lectors.

Avantatge principal: les lectures no bloquegen les escriptures i les escriptures no bloquegen les lectures, cosa que millora molt el rendiment en entorns d'alta concurrència.

Bloat a PostgreSQL

El MVCC de PostgreSQL no elimina les versions antigues de les files immediatament — les "dead tuples" s'acumulen als fitxers de dades. El procés VACUUM (o autovacuum) s'encarrega de recuperar aquest espai. Si l'autovacuum no s'executa prou freqüentment, la taula pot créixer indefinidament (table bloat), degradant el rendiment.

---

7. Motors d'emmagatzematge

7.1 MySQL / MariaDB: InnoDB vs MyISAM

MySQL té un disseny únic: el motor d'emmagatzematge és un component intercanviable. Cada taula pot usar un motor diferent.

Característica	InnoDB	MyISAM
Transaccions	Sí (ACID complet)	No
Claus foranes	Sí	No
Bloqueig	A nivell de fila (row-level)	A nivell de taula (table-level)
Crash recovery	Automàtica (redo log)	Manual (myisamchk)
Full-text search	Sí (MySQL 5.6+)	Sí (tradicional)
MVCC	Sí	No
Rendiment lectura	Bo	Molt bo (sense overhead ACID)
Rendiment escriptura	Bo (concurrència alta)	Dolent (bloqueig de taula)
Cas d'ús actual	Pràcticament tot	Llegat, no recomanat per a nous projectes

MyISAM obsolet

MyISAM era el motor per defecte de MySQL fins a la versió 5.5. Des de MySQL 5.5 (2010), InnoDB és el motor per defecte. No s'hauria d'usar MyISAM en nous projectes perquè no és transaccional i no té claus foranes. Molts sistemes llegats encara l'usen; la migració a InnoDB és senzilla:

ALTER TABLE nom_taula ENGINE = InnoDB;

7.2 PostgreSQL: heap i B-tree

PostgreSQL no té motors d'emmagatzematge intercanviables com MySQL — usa sempre el seu propi motor. Les taules s'emmagatzemen en format heap (pàgines desordenades) i els índexos usen per defecte l'estructura B-tree.

PostgreSQL sí que ofereix múltiples tipus d'índex:

Tipus d'índex	Millor per a
B-tree (per defecte)	Igualtat i rangs: =, <, >, BETWEEN, LIKE 'abc%'
Hash	Únicamente igualtat: =. Més ràpid que B-tree per a equi-joins
GIN (Generalized Inverted Index)	Arrays, JSONB, full-text search, @>, &&
GiST (Generalized Search Tree)	Geometria (PostGIS), rangs, full-text
BRIN (Block Range Index)	Taules molt grans amb dades correlacionades físicament (sèries temporals)
SP-GiST	Estructures de dades no balancejades: punts, polígons

7.3 SQL Server: filegroups i estructures d'emmagatzematge

SQL Server organitza l'emmagatzematge en filegroups, que és una agrupació lògica de fitxers de dades. Cada base de dades té com a mínim el filegroup PRIMARY. Es poden crear filegroups addicionals per distribuir les dades en diversos discos físics.

Les taules poden ser: - Heap: Taula sense índex clusteritzat. Les files s'emmagatzemen sense ordre específic. - Clustered Index: La taula s'emmagatzema ordenada físicament per la clau de l'índex clusteritzat (normalment la PK). Només n'hi pot haver un per taula. - Nonclustered Index: Estructura d'índex separada que apunta a les files de la taula heap o a l'índex clusteritzat.

7.4 Oracle: tablespaces i segments

Oracle organitza l'emmagatzematge en tablespaces, que contenen un o més datafiles al disc. Dins d'un tablespace, les dades s'organitzen en:

• Segments: Un objecte lògic (taula, índex, partició) és un segment.
• Extents: Un segment creix en extents — blocs contigus de disc.
• Oracle Blocks: La unitat mínima d'I/O, de 2 KB a 32 KB (per defecte 8 KB).

Oracle inclou dos tablespaces especials: - SYSTEM: Conté el diccionari de dades. Mai s'ha d'usar per a dades d'usuari. - SYSAUX: Tablespace auxiliar del sistema per a components com l'AWR (Automatic Workload Repository).

---

8. Miniactivitat — AC0102

Miniactivitat — AC0102 · Exploració de l'arquitectura interna

Objectiu: Observar l'arquitectura interna de PostgreSQL i MySQL en temps real.

Part 1 — PostgreSQL amb Docker:

docker run --name pg-fonaments \
  -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 -d postgres:17

docker exec -it pg-fonaments psql -U postgres

Un cop dins de psql, executa:

-- Veure els processos de PostgreSQL
SELECT pid, usename, application_name, state, query
FROM pg_stat_activity
WHERE state IS NOT NULL;

-- Veure els paràmetres de configuració clau
SHOW shared_buffers;
SHOW work_mem;
SHOW max_connections;
SHOW wal_level;

-- Veure el nivell d'aïllament per defecte
SHOW default_transaction_isolation;

-- Explorar el catàleg del sistema
SELECT tablename, tableowner
FROM pg_catalog.pg_tables
WHERE schemaname = 'pg_catalog'
LIMIT 20;

Part 2 — MySQL/MariaDB amb Docker:

docker run --name mysql-fonaments \
  -e MYSQL_ROOT_PASSWORD=root \
  -p 3306:3306 -d mysql:8.4

docker exec -it mysql-fonaments mysql -u root -proot

Dins de MySQL:

-- Veure el motor d'emmagatzematge per defecte
SHOW VARIABLES LIKE 'default_storage_engine';

-- Veure el nivell d'aïllament
SELECT @@transaction_isolation;

-- Veure l'estat del buffer pool d'InnoDB
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- Veure les taules del sistema
USE information_schema;
SHOW TABLES;

-- Crear una taula i veure'n el motor
CREATE DATABASE fonaments;
USE fonaments;
CREATE TABLE prova (id INT PRIMARY KEY, nom VARCHAR(100)) ENGINE=InnoDB;
SHOW TABLE STATUS LIKE 'prova'\G

Preguntes de reflexió:

1. Quin és el valor de shared_buffers de PostgreSQL? Sembla adequat per a una màquina de producció?
2. Quants processos/fils actius has vist a pg_stat_activity?
3. Quin és el nivell d'aïllament per defecte de PostgreSQL? I de MySQL?
4. Quants bytes té el buffer pool d'InnoDB? Quina és la taxa d'encerts?

Temps estimat: 45 minuts.