Séquence et ordre chronologique des valeurs fournies

[Phil]

Merci à Sébastien pour sa question :

"Des IDs, basés sur une séquence avec un cache de 20, sont stockés dans un ordre totalement désordonné par rapport à la chronologie des évènements. Une colonne timestamp a permis de mettre en évidence le problème. Contrairement à Oracle, les IDs pré-alloués pour une séquence (avec cache > 1) seraient alloués par session. "

Réponse :

Le standard SQL impose l'unicité, pas de fournir les numéros dans un ordre chronologique.
Le cache des séquences PostgreSQL est, comme tu le supposes, implémenté en le plaçant dans la mémoire de travail de chaque session. C'est bien plus efficace que de le mettre en mémoire partagée puisqu'il s'adapte dynamiquement à la concurrence. Si 20 sessions travaillent en parallèle en utilisant une séquence dotée d'un cache de 20 alors le cache effectif est de 400 etc.
L'implémentation d'Oracle est en mémoire partagée. La taille du cache est statique et doit être calculée a priori en fonction de la plus grande charge concurrente attendue. En contrepartie, il est possible pour Oracle de garantir la livraisons des numéros de séquence dans l'ordre chronologique en utilisant explicitement le mot clé "ORDER". "NOORDER" est la valeur par défaut car "ORDER" doit être moins performant, je pense notamment au cas de RAC (plusieurs instances pour la même base) où le cache de la séquence doit nécessairement être dupliqué/partagé au niveau des différentes instances.

Mon conseil, dans tous les cas, est de s'en tenir au standard SQL. S'il y a un tri chronologique nécessaire, la clause "ORDER BY" doit porter sur une colonne contenant à coup sûr cette information, pas sur une colonne d'identifiants basés sur une séquence avec un comportement dépendant de l'implémentation. A l'avenir, les uuidv7 pourraient répondre à ce besoin, en assurant en plus une unicité universelle. Ils sont déjà disponibles sur PostgreSQL sous forme d'extensions et seront nativement disponibles à partir de PostgreSQL 17.