Quiz: Postgres in profondità, parte 2

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)

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.

Esempi di estensioni PostgreSQL rispetto ad ANSI SQL:

• La clausola RETURNING.
• ILIKE per il matching case-insensitive.
• Tipi array e relative operazioni.
• Tipi JSON/JSONB.

Lo standard ANSI SQL originale include:

• WITH (Common Table Expressions).
• LIKE per il pattern matching.
• Join LATERAL.
• Window functions.

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;

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.

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!

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

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.

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! 😅

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.

Postgres utilizzerà l’indice solo quando la condizione status = 'active' è presente nella query.

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

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)

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;