Date: Sat, 2 May 2020 10:01:09 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Arya F <arya6000@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Best partition type for billions of addresses
Message-ID: <20200502140109.GX13712@tamriel.snowman.net>
References: 
 <CAFoK1ayT3Ma8e_d+AXEBVO6wGffH0bs_LDeY2+9X5NRcGYp_SA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="kRPgt2ku+S/0Bic/"
Content-Disposition: inline
In-Reply-To: 
 <CAFoK1ayT3Ma8e_d+AXEBVO6wGffH0bs_LDeY2+9X5NRcGYp_SA@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--kRPgt2ku+S/0Bic/
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Arya F (arya6000@gmail.com) wrote:
> I need to store about 600 million rows of property addresses across
> multiple counties. I need to have partitioning setup on the table as
> there will be updates and inserts performed to the table frequently
> and I want the queries to have good performance.

That's not what partitioning is for, and 600m rows isn't all *that*
many.

> >From what I understand hash partitioning would not be the right
> approach in this case, since for each query PostgreSQL has to check
> the indexes of all partitions?
>=20
> Would list partitioning be suitable? if I want PostgreSQL to know
> which partition the row is it can directly load the relevant index
> without having to check other partitions. Should I be including the
> partition key in the where clause?
>=20
> I'd like to hear some recommendations on the best way to approach
> this. I'm using PostgreSQL 12

In this case, it sounds like "don't" is probably the best option.

Partitioning is good for data management, particularly when you have
data that "ages out" or should be removed/dropped at some point,
provided your queries use the partition key.  Partitioning doesn't speed
up routine inserts and updates that are using a proper index and only
updating a small set of rows at a time.

Thanks,

Stephen

--kRPgt2ku+S/0Bic/
Content-Type: application/pgp-signature; name="signature.asc"

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

iQIcBAEBCgAGBQJerX0lAAoJEO1sijiDR2RVq/wQAKLHiL98vKjxL5Tv4QmHeyp9
fGm0J3wlZ0vKnA150c/XCB995xCkB/QVooT+8XOjNRc8bL2bdLoNX8MQkssKFURZ
HEWcWOzrqdWGLcb7IhmFlLL4AFTLyHW1uoyT3QxwNrUQeDDVOlKSQfmze2IxcaWn
4zuF1kpRwcJ/CPMJjifpAnPlaByZV7uF+/f4KjYUX8H7WRijcR0G6z1/5uP0hnXS
X0wNgcaqWLZoijCvbSKlxRzgc7L/nXNc/8qTzlHpOb7tXIP+I6KaZH91ZwqVc/Ru
YlBugFUY+MUJHLTirBUMg/Xoqxs9v4Emg1p421PZ01FO0ZIyKpxyOwZcweLFh+p2
nmxhWWo58syCq4gjRe3MED3uXncPQQ3CTBUhMA84NjMc4hkYp9VPiqZ2Wlh/0HbV
FVeqwia8zuIwxcPO9pagfA2mwwaTn7JSJf6VE5CsmVPH632iQFyThfemDY8Ww2PF
hJKa++tCCn2nfIKL25ZxMEaO8P1Xqn0GYsHRu38juifU2E1tphuGgX9nkQAF1hhl
9Ifs2LRvjwXO3UatqYEjCa/OEIHbI1mWKzCSWQqQd7cJVDTXfdb4FZi+FTzx29D/
FmJRRBLrdW5Qu7sZlHhcwlj/6IBwICpegPuKUillMgrUjuwDTZXF9LJPJEdbaKKL
7VcOd4DqsQRgI0nr9Aom
=DZp9
-----END PGP SIGNATURE-----

--kRPgt2ku+S/0Bic/--