Do you happen to know...
In 9.6 if you do a pg_dump on a schema and you have a few very large tables in the schema will this cause the database
tolock the table for writing?
This is an issue I ran into with older version of PostgreSQL using pg_dump on entire schemas. To get around this issue
Ipartitioned the really large tables so that the locking would be a much shorter time period while running pg_dump.
Butthis may not be necessary with 9.6
Thanks,
Lance
-----Original Message-----
From: Stephen Frost [mailto:sfrost@snowman.net]
Sent: Monday, April 10, 2017 8:57 AM
To: Campbell, Lance <lance@illinois.edu>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Partition tables in AWS RDS environment
Greetings, Lance,
* Campbell, Lance (lance@illinois.edu) wrote:
> 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
anyperformance gain?
This really depends on what you're doing, exactly. Generally speaking, I find that partitioning is great for data
management(you're able to get rid of an entire partition at a time, based on your retention policy, for example) and it
isn'twhat you look to for improving individual query performance. Having smaller tables can make things a bit easier
forautovacuum, but 9.6 also has improvements that can make VACUUMs much less painful (the all-frozen visability bit in
theVM).
> I know we would get a performance gain on really large tables if we partitioned them and put their partitions in
differenttable spaces that use different disks. However since we are looking at using the more generalized hands off
automatedAWS RDS PostgreSQL option I don't see us using table spaces and different disks. So would there be any gain
inpartitioning tables in the more automated AWS RDS PostgreSQL path?
This really depends on what your queries are doing. It's not necessairly a guarantee that a given query will perform
betterif the table is split up into partitions and spread across tablespaces.
In short, there isn't a simple answer to that question.
Thanks!
Stephen