pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Partitions to improve write/update speed for tables with indexes? 2+ messages / 2 participants [nested] [flat]
* Partitions to improve write/update speed for tables with indexes? @ 2020-03-23 01:22 Arya F <arya6000@gmail.com> 0 siblings, 1 reply; 2+ messages in thread From: Arya F @ 2020-03-23 01:22 UTC (permalink / raw) To: pgsql-performance@lists.postgresql.org 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. 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? ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Partitions to improve write/update speed for tables with indexes? @ 2020-03-23 01:29 Justin Pryzby <pryzby@telsasoft.com> parent: Arya F <arya6000@gmail.com> 0 siblings, 0 replies; 2+ messages in thread From: Justin Pryzby @ 2020-03-23 01:29 UTC (permalink / raw) To: Arya F <arya6000@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2020-03-23 01:29 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-03-23 01:22 Partitions to improve write/update speed for tables with indexes? Arya F <arya6000@gmail.com> 2020-03-23 01:29 ` Justin Pryzby <pryzby@telsasoft.com>
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