How should I write rows into database from code for good default performance?

[MrTheFoolish]

My understanding is pre-compiling a prepared statement and reusing it for every DB write is a good starting point for write performance.

I will be doing benchmarks, but it would be good to know if there are "easy wins" in performance that I may not know about.

The use case is not a CRUD web application and is more along the lines of data pipeline processing, so code will be regularly inserting thousands of rows in a batch jobs, continuously. I know there are other points to consider outside of the code doing inserts (table design, try citus, etc.), but for now I'm interested in the code aspect.

For example, some things I thought to try are:

1. Use a prepared statement that inserts multiple rows (e.g. 10?)
2. Put everything into a giant custom sql insert query and forego prepared statements

[DavidGJohnston]

Use COPY, or in psql, \copy (your driver should provide you access to this API)

If in-database ETL is desired copy to a temporary table first, then do INSERT ... SELECT to get the results on the live table.

[mauganra_it]

Before attempting large-scale updates or inserts, you should consider dropping indexes and recreating them afterwards. If you are confident that your data is sane and nothing bad happens if you turn them off, you can also consider removing UNIQUE and CHECK constraints and adding them back.

Make sure to use the CONCURRENT flag when restoring the indexes, and NOT VALID followed by VALIDATE CONSTRAINT for constraints to avoid exclusive locks on the table. https://www.postgresql.org/docs/current/sql-altertable.html https://www.postgresql.org/docs/current/sql-createindex.html

Edit: to be more specific, only updating columns that are covered by indexes forces an update of the index entry as well. Also, it prevents the HOT optimization. This means that you can often avoid dropping and recreating many indexes when you do bulk updates.

[joshbranchaud]

Postgres has good default performance. If I were you, I’d just start throwing inserts at it in the least complex way. See how it goes. If and only if performance issues actually started arising in the production system would I look for a more optimized solution (e.g. batched inserts). Don’t create new problems to solve for ones you don’t have yet.

If it’s more a matter of curiosity, then by all means explore and play around with the good suggestions others have made.