Hi Craig, Yes we do put the creation of the 300 tables into a single transaction. The difference between putting them in a single transaction and individual transactions is about 30 seconds over the 3 minutes. As for the creation of 300 individual tables for an account... yes we were trying to think through that issue very hard. It's the SaaS maturity levels discussion: How much do you separate the databases for each account, vs sharing customer information into large tables. I hear SalesForce puts most everything in giant tables, whereas we've decided to separate customer accounts into separate schemas. -----Original Message----- From: Craig Ringer [mailto:craig@xxxxxxxxxxxxxxxxxxxxx] Sent: Saturday, November 07, 2009 10:48 PM To: Aris Samad-Yahaya Cc: pgsql-performance@xxxxxxxxxxxxxx Subject: Re: CREATE TABLE slowing down significantly over time On 8/11/2009 11:15 AM, Aris Samad-Yahaya wrote: > It used to take about 15 seconds to create those 300 tables in a new > schema (when there were only a few schemas, say about 50). It now takes > about 3 minutes (and now we have about 200 schemas, with more data but > not hugely so). 200 schemas, 300 tables per schema. That's sixty THOUSAND tables. > * Is the problem caused by the increasing number of schemas? and increasing table count, I expect. You do batch the table and schema creation into a single transaction, right? If not, do that first, rather than creating each table in a separate transaction (ie: relying on autocommit). It may also be worth thinking about the app's design. Is a new schema and 300 new tables for each user really the best way to tackle what you're doing? (It might be, but it's a question worth asking yourself). -- Craig Ringer -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
