Aller au contenu

Postgresql

Basics: PGconf 2017 - Postgresql when it's not your job

Upgrading versions

Major: internal data storage is subject to change. Either dump (pg_dumpall from the new version) -> reload the database, or pg_upgrade.

All: look at the migration section in the releases notes

  • administration: CHANGE how to monitor
  • sql: ADD new sql commands
  • system catalogs

  • c-language api

  • Test client applications

  • Setup multiple conccurent applications with different db versions

PGPedia - Postgresql versions

Basics

write-ahead log (WAL)

fonctionnement

  • Quand une transaction est commit, elle est loggée dans le WAL
  • Puis un flush est réalisé et la transaction passe sur le disque
  • Si on a synchronous_commit = on (default, le plus safe), commit se termine uniquement quand le WAL flush est terminé

autres

  • un checkpoint = un point où le WAL écrit un de ses segments de 16MB
  • Le WAL sert aussi à reconstruire la BDD suite à un crash !
  • Stocké dans pg_xlog/

Replication

  • Binary = les modifs du WAL sont envoyées au slave et le slave les apply en local. Just works! Mais pas trop de configurabilité (tout est répliqué)
  • Logique = décode le WAL et retransforme en SQL
  • Trigger

Query planner

Transactions

VACUUM: process qui récupère les tuples utilisés par aucune transaction et redonne de l'espace

Stats

citusdata: statistics

ANALYZE: calcule des stats sur la BDD et stocke dans la table pg_statistic et pg_class. ANALYZE est lancé par autocacuum. Mais sinon c'est conseillé de faire un ANALYZE après chaque grosse opération/changement sur la BDD. On peut faire analyze verbose pour avoir plus di'nfos

par table: select * from pg_stats where tablename = 'thing'

Optimisations

  • Les defaults de postgres sont orientés compatibilité, pas performance: tuning obligatoire! Tuning Postgresql server
  • Voir tous les settings : show all ou show max_connections pour voir un setting particulier

Connection Pooling

PgBouncer ou PgPool-II

  • Postgres n'est pas multi-threadé. Il spawn un nouveau process pour chaque connection client (voir max_connections)
  • Autrement dit, quand il y a bcp de connections courtes, overhead d'ouverture/fermeture et multiplication des process

  • Avec la méthode du pooling, notion de connection virtuelle:

    • quand une connection est refermée pour le client, elle ne l'est pas pour Postgres et retourne dans un pool de connections disponibles à être réutilisées
      • de connections peuvent être gérées sans trop d'overhead pour Postgres

Config mémoire

  • shared_buffers: caching memoire Postgres. Règle officielle: 1/4 de la mémoire système
  • work_mem: mémoire allouée pour les sort. Attention, le réglage est par user.
  • maintenance_work_mem: mémoire allouée pour les opérations de maintenance comme VACUUM, CREATE INDEX etc. A augmenter temporairement pour les restaurations de dump par exemple. Sinon mettre 10%.
  • effective_cache_size: estimation taille du caching disque (ce n'est pas une allocation, utilisé par le query planner pour estimer s'il utilise la RAM ou le disque). Mettre 75% de la RAM.

Insertion de masse

COPY, pas INSERT

Data types

JSON representation

  • Les opérateurs -> et ->> permettent de query l'intérieur du json

JSON:

  • Stocke l'input tel quel sans modification (duplicatas, ordre...).
  • Doit etre parsé ) chaque exécution

JSONB:

  • Stocke en format binaire, plus compact
  • La conversion initiale prend du temps mais ensuite, pas de re-parsing à chaque exécution.
  • Support des index sur le type jsonb
  • Attention: pas de statistiques récupérables donc query planning mauvais? when to avoid jsonb

HSTORE

HSTORE: type de donnée key-value (compatible avec les index). Attention, pas de nesting! Utiliser JSONB pour ça.

Exemple: la colonne 'data' est un HSTORE:

INSERT INTO mytable(data) VALUES ('foo=>1, bar=>abc, baz=>stuff');

Pour récupérer une valeur dedans, il faut bien sûr appeler la clé:

SELECT data->'bar' from mytable; retourne abc

Index

index B-Tree:

  • Les single column accélèrent les recherches sur >, <, = etc.
  • Multi-column (composite) marche en fonction de l'ordre, si l'index est sur A,B,C il faut au moins avoir A,B en clause
  • Expression index: c'est une expression qui est indexée !
  • Partial index: pas obligé d'indexer la totalité de la colonne, on utilise un WHEN pour limiter

select * from pg_stat_user_indexes; combien de fois un index est utilisé

index GIN/GIST

Materialized views

  • Une view normale est juste une query prédéfinie. Quand on appelle la vue, on appelle la query... (create view documentation)
  • Une materialized view (create materialized view masupervue as) est un snapshot du résultat de la query mis dans une table.
  • Il faut donc utiliser la commande refresh materialized view masupervue pour remettre à jour TOUTE la vue (pas de maj partielle). Possibilités: eager update avec des trigger (bof...), lazy update (expiration...)

CLI

  • \x pour passer en mode liste
  • \i pour inserer un fichier local
  • \d pour lister les tables

Activer les logs

dans postgresql.conf

logging_collector = on
log_connections = on
log_disconnections = on
log_hostname = on

vérifier dans pgcli: show logging_collector; (rien ne sera loggé tant que le logging_collector est off)

où sont les logs ? = show data_directory + show log_directory

array_agg

renvoie un array formé de plusieurs valeurs récupérées en requête sql

postgres@localhost:postgres> select array_agg(x) from generate_series(3,10) as x
+---------------------------+
| array_agg                 |
|---------------------------|
| [3, 4, 5, 6, 7, 8, 9, 10] |
+---------------------------+

Attention: generate_series est proprietaire à Postgres...

window function

  • operateur over dans le select. Attention, le over sera toujours traité apres le where
  • l'expression doit correspondre à une window,