From: Justin Pryzby <pryzby@telsasoft.com>
To: Arya F <arya6000@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Partitions to improve write/update speed for tables with indexes?
Date: Sun, 22 Mar 2020 20:29:04 -0500
Message-ID: <20200323012904.GG2563@telsasoft.com> (raw)
In-Reply-To: <CAFoK1azkv1Z=Rr7ZWrJjk0RQSoF6ah+QMpLSSyBs1KsEiQ3=vw@mail.gmail.com>
References: <CAFoK1azkv1Z=Rr7ZWrJjk0RQSoF6ah+QMpLSSyBs1KsEiQ3=vw@mail.gmail.com>
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
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: pryzby@telsasoft.com, arya6000@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Partitions to improve write/update speed for tables with indexes?
In-Reply-To: <20200323012904.GG2563@telsasoft.com>
* 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