Subject: Re: Declarative partitioning
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>,
 Pavan Deolasee <pavan.deolasee@gmail.com>
References: <55D3093C.5010800@lab.ntt.co.jp>
 <CABOikdMMgkAG_iX1B_Yq4s-L8wd5PN4f1ptmPe0+z3E58bsArA@mail.gmail.com>
 <55D5A1D0.2070709@lab.ntt.co.jp>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
From: David Steele <david@pgmasters.net>
Message-ID: <55D5C9E5.6040900@pgmasters.net>
Date: Thu, 20 Aug 2015 08:36:53 -0400
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.10; rv:38.0)
 Gecko/20100101 Thunderbird/38.2.0
MIME-Version: 1.0
In-Reply-To: <55D5A1D0.2070709@lab.ntt.co.jp>
Content-Type: multipart/signed; micalg=pgp-sha512;
 protocol="application/pgp-signature";
 boundary="ok3HRCTdrUgQgaOcitDRKRWCeqekEhGcD"
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

This is an OpenPGP/MIME signed message (RFC 4880 and 3156)
--ok3HRCTdrUgQgaOcitDRKRWCeqekEhGcD
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable

On 8/20/15 5:45 AM, Amit Langote wrote:
> On 2015-08-20 PM 06:27, Pavan Deolasee wrote:
>> On Tue, Aug 18, 2015 at 4:00 PM, Amit Langote <Langote_Amit_f8@lab.ntt=
=2Eco.jp
>>> wrote:
>>>
>>> PARTITION BY LIST ON (name)
>>> PARTITION BY RANGE ON (year, month)
>>>
>>> PARTITION BY LIST ON ((lower(left(name, 2)))
>>> PARTITION BY RANGE ON ((extract(year from d)), (extract(month from d)=
))
>>>
>>>
>>
>> How about HASH partitioning? Are there plans to support foreign tables=
 as
>> partitions?
>>
>=20
> I've not given HASH partitioning a lot of thought yet. About the latter=
,
> it seems it should not be too difficult to incorporate into the propose=
d
> partitioning internals.

Hash partitioning seems like it could wait.  If fact, I've nearly always
implemented hash partitions as list partitions.  This gives me control
over the hash function and allows me to use properties of the key to my
advantage.  For instance - if your key is a sha1 hash there's no need to
rehash, just grab the required number of bits off the end of the key.

My experiences with Oracle's hash function were generally not good -
there's a reason many hash algorithms exist.  If/when we do hash
partitioning in Postgres I'd like to see the hash function be
user-definable.

Meanwhile, I think list and range are a good start.  I'd prefer to see
sub-partitioning before hash partitioning.

--=20
-David
david@pgmasters.net


--ok3HRCTdrUgQgaOcitDRKRWCeqekEhGcD
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Comment: GPGTools - https://gpgtools.org

iQIcBAEBCgAGBQJV1cnmAAoJEIA2uIJQ5SFAXDIP/3Dt76/Ii28ryKMU2ekgtkF1
7m139eY/PgNGGSdrv7NpV0RT3vdwrgxHhYjnzlCgIaNO5SZpcEja0z7Z0TYkxf9n
BUwgWvLXStY4J1gWL4VExcJCO+GRC3I/hI0OLnT7uijMem+QbelYjBqRCAEC9ppj
Yo1q7LdfQPNC/x9onWV39HsLFGxBIDtlfCnhatTF5QTKuQ35Ey7fnaQi3q6FMG0r
nugQT+q0pEjy2/4AEy/LajKbQ/qH5BU1rcyO63SiPpIz0imexUFoh2jkyxPOfLni
Ce1C9zt0E1msrfj5GZMZq2xeppx50IrCPj5oKbuM2kQonpdkdFKKBJKgV8q2Q+no
ld3q/5iUfKp9MLAotIQKm202lORoCCvoBBQVc+tic/Spd0nTjyS+22AlWxrKWPvh
MesyRAx3847+GaleW1mFKSRUwQdtQvysj10adPJb78uhKuQj9Z8JvE5K1fePu/Kr
75DaU1Y1ALgDR7ZXDnpK4Qn1wyR1P9m+Ea/RPh14rD+bq8165e+9VAdSHxEVtCkk
NXxqZ1uVppGudKcrixNbscILyrId3edxex+hXh/JErmILFR0or3Iqv+ST92ctMXs
cdkM9aykXdfFoKQ0srJv2x8BUTv3diNuOSkZS3ZaJPr7G8XCMcM2PrGWi8y9UBjA
xu5YBrS7fNn1CmDT8X9M
=B7pv
-----END PGP SIGNATURE-----

--ok3HRCTdrUgQgaOcitDRKRWCeqekEhGcD--