From: Craig Ringer <craig@postnewspapers.com.au>
To: Aris Samad-Yahaya <aris@quickschools.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: CREATE TABLE slowing down significantly over time
Date: Sun, 08 Nov 2009 11:47:39 +0800
Message-ID: <4AF63F5B.2060300@postnewspapers.com.au> (raw)
In-Reply-To: <008801ca6021$c1cc7630$45656290$@com>
References: <008801ca6021$c1cc7630$45656290$@com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig@postnewspapers.com.au, aris@quickschools.com
Subject: Re: CREATE TABLE slowing down significantly over time
In-Reply-To: <4AF63F5B.2060300@postnewspapers.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox