Date: Sat, 2 May 2020 10:39:24 -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: <20200502143923.GY13712@tamriel.snowman.net>
References: 
 <CAFoK1ayT3Ma8e_d+AXEBVO6wGffH0bs_LDeY2+9X5NRcGYp_SA@mail.gmail.com>
 <20200502140109.GX13712@tamriel.snowman.net>
 <CAFoK1awNHv2SB2F2bFGkCgZTa9YvBBKJ83yrWvyXvAARNhtzWw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="86kyXGETKrp/nLZ+"
Content-Disposition: inline
In-Reply-To: 
 <CAFoK1awNHv2SB2F2bFGkCgZTa9YvBBKJ83yrWvyXvAARNhtzWw@mail.gmail.com>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--86kyXGETKrp/nLZ+
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Arya F (arya6000@gmail.com) wrote:
> > * 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.
>=20
> But I have noticed that my updates and inserts have slowed down
> dramatically when I started going over about 20 million rows and the
> reason was because every time it has to update the index. When I
> removed the index, my insert performance stayed good no matter the
> size of the table.

Sure it does.

> So I should be able to achieve good performance with just one
> partition? Maybe I just need to get hardware with more memory?

Instead of jumping to partitioning, I'd suggest you post your actual
table structures, queries, and explain results here and ask for help.

https://wiki.postgresql.org/wiki/Slow_Query_Questions

Thanks,

Stephen

--86kyXGETKrp/nLZ+
Content-Type: application/pgp-signature; name="signature.asc"

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

iQIcBAEBCgAGBQJerYYbAAoJEO1sijiDR2RVYT0P/3ntfEhPrZyN1aMFCD+7NjWO
jxZBJKeJb5SjxYbl0DY8hZcLvTkc+1zDx6Aie0uaYk4o92WdhM6SukfLF4qEO3pO
O5oVosC8sbtFEh2HZpOXHBldCpkDtgOxeeeOJ3sg96eUv9AF5uu7CBDKYk/N3gdx
Gxm+52wObbURp6f5yUJuAm4dJmG8UqA/ModgCY/gUxmO8wL5u2yxIMbJl8c87AdT
FcTn51Rfl8BuW+7hPIoplKyZyZNXMiy+Tjx2GLQksGqpC6CRzWEmWT3kUkTz8NOK
V01Rwc8b0iMdjAQoJoDs32XUDOo4U+WWo0dvYHDJEdD0aenJsY7NZ/Oe4OcHgmSs
zoasxTyBjBpF6j6BAgab4qgSq4fGjdLQhXQH9s8fvUtes1JRxNBYqvZI8stJosHl
6bLkJEaO9YTKkrUuN1w2aXWi8wRpAwnceA4RL6tM+fMzu03Czkg8A3r06Fxv40V5
sYcXzrec2TiC2/QTQkHWaq12wLWxS4zr9mu1SAjYMuPjpOihlcU00dZjHGWKF1vv
8u0+0/F1rDnP1ZRjkmz8E3R36lnqs1ZdVFu88AsPmu6G9nagDXNh9ykazysuA15Q
Qa2vlCJULM6eu4W+HpyTUpQPM9yU4pAMguvZDkrzR7+SyzUTTPUoPeKAnRfCPnyW
X2pK20ItI6dv622ZPCQb
=SDdC
-----END PGP SIGNATURE-----

--86kyXGETKrp/nLZ+--