I have a huge table with rows > 1000000. However, people will only care about rows that were ordered by updated_at and created recently. There are updated_at and created_at columns in the table. By default, there is a filter for the last 30 days of created_at and there is an order by updated_at desc and a Limit of 30 in the end. The values of created_at goes over 2 years but the users mostly care about the last month.

I have created an index on updated_at and created_at using BTree each.

I have explored partition and it is really what I am looking for where you can divide the tables by created_at. However, there is no way to create a unique index on multiple child tables.

Is there any other way to increase performance? Very few users care about very old data and it should be accessible even though it will slow down.

  • Upgrade to Postgres 12, then partition by month – a_horse_with_no_name Dec 10 '19 at 12:29
  • yes but even with postgres 12, there is no global unique constraint. That is the main blocker for me. postgresql.org/docs/current/ddl-partitioning.html ``` There is no way to create an exclusion constraint spanning all partitions; it is only possible to constrain each leaf partition individually. Unique constraints on partitioned tables must include all the partition key columns. This limitation exists because PostgreSQL can only enforce uniqueness in each partition individually. ``` – user3441114 Dec 10 '19 at 14:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.