Tables temporaires PostgreSQL

Question

Je dois exécuter une requête 2,5 millions de fois. Cette requête génère quelques lignes que je dois AVG(column) et ensuite utiliser ceci AVG pour filtrer le tableau de toutes les valeurs inférieures à la moyenne. Je dois ensuite INSERT ces résultats filtrés dans un tableau.

La seule façon de faire une telle chose avec une efficacité raisonnable, semble être de créer une TEMPORARY TABLE pour chaque query-postmaster python-thread. J'espère juste que ces TEMPORARY TABLE ne seront pas persistés sur le disque dur (du tout) et resteront en mémoire (RAM), à moins qu'ils ne soient hors de la mémoire de travail, bien sûr.

Answer 1

Veuillez noter que, dans Postgres, le comportement par défaut des tables temporaires est qu'elles ne sont pas automatiquement abandonnées, et que les données sont persistées lors de la validation. Voir ON COMMIT .

Les tables temporaires le sont, cependant, déposé à la fin d'une session de base de données :

Les tables temporaires sont automatiquement abandonnées à la fin d'une session, ou facultativement à la fin de la transaction en cours.

Il y a de multiples considérations à prendre en compte :

• Si vous voulez explicitement DROP une table temporaire à la fin d'une transaction, créez-la avec la commande CREATE TEMPORARY TABLE ... ON COMMIT DROP la syntaxe.
• En présence d'une mise en commun des connexions Pour éviter les conflits entre les sessions de clients, une session de base de données peut couvrir plusieurs sessions de clients. CREATE vous devez supprimer vos tables temporaires - soit avant de renvoyer une connexion au pool (par exemple, en effectuant tout dans une transaction et en utilisant la fonction ON COMMIT DROP syntaxe de création), ou en fonction des besoins (en faisant précéder toute demande d'accès à l'information d'une demande d'accès à l'information). CREATE TEMPORARY TABLE avec une déclaration correspondante DROP TABLE IF EXISTS qui a l'avantage de fonctionner également en dehors des transactions, par exemple si la connexion est utilisée en mode auto-commit).
• Pendant que la table temporaire est en cours d'utilisation, quelle quantité de celle-ci pourra tenir en mémoire avant de déborder sur le disque ? Voir le temp_buffers option dans postgresql.conf
• Y a-t-il autre chose dont je dois m'inquiéter lorsque je travaille souvent avec des tables temporaires ? Il est recommandé de faire le vide après avoir supprimé les tables temporaires, afin de nettoyer les tuples morts du catalogue. Postgres fera automatiquement le vide toutes les 3 minutes environ pour vous si vous utilisez les paramètres par défaut ( auto_vacuum ).

Par ailleurs, sans rapport avec votre question (mais peut-être en rapport avec votre projet), gardez à l'esprit que, si vous devez exécuter des requêtes sur une table temporaire, il est possible que vous ayez besoin d'une table temporaire. après vous l'avez alimenté, il est bon de créer des indices appropriés et d'émettre un message d'alerte. ANALYZE sur la table temporaire en question après vous avez fini d'y insérer des données. Par défaut, l'optimiseur basé sur les coûts suppose qu'une table temporaire nouvellement créée contient ~1000 lignes, ce qui peut entraîner des performances médiocres si la table temporaire contient en réalité des millions de lignes.

Answer 2

Les tables temporaires n'offrent qu'une seule garantie : elles sont abandonnées à la fin de la session. Pour une petite table, vous aurez probablement la plupart de vos données dans le backing store. Pour une grande table, je vous garantis que les données seront périodiquement transférées sur le disque, car le moteur de la base de données a besoin de plus d'espace de travail pour d'autres demandes.

EDIT : Si vous avez absolument besoin de tables temporaires en RAM uniquement, vous pouvez créer un espace de table pour votre base de données sur un disque RAM (/dev/shm fonctionne). Cela réduit la quantité d'entrées-sorties sur le disque, mais attention, il n'est actuellement pas possible de le faire sans écriture sur le disque physique ; le moteur de base de données effacera la liste des tables vers le stockage stable lorsque vous créerez la table temporaire.