From: | Justin Pryzby <pryzby(at)telsasoft(dot)com> |
---|---|
To: | Arya F <arya6000(at)gmail(dot)com> |
Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Partitions to improve write/update speed for tables with indexes? |
Date: | 2020-03-23 01:29:04 |
Message-ID: | 20200323012904.GG2563@telsasoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Sun, Mar 22, 2020 at 09:22:50PM -0400, Arya F wrote:
> I have noticed that my write/update performance starts to dramatically
> reduce after about 10 million rows on my hardware. The reason for the
> slowdown is the index updates on every write/update.
It's commonly true that the indexes need to fit entirely in shared_buffers for
good write performance. I gave some suggestions here:
https://www.postgresql.org/message-id/20200223101209.GU31889%40telsasoft.com
> The solution would be partitioning? One of my tables will have more
> than 1 billion rows of data, so I would have to create about 100
> partitions for that table. Is the a practical limit to the amount of
> partitions I can have with Postgresql 12?
The recommendation since pg12 is to use at most a "few thousand" partitions, so
for the moment you'd be well within the recommendation.
https://www.postgresql.org/docs/12/ddl-partitioning.html
--
Justin
From | Date | Subject | |
---|---|---|---|
Next Message | Ronnie S | 2020-03-23 03:45:53 | Partition Pruning (Hash Partitions) Support for DELETEs in PostgreSQL 11 and 12 |
Previous Message | Arya F | 2020-03-23 01:22:50 | Partitions to improve write/update speed for tables with indexes? |