I imagine there are similar issues to managing schemas as there are to managing subclass tables. I have to deal with indexes for the thousands of tables because there is no inheritance of indexes or constraints. So any time I discover a new column that should be indexed, I have to make sure to propagate the index across all subclasses.
Where this hits performance is certainly peculiar to inheritance because doing even primary key lookups in the superclass tables becomes a slow operation when it's having to break this down into lookups in every child table.
-tfo
-- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005
On Feb 20, 2005, at 7:24 AM, Phil Endecott wrote:
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 interested to know if anyone has any experiences to share with similar large numbers of tables. Should I worry about it? I don't want to wait until something breaks badly if I need architectural changes. Presumably tablespaces could be used to avoid the too-many-files-per-directory issue, though I've not moved to 8.0 yet.
Thanks
Phil.
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)
