I'm using PostgreSQL 13.8 and i have a lot of big tables (with a lot of records) and queries are spending too much. I though it's a good idea to partition some tables to speed them up. But i'm totally stuck, because i need to partition an existing table instead of create a new one. I wanted to start with only one of them, called "Users", to test the improvvement. And i want to do everything 100% sure to not screw everything up. I followed all the steps that I found on the internet to reduce this probability and get the new table with the same structure (indexes, keys, columns, etc), but most of the discussions were about postgresql 10/11 and I've found some difficulties:
- Create a new partition table:
CREATE TABLE "Users_partitioned" (
_id int4 NOT NULL DEFAULT nextval('"_id"'::regclass),
creationDate timestamptz NOT NULL
... rest of the columns
) PARTITION BY RANGE ("creationDate");
- Create manually all indexes and constraint in the new partitioned table.
- Create some partitions tables, e.g. :
CREATE TABLE "Users_partitioned_20230101"
PARTITION OF "Users_partitioned"
FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');
- Then I move all the rows from the original table to the new one.
But after doing everything, I've noticed both "Dependencies and Referencies" are not the same than the original, very different. The new table has a lot of missing rules and FK (inside Dependencias and Referencies folder). My idea was to rename the new table to the name of the old table and test if everything works well. But i think it won't happen by doing things this way.
So I tried create the partition table doing something like this:
CREATE TABLE Users_partitioned (
LIKE Users INCLUDING ALL
) PARTITION BY RANGE (createdAt);
But it didn't work...
So, i dont know the best way to migrate an existing table to a partitioned one in postgresql 13. I have to do this optimization with most tables. It's a lot if I have to create manually all the tables, columns, indexes, check every FK, rules, etc it's crazy Also i don't know if I rename the new table to the old one, it'll work or all the referencias will broke I'll really appreciate any kind of help