Indexation lors d'une migration d'Oracle Database vers PostgreSQL

[Phil]

Merci à Chantal pour sa question :

Lors de notre migration vers PostgreSQL, sera-t-il nécessaire de revoir tous les index(s) créés aujourd’hui sur Oracle ?

Réponse :

PostgreSQL présente bien plus de méthodes d'indexation qu'Oracle Database (BLOOM, HASH, BRIN etc.) mais l'index le plus courant reste le B-Tree.
Dans un premier temps, je suggère de recréer avec PostgreSQL les index B-Tree utilisés avec Oracle Database. C'est une bonne base de travail.
En théorie, les optimiseurs d’Oracle et PostgreSQL fonctionnent de manière assez proche en ce qui concerne le choix de passage par un index, cf https://pgphil.ovh/correlation_10_devel_01.php
Ensuite, lors de tests et en préproduction, il est nécessaire d'analyser les performances. Dans ce cadre, il faut déterminer s’il convient d’ajouter des index ou de les modifier. Attention par exemple aux index créés sur les colonnes de type texte pour les filtres LIKE : cf https://pgphil.ovh/chaines94.php
Enfin, choisir de retirer un index est toujours compliqué. C'est malgré tout possible après une période d'évaluation étendue. Je prépare une page sur le sujet.

[Phil]

Une page consacrée au cas particulier des clés étrangères : https://pgphil.ovh/index_foreign_key_11_01.php

[Phil]

Merci à un administrateur Oracle pour sa question (ou plutôt sa remarque) :

"Dans ta page sur les index des FK tu dis qu'Oracle verrouille moins finement par rapport à PostgreSQL et que c'est un avantage de PostgreSQL. Mais tu utilises la version gratuite Express Edition que personne n'utilise en prod et pas une Enterprise Edition qui est bien plus efficace."

Réponse :

J'ai choisi l'Express Edition parce qu'elle est faite pour ça, pas pour présenter artificiellement un cas défavorable à Oracle. Il faut noter que l'Express Edition 18c est bien plus proche de l'Enterprise Edition 18c que l'Express Edition 11g ne l'était de l'Enterprise Edition 11g.
De toute façon, Express Edition, Standard Edition 2, ou Enterprise Edition avec toutes les options, cela ne change rien sur cet aspect. Je vous invite à refaire le test avec une 18c Enterprise Edition pour vous en convaincre.
PostgreSQL ne nécessite pas d'indexer la clé étrangère pour éviter la situation de blocage que je présente, Oracle Database même dans sa dernière version en date au 05/12/2018 et son édition la plus onéreuse ne peut pas l'éviter sans index.

[Phil]

Merci à un administrateur Oracle pour sa question :

"Tu es vraiment sûr que PostgreSQL ne verrouille pas comme Oracle ? Dans ton test, tu demandes des plans d'exécution mais tu ne fais pas les opérations..."

Réponse :
Sûr. Je lance des EXPLAIN ANALYZE qui, au contraire des EXPLAIN, exécutent l'instruction. Vous pouvez refaire le test sans EXPLAIN ANALYZE pour vous convaincre.