Hi again,
I am testing how declarative partitioning behaves with lots of
partitions.
But when dropping the tables postgresql quickly used up all my memory.
So my test is setting up a table with 10000 partitions. Similar to the
following:
BEGIN;
CREATE TABLE myschema.test_table
(
time bigint,
value bigint
)
PARTITION BY RANGE (time);
CREATE TABLE myschema.test_table_0
PARTITION OF test_table
(
PRIMARY KEY (time)
)
FOR VALUES FROM (0) TO (1000);
CREATE TABLE myschema.test_table_1000
PARTITION OF test_table
(
PRIMARY KEY (time)
)
FOR VALUES FROM (1000) TO (2000);
-- [...]
CREATE TABLE myschema.test_table_9999000
PARTITION OF test_table
(
PRIMARY KEY (time)
)
FOR VALUES FROM (9999000) TO (10000000);
COMMIT;
When I later try to drop the schema ("DROP SCHEMA myschema CASCADE") and
thus that table, the postgres backend use up one CPU core and quickly
allocates a lot of memory (I only have 32GB on my test machine, so I
don't know if it finally stops allocating more memory or not).
It behaves the same if I only try to drop the parent table ("DROP TABLE
test_table CASCADE").
Dropping individual partitions work fine.
I did a "perf record" during this and I have attached what looks like
the most relevant part.
As a side note, I noticed that it is a lot slower to add partitions using
declarative method compared to the inheritance based method. Adding
the 10000 partitions using the sql from above takes about 250 seconds
compared to about 13s for the similar setup but with normal inheritance.
I am not sure how many partitions are supposed to be handled and 10000
partitions may be out of scope, but I report this because it is
unexpected that postgres use that much memory when deleting tables it
was able to add in the first place.
/Ragnar Ouchterlony, Agama Technologies
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs