Date: Mon, 10 Apr 2017 09:57:23 -0400
From: Stephen Frost <sfrost@snowman.net>
To: "Campbell, Lance" <lance@illinois.edu>
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: Partition tables in AWS RDS environment
Message-ID: <20170410135722.GI9812@tamriel.snowman.net>
References: 
 <B75CD08C73BD3543B97E4EF3964B7D7047B47CF7@CITESMBX1.ad.uillinois.edu>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="26tnOnrpsK2tpkS+"
Content-Disposition: inline
In-Reply-To: 
 <B75CD08C73BD3543B97E4EF3964B7D7047B47CF7@CITESMBX1.ad.uillinois.edu>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org


--26tnOnrpsK2tpkS+
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

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

--26tnOnrpsK2tpkS+
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJY649CAAoJEO1sijiDR2RVlucP/iFa/U11Bjf6HU5odsazqpYV
9V9o1R2wfwbQkKV64VCX828geMxuam8zPRPFoLyz6SPEwBZqpi0zIE/Ghf1h6v6c
HeKj7GOGljKIT3AEaWHKB0MLdHWBJiujgCgPALHUaw597QYEe1PRImZ65Bz1eiU+
e//gr3S5+Osptp3XMkficX0Xxh8OgNgJ+cODldIxMn7Hv0fwRVH+s6G9bOpSlwC+
C9lgM8ZIhHNE99yqhlp14BPgPaZfYfYJGN4p5HEUM5/UHxNildu90O5fVXGYsR1S
F69HW9fRGQcGKhhN0qTURnoa0tmPUP6lRYc0TtV5pnGvxTVGL7e9BTP8rVsnGlNG
aUhXYUAdVVvUEdkQTmmQXy5kZuSneHM+JcNsA1wU8wiHfrCfNMtzpSS+5Guan9ex
kgCp/aXFLNT5212eG4HsnF5m46MBXwssPLmku0wPsh3oO2W+o/yiOeDXxty1nqzO
k178tshr2P+/4N7U25579u0IMNQO01REgNRISHWPwgCIpMfCIHvRKmFpuUL9X0qa
CcVmDPFgPObZXXdU2CdNQ9xAFdRcmrYkYy5oUxdLCD/7AZVrSxdoZvdn4ymhMg7q
O/Z5qJxI0w8NbPPlIKACHSvBRCbqktowbytFTHv1xCT2aRSUyrvLPWgBGTxa6nbE
UNPGHshi1pmNv0mFrIh7
=qIx+
-----END PGP SIGNATURE-----

--26tnOnrpsK2tpkS+--