Date: Tue, 2 Jun 2020 13:47:12 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Oleksandr Shulgin <oleksandr.shulgin@zalando.de>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: When to use PARTITION BY HASH?
Message-ID: <20200602174712.GX6680@tamriel.snowman.net>
References: 
 <CACACo5StuUuFox+65KQHB8u+51kMUcEeQuV=XmsUv7d8VnaO7A@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="CRrRoVXEpX/Dc4YP"
Content-Disposition: inline
In-Reply-To: 
 <CACACo5StuUuFox+65KQHB8u+51kMUcEeQuV=XmsUv7d8VnaO7A@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--CRrRoVXEpX/Dc4YP
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

Please don't cross post to multiple lists without any particular reason
for doing so- pick whichever list makes sense and post to that.

* Oleksandr Shulgin (oleksandr.shulgin@zalando.de) wrote:
> I was reading up on declarative partitioning[1] and I'm not sure what cou=
ld
> be a possible application of Hash partitioning.

Yeah, I tend to agree with this.

> Is anyone actually using it?  What are typical use cases?  What benefits
> does such a partitioning scheme provide?

I'm sure folks are using it but that doesn't make it a good solution.

> On its face, it seems that it can only give you a number of tables which
> are smaller than the un-partitioned one, but I fail to see how it would
> provide any of the potential advantages listed in the documentation.

Having smaller tables can be helpful when it comes to dealing with
things like VACUUM (particularly since, even though we can avoid having
to scan the entire heap, we have to go through the indexes in order to
clean them up and generally larger tables have larger indexes),
however..

> With a reasonable hash function, the distribution of rows across partitio=
ns
> should be more or less equal, so I wouldn't expect any of the following to
> hold true:
> - "...most of the heavily accessed rows of the table are in a single
> partition or a small number of partitions."
> - "Bulk loads and deletes can be accomplished by adding or removing
> partitions...",
> etc.
>=20
> That *might* turn out to be the case with a small number of distinct valu=
es
> in the partitioning column(s), but then why rely on hash assignment inste=
ad
> of using PARTITION BY LIST in the first place?

You're entirely correct with this- there's certainly no small number of
situations where you end up with a 'hot' partition when using hashing
(which is true in other RDBMS's too, of course...) and that ends up
being pretty painful to deal with.

Also, you're right that you don't get to do bulk load/drop when using
hash partitioning, which is absolutely one of the largest benefits to
partitioning in the first place, so, yeah, their usefullness is.. rather
limited.  Better to do your own partitioning based on actual usage
patterns that you know and the database's hash function certainly
doesn't.

Thanks,

Stephen

--CRrRoVXEpX/Dc4YP
Content-Type: application/pgp-signature; name="signature.asc"

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

iQIcBAEBCgAGBQJe1pCfAAoJEO1sijiDR2RVdZYP/R/C/dfyT68yE/cOrd5l80El
trT96gxXlZkIq7cNm2bYaqQKo3HDxj5CUHXR2Q2gYD5KbMHA2T18Fg/Dj13tjNYW
QZub7mn7y3UpeolI8sRWMgm2UUodKfdnqiEQs26N9M4v2qTWlWIVXCh9E7U1tyGS
+eopyJ5qJwEu/pCV6vYXCQ/S7XoIqqseIqa4p8Nk8kuAN1Up8PtE1DutQ7kWrKIY
7jGp/uO8v4Y3UXu8D055+JDjvYts/C6l+qvfHwUZWeQufrOn6gnkZ3qS6FYUevoE
WGKn0V+kNqKT5jo/yo2T9EbH/habEcs+GxA3OBhTR4AC+Ot4XorKw1OM3xKZR00M
msERNqXmgHSJl+RdH2EyBFm3LPWB0vSVtyR8mrXEyOAY9hkr+G+JQnsLo23CCIgj
AvagbYFH52nPymFuX0w3BmeLGMvWiVKF7sziUidgKIhiUHqGSvAm0iI3+U5a0PrT
ZvjdR3ERQjjYpv33gZ82zC+g6F0oDheMyTJq5Pd7xhpTFwNU7FmO7qzPXGhvVWGW
aWuJ3SP2Y9i/Lw6X4olNK+bFY1YeUUXiPBlmYJ5XRn/8c8RfkbVrq5RETghkag9n
PYN3VmET7Ahn8FzbaBlIgyNyrQzoEdzRNltb9a0tor9K89MOMa8gVJBX3sS+gZs/
JYn7sMv/8rc/h3VFA1tF
=mRWh
-----END PGP SIGNATURE-----

--CRrRoVXEpX/Dc4YP--