Nombre maximal (utilisable) de lignes dans une table Postgresql

Question

Je réalise que, selon la documentation de Pg ( http://www.postgresql.org/about/ ), on peut stocker un nombre illimité de lignes dans un tableau. Toutefois, quelle est la "règle empirique" pour le nombre de lignes utilisables, le cas échéant ?

Le contexte : Je veux stocker des relevés quotidiens pendant quelques décennies pour 13 millions de cellules. Cela donne 13 M * (366|365) * 20 ~ 9,5e10, soit 95 B lignes (en réalité, environ 120 B lignes).

Donc, en utilisant le partitionnement des tables, j'ai créé une table principale, puis des tables héritées par année. Cela divise les lignes à ~ 5.2 B lignes par table.

Chaque rangée est composée de 9 SMALLINTs et de deux INTs, soit 26 octets. Ajoutez à cela, l'overhead Pg de 23 octets par ligne, et nous obtenons 49 octets par ligne. Ainsi, chaque table, sans PK ou autre index, pèsera environ 0,25 To.

Pour commencer, je n'ai créé qu'un sous-ensemble des données ci-dessus, c'est-à-dire seulement pour environ 250 000 cellules. Je dois effectuer un certain nombre de réglages (créer des index appropriés, etc.), mais les performances sont vraiment terribles pour le moment. En outre, chaque fois que j'ai besoin d'ajouter des données, je dois abandonner les clés et les recréer. Le point positif est qu'une fois que tout sera chargé, ce sera une base de données en lecture seule.

Des suggestions ? Une autre stratégie de partitionnement ?

Answer 1

Il ne s'agit pas seulement de "quelques réglages (index, etc.)". C'est crucial et indispensable.

Vous avez posté peu de détails, mais essayons.

La règle est la suivante : Essayer de trouver l'ensemble de travail le plus commun. Voyez s'il tient dans la RAM. Optimisez le matériel, les paramètres de tampon PG/OS et les index/clusters PG pour cet ensemble. Sinon, cherchez des agrégats, ou si ce n'est pas acceptable et que vous avez besoin d'un accès totalement aléatoire, réfléchissez au matériel qui pourrait scanner la table entière pour vous en un temps raisonnable.

Quelle est la taille de votre tableau (en gigaoctets) ? Comment se compare-t-elle à la RAM totale ? Quels sont vos paramètres PG, notamment shared_buffers et effective_cache_size ? S'agit-il d'un serveur dédié ? Si vous avez une table de 250 gigaoctets et environ 10 gigaoctets de RAM, cela signifie que vous ne pouvez contenir que 4 % de la table.

Y a-t-il des colonnes qui sont couramment utilisées pour le filtrage, comme l'état ou la date ? Pouvez-vous identifier l'ensemble de travail le plus couramment utilisé (comme le mois dernier uniquement) ? Si c'est le cas, envisagez le partitionnement ou le clustering sur ces colonnes, et indexez-les certainement. Fondamentalement, vous essayez de vous assurer que la plus grande partie possible de l'ensemble de travail tient dans la RAM.

Évitez à tout prix de scanner le tableau s'il ne tient pas dans la mémoire vive. Si vous avez vraiment besoin d'un accès absolument aléatoire, la seule façon dont il pourrait être utilisable est un matériel vraiment sophistiqué. Vous auriez besoin d'une configuration stockage persistant/RAM capable de lire 250 Go en un temps raisonnable.