Le saviez-vous ? Les tables dans PostgreSQL sont limitées à 1600 colonnes

Saviez-vous qu’une table ne peut pas contenir plus de 1 600 colonnes ? Cet article de blog est inspiré d’une conversation avec Pierre Ducroquet.

Tout d’abord, la documentation

L’annexe K de la documentation PostgreSQL indique qu’une table peut contenir jusqu’à 1 600 colonnes.

Il s’agit d’une limite codée en dur, visible dans le code source à l’adresse src/include/access/htup_details.h :

#define MaxTupleAttributeNumber 1664
#define MaxHeapAttributeNumber	1600

Atteindre la limite de manière classique

Vérifions d’abord cette affirmation par quelques tests.

Jouer avec la définition de la table

Nous utilisons ici un script bash simple, facile à adapter aux différents tests.

-- Exemple classique

DO $$
DECLARE
    i int;
BEGIN
    EXECUTE 'DROP TABLE IF EXISTS tint_1601;';
    EXECUTE 'CREATE TABLE tint_1601(i_1 int);';
    FOR i IN 2..1601 LOOP
        EXECUTE format('ALTER TABLE tint_1601 ADD COLUMN i_%s int;', i);
    END LOOP;
END $$;

Le résultat est le suivant :

NOTICE:  table "tint_1600" does not exist, skipping
ERROR:  tables can have at most 1600 columns
CONTEXT:  SQL statement "ALTER TABLE tint_1600 ADD COLUMN i_1601 int;"
PL/pgSQL function inline_code_block line 8 at EXECUTE

Jusqu’ici, tout va bien (ou du moins, tout fonctionne comme prévu).

Vous pourriez tenter de remplacer le type int4 par le type int2 pour arriver à créer une table de plus de 1 600 colonnes. Mais cela ne fonctionnera pas plus, car il s’agit d’une limite codée en dur.

Jouer avec le contenu d’une table

Générons une table de 1600 colonnes à partir du script sql précédent :

DO $$
DECLARE
    i int;
BEGIN
    EXECUTE 'DROP TABLE IF EXISTS tint_1600;';
    EXECUTE 'CREATE TABLE tint_1600(i_1 int);';
    FOR i IN 2..1600 LOOP
        EXECUTE format('ALTER TABLE tint_1600 ADD COLUMN i_%s int;', i);
    END LOOP;
END $$;

Construisons puis insérons un tuple de 1600 colonnes :

DO $$
DECLARE
    s TEXT;
    rows_inserted int;
BEGIN
    s := format(
                 'INSERT INTO tint_1600 VALUES (1%s);'
               , repeat( ',1' , 1599 )
               );
    EXECUTE s;

    GET DIAGNOSTICS rows_inserted = ROW_COUNT;
    RAISE NOTICE 'Rows inserted: %', rows_inserted;
END $$;

The output is:

NOTICE:  Rows inserted: 1
DO

Sans surprise, c’est encore un succès.

Trop gros pour échouer ?

Continuons à repousser les limites.

On créé maintenant une autre table de 1 600 colonnes, cette fois avec le type de données char(127).

On réutilise notre script bash avec quelques modifications :

-- Create a table with 1,600 columns: 1 x int + 1599 x char(127)
DO $$
DECLARE
    i int;
BEGIN
    EXECUTE 'DROP TABLE IF EXISTS tint_1600;';
    EXECUTE 'CREATE TABLE tint_1600(i_1 int);';
    FOR i IN 2..1600 LOOP
        EXECUTE format('ALTER TABLE tint_1600 ADD COLUMN c_%s char(127) NOT NULL;', i);
    END LOOP;
END $$;

-- Insert a tuple - 1 x int + 1599 x char(127)
DO $$
DECLARE
    s TEXT;
BEGIN
    s := format(
                 'INSERT INTO tint_1600 VALUES (1%s);'
               , repeat( $q$,'1'::char(127)$q$ , 1599 )
               );
    EXECUTE s;
END $$;```

Le résultat est :

ERROR: row is too big: size 25616, maximum size 8160


Comme on peut le voir, la table comporte effectivement 1 600 colonnes, mais cette fois, le tuple ne peut pas tenir dans une seule page heap, ce qui explique l'erreur « row is too big: size 25616, maximum size 8160 ». Si vous avez fait attention au script, vous avez pu constater que les colonnes sont définies comme `NOT NULL`. Aussi, lors de la création de la table, PostgreSQL aurait pu se rendre compte que l'insertion de données serait impossible. Mais il n'y a pas eu d'alerte.

## Quid des JOINs ?

Pour simplifier, testons avec une jointure automatique :

```sql
SELECT a.*,b.* FROM tint_1600 a, tint_1600 b;
ERROR:  target lists can have at most 1664 entries

Maintenant, c’est la clause SELECT de (a.*,b.*) qui a atteint sa propre limite (MaxTupleAttributeNumber = 1664).

Atteindre la limite de colonnes de manière inattendue

Il arrive que vous deviez modifier votre application, et que cela entraîne des modifications de schéma.
La plupart du temps, il s’agit de modifications de tables, comme l’ajout ou la suppression de colonnes.

Explorer `ADD` / `DROP COLUMN`

Voyons ce qui se passe côté SQL lorsque nous ajoutons, puis supprimons, une colonne.

=# CREATE TABLE tadc_1600(i_1 int NOT NULL);

CREATE TABLE

=# ALTER TABLE tadc_1600 ADD COLUMN i_2 int NOT NULL;

ALTER TABLE

=# SELECT attname,attnum,attstorage,attnotnull,attisdropped
   FROM pg_attribute
   WHERE attrelid=(
                   SELECT oid
                   FROM pg_class
                   WHERE relname='tadc_1600'
                   )
     AND attnum > 0 ORDER BY attnum;

 attname | attnum | attstorage | attnotnull | attisdropped
---------+--------+------------+------------+--------------
 i_1     |      1 | p          | t          | f
 i_2     |      2 | p          | t          | f
(2 rows)

=# ALTER TABLE tadc_1600 DROP COLUMN i_2;

ALTER TABLE

=# SELECT attname,attnum,attstorage,attnotnull,attisdropped
   FROM pg_attribute
   WHERE attrelid=(
                   SELECT oid
                   FROM pg_class
                   WHERE relname='tadc_1600'
                   )
     AND attnum > 0 ORDER BY attnum;

           attname            | attnum | attstorage | attnotnull | attisdropped
------------------------------+--------+------------+------------+--------------
 i_1                          |      1 | p          | t          | f
 ........pg.dropped.2........ |      2 | p          | f          | t
(2 rows)

Lors de la suppression d’une colonne, cette colonne

est renommée en « . » + « pg.dropped.» + attnum + « . »,
devient NULLable,
est marquée comme supprimée.

Itérer sur l’ajout/la suppression de colonnes

On peut se demander s’il existe une limite au nombre d’opérations d’ajout/suppression qui peuvent être exécutées sur une table donnée.

Comme d’habitude, testons :

-- ADD / DROP COLUMN example
DO $$
DECLARE
    i int;
BEGIN
    EXECUTE 'DROP TABLE IF EXISTS tadc;';
    EXECUTE 'CREATE TABLE tadc(i_1 int);';
    FOR i IN 2..1601 LOOP
        EXECUTE format('ALTER TABLE tadc ADD COLUMN i_%s int;', i);
        EXECUTE format('ALTER TABLE tadc DROP COLUMN i_%s;', i);
    END LOOP;
END $$;

Le résultat est :

ERROR:  tables can have at most 1600 columns
CONTEXT:  SQL statement "ALTER TABLE tadc ADD COLUMN i_1601 int;"
PL/pgSQL function inline_code_block line 8 at EXECUTE

Oh oh ! On a atteint la limite des 1 600 ici aussi !

Explorons un peu ce qui se passe :

=# SELECT attname,attnum,attstorage,attnotnull,attisdropped
   FROM pg_attribute
   WHERE attrelid=(
                   SELECT oid
                   FROM pg_class
                   WHERE relname='tadc'
                   )
     AND attnum > 0 ORDER BY attnum;

             attname             | attnum | attstorage | attnotnull | attisdropped
---------------------------------+--------+------------+------------+--------------
 i_1                             |      1 | p          | t          | f
 ........pg.dropped.2........    |      2 | p          | f          | t
 ........pg.dropped.3........    |      3 | p          | f          | t
 ........pg.dropped.4........    |      4 | p          | f          | t
 ........pg.dropped.5........    |      5 | p          | f          | t

 ........pg.dropped.1599........ |   1599 | p          | f          | t
 ........pg.dropped.1600........ |   1600 | p          | f          | t
(1600 rows)

La table tadc comporte 1 600 colonnes. Vous pouvez vous en apercevoir car toutes les modifications sont ajoutées à la table, et son contenu n’est pas réécrit.

Aussi, à ce stade, toute modification supplémentaire visant à ajouter ou supprimer des colonnes sera vouée à l’échec.

Comment faire pour me sortir de cette situation ?

Le chevalier du VACUUM devrait sauver la princesse PostgreSQL, non ?

Et non ! La commande VACUUM fonctionne au niveau du tuple, donc même si vous exécutez un VACUUM FULL, la structure de la table ne changera pas.

Donc le dragon a mangé le chevalier, que fait-on ?

Il ne s’agit pas d’un problème de tuples morts, mais plutôt d’un problème de catalogue.
Vous devez créer une nouvelle définition de table.

Voici quelques solutions, des plus simples aux plus complexes :

Créer une nouvelle table avec interruption de service
- CREATE TABLE LIKE (INCLUDING ALL)
- Copier (COPY) les données de l’ancienne table vers la nouvelle
- Renommer les tables
- Supprimer l’ancienne table
Exploiter la réplication logique pour minimiser les interruptions de service
- CREATE TABLE LIKE (INCLUDING ALL)
- CREATE local PUBLICATION/SUBSCRIPTION
- Une fois les données synchronisées, arrêter/mettre en pause le service applicatif
- Supprimer l’abonnement
- Renommer les tables
- Redémarrer/relancer l’application
- Supprimer l’ancienne table

Contactez-nous

Vous avez d’autres idées pour résoudre ce problème ? Vous avez trouvé d’autres moyens étranges de déclencher cette erreur liée à la limite codée en dur ? Contactez-nous ! Nous sommes toujours ravis d’échanger sur PostgreSQL.