From: Justin Pryzby <pryzby@telsasoft.com>
To: Arya F <arya6000@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Writing 1100 rows per second
Date: Wed, 5 Feb 2020 11:15:49 -0600
Message-ID: <20200205171548.GB403@telsasoft.com> (raw)
In-Reply-To: <CAFoK1aztep-079Fxmaos6umR8X6m3x1K_aZLGtQGpYxfENh9=A@mail.gmail.com>
References: <CAFoK1aztep-079Fxmaos6umR8X6m3x1K_aZLGtQGpYxfENh9=A@mail.gmail.com>
On Wed, Feb 05, 2020 at 12:03:52PM -0500, Arya F wrote:
> I'm looking to write about 1100 rows per second to tables up to 100 million
> rows. I'm trying to come up with a design that I can do all the writes to a
> database with no indexes. When having indexes the write performance slows
> down dramatically after the table gets bigger than 30 million rows.
>
> I was thinking of having a server dedicated for all the writes and have
> another server for reads that has indexes and use logical replication to
> update the read only server.
Wouldn't the readonly server still have bad performance for all the wites being
replicated to it ?
> 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.
brin indices may help for some, but likely not for all your indices.
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: Writing 1100 rows per second
In-Reply-To: <20200205171548.GB403@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