Re: Partition tables in AWS RDS environment

I forgot to mention something in my post.

We do like to load particular schemas/namespaces from production onto our test database each night. We use pg_dump to do this. If we went from using partitioned tables to just single tables would we run into a situation where we would be locking the extremely large table in production while we pull down this data?

I cannot just load the entire data base on to our test server. There are times where we are changing data on our test database relating to new development.

Lance

From: pgsql-admin-owner(at)postgresql(dot)org [mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Campbell, Lance
Sent: Monday, April 10, 2017 8:51 AM
To: pgsql-admin(at)postgresql(dot)org
Subject: [ADMIN] Partition tables in AWS RDS environment

PostgreSQL 9.6.2

My group is planning on moving our PostgreSQL install to AWS. We plan to use their RDS option for running PostgreSQL. When we make this move is there any reason to use Partitioned Tables on really large tables? Will we get any performance gain?

I know we would get a performance gain on really large tables if we partitioned them and put their partitions in different table spaces that use different disks. However since we are looking at using the more generalized hands off automated AWS RDS PostgreSQL option I don't see us using table spaces and different disks. So would there be any gain in partitioning tables in the more automated AWS RDS PostgreSQL path?

Thanks,

Lance Campbell
University of Illinois