postgresql-docs

Documentation PostgreSQL

Règles issues de la documentation PostgreSQL 18 qu'un modèle est susceptible de mal interpréter : valeurs par défaut surprenantes, pièges de verrouillage et de concurrence, changements de PostgreSQL 18 et les modèles recommandés par la documentation. Chaque règle se termine par l'URL de la documentation qui la justifie.

Version couverte

Les références suivent la documentation de PostgreSQL 18 (version mineure 18.6). Les faits qui ne sont valables qu'à partir de la version 18 sont précédés de "PostgreSQL 18". Vérifiez d'abord la version du serveur. Sur un serveur plus ancien, traitez chaque règle marquée "PostgreSQL 18" comme indisponible.

Comment naviguer

Lisez uniquement la référence nécessaire à la tâche, à partir du tableau ci-dessous. Sources : SOURCES.md.

Règles rapides

1. Dans PostgreSQL 18, une colonne générée sans mot-clé est VIRTUAL, calculée à la lecture. Écrivez STORED pour les statistiques ou la réplication logique. https://www.postgresql.org/docs/18/ddl-generated-columns.html https://www.postgresql.org/docs/18/sql-altertable.html
2. Utilisez des colonnes d'identité, pas serial. Une colonne d'identité est NOT NULL mais pas unique : ajoutez la PRIMARY KEY. https://www.postgresql.org/docs/18/ddl-identity-columns.html
3. Indexez vous-même les colonnes de référence de chaque clé étrangère. PostgreSQL n'indexe que le côté référencé. https://www.postgresql.org/docs/18/ddl-constraints.html
4. Un CREATE INDEX simple bloque les écritures pendant toute la construction. Sur les tables en production, utilisez CREATE INDEX CONCURRENTLY en dehors d'un bloc de transaction, et supprimez l'index INVALID qu'il laisse en cas d'échec. https://www.postgresql.org/docs/18/sql-createindex.html
5. Ajoutez les contraintes sur les grandes tables avec NOT VALID, puis VALIDATE CONSTRAINT, ce qui ne bloque pas les écritures. PostgreSQL 18 accepte également cela pour NOT NULL. https://www.postgresql.org/docs/18/sql-altertable.html
6. ADD COLUMN avec une valeur par défaut volatile, une colonne générée stockée ou une identité réécrit toute la table sous ACCESS EXCLUSIVE. Une valeur par défaut non volatile ne le fait pas. https://www.postgresql.org/docs/18/sql-altertable.html
7. Définissez lock_timeout dans les sessions de migration, et statement_timeout, lock_timeout et transaction_timeout par rôle ou session, jamais globalement. https://www.postgresql.org/docs/18/runtime-config-client.html
8. Utilisez l'upsert avec INSERT ... ON CONFLICT (cols) DO UPDATE, qui en READ COMMITTED insère ou met toujours à jour sous concurrence. MERGE peut échouer avec une violation unique lorsqu'une autre session insère la même clé. https://www.postgresql.org/docs/18/transaction-iso.html
9. Réessayez toute la transaction sur SQLSTATE 40001 et 40P01. PostgreSQL ne réessaie jamais tout seul. Testez le SQLSTATE, pas le message. https://www.postgresql.org/docs/18/mvcc-serialization-failure-handling.html
10. L'isolation par défaut est READ COMMITTED : chaque instruction voit un nouvel instantané. Les règles métier multi-lignes nécessitent SERIALIZABLE ou des verrous explicites. https://www.postgresql.org/docs/18/transaction-iso.html https://www.postgresql.org/docs/18/applevel-consistency.html
11. Pour les files d'attente de travaux, utilisez SELECT ... FOR UPDATE SKIP LOCKED, et FOR NO KEY UPDATE lorsque les clés restent inchangées. https://www.postgresql.org/docs/18/sql-select.html https://www.postgresql.org/docs/18/explicit-locking.html
12. NOT IN avec un NULL dans la liste ou la sous-requête n'est jamais vrai. Les fonctions d'agrégation autres que count renvoient NULL sur zéro ligne. https://www.postgresql.org/docs/18/functions-comparisons.html https://www.postgresql.org/docs/18/functions-aggregate.html
13. La pagination et LIMIT nécessitent un ORDER BY sur une clé unique, sinon les résultats diffèrent entre les exécutions. https://www.postgresql.org/docs/18/queries-limit.html
14. Stockez les instants en tant que timestamptz. TIMESTAMP '2004-10-19 10:23:54+02' est un horodatage sans fuseau horaire et le décalage est ignoré. https://www.postgresql.org/docs/18/datatype-datetime.html
15. now() est l'heure de début de la transaction. Utilisez clock_timestamp() pour l'heure réelle. Ne définissez jamais la valeur par défaut sur la chaîne 'now'. https://www.postgresql.org/docs/18/functions-datetime.html
16. Utilisez jsonb, indexé avec GIN (col jsonb_path_ops) pour les requêtes @>. L'opérateur doit s'appliquer à l'expression indexée pour que l'index soit utilisé. https://www.postgresql.org/docs/18/datatype-json.html
17. PostgreSQL 18 ajoute uuidv7(), un UUID ordonné dans le temps, à côté de gen_random_uuid(). https://www.postgresql.org/docs/18/functions-uuid.html
18. Le RETURNING de PostgreSQL 18 lit old.col et new.col dans INSERT, UPDATE, DELETE et MERGE. https://www.postgresql.org/docs/18/dml-returning.html
19. EXPLAIN ANALYZE exécute l'instruction : enveloppez les écritures dans BEGIN ... ROLLBACK. Dans PostgreSQL 18, il inclut automatiquement BUFFERS. https://www.postgresql.org/docs/18/sql-explain.html https://www.postgresql.org/docs/18/using-explain.html
20. Un arbre B multi-colonnes peut servir une requête sans sa colonne de tête dans PostgreSQL 18 (skip scan), mais uniquement lorsque cette colonne a peu de valeurs distinctes. https://www.postgresql.org/docs/18/indexes-multicolumn.html
21. Maintenez autovacuum activé et évitez VACUUM FULL. Autovacuum n'analyse jamais les tables partitionnées ou temporaires : analysez-les vous-même. https://www.postgresql.org/docs/18/routine-vacuuming.html
22. La sécurité au niveau des lignes ne fait rien jusqu'à ENABLE ROW LEVEL SECURITY, et les propriétaires de table la contournent sauf si FORCE ROW LEVEL SECURITY. https://www.postgresql.org/docs/18/ddl-rowsecurity.html
23. Une fonction SECURITY DEFINER a besoin de SET search_path = trusted_schema, pg_temp et d'EXECUTE révoqué de PUBLIC dans la même transaction. https://www.postgresql.org/docs/18/sql-createfunction.html
24. L'octroi d'une table ne s'étend pas aux séquences qu'elle utilise, y compris celles des colonnes serial. Définissez les privilèges de séquence séparément. https://www.postgresql.org/docs/18/sql-grant.html
25. Les clients ont par défaut sslmode=prefer, qui ne vérifie pas le serveur. Utilisez sslmode=verify-full. https://www.postgresql.org/docs/18/libpq-ssl.html
26. Construisez du SQL dynamique avec format('%I', ident) et EXECUTE ... USING pour les valeurs, jamais par concaténation. https://www.postgresql.org/docs/18/plpgsql-statements.html
27. Les index d'expression de recherche en texte intégral doivent utiliser to_tsvector('english', col) à 2 arguments, et les requêtes doivent répéter cette forme exacte. https://www.postgresql.org/docs/18/textsearch-tables.html

