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