pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Partition tables in AWS RDS environment 5+ messages / 2 participants [nested] [flat]
* Partition tables in AWS RDS environment @ 2017-04-10 13:50 Campbell, Lance <lance@illinois.edu> 0 siblings, 2 replies; 5+ messages in thread From: Campbell, Lance @ 2017-04-10 13:50 UTC (permalink / raw) To: pgsql-admin 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Partition tables in AWS RDS environment @ 2017-04-10 13:57 Campbell, Lance <lance@illinois.edu> parent: Campbell, Lance <lance@illinois.edu> 1 sibling, 0 replies; 5+ messages in thread From: Campbell, Lance @ 2017-04-10 13:57 UTC (permalink / raw) To: Campbell, Lance <lance@illinois.edu>; pgsql-admin 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@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Campbell, Lance Sent: Monday, April 10, 2017 8:51 AM To: pgsql-admin@postgresql.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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Partition tables in AWS RDS environment @ 2017-04-10 13:57 Stephen Frost <sfrost@snowman.net> parent: Campbell, Lance <lance@illinois.edu> 1 sibling, 1 reply; 5+ messages in thread From: Stephen Frost @ 2017-04-10 13:57 UTC (permalink / raw) To: Campbell, Lance <lance@illinois.edu>; +Cc: pgsql-admin 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 any performance 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't what you look to for improving individual query performance. Having smaller tables can make things a bit easier for autovacuum, but 9.6 also has improvements that can make VACUUMs much less painful (the all-frozen visability bit in the VM). > 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? This really depends on what your queries are doing. It's not necessairly a guarantee that a given query will perform better if the table is split up into partitions and spread across tablespaces. In short, there isn't a simple answer to that question. Thanks! Stephen Attachments: signature.asc (application/pgp-signature, 818B) download ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Partition tables in AWS RDS environment @ 2017-04-10 14:25 Campbell, Lance <lance@illinois.edu> parent: Stephen Frost <sfrost@snowman.net> 0 siblings, 1 reply; 5+ messages in thread From: Campbell, Lance @ 2017-04-10 14:25 UTC (permalink / raw) To: Stephen Frost <sfrost@snowman.net>; +Cc: pgsql-admin 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 to lock 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 I partitioned the really large tables so that the locking would be a much shorter time period while running pg_dump. But this 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 any performance 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't what you look to for improving individual query performance. Having smaller tables can make things a bit easier for autovacuum, but 9.6 also has improvements that can make VACUUMs much less painful (the all-frozen visability bit in the VM). > 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? This really depends on what your queries are doing. It's not necessairly a guarantee that a given query will perform better if the table is split up into partitions and spread across tablespaces. In short, there isn't a simple answer to that question. Thanks! Stephen -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Partition tables in AWS RDS environment @ 2017-04-10 14:31 Stephen Frost <sfrost@snowman.net> parent: Campbell, Lance <lance@illinois.edu> 0 siblings, 0 replies; 5+ messages in thread From: Stephen Frost @ 2017-04-10 14:31 UTC (permalink / raw) To: Campbell, Lance <lance@illinois.edu>; +Cc: pgsql-admin Greetings Lance, * Campbell, Lance (lance@illinois.edu) wrote: > 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 to lock the table for writing? In PG, readers do not block writers. > This is an issue I ran into with older version of PostgreSQL using pg_dump on entire schemas. To get around this issue I partitioned the really large tables so that the locking would be a much shorter time period while running pg_dump. But this may not be necessary with 9.6 I'm not sure what issue you ran into, but pg_dump would block someone from DROP'ing or TRUNACTE'ing a table (or other operations requiring a very strong lock), not from simply doing an INSERT, UPDATE, or DELETE (which take a lower lock that doesn't conflict with the locks pg_dump takes). Information about the various locks in PG is available here: https://www.postgresql.org/docs/current/static/explicit-locking.html Thanks! Stephen Attachments: signature.asc (application/pgp-signature, 818B) download ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2017-04-10 14:31 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2017-04-10 13:50 Partition tables in AWS RDS environment Campbell, Lance <lance@illinois.edu> 2017-04-10 13:57 ` Campbell, Lance <lance@illinois.edu> 2017-04-10 13:57 ` Stephen Frost <sfrost@snowman.net> 2017-04-10 14:25 ` Campbell, Lance <lance@illinois.edu> 2017-04-10 14:31 ` Stephen Frost <sfrost@snowman.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox