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