Re: Writing 1100 rows per second

Arya,
We ran into the issue of decreasing insert performance for tables of
hundreds of millions of rows and they are indeed due to index updates.
We tested TimescaleDB (a pgsql plugin) with success in all use cases that
we have. It does a "behind the scenes" single-level partitioning that is
indeed very efficient.
Not sure about the 1100 inserts/s as it is hardware dependent, but we got
the flat response curve (inserts per second stayed stable with hundreds of
millions of rows, regardless of indexes).
My suggestion: have a look at
https://blog.timescale.com/timescaledb-vs-6a696248104e/ , and do some PoCs.

Regards,
Haroldo Kerry

On Wed, Feb 5, 2020 at 2:25 PM Arya F <arya6000(at)gmail(dot)com> wrote:

> If I run the database on a server that has enough ram to load all the
> indexes and tables into ram. And then it would update the index on the HDD
> every x seconds. Would that work to increase performance dramatically?
>
> On Wed, Feb 5, 2020, 12:15 PM Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:
>
>> 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
>>
>

--

Haroldo Kerry

CTO/COO

Rua do Rócio, 220, 7° andar, conjunto 72

São Paulo – SP / CEP 04552-000

hkerry(at)callix(dot)com(dot)br

www.callix.com.br