Dan Levy

Quiz: Postgres in profondità, parte 2

Sei un duro dell'SQL? O un rammollito?

Parte 2 di 2. Torna alla Parte 1

Benvenuti alla seconda parte del mio Deep Postgres Quiz!

Questo quiz copre un mix di funzionalità familiari e aspetti meno noti di PostgreSQL, oltre a diverse insidie: JOIN, NULL, standard ANSI SQL, transazioni, internals, indicizzazione, campionamento dei dati… e molto altro!

In bocca al lupo! 🍀

1. 

Riscaldamento: Join

Quale non è ❌ un’espressione JOIN valida in PostgreSQL?

Sapevi dell’esistenza di CROSS JOIN, vero?

Il JOIN ALL ti ha fatto pensare a un’altra operazione, UNION ALL?

La risposta corretta qui è JOIN ALL ❌.

I tipi di JOIN validi sono:

  • INNER JOIN (il JOIN predefinito)
  • LEFT JOIN (o LEFT OUTER JOIN)
  • RIGHT JOIN (o RIGHT OUTER JOIN)
  • FULL JOIN (o FULL OUTER JOIN)
  • CROSS JOIN (il prodotto cartesiano, a meno che una clausola WHERE successiva non lo filtri)

2. 

Colonne Identity

Qual è la sintassi (Standard SQL) per una colonna IDENTITY ad incremento automatico in PostgreSQL (v10+)?

Sebbene SERIAL sia ancora comunemente usato, il modo conforme allo Standard SQL è:

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  • id INT IDENTITY(1,1) è la sintassi di SQL Server.
  • id INT AUTO_INCREMENT è la sintassi di MySQL.
  • id INTEGER AUTO_INCREMENT PRIMARY KEY è la sintassi di MySQL.

Questo approccio presenta diversi vantaggi rispetto a SERIAL:

  • È conforme allo Standard SQL
  • Migliore gestione della proprietà della sequenza
  • Semantica più chiara riguardo agli inserimenti manuali
  • Funziona meglio con la replica

SERIAL è in realtà solo una scorciatoia per creare una sequenza e impostare un valore predefinito, il che può causare problemi con dump e restore.

3. 

ANSI SQL

Quale clausola è documentata da PostgreSQL come un’estensione allo standard SQL?

La clausola RETURNING è documentata da PostgreSQL come un’estensione. Anche ILIKE è un’estensione, e jsonb è un tipo specifico di PostgreSQL. Array e JSON hanno invece funzionalità definite nello standard SQL: non vanno classificati in blocco come non standard. WITH, LATERAL e le funzioni finestra sono entrati in edizioni successive dello standard SQL, non nell’originale ANSI SQL.

4. 

Transazioni

A parte “Dipende dagli effetti collaterali”, cosa succede dopo un errore?

BEGIN;
UPDATE students
  SET status = 'alumni'
  WHERE grade = 12;
UPDATE students
  SET graduation_date = CURRENT_DATE
  WHERE grade = 12;
UPDATE students
  SET grade = 'GRADUATED'; -- 🚨 Error: type mismatch
COMMIT;

Quando si verifica un errore qui:

  • L’intera transazione viene annullata (rollback)
  • Nessuna modifica viene confermata (commit)
  • La transazione viene contrassegnata come fallita

Per eseguire un commit parziale:

BEGIN;
UPDATE ...;
SAVEPOINT my_save;
UPDATE ...; -- Error
ROLLBACK TO my_save;
COMMIT;

5. 

SQL Internals

Quale condizione ON può utilizzare PostgreSQL per un HASH JOIN?

SELECT *
FROM students a
JOIN grades b
ON /* What goes here? ⁉ */;

Un hash join è possibile con una condizione di uguaglianza (=) nella clausola ON.

  • = è la forma del predicato chiave per gli hash join
  • > e >= non sono predicati per hash join
  • HASH JOIN non è una sintassi valida in PostgreSQL

Il planner potrebbe comunque scegliere un nested loop o un merge join a seconda delle dimensioni della tabella, delle statistiche, degli indici e delle impostazioni della memoria.

6. 

Indexing

Quale indice è il migliore per questa query?

-- Query:
SELECT * FROM students
WHERE grade_level = 42
  AND last_name IN ('Levy', 'Smith');

Per me, la cosa sorprendente qui è che un indice multi-colonna può essere utilizzato anche quando l’ordine delle colonne nel testo della query non corrisponde alla definizione dell’indice.

Nota: Con gli indici B-tree, la colonna principale (leading column) è fondamentale. Poiché questa query filtra per grade_level e last_name, un indice che inizia con grade_level seguito da last_name è un’ottima scelta.

Più velocemente SQL riesce a selezionare il minor numero di righe, migliori saranno le prestazioni.

Ad esempio, se hai una tabella task e sai che status è più selettivo di created_at, potresti scegliere un indice che inizia con status per una query come status = 'done' AND created_at > '2024-01-01'. L’ordine delle condizioni nel testo della clausola WHERE non è ciò che conta; contano l’ordine delle colonne dell’indice e la forma dei predicati.

Un indice B+Tree può essere utilizzato per:

  • La colonna principale, o un prefisso sinistro delle colonne.
  • Operatori come =, >, <, BETWEEN e IN, a seconda della forma della query.
  • Corrispondenza di prefissi, LIKE 'prefix%'.

Grazie a u/mwdb2 per aver segnalato un errore precedente in questa spiegazione!

7. 

Quoting 1

Qual è la sintassi corretta per i nomi delle colonne case-sensitive?

CREATE TABLE grades (
  "Student Name" VARCHAR(100),
  'Student Grade' INTEGER
);

In PostgreSQL:

  • Le virgolette doppie (") si usano per gli identificatori (nomi di tabelle, nomi di colonne)
  • Le virgolette singole (') si usano per le stringhe letterali

Best practice:

  • Evita l’uso di maiuscole/minuscole miste o spazi nei nomi
  • Usa lo snake_case per gli identificatori
  • Usa gli identificatori tra virgolette solo quando è assolutamente necessario

8. 

Quoting 2

Come si specifica un nome di colonna che contiene un punto?

CREATE TABLE teachers (
  id INT GENERATED BY DEFAULT AS IDENTITY,
  first.name TEXT,
  -- How do you include the column: first.name?
  salary NUMERIC
);

PostgreSQL utilizza i doppi apici per il quoting degli identificatori:

  • Doppi apici "..." per gli identificatori (nomi di colonne, nomi di tabelle)
  • Singoli apici '...' per le stringhe letterali
  • La forma first.name viene interpretata come nome_tabella.nome_colonna

Senza virgolette, un punto nel nome di una colonna verrebbe interpretato come un separatore schema/tabella! Inoltre, tutti gli identificatori non virgolettati vengono convertiti in minuscolo per impostazione predefinita.

Per ridurre le sorprese, è meglio usare lo snake_case ed evitare l’uso di caratteri speciali.

9. 

Campionamento dei Dati

Qual è la sintassi valida per campionare casualmente il 10% degli studenti?

Questa è difficile! Contiene circa 6 trappole, tra cui:

  1. Descrizione di alto livello + frammenti di sintassi. Mette alla prova come adatti e assembli informazioni incomplete.
  2. Richiede una conoscenza approfondita della sintassi di TABLESAMPLE, poiché nessuna delle opzioni TABLESAMPLE utilizza la sintassi corretta! (Sinistro, lo so! Continua così, credo in te!!! ❤️)
  3. ROW_NUMBER() OVER (ORDER BY RAND()) e ORDER BY RANDOM() > 10 sono ulteriori depistaggi. Sembrano corretti, ma non lo sono. RAND() non esiste. E RANDOM() > 10 è un’espressione booleana che sarà sempre falsa poiché RANDOM() restituisce sempre un valore nell’intervallo 0.0-1.0. Un altro problema: ORDER BY RANDOM() > 10 ordinerebbe in base a un valore booleano, non a un numero casuale. Ma quel > 10 sembrava proprio una percentuale, vero?
  4. BERNOULLI è valido, tuttavia SAMPLETABLE non lo è.
  5. WHERE RANDOM() >= 0.1 è un trucco! RANDOM() restituisce un valore nell’intervallo 0.0 <= x < 1.0, quindi >= 0.1 restituirebbe circa il 90% delle righe, non il 10%.

Per quanto riguarda il metodo TABLESAMPLE, PostgreSQL supporta due metodi di campionamento:

  • BERNOULLI: Ogni riga ha la stessa probabilità.
  • SYSTEM: Campionamento a livello di blocco (più veloce ma meno casuale).

Per saperne di più sul campionamento, consulta questo articolo di Render.

Esempio:

-- Sample 10% of rows
SELECT * FROM students TABLESAMPLE BERNOULLI (10);


-- For repeatable results, use REPEATABLE:
SELECT * FROM students
TABLESAMPLE BERNOULLI (10) REPEATABLE (42);

Vale anche la pena menzionare che TABLESAMPLE è stato segnalato per non essere molto casuale. Se hai bisogno di un campione di dimensioni fisse più casuale, considera l’uso di ORDER BY RANDOM() LIMIT 10. Ma tieni presente che questo restituisce 10 righe, non il 10%, e può essere lento su tabelle di grandi dimensioni.

Esistono anche alcune estensioni di terze parti che forniscono metodi di campionamento casuale migliori, ma questo va oltre lo scopo di questa sfida! 😅

10. 

Indicizzazione

Cosa fa questo incantesimo?

CREATE INDEX idx_active_students
ON students(last_name)
WHERE status = 'active';

Questo trucco è chiamato Partial Index (Indice Parziale).

Sono utili per ridurre l’uso del disco e velocizzare le query quando viene interrogato tipicamente solo un sottoinsieme di righe.

Il planner può considerare l’indice quando riesce a dimostrare che il predicato della query implica status = 'active'. Non serve necessariamente lo stesso testo, ma il riconoscimento ha limiti. L’idoneità non garantisce che l’indice venga scelto.

Promemoria: Postgres non supporta le ‘query hints’ come altri RDBMS. Il query planner deciderà autonomamente quando utilizzare gli indici appropriati.

11. 

Comportamento di NULL

Quante righe restituirà questa query?

SELECT COUNT(*)
FROM students
WHERE grade = NULL;

Ok, questa era un po’ a trabocchetto. Il punto è che x = NULL non corrisponderà mai a nessuna riga, e restituirà SEMPRE una riga con un conteggio di 0.

Poiché NULL è un valore sconosciuto, non può essere confrontato con nulla, nemmeno con se stesso.

Il modo corretto per verificare i valori NULL è con IS NULL.

Esempio:

postgres=# SELECT count(*)
  FROM students
  WHERE age = null;
count
-------
    0
(1 row)


postgres=#
postgres=# SELECT count(*)
  FROM students
  WHERE age is null;
count
-------
    3
(1 row)

12. 

Performance

Cosa fa EXPLAIN ANALYZE per le istruzioni UPDATE?

EXPLAIN ANALYZE
UPDATE students
SET grade = grade + 1
WHERE status = 'active';

EXPLAIN ANALYZE esegue effettivamente la query! Fai attenzione quando analizzi un’operazione di scrittura, nello specifico le istruzioni UPDATE, DELETE e INSERT. Potresti dover eseguire operazioni di pulizia dopo ogni EXPLAIN ANALYZE per ottenere risultati ripetibili.

Best practice:

  • Usa EXPLAIN (senza ANALYZE) per UPDATE/DELETE/INSERT
  • Avvolgi le modifiche in transazioni:
BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;

Ottimo lavoro! Hai approfondito diversi aspetti di PostgreSQL! 🐘

Spero che tu abbia imparato qualcosa di nuovo, o che almeno tu abbia ottenuto un punteggio di cui vantarti! 🏆

Vuoi altre emozioni forti? Dai un’occhiata alla mia Quiz Collection per un divertimento infinito*!

Loading comments...