Billion tables ?!

Thanks to the presentation by Selena Deckelmann at pgCon , some of us got involved in a discussion on the topic “How many tables could theoretically pull PostgreSQL”. A quick script was written with a bold hope of creating one billion tables of the following form:

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

It should be noted that such a construction, among other things, will create a billion sequences (sequences), indices, constraints (constraints), and two billion fields.

The Perl script was launched on GoGrid cloud hosting in 4 parallel processes. It worked quite well, producing about 300,000 tables per hour, until the disk space was over.
')
Based on the fact that 100,000 empty tables takes up almost 2GB of disk space, after creating almost 3 million tables, the notorious northern fur animal came to the server. Yes, such that it was only possible to start PostgreSQL if you disable fsync :

fsync=off

Who would have thought…

So, if you, dear friend, have a dedicated server with dohrennillion free gigabytes - try yourself. Try to create a billion tables.

Note translator : Sequences will be automatically created for SERIAL fields, while indices will be created for PRIMARY KEY constraints. But that is not all. In view of the fact that the second field is of type TEXT, the server will also create a TOAST table for each table along with a unique index.

The comments to the original article cited such mathematical calculations. Each table actually creates 5 objects: the table itself, the sequence, the index, the TOAST table, the index on the TOAST table. Each such group eats up on a 24KB disk (3 x 8 + 2 x 0). Even ignoring the size of the system catalog, such a number of objects will occupy 22.3TB.

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