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
