Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 82354633F43 for ; Sat, 7 Nov 2009 23:22:43 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 44946-08 for ; Sun, 8 Nov 2009 03:22:23 +0000 (UTC) X-Greylist: delayed 00:06:40.762633 by SQLgrey-1.7.6 Received: from smtpauth22.prod.mesa1.secureserver.net (smtpauth22.prod.mesa1.secureserver.net [64.202.165.44]) by mail.postgresql.org (Postfix) with SMTP id 4430E63268E for ; Sat, 7 Nov 2009 23:22:31 -0400 (AST) Received: (qmail 23737 invoked from network); 8 Nov 2009 03:15:48 -0000 Received: from unknown (12.110.105.2) by smtpauth22.prod.mesa1.secureserver.net (64.202.165.44) with ESMTP; 08 Nov 2009 03:15:47 -0000 From: "Aris Samad-Yahaya" To: Subject: CREATE TABLE slowing down significantly over time Date: Sat, 7 Nov 2009 22:15:40 -0500 Message-ID: <008801ca6021$c1cc7630$45656290$@com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0089_01CA5FF7.D8F66E30" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: AcpgIb4ht6AD3IngQVO7bgIIC+qQFw== Content-Language: en-us X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200911/79 X-Sequence-Number: 36247 This is a multi-part message in MIME format. ------=_NextPart_000_0089_01CA5FF7.D8F66E30 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit I'm facing a problem where running a CREATE TABLE has slowed down significantly over time. This is problematic because my application needs to routinely create a new schema and create 300 tables in each new schema. In total it takes about 3 minutes, which may not seem like a big deal, but this is time sensitive because users of our SaaS application are waiting in real-time for the schema and 300 the tables to be created. 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). To debug this problem, I've created a new database in a separate (and dinky) laptop, and running a single test CREATE TABLE command takes about 19 ms. But on the server with 200+ schemas, this single command takes between 200 and 300 ms. My test command on psql is: CREATE TABLE .academicsemesters ( id text NOT NULL, creationdate timestamp with time zone, academicsemestername text, academicyearandsemestername text, startdate timestamp with time zone, enddate timestamp with time zone, isplanningmode boolean NOT NULL, isclosed boolean NOT NULL, isactive boolean NOT NULL, status text, workflowstubid text, deleted boolean NOT NULL, academicyearid text ); * Any tips anyone can give on what might be the underlying cause of the slowing down of the CREATE TABLE command over time? * Is the problem caused by the increasing number of schemas? Thanks in advance, Aris ------=_NextPart_000_0089_01CA5FF7.D8F66E30 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

I’m facing a problem where running a CREATE = TABLE has slowed down significantly over time.

 

This is problematic because my application needs to = routinely create a new schema and create 300 tables in each new schema. In total = it takes about 3 minutes, which may not seem like a big deal, but this is time = sensitive because users of our SaaS application are waiting in real-time for the = schema and 300 the tables to be created.

 

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).

 

To debug this problem, I’ve created a new = database in a separate (and dinky) laptop, and running a single test CREATE TABLE = command takes about 19 ms.

 

But on the server with 200+ schemas, this single = command takes between 200 and 300 ms.

 

My test command on psql is:

CREATE TABLE = <TheSchemaName>.academicsemesters (

    id text NOT NULL,

    creationdate timestamp with time = zone,

    academicsemestername = text,

    academicyearandsemestername = text,

    startdate timestamp with time = zone,

    enddate timestamp with time = zone,

    isplanningmode boolean NOT = NULL,

    isclosed boolean NOT = NULL,

    isactive boolean NOT = NULL,

    status text,

    workflowstubid = text,

    deleted boolean NOT = NULL,

    academicyearid = text

);

 

* Any tips anyone can give on what might be the = underlying cause of the slowing down of the CREATE TABLE command over = time?

* Is the problem caused by the increasing number of = schemas?

 

Thanks in advance,

Aris

------=_NextPart_000_0089_01CA5FF7.D8F66E30--