Sujet : postgresql
-
Gérer les extensions PostgreSQL : installation, gestion des versions, mise à jour et sauvegarde
Une extension regroupe des objets SQL, et souvent une bibliothèque partagée, sous un même nom avec un fichier de contrôle et des scripts versionnés ; CREATE EXTENSION l'installe par base de données, ALTER EXTENSION UPDATE applique les scripts de mise à jour de l'auteur, et pg_dump n'émet que la ligne CREATE EXTENSION. Il faut garder synchronisés les fichiers installés, la version du catalogue et la bibliothèque chargée, en particulier lors des mises à jour de paquets et de pg_upgrade.
-
Concevoir une table de séries temporelles en ajout seul dans PostgreSQL
Stocker les mesures dans une table partitionnée par plage temporelle avec timestamptz, une clé composite de série et de temps, des index adaptés au schéma de requête (B-tree par série, BRIN pour les scans temporels sur toute la table) et une rétention mise en œuvre en détachant puis en supprimant des partitions plutôt qu'avec DELETE ; ces choix découlent du fait que les lignes arrivent dans l'ordre chronologique et repartent par tranches temporelles entières.
-
Stocker des données dérivées dans PostgreSQL : colonnes générées contre vues matérialisées
Une colonne générée dérive une valeur par ligne à partir de cette seule ligne, soit virtuelle (calculée à la lecture), soit stockée (calculée à l'écriture), et ne peut utiliser que des expressions immuables ; une vue matérialisée stocke le résultat d'une requête quelconque et n'est fraîche qu'à hauteur de son dernier REFRESH. Utiliser des colonnes générées stockées pour la normalisation par ligne qui doit être indexée, des vues matérialisées pour les agrégats coûteux qui peuvent accuser un retard, et une table de synthèse maintenue lorsque ni l'une ni l'autre ne convient.
-
VACUUM, autovacuum et le ballonnement des tables
Le MVCC de PostgreSQL laisse derrière lui des versions de lignes mortes après les mises à jour et les suppressions ; VACUUM les récupère et maintient les statistiques ainsi que la protection contre le rebouclage des identifiants de transaction. Autovacuum devrait rester activé et être ajusté pour les tables très sollicitées.
-
Bases de données éphémères en conteneurs pour les tests d'intégration
Démarrer le véritable moteur de base de données dans un conteneur jetable par exécution de tests, garder ses données en mémoire, appliquer les migrations une seule fois sur une base de données modèle et la copier pour chaque fichier de test ; les tests sollicitent alors le véritable planificateur, les véritables contraintes et le véritable comportement d'isolation, au lieu d'un substitut en mémoire.
-
Upsert, row limits and identifier quoting across PostgreSQL, MySQL, SQLite and SQL Server
PostgreSQL's ON CONFLICT, MySQL's ON DUPLICATE KEY UPDATE, SQLite's PostgreSQL-style ON CONFLICT and SQL Server's MERGE are three families of upsert syntax, LIMIT/OFFSET has at least three shapes, and a quoted identifier means double quotes, backticks or square brackets depending on the engine.
-
Les niveaux d'isolation des transactions en pratique
Read committed, repeatable read et serializable échangent de la concurrence contre de la cohérence ; savoir quelles anomalies chaque niveau autorise permet de décider quand ajouter des verrous explicites ou des nouvelles tentatives.
-
Modifications de base de données sans interruption de service : expand et contract
Modifier un schéma en trois étapes livrables séparément : étendre (créer une nouvelle colonne ou table, conserver l'ancienne), migrer (écrire en double et rattraper par lots), puis réduire (supprimer l'ancien dès que tout le code utilise le nouveau) ; éviter les verrous longs en ne réécrivant jamais une table entière dans une seule instruction, et en limitant chaque attente avec lock_timeout.
-
Expressions de table commune et requêtes récursives avec WITH
WITH nomme une sous-requête pour le reste d'une instruction ; WITH RECURSIVE évalue un terme non récursif, puis répète un terme récursif jusqu'à ce qu'il ne produise plus de nouvelle ligne, ce qui permet de parcourir des arbres et des graphes de profondeur quelconque en une seule requête. Les CTE à usage unique et sans effet de bord sont repliées dans la requête externe sauf si MATERIALIZED est écrit, et les clauses SEARCH et CYCLE gèrent l'ordre et les boucles.
-
Colonnes JSONB : à quoi elles servent et quand une colonne classique vaut mieux
jsonb stocke du JSON analysé sous une forme binaire indexable avec GIN et interrogeable avec des opérateurs d'inclusion et de chemin ; il convient aux attributs épars, définis en externe ou réellement variables. Les données de forme fixe, celles qui nécessitent des contraintes, des clés étrangères ou des mises à jour champ par champ, ainsi que les documents volumineux fréquemment modifiés, ont leur place dans des colonnes ordinaires.
-
La mutualisation des connexions à la base de données et ses limites
Chaque connexion PostgreSQL est un processus qui a un coût en mémoire ; les applications devraient conserver un petit pool dimensionné à la concurrence réelle, définir des délais d'expiration à l'acquisition, et ne jamais laisser les gestionnaires de requêtes ouvrir des connexions à la volée.
-
Mesure des captures PostgreSQL SKIP LOCKED avec quatre consommateurs de file concurrents
Quatre transactions concurrentes ont chacune capturé 25 tâches synthétiques dans PostgreSQL 16.15. Les 100 identifiants renvoyés étaient uniques et aucune tâche n'est restée non capturée ; cela vérifie une phase de capture bornée, pas un traitement exactement-une-fois ni le remplacement d'un courtier de messages.
-
Statistiques du planificateur dans PostgreSQL : cibles de statistiques, colonnes corrélées et erreurs d'estimation
Le planificateur estime le nombre de lignes à partir de statistiques par colonne échantillonnées par ANALYZE (valeurs les plus fréquentes, histogrammes, nombres de valeurs distinctes) et suppose les colonnes indépendantes ; les erreurs d'estimation viennent de statistiques périmées, de colonnes asymétriques avec trop peu d'entrées, de colonnes corrélées et d'expressions. Relever la cible de statistiques sur des colonnes précises, créer des statistiques étendues pour les colonnes corrélées ou les expressions, relancer ANALYZE, et comparer les lignes estimées aux lignes réelles.
-
NULL en SQL : la logique à trois valeurs et ses pièges
NULL signifie inconnu ; c'est pourquoi toute comparaison avec NULL ne donne ni vrai ni faux, mais inconnu. WHERE rejette les lignes dont le résultat est inconnu, NOT IN avec un NULL ne sélectionne rien, les fonctions d'agrégation ignorent NULL, et une contrainte d'unicité autorise plusieurs NULL. Utiliser IS NULL, IS DISTINCT FROM et COALESCE de façon délibérée.
-
Les migrations de schéma avec un lock_timeout court et une reprise automatique provoquent moins d'incidents au déploiement
Hypothèse : la plupart des formes d'ALTER TABLE posent un verrou ACCESS EXCLUSIVE qui se met en file d'attente derrière toute transaction longue tout en bloquant chaque requête ultérieure ; les migrations exécutées avec un lock_timeout de quelques secondes et une boucle de reprise bornée produisent donc des interruptions de déploiement moins nombreuses et plus courtes que les mêmes migrations exécutées avec l'attente illimitée par défaut, au prix de quelques migrations qui doivent être relancées manuellement.
-
Les politiques de sécurité au niveau des lignes réduisent les fuites de données entre locataires par rapport au filtrage côté application
Hypothèse : les services multi-locataires qui appliquent l'isolation des locataires au moyen de politiques de sécurité au niveau des lignes de PostgreSQL (un réglage du locataire par connexion, vérifié par la base de données) présentent moins de bogues d'exposition entre locataires que les services qui ajoutent un prédicat tenant_id à chaque requête, car une requête qui omet le prédicat de locataire ne voit malgré tout que les lignes du locataire courant, et une requête qui ne définit jamais le locataire ne renvoie rien ou une erreur, au lieu des lignes de tous les locataires.
-
Les points de sauvegarde et l'état de transaction avortée dans PostgreSQL
Après toute erreur survenue à l'intérieur d'un bloc de transaction, PostgreSQL rejette toute commande ultérieure tant que le bloc n'a pas été annulé (rollback) ; un point de sauvegarde posé avant une instruction risquée permet à ROLLBACK TO SAVEPOINT de n'annuler que cette partie et de poursuivre. Utiliser les points de sauvegarde délibérément et avec parcimonie, et faire en sorte que les gestionnaires d'erreur effectuent un rollback plutôt que de relancer la même instruction sur la même connexion.
-
Réplication logique dans PostgreSQL : publications, abonnements et différences avec la réplication en continu
La réplication en continu (physique) transmet le journal des écritures anticipées vers un standby identique octet pour octet, de la même version majeure et de la même architecture ; la réplication logique publie les changements de lignes de tables sélectionnées vers un abonné qui peut exécuter une version majeure différente et conserver ses propres données. La réplication logique nécessite une identité de réplique, ne transporte ni le DDL ni les valeurs de séquence, et son slot retient le WAL sur le serveur de publication tant que l'abonné est en retard.
-
Répliques en lecture et retard de réplication : à quoi ressemblent les lectures obsolètes et comment les borner
Une réplique en flux (streaming replica) applique le journal du primaire avec un certain délai, si bien qu'une lecture effectuée juste après une écriture peut ne pas voir cette écriture. Il faut orienter les lectures selon le niveau d'obsolescence toléré par chaque appelant, n'utiliser les modes de réplication synchrone que là où leur latence est acceptable, et comprendre que le rejeu du journal peut annuler des requêtes longues sur la réplique.
-
Sessions longues et inactives-en-transaction dans PostgreSQL : ce qu'elles bloquent et comment les borner
Une transaction ouverte conserve ses verrous et épingle l'horizon xmin, si bien que VACUUM ne peut pas supprimer les lignes effacées après son début et que le DDL fait la queue derrière elle ; le pire cas est une session inactive en transaction parce qu'un client n'a jamais validé. Les repérer dans pg_stat_activity via xact_start et state, et les borner par rôle avec idle_in_transaction_session_timeout, transaction_timeout et statement_timeout.
Lisible par machine : JSON