Re: Scalability with large numbers of tables

Oops! spam_from_postgresql_general(at)chezphil(dot)org (Phil Endecott) was seen spray-painting on a wall:
> Dear Postgresql experts,
>
> I have a single database with one schema per user. Each user has a
> handful of tables, but there are lots of users, so in total the
> database has thousands of tables.
>
> I'm a bit concerned about scalability as this continues to grow.
> For example I find that tab-completion in psql is now unusably slow;
> if there is anything more important where the algorithmic complexity
> is the same then it will be causing a problem. There are 42,000
> files in the database directory. This is enough that, with a
> "traditional" unix filesystem like ext2/3, kernel operations on
> directories take a significant time. (In other applications I've
> generally used a guide of 100-1000 files per directory before adding
> extra layers, but I don't know how valid this is.)

I'm pretty sure that slowness of tab completion has little to do with
the performance of the filesystems.

If you've got tens of thousands of relations, the tab completion code
has to draw the whole list of relations from pg_class into memory and
"marshal" it into a form usable by GNU Readline. THAT is what you're
seeing slow down. As the number of tables, n, grows, the cost of that
grows with order of complexity O(n).

Actual queries on actual tables won't be slow; they will look up
relation names directly in pg_class, and presumably go from there to
get the file name(s) on the filesystem, which each represent
operations of complexity of order O(n log n). Which remains fast even
if there are millions of tables.
--
(format nil "~S(at)~S" "cbbrowne" "gmail.com")
http://linuxdatabases.info/info/slony.html
It isn't that physicists enjoy physics more than they enjoy sex, its
that they enjoy sex more when they are thinking of physics.