The issue isn't so much the speed as yes I use bulk SQL statements and can upsert like 4-5k rows per second, it's more the disk writes which I find unsettling as after a few months I've noticed several 10s of TBs of disk writes from Postgres onto the SSDs which seems like unnecessary wear.
Re:Just use Postgres for everything
by crazygringo on Saturday December 10, 2022 @05:00PM
(#33937324)
Then it seems your setup is totally fine. If you're upserting 10 million rows a day with lots of columns, then of course you're going to be seeing TB's of disk writes. And remember that SSD's can't even write individual bytes the way HDD's can, they necessarily write a whole page at a time, which might be 4K or 16K, even if you're just updating a single integer.
Your database and SSD are functioning totally normally, as designed.
Several 10s of TBs of writes over a few months is literally nothing to be concerned about for any recently modern enterprise ssd in a production setting.
This seems like a bizarre rationale to make a database choice.
But 40 columns should be a cakewalk for any RDBMS to handle. And I assume you're not storing KB's of data in the different columns? Because if your strings are large enough to be allocated as references to separate blob storage, rather than in the row, that could be a performance problem.
Bulk operations with millions of rows can also sometimes take way longer if you're doing them as a transaction that can be rolled back. If it's acceptable to disable that, that could be a huge improvement.
You can also sometimes find massive speedups in using bulk SQL statements (upserting 1000 rows per query, rather than 1 row per query) or CSV file import rather than SQL.
And obviously make sure you're using indexes wherever appropriate.
Because generally speaking, upserting ~10 million reasonably-sized records is the kind of thing that should only take a few minutes on an SSD. You're not going to do it in seconds, but it shouldn't be taking an hour or anything either.