Oops! spam_from_postgresql_general@xxxxxxxxxxxx (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@~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. ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