Références

Référence	À lire quand
schema-design.md	Création de tables : identité et séquences, colonnes générées, contraintes, clés temporelles, domaines, schémas et search_path, conception de partitionnement
migrations-and-locks.md	Rédaction d'une migration : niveaux de verrouillage ALTER TABLE et réécritures, NOT VALID, constructions d'index concurrents, REINDEX, attachement et détachement de partition
data-types.md	Choix des types numériques, text, bytea, enum, UUID, tableau ou intervalle, contraintes d'exclusion sur les intervalles
datetime.md	Tout ce qui concerne les horodatages, fuseaux horaires, intervalles, extract, date_trunc, to_char, to_timestamp
json.md	Stockage, interrogation, indexation ou mise à jour de JSONB, jsonpath, JSON_VALUE, JSON_TABLE, fonctions d'agrégation JSON
queries.md	Logique SELECT : sémantique NULL, jointures, LATERAL, regroupement, fonctions d'agrégation, fonctions de fenêtrage, pagination, UNION, CTE
writes.md	INSERT ON CONFLICT, UPDATE et DELETE avec jointures, traitement par lots des grandes modifications, RETURNING, MERGE, COPY, chargement en masse
indexes.md	Choix ou révision d'un index : types, ordre des colonnes, skip scan, partiel, expression, INCLUDE, pattern_ops, GIN, BRIN
concurrency.md	Niveaux d'isolation, nouvelles tentatives, verrous de ligne et de table, interblocages, verrous consultatifs, timeouts, LISTEN et NOTIFY
performance.md	Lecture d'EXPLAIN, statistiques et statistiques étendues, vacuum, paramètres de mémoire et du planificateur, requêtes parallèles, pg_stat_statements
security.md	GRANT et privilèges par défaut, rôles, sécurité au niveau des lignes, SECURITY DEFINER, mots de passe, TLS et paramètres de connexion libpq
plpgsql-triggers.md	Rédaction de fonctions, PL/pgSQL, SQL dynamique, exceptions, triggers, procédures
text-search.md	Fonctions de chaîne, LIKE et regex, guillemets, recherche en texte intégral avec tsvector, classement, ts_headline

Changements de PostgreSQL 18 à garder à l'esprit lors de la mise à niveau

• initdb active les sommes de contrôle de données par défaut, et pg_upgrade nécessite des paramètres correspondants. https://www.postgresql.org/docs/18/release-18.html
• VACUUM et ANALYZE sur une table partitionnée traitent également ses partitions. Écrivez ONLY pour le parent seul. https://www.postgresql.org/docs/18/release-18.html
• pg_upgrade conserve les statistiques du planificateur mais pas les statistiques étendues. https://www.postgresql.org/docs/18/release-18.html
• Les triggers AFTER s'exécutent avec le rôle actif lorsque l'événement a été mis en file d'attente. https://www.postgresql.org/docs/18/release-18.html
• Entrées/Sorties asynchrones (io_method, par défaut worker), et effective_io_concurrency par défaut à 16. https://www.postgresql.org/docs/18/runtime-config-resource.html

Anciens modèles

Ancien	Actuel
'now' ou 'today' littéraux dans les valeurs par défaut	now(), CURRENT_DATE (https://www.postgresql.org/docs/18/datatype-datetime.html)
Mots de passe MD5, méthode d'authentification password	scram-sha-256 (https://www.postgresql.org/docs/18/auth-password.html)