Postgres performance tuning

Inserting records in an existing database

When inserting new records in an existing table that can become very slow. One thing that could help is to set the fill-factor to a lower value. The result will be some waste of storage but also safe a lot of moving around of records. They might fit in the same page and so safe a move to another page. Even the index then can stay the same.

Links:

• Postgres Upsert Performance Considerations (millions of rows/hour)
• What is fillfactor and how does it affect PostgreSQL performance?

Statements:

ALTER TABLE payment_buyer SET (fillfactor = 70);
INSERT INTO ... SELECT ... FROM ... ON CONFLICT UPDATE ... WHERE ...
ALTER TABLE payment_buyer RESET (fillfactor);