Share Tweet Share





My previous trial merely whet my appetite to see how many tables I could actually load into a PostgreSQL instance. The PL/Summit proceeded to Paddy Boland’s Pub, though, and one thing led to another and pretty soon Jan Urbanski and I had a race. Who could get to a billion tables first — or run out of disk space, which seemed more likely.

So, from the pub wireless, I deployed two 4GB GoGrid cloud servers. Jan was writing a program in Twisted Python, and I was writing in Perl. Both of us were to create as many servers as we could using the following schema:

CREATE TABLE tab_ ( id INT, value TEXT );

As you’ll notice, we elimintated the SERIAL and the index. This was for speed and disk space; empty indexes are 16K each. Then we turn off as much of the I/O as we could:

fsync = off

full_page_writes = off

synchronous_commit = off

log_min_error_statement = 'PANIC'



I wrote a simple script designed to run in parallel and just create tables full-tilt, in transaction batches of 100 at a time. I’d have liked to do something elegant with forks, but this was in a pub, so I just started the workers manually. Here’s the script:

(apologies with formatting issues with the scripts below. I’ve notified Toolbox of the problems, maybe they’ll fix)

#!/usr/bin/perl

use DBI;

my ( $worker ) = @ARGV;

my ( $dbname, $dbuser, $dbpass ) = ( "onebillion", "postgres", undef );

my $pdbh = DBI->connect(

"dbi:Pg:dbname=$dbname;host=/tmp",

$dbuser, $dbpass, {

AutoCommit => 0,

RaiseError => 0,

});

my $curtab = 1;$pdbh->begin_work();

while ( $curtab < 250000000 ) {

my $qry = "CREATE TABLE tab_${worker}_${curtab} ( id int, value text );";

my $sth = $pdbh->prepare($qry);

$sth->execute();

unless ( $curtab % 100 ) {

$pdbh->commit();

$pdbh->begin_work();

}

$curtab++;

};

$pdbh->commit();

exit(0);

Jan’s script was a bit more elegant. Among other things, it works by building SCHEMA with 100 tables in them at a time in one statement, reducing round-trip time to the database. It also made things interesting since he’d be creating millions of namespaces (apologies for line wrapping issues introduced by Toolbox):

#!/usr/bin/python

import sysfrom txpostgres

import txpostgres

import itertools

from twisted.internet import reactor, task



numbers = itertools.count(1)



def start_creating(conn, prefix):

lc = task.LoopingCall(create, conn, prefix)

return lc.start(0)



TABLES_PER_TX = 100



def create(conn, prefix):

num = numbers.next()

sql = ['create schema %s_jan_%d' % (prefix, num)]

for i in xrange(TABLES_PER_TX):

sql.append("create table jan_%d (id int, value text)" % i)

print "creating tables in schema %s_jan_%d" % (prefix, num)

return conn.runOperation(' '.join(sql))



def end(ret):

print "ending"

print ret

dsn, prefix = sys.argv[1:]

conn = txpostgres.Connection()

d = conn.connect(dsn)

d.addCallback(start_creating, prefix)

d.addBoth(end)

reactor.run()

And the race was on! Whoever crashed the server last, bought the beer.

The table creation started at a rush, around 1000 tables per second. We were pretty pleased with the virtual servers; I don’t know that we could have done better on real hardware without significant disk setup.

I pulled ahead soon, getting around 200,000 tables ahead of Jan. Then, around 1 million tables in, things started to bog down.

Oh. We’d forgotten to turn of autovacuum. Apparently with 1 million tables, vacuuming the system catalogs got kinda slow, and slowed down table creation by 80%. Three autovacuum workers were going full-blast and not keeping up. So we both turned off autovacuum, reloaded, and went on.

Pretty soon, though, Jan started to pull ahead, Creating 100-table schemas made one of the unique indexes on pg_class smaller and updated less frequently, and this made just enough of a difference.

3 million. 3.5 million. 4.1 million. 4.6 million. Stop. Database server crash.

Huh? What happened? We weren’t out of disk space. Did we hit a limit with PostgreSQL? Oh. No, Jan was out of inodes.

Seems that PostgreSQL uses three inodes for each table; one for the table, one for the TOAST table, and one for the FSM file.

Since we were running fsync = off, Jan’s database didn’t recover from the crash. I stopped mine, with still a few thousand inodes left so that we could test what a database with four million tables behaved like.

First, just connecting to the database in psql took a really long time … around 10 minutes. psql must scan at least one of the system tables on connection; there was some speculation in the pub as to what. I could actually query a single table fine, though. However, a dt in psql hung for as long as we were in the pub, another half hour. Then I had to shut down the cloud servers so they didn’t run up my bill.

We got one thing out of the test, though. If someone asks you how many tables you can have in PostgreSQL, you now have an answer:

“More than four million.”

Anyway, I’ve posted the scripts in this blog so that others can try the same experiment on a system which supports more inodes. Have fun!