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
Date: Sat, 2 May 2020 10:39:24 -0400
Message-ID: <20200502143923.GY13712@tamriel.snowman.net> (raw)
In-Reply-To: <CAFoK1awNHv2SB2F2bFGkCgZTa9YvBBKJ83yrWvyXvAARNhtzWw@mail.gmail.com>
References: <CAFoK1ayT3Ma8e_d+AXEBVO6wGffH0bs_LDeY2+9X5NRcGYp_SA@mail.gmail.com>
<20200502140109.GX13712@tamriel.snowman.net>
<CAFoK1awNHv2SB2F2bFGkCgZTa9YvBBKJ83yrWvyXvAARNhtzWw@mail.gmail.com>
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.
>
> 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
Attachments:
[application/pgp-signature] signature.asc (818B, ../20200502143923.GY13712@tamriel.snowman.net/2-signature.asc)
download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sfrost@snowman.net, arya6000@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Best partition type for billions of addresses
In-Reply-To: <20200502143923.GY13712@tamriel.snowman.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox