On Sun, May 03, 2020 at 11:51:44PM -0400, Arya F wrote: > On Sun, May 3, 2020 at 11:46 PM Michael Lewis <mlewis@xxxxxxxxxxx> wrote: > > What kinds of storage (ssd or old 5400 rpm)? What else is this machine running? > > Not an SSD, but an old 1TB 7200 RPM HDD > > > What configs have been customized such as work_mem or random_page_cost? > > work_mem = 2403kB > random_page_cost = 1.1 I mentioned in February and March that you should plan to set shared_buffers to fit the indexes currently being updated. Partitioning can help with that *if* the writes mostly affect 1-2 partitions at a time (otherwise not). On Wed, Feb 05, 2020 at 11:15:48AM -0600, Justin Pryzby wrote: > > Would that work? Or any recommendations how I can achieve good performance > > for a lot of writes? > > Can you use partitioning so the updates are mostly affecting only one table at > once, and its indices are of reasonable size, such that they can fit easily in > shared_buffers. On Sun, Mar 22, 2020 at 08:29:04PM -0500, Justin Pryzby wrote: > 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
