pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWriting 1100 rows per second
8+ messages / 7 participants
[nested] [flat]
* Writing 1100 rows per second
@ 2020-02-05 17:03 Arya F <arya6000@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Arya F @ 2020-02-05 17:03 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
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.
Would that work? Or any recommendations how I can achieve good performance
for a lot of writes?
Thank you
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Writing 1100 rows per second
@ 2020-02-05 17:12 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Arya F <arya6000@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Laurenz Albe @ 2020-02-05 17:12 UTC (permalink / raw)
To: Arya F <arya6000@gmail.com>; pgsql-performance@lists.postgresql.org
On Wed, 2020-02-05 at 12:03 -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.
>
> Would that work? Or any recommendations how I can achieve good performance for a lot of writes?
Logical replication wouldn't make a difference, because with many indexes, replay of the
inserts would be slow as well, and replication would lag more and more.
No matter what you do, there will be no magic way to have your tables indexed and
have fast inserts at the same time.
One idea I can come up with is a table that is partitioned by a column that appears
in a selective search condition, but have no indexes on the table, so that you always get
away with a sequential scan of a single partition.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Writing 1100 rows per second
@ 2020-02-05 17:15 Justin Pryzby <pryzby@telsasoft.com>
parent: Arya F <arya6000@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Justin Pryzby @ 2020-02-05 17:15 UTC (permalink / raw)
To: Arya F <arya6000@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Writing 1100 rows per second
@ 2020-02-05 17:25 Arya F <arya6000@gmail.com>
parent: Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 2 replies; 8+ messages in thread
From: Arya F @ 2020-02-05 17:25 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance@lists.postgresql.org
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@telsasoft.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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Writing 1100 rows per second
@ 2020-02-05 17:46 Haroldo Kerry <hkerry@callix.com.br>
parent: Arya F <arya6000@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Haroldo Kerry @ 2020-02-05 17:46 UTC (permalink / raw)
To: Arya F <arya6000@gmail.com>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; pgsql-performance@lists.postgresql.org
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@gmail.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@telsasoft.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@callix.com.br
www.callix.com.br
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Writing 1100 rows per second
@ 2020-02-06 18:15 Ogden Brash <obrash@litika.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 8+ messages in thread
From: Ogden Brash @ 2020-02-06 18:15 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Arya F <arya6000@gmail.com>; pgsql-performance@lists.postgresql.org
On Wed, Feb 5, 2020 at 9:12 AM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
> One idea I can come up with is a table that is partitioned by a column
> that appears
> in a selective search condition, but have no indexes on the table, so that
> you always get
> away with a sequential scan of a single partition.
>
>
This is an approach that I am currently using successfully. We have a large
dataset that continues to be computed and so insert speed is of importance
to us. The DB currently has about 45 billion rows. There are three columns
that are involved in all searches of the data. We have separate tables for
all unique combination of those 3 values (which gives us about 2000
tables). Thus, we were able to save the space for having to store those
columns (since the name of the table defines what those 3 columns are in
that table). We don't have any indices on those tables (except for the
default one which gets created for the pk serial number). As a result all
searches only involve 1 table and a sequential scan of that table. The
logic to choose the correct tables for insertionse or searches lives in our
application code and not in SQL.
The size of the 2000 tables forms a gaussian distirbution, so our largest
table is about a billion rows and there are many tables that have hundreds
of millions of rows. The ongoing insertions form the same distribution, so
the bulk of insertions is happening into the largest tables. It is not a
speed demon and I have not run tests recently but back of the envelope
calculations give me confidence that we are definitely inserting more than
1100 per second. And that is running the server on an old puny i5 processor
with regular HDDs and only 32Gb of memory.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Writing 1100 rows per second
@ 2020-02-09 19:30 Jeff Janes <jeff.janes@gmail.com>
parent: Arya F <arya6000@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Jeff Janes @ 2020-02-09 19:30 UTC (permalink / raw)
To: Arya F <arya6000@gmail.com>; +Cc: Justin Pryzby <pryzby@telsasoft.com>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
On Wed, Feb 5, 2020 at 12:25 PM Arya F <arya6000@gmail.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?
>
Perhaps. Probably not dramatically though. If x seconds (called a
checkpoint) is not long enough for the entire index to have been dirtied,
then my finding is that writing half of the pages (randomly interspersed)
of a file, even in block order, still has the horrid performance of a long
sequence of random writes, not the much better performance of a handful of
sequential writes. Although this probably depends strongly on your RAID
controller and OS version and such, so you should try it for yourself on
your own hardware.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Writing 1100 rows per second
@ 2020-02-12 05:28 Amol Tarte <amoltarte@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Amol Tarte @ 2020-02-12 05:28 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: Arya F <arya6000@gmail.com>; Justin Pryzby <pryzby@telsasoft.com>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
I am using npgsql in my c# program,
I have a table in which we need to dump data from a flat file approx. 5.5
million rows once in every 24 hours.
Soon it will grow to 10 million rows.
We are using npgsql binary import and all this is done (5.5 million)
inserts in < 3 minutes.
Then we create two indexes on the table which takes 25-30 seconds.
With Warm Regards,
Amol P. Tarte
Project Manager,
Rajdeep Info Techno Pvt. Ltd.
Visit us at http://it.rajdeepgroup.com
On Mon, Feb 10, 2020 at 1:00 AM Jeff Janes <jeff.janes@gmail.com> wrote:
> On Wed, Feb 5, 2020 at 12:25 PM Arya F <arya6000@gmail.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?
>>
>
> Perhaps. Probably not dramatically though. If x seconds (called a
> checkpoint) is not long enough for the entire index to have been dirtied,
> then my finding is that writing half of the pages (randomly interspersed)
> of a file, even in block order, still has the horrid performance of a long
> sequence of random writes, not the much better performance of a handful of
> sequential writes. Although this probably depends strongly on your RAID
> controller and OS version and such, so you should try it for yourself on
> your own hardware.
>
> Cheers,
>
> Jeff
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2020-02-12 05:28 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-02-05 17:03 Writing 1100 rows per second Arya F <arya6000@gmail.com>
2020-02-05 17:12 ` Laurenz Albe <laurenz.albe@cybertec.at>
2020-02-06 18:15 ` Ogden Brash <obrash@litika.com>
2020-02-05 17:15 ` Justin Pryzby <pryzby@telsasoft.com>
2020-02-05 17:25 ` Arya F <arya6000@gmail.com>
2020-02-05 17:46 ` Haroldo Kerry <hkerry@callix.com.br>
2020-02-09 19:30 ` Jeff Janes <jeff.janes@gmail.com>
2020-02-12 05:28 ` Amol Tarte <amoltarte@gmail.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