Tables temporaires PostgreSQL

Je dois effectuer une requête 2,5 millions de fois. Cette requête génère des lignes dont j’ai besoin pour AVG(column) , puis utilise cette AVG pour filtrer la table de toutes les valeurs inférieures à la moyenne. Je dois ensuite INSERT ces résultats filtrés dans une table.

La seule façon de faire une telle chose avec une efficacité raisonnable semble être de créer une TEMPORARY TABLE pour chaque python-thread de requête-postmaster. J’espère juste que ces TEMPORARY TABLE ne seront pas persistés sur le disque dur (du tout) et restront en mémoire (RAM), sauf s’ils sont en panne de mémoire, bien sûr.

Je voudrais savoir si une TABLE TEMPORAIRE entraînera des écritures sur disque (qui interféreraient avec les INSERTS, c.-à-d. Ralentir le processus entier)

Notez que, dans Postgres, le comportement par défaut des tables temporaires est qu’elles ne sont pas automatiquement supprimées et que les données sont persistantes lors de la validation. Voir ON COMMIT .

Les tables temporaires sont cependant supprimées à la fin d’une session de firebase database :

Les tables temporaires sont automatiquement supprimées à la fin d’une session ou éventuellement à la fin de la transaction en cours.

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

  • Si vous souhaitez explicitement DROP une table temporaire à la fin d’une transaction, créez-la avec la syntaxe CREATE TEMPORARY TABLE ... ON COMMIT DROP .
  • En présence d’un pool de connexions , une session de firebase database peut s’étendre sur plusieurs sessions client. pour éviter les conflits dans CREATE , vous devez supprimer vos tables temporaires – avant de renvoyer une connexion au pool (par exemple, en effectuant tout ce qui est contenu dans une transaction et en utilisant la syntaxe de création ON COMMIT DROP ). précéder toute instruction CREATE TEMPORARY TABLE par une DROP TABLE IF EXISTS , qui présente l’avantage de fonctionner également en dehors des transactions, par exemple si la connexion est utilisée en mode de validation automatique.)
  • Pendant que la table temporaire est en cours d’utilisation, quelle quantité de mémoire correspondra-t-elle avant de déborder sur le disque? Voir l’option temp_buffers dans postgresql.conf
  • Y a-t-il autre chose dont je devrais m’inquiéter lorsque je travaille souvent avec des tables temporaires? Un vide est recommandé après que vous ayez des tables temporaires DROPped, pour nettoyer tous les tuples morts du catalogue. Postgres passera automatiquement l’aspirateur toutes les 3 minutes environ lors de l’utilisation des parameters par défaut ( auto_vacuum ).

Aussi, sans rapport avec votre question (mais peut-être liée à votre projet): gardez à l’esprit que si vous devez exécuter des requêtes sur une table temporaire après l’ avoir remplie, il est préférable de créer des index appropriés et de lancer un ANALYZE sur la table temporaire en question une fois que vous avez fini de l’insérer. Par défaut, l’optimiseur basé sur les coûts suppose qu’une nouvelle table temporaire a ~ 1000 lignes, ce qui peut entraîner de mauvaises performances si la table temporaire contient des millions de lignes.

Les tables temporaires ne fournissent qu’une garantie – elles sont supprimées à la fin de la session. Pour une petite table, vous aurez probablement la plupart de vos données dans le magasin de sauvegarde. Pour un grand tableau, je garantis que les données seront vidées sur le disque périodiquement, car le moteur de firebase database a besoin de plus d’espace de travail pour les autres demandes.

EDIT: Si vous avez absolument besoin de tables temporaires de RAM uniquement, vous pouvez créer un espace table pour votre firebase database sur un disque RAM (/ dev / shm works). Cela réduit la quantité d’E / S disque, mais attention, il est actuellement impossible de le faire sans écriture sur disque physique; le moteur de firebase database videra la liste des tables dans un stockage stable lorsque vous créerez la table temporaire.