I stumbled on an interesting case.

I knew before that copy-pasting entire table from Manifold to PostgreSQL is much quicker than copying rows from Manifold table and pasting into a pre-existing PostgreSQL table. One uses COPY and the other INSERT-s.

But that was not the full story.

1. First, I copied a table to PostgreSQL afresh. I did not think of the time it took. (It wasn't great)

2. I needed to clear and refill the same table. I chose copy-rows/paste-rows. This was horribly slow. I needed to investigate.

3. Claude Opus picked up what probably caused the slowness

• per row commits
• PostgreSQL configured with `synchronous_commit = on`

• slow storage

• Wait event == IO: WALSync - That indicates the backend is blocked in fsync()/fdatasunc() on the WAL, which is what a COMMIT does.
• Transaction start time == Query start time - That indicates every row is its own autocommit transaction.

So you're paying one durable disk flush per row, and that flush is taking tens of milliseconds (later measured 104 ms).

4. I had forgotten about Asynchronous commit. I have set `synchronous_commit = off` for another database before. It was easy to change here too and it helped a lot, but the storage story was still very interesting.

5. Claude suggested a command `pg_test_fsync -f /path/to/pg_wal/testfile`. That revealed a situation

worse than guessed: fdatasync on WAL is 9.6 ops/sec — 104 ms per flush. Non-synced writes run at 2.5M ops/sec, so the storage isn't slow, only durable writes are.

6. Claude asked for device model and replied

your PG volume is on a Samsung 870 QVO. The QVO is arguably the worst consumer SSD ever made for a database for 2 reasons:

• No power-loss protection.
• QLC.

and cited 2 sources:

• https://forum.proxmox.com/threads/samsung-870-qvo-1tb-terrible-write-performance.82026/

Without capacitors, the drive can't acknowledge a FLUSH until the data is physically in NAND. The drive lacks power loss protection, so sync writes can't be considered complete until they're genuinely on media — and that's slow.

• https://www.techpowerup.com/review/samsung-870-qvo-1-tb/6.html

"physically in NAND" means writing to QLC cells. TechPowerUp found that once the 870 QVO's SLC cache is exhausted, transfer rates collapse to under 100 MB/s — slower than most hard drives — and called it the slowest SSD they had ever tested.

7. The fix for me (besides `ALTER SYSTEM SET synchronous_commit = off;` ) was to use nvme 970 EVO Plus (2TB vs 8TB QVO vs 0.5TB database) that was also present on the machine.

The wild speed differences are shown below. In summary, any COPY wins INSERT-s, but some INSERTS are close and others definitely aren't.

Attachments:
time_COPY_n_INSERT_w_Manifold9_n_PostgreSQL.png