pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedBest partition type for billions of addresses
5+ messages / 3 participants
[nested] [flat]
* Best partition type for billions of addresses
@ 2020-05-02 13:20 Arya F <arya6000@gmail.com>
2020-05-02 14:00 ` Re: Best partition type for billions of addresses Justin Pryzby <pryzby@telsasoft.com>
2020-05-02 14:01 ` Re: Best partition type for billions of addresses Stephen Frost <sfrost@snowman.net>
0 siblings, 2 replies; 5+ messages in thread
From: Arya F @ 2020-05-02 13:20 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Best partition type for billions of addresses
2020-05-02 13:20 Best partition type for billions of addresses Arya F <arya6000@gmail.com>
@ 2020-05-02 14:00 ` Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 0 replies; 5+ messages in thread
From: Justin Pryzby @ 2020-05-02 14:00 UTC (permalink / raw)
To: Arya F <arya6000@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Sat, May 02, 2020 at 09:20:06AM -0400, Arya F 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.
I dug up the last messages about this:
https://www.postgresql.org/message-id/flat/CAFoK1aztep-079Fxmaos6umR8X6m3x1K_aZLGtQGpYxfENh9%3DA%40m...
https://www.postgresql.org/message-id/flat/CAFoK1azkv1Z%3DRr7ZWrJjk0RQSoF6ah%2BQMpLSSyBs1KsEiQ3%3Dvw...
https://www.postgresql.org/message-id/CAFoK1axr_T6nB8ZAq8g2QBcqv_pE%3DdsZsxyjatz8Q67k1VKAnw%40mail.g...
> 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?
Indexes are separate from partitioning. Typically, the partitioned columns are
indexed, but it's not required.
If the partition key isn't used in your typical query, then partitioning didn't
help you, and you chose the wrong partition strategy/key.
> 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?
It sounds like you're thinking about this backwards.
What are your typical queries ? That should determines the partition strategy
and key, not the other way around. You should maybe think about whether there
are views/functions/joins of the partitioned column.
For example, at telsasoft, our report queries *always* say "tbl.start_time >=
t1 AND tbl.start_time < t2", so I partitioned our tables BY RANGE(start_time),
so a typical report hits only a single table. And, start_time has an index on
it, so a typical query over 1-2 days will only hit a fraction of that table.
--
Justin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Best partition type for billions of addresses
2020-05-02 13:20 Best partition type for billions of addresses Arya F <arya6000@gmail.com>
@ 2020-05-02 14:01 ` Stephen Frost <sfrost@snowman.net>
2020-05-02 14:33 ` Re: Best partition type for billions of addresses Arya F <arya6000@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Stephen Frost @ 2020-05-02 14:01 UTC (permalink / raw)
To: Arya F <arya6000@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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?
>
> 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?
>
> 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
Attachments:
[application/pgp-signature] signature.asc (818B, ../../20200502140109.GX13712@tamriel.snowman.net/2-signature.asc)
download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Best partition type for billions of addresses
2020-05-02 13:20 Best partition type for billions of addresses Arya F <arya6000@gmail.com>
2020-05-02 14:01 ` Re: Best partition type for billions of addresses Stephen Frost <sfrost@snowman.net>
@ 2020-05-02 14:33 ` Arya F <arya6000@gmail.com>
2020-05-02 14:39 ` Re: Best partition type for billions of addresses Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 5+ messages in thread
From: Arya F @ 2020-05-02 14:33 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: pgsql-performance@lists.postgresql.org
> * 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.
So I should be able to achieve good performance with just one
partition? Maybe I just need to get hardware with more memory?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Best partition type for billions of addresses
2020-05-02 13:20 Best partition type for billions of addresses Arya F <arya6000@gmail.com>
2020-05-02 14:01 ` Re: Best partition type for billions of addresses Stephen Frost <sfrost@snowman.net>
2020-05-02 14:33 ` Re: Best partition type for billions of addresses Arya F <arya6000@gmail.com>
@ 2020-05-02 14:39 ` Stephen Frost <sfrost@snowman.net>
0 siblings, 0 replies; 5+ messages in thread
From: Stephen Frost @ 2020-05-02 14:39 UTC (permalink / raw)
To: Arya F <arya6000@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2020-05-02 14:39 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-05-02 13:20 Best partition type for billions of addresses Arya F <arya6000@gmail.com>
2020-05-02 14:00 ` Justin Pryzby <pryzby@telsasoft.com>
2020-05-02 14:01 ` Stephen Frost <sfrost@snowman.net>
2020-05-02 14:33 ` Arya F <arya6000@gmail.com>
2020-05-02 14:39 ` Stephen Frost <sfrost@snowman.net>
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