0

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:

  1. 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");

  1. Create manually all indexes and constraint in the new partitioned table.
  2. 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');

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

3
  • 2
    Partitioning will not increase the speed, query optimization will bring much more. Commented Sep 1, 2023 at 20:17
  • What is your definition of “big”? How many GB/TB and how many millions of records are we talking about? We use partitions with a 500 million records per partition, just to make the future delete much easier: drop table …; Commented Sep 2, 2023 at 3:23
  • This lacks detail. But typically, when you use partitioning, you'll have to live without foreign keys. Often, you cannot even define the primary keys you'd like. Commented Sep 2, 2023 at 16:37

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.