Calcul et d'économiser de l'espace dans PostgreSQL

J'ai une table dans pg comme suit:

CREATE TABLE t (
    a BIGSERIAL NOT NULL,               -- 8 b
    b SMALLINT,                         -- 2 b
    c SMALLINT,                         -- 2 b
    d REAL,                             -- 4 b
    e REAL,                             -- 4 b
    f REAL,                             -- 4 b
    g INTEGER,                          -- 4 b
    h REAL,                             -- 4 b
    i REAL,                             -- 4 b
    j SMALLINT,                         -- 2 b
    k INTEGER,                          -- 4 b
    l INTEGER,                          -- 4 b
    m REAL,                             -- 4 b
    CONSTRAINT a_pkey PRIMARY KEY (a)
);

Ci-dessus ajoute jusqu'à 50 octets par ligne. Mon expérience est que j'ai besoin d'un autre 40% à 50% de la charge du système, sans même que l'utilisateur créé des index à la ci-dessus. Ainsi, environ 75 octets par ligne. Je vais avoir beaucoup, beaucoup de lignes dans la table, potentiellement, la hausse de 145 milliards de lignes, de sorte que le tableau va être repousser 13-14 téraoctets. Quels trucs, le cas échéant, pourrais-je l'utiliser pour compacter ce tableau? Mes idées possibles ci-dessous ...

Convertir le real valeurs de integer. Si ils peuvent stockées en tant que smallint, c'est une économie de 2 octets par champ.

Convertir les colonnes b .. m dans un tableau. Je n'ai pas besoin de faire une recherche sur ces colonnes, mais j'ai besoin d'être en mesure de retourner une valeur de la colonne à la fois. Donc, si j'ai besoin de la colonne g, je pourrais faire quelque chose comme

SELECT a, arr[5] FROM t;

Aurais-je économiser de l'espace avec l'option matrice? Y aurait-il une pénalité sur la vitesse?

D'autres idées?

  • Je pense que Erwin réponse est plus approprié de réponse ici pour la accepté de répondre.
InformationsquelleAutor punkish | 2010-06-03
  1. 11

    Je ne vois rien à gagner (et quelque chose à perdre) dans le stockage de plusieurs champs numériques dans un tableau.

    La la taille de chaque type numérique est clairement documenté, vous devez tout simplement utiliser la plus petite taille de type compatible avec votre choix de la gamme, la résolution; et c'est tout ce que vous pouvez faire.

    Je ne pense pas (mais je ne suis pas sûr) si il y a un alignement d'octets exigence pour les colonnes le long d'une ligne, dans ce cas, une réorganisation des colonnes pourrait modifier l'espace utilisé - mais je ne le pense pas.

    BTW, il y a un correctif frais généraux par ligne, sur 23 octets.

    • Que de 9,2, c'est le 24 octets par ligne pour la ligne d'en-tête et de 4 octets pour le décalage de page (stocké dans l'en-tête de page), ou de 28 octets par ligne. Il y a d'autres éléments qui peuvent entrer en jeu, par exemple de 1 octet par 8 colonnes qui soutiennent les valeurs NULL (NULL valeurs sont stockées sous un masque de bits).
    • Ce n'est pas tout à fait correct. L'en-tête de ligne (HeapTupleHeader) a 23 Octets, et non pas 24, selon le manuel de l' ici: There is a fixed-size header (occupying 23 bytes on most machines), followed by an optional null bitmap, an optional object ID field. La différence est pertinente, la valeur NULL masque de bits pour les tables avec 8 colonnes, s'inscrit dans cette réserve octet de décisions NULL stockage de libérer de ces tables.
    • Correct, mais en raison de l'alignement de types de données, entre les octets, les 23 et 24 il s'agit certainement d'un trou, et de départ sur l'octet 25 INT commence. Si l'en-tête est à seulement 23 octets, mais l'espace consommé est de 24 octets.
    InformationsquelleAutor leonbloy
  2. 171

    De La Colonne"Tetris"

    Fait, vous pouvez faire quelque chose, mais cela demande une compréhension plus profonde. Le mot-clé est alignement rembourrage. Chaque type de données a ses propres exigences alignement.

    Vous pouvez réduire l'espace perdu de rembourrage entre les colonnes en les ordonnant favorablement. La suite (extrême) exemple serait de perdre beaucoup d'espace disque physique:

    CREATE TABLE t (
    e int2    -- 6 bytes of padding after int2
  , a int8
  , f int2    -- 6 bytes of padding after int2
  , b int8
  , g int2    -- 6 bytes of padding after int2
  , c int8
  , h int2    -- 6 bytes of padding after int2
  , d int8)

    Pour enregistrer 24 octets par ligne, utiliser à la place:

    CREATE TABLE t (
    a int8
  , b int8
  , c int8
  , d int8
  , e int2
  , f int2
  , g int2
  , h int2)   -- 4 int2 occupy 8 byte (MAXALIGN), no padding at the end

    SQL Violon.

    En règle générale, si vous mettez de 8 octets colonnes d'abord, puis de 4 octets 2 octets 1 octet colonnes dernier, vous ne pouvez pas vous tromper.

    boolean, uuid et quelques autres types de besoin de rien d'alignement de rembourrage. text, varchar et autres "varlena" (de longueur variable) types nominalement besoin de "int" alignement (4 octets sur la plupart des machines). Mais en fait, il n'y a pas d'alignement de rembourrage dans le format du disque (à la différence dans la RAM). J'ai vérifié dans de nombreux tests. Finalement, j'ai trouvé l'explication dans un remarque: dans le code source:

    Notez également que nous permettons à la valeur nominale de l'alignement à être violés lors de l'entreposage de "paniers" varlenas;

    Normalement, vous pouvez économiser quelques octets par ligne au meilleur jouer de la de la colonne"tetris". Rien de tout cela est nécessaire dans la plupart des cas. Mais avec des milliards de lignes, il peut signifier un couple de gigaoctets facilement.

    Vous pouvez tester la colonne réel /taille de la ligne avec la fonction pg_column_size().

    Certains types d'occuper plus d'espace dans la mémoire RAM que sur disque (comprimé ou "paniers" format). Vous pouvez obtenir plus de résultats pour les constantes (RAM) que pour les colonnes de la table lors de l'essai de la même valeur (ou de la ligne des valeurs de vs ligne du tableau) avec pg_column_size().

    Enfin, certains types peuvent être comprimé ou "grillé" (stockées hors de la ligne), ou les deux.

    Frais généraux par tuple (ligne)

    4 octets par ligne pour l'élément pointeur - ne pas être sujet à des considérations ci-dessus.

    Et au moins 24 octets (23 + padding) pour le n-uplet d'en-tête. Le manuel pour la Base de données de Mise en Page:

    Il y a une taille fixe de l'en-tête (occupant 23 octets sur la plupart des machines),
    suivie par une option null, un objet facultatif champ ID, et
    les données de l'utilisateur.

    Pour le rembourrage entre en-tête et les données de l'utilisateur, vous avez besoin de savoir MAXALIGN sur votre serveur en général de 8 octets sur un système d'exploitation 64 bits (soit 4 octets sur un 32-bit OS). Si vous n'êtes pas sûr, consultez pg_controldata.

    Exécuter la ligne suivante dans votre Postgres binaire dir pour obtenir une réponse définitive:

    ./pg_controldata /path/to/my/dbcluster

    Le manuel de:

    Le réel de l'utilisateur des données (colonnes de la rangée) commence à l'offset
    indiqué par t_hoff, qui doit toujours être un multiple de la MAXALIGN
    distance de la plate-forme.

    De sorte que vous obtenez habituellement le stockage optimale par les données d'emballage multiple de 8 octets.

    Il n'y a rien à gagner dans la exemple que vous avez posté. Il est déjà emballé hermétiquement. 2 octets de remplissage après la dernière int2, 4 octets à la fin. Vous pouvez consolider le rembourrage à 6 octets à la fin, ce qui ne changerait rien.

    Frais généraux par les données de la page

    Taille de page de données est en général de 8 KO. Certains frais généraux /ballonnement à ce niveau, trop: Restes pas assez grand pour s'adapter à un autre tuple, et plus important encore morts lignes ou un pourcentage réservé aux taux de remplissage paramètre.

    Il ya un couple de d'autres facteurs de taille sur le disque à prendre en compte:

    Types de tableau?

    Avec un tableau type comme si vous étiez à l'évaluation, vous ajoutez 24 octets de surcharge pour le type. De Plus, les éléments du tableau occupent de l'espace comme d'habitude. Rien à y gagner.

    • en règle générale, si vous mettez de 8 octets colonnes d'abord, puis de 4 octets 2 octets 1 octet colonnes dernier, vous ne pouvez pas vous tromper. quelques légitime cool des conseils.
    • C'est un grand/réponse intéressante, mais une chose que je ne comprends pas est pourquoi l'ordre des noms de colonne dans la CREATE TABLE est important à tous. Je ne pensais pas que c'était important. Pourquoi ne pas/ne peut pas postgres ce faire tetris optimisation pour vous? Pourquoi est-ordre de la colonne considérée comme suffisamment importante pour garder que définis?
    • Parce que personne n'a mis en œuvre la division entre logique et physique de l'ordre de la colonne. l'élément TODO, mais pas trivial puisque c'est de jouer avec le système de catalogues de tous sur la place. Encore vrai presque 6 ans plus tard. Depuis Postgresql 9.3 un VIEW peut servir à présenter une autre colonne ordre de simple de cas (écrit sont propagées automatiquement, aussi.)
    • Merci, @Erwin. Il semble encore étrange de laisser à l'utilisateur, si. Je reçois que de jouer avec les catalogues système après la création de la table serait difficile, mais il ne semble pas que ce serait un problème si le réorganiser a été fait avant. Quel serait le problème avec quelque chose comme un paramètre de stockage pour y parvenir? Quelque chose le long des lignes de CREATE TABLE WITH column_reorder_ok qui implique "je ne se soucient pas de la logique de l'ordre des colonnes, afin de le modifier comme vous le souhaitez pour l'optimisation de la table". Ou, avec votre terminologie, WITH column_tetris_ok. 🙂
    • Sonne comme un élément utile. Un outil pour réorganiser les colonnes avant, le tableau est créé, ce qui évite les complications de déconner avec les catalogues système. Peut également être mis en œuvre dans un logiciel client, un outil pour optimiser CREATE TABLE déclaration pour un minimum de stockage ...
    • Pour info: votre réponse dans le top 15 sur news.ycombinator.com (2018-02-26). félicitations! les commentaires sont ici: news.ycombinator.com/item?id=16471242
    • Merci, c'est ce qui explique, tout d'un coup l'amour.
    • yup. j'ai pensé que vous pourriez vous demander à propos de tous les récents upvotes...
    • Je suis d'accord que quelque chose peut être fait, mais il est complexe. Lorsque vous ajouter et supprimer des colonnes, des lignes existantes restent compatibles, car la commande ne change pas. Récemment ajouté des colonnes d'aller sur la fin de la ligne, tandis que les colonnes supprimées jamais fait disparaître, mais sont juste désactivé et effectivement NULL avais. (Et, comme un avantage, les transactions peut encore voir cette colonne.) Certes, certains d'optimisation peut être fait avec une logique-physique de la carte sur la table, ou même sur le disque-segment (ou peu importe son nom).

    InformationsquelleAutor Erwin Brandstetter