Re: Writing 1100 rows per second

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxx> 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@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxx

www.callix.com.br


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux