Postgresql
Basics: PGconf 2017 - Postgresql when it's not your job
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
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
oushow 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
Activer les logs
dans postgresql.conf
1 2 3 4 | 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
1 2 3 4 5 6 | 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, leover
sera toujours traité apres lewhere
- l'expression doit correspondre à une window,