Billion tables?!

Thanks to the presentation of Selena Deckelman (Selena Deckelmann) on pgCon, some of us got involved in a discussion about "How many tables can PostgreSQL theoretically pull". Hastily wrote a script with a bold attempt to create one billion tables of the following form:

the

CREATE TABLE tab_### (
id SERIAL NOT NULL PRIMARY KEY,
value TEXT NOT NULL
);

It should be noted that such a design among other things will create billions of sequences (sequences), indexes, constraints (constraints), and two billion fields.

A Perl script was running on the GoGrid cloud hosting in 4 parallel processes. It worked tolerably well, the fruit of about 300 000 tables per hour, until the disk space is not over.

Based on the fact that 100,000 empty tables takes almost 2GB of disk space, after creating almost 3 million tables to the server came the notorious Northern fur animal. So, start PostgreSQL was only if I disable the fsync:

fsync=off

Who would have thought...

So if you, dear friend, have a dedicated server with gigabytes of free dobrinina — test yourself. Try to create a billion tables.

Approx. interpreter: the Sequence will be automatically created for SERIAL fields, indexes will be created for PRIMARY KEY constraints. But that's not all. In view of the fact that the second field is of type TEXT, the server will create for each table and even the TOAST table along with a unique index.

In the comments to the original article cited the following mathematical calculations. Each table actually pulls create 5 objects: table, sequence, index, TOAST table, index on the TOAST table. Each such group eats away at the disk 24KB (3 x 8 + 2 x 0). Even ignoring the size of the system catalog, the number of objects is 22.3 TB.

If we add to this the size of the system catalog, the amount will increase to 12TB, and will be 34TB.

Article based on information from habrahabr.ru