Re: CREATE TABLE slowing down significantly over time - Mailing list pgsql-performance

From Aris Samad-Yahaya
Subject Re: CREATE TABLE slowing down significantly over time
Date
Msg-id 009501ca602f$a13169e0$e3943da0$@com
Whole thread Raw
In response to Re: CREATE TABLE slowing down significantly over time  (Craig Ringer <craig@postnewspapers.com.au>)
List pgsql-performance
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@postnewspapers.com.au]
Sent: Saturday, November 07, 2009 10:48 PM
To: Aris Samad-Yahaya
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] 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


pgsql-performance by date:

Previous
From: Craig Ringer
Date:
Subject: Re: CREATE TABLE slowing down significantly over time
Next
From: "Aris Samad-Yahaya"
Date:
Subject: Re: CREATE TABLE slowing down significantly over time