0

I used RDS Aurora PostreSQL in AWS.

The size of the data I manage and the number of rows are too large (7 billion rows and 4TB), so I am considering table partitioning. (I also considered the citus of postgresql... but unfortunately it is not available in aws...)

When I request some query in that table, it is very slow... So I applied table partitioning (10 partitions) and the query performance was there, but still slow.

The site below recommends ‘Tables bigger than 2GB should be considered.’, but in this case, there are too many partitioning tables and it seems difficult to manage.

https://hevodata.com/learn/postgresql-partitions/#t10

What would be the appropriate table size?

And is the pg_partman extension required in this case?

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_Partitions.html

Is there any other way to improve query performance other than partitioning if there is too much data in the table?

Jmob
  • 49
  • 7
  • It is impossible to give a recommendation based on size, but 2GB seems ridiculously small. You only need pg_partman or something similar if you need partitions to be created automatically. – Laurenz Albe Jul 06 '22 at 11:22
  • Why is it slow? Is there a relation with the size of your table? Could you share the results from EXPLAIN(ANALYZE, VERBOSE, BUFFERS) for one of your slow queries? Partitioning makes sense when you only query a (small) portion of the data. But 2GB for a table, that is imho very very small – Frank Heikens Jul 06 '22 at 11:55
  • Sorry. For security reasons, it is difficult to inform the EXPLAIN Query of the table. However, although the column used in the query statement is indexed, duplication is possible. So it is guessed that it gets slower as the number of rows increases... – Jmob Jul 06 '22 at 15:20

0 Answers0