Такое количество лучше вставлять через COPY (пачками по несколько десятков тысяч записей)

Есть, но связано это с другими факторами, сейчас попробую найти

А зачем copy пачками, если можно всё сразу?

Транзакции существуют для того, чтобы обеспечить ACID, а не для "преодоления" каких-то (особенно, придуманных) ограничений по производительности. ;)
Т.е. если "2 миллиона вставок" является одной бизнес-транзакцией — место им всем в транзакции, а если нет — то, в общем случае, чем короче (меньше действий выполняется) транзакция, тем лучше.
Насчёт "общего случая" — если как по одной, так и по 2 миллиона записей в транзакции вставлять правильно, то можно выбирать любое количество на транзакцию.
В этом плане, каждый commit требует fsync, а их "железо" может выдавать не так много (это зависит в основном от дисков), т.е. может быть смысл укрупнять транзакции.

> там было ограничение на 60 вставок в секунду

Не было. А если на том "железе", где у sqlite было, Вы поставили PostgreSQL, и у него TPS больше (особенно, если существенно больше) — у меня для Вас [возможно] плохие новости — где-то в I/O stack (начиная от PostgreSQL и заканчивая дисками) не выполняются гарантии fsync. Т.е. если что случится — Ваш кластер баз postgres запросто станет corrupted, что невесело, даже если есть backup. ;(

Не могу найти, раньше как-то документацию самого sqlite нагугливал, сейчас уже не помню, вроде бы проблема была с синхронизацией самой субд был связан. Вот ишью на гитхабе, тут с такой же проблемой столкнулись https://github.com/mapbox/node-sqlite3/issues/437

Выше (в логе) смотрели? Может, пропустили из-за длинного statement?

Бывало и не раз, когда копи на много-много записей ломался по аут оф мемори

Не могу найти, раньше как-то документацию самого sqlite нагугливал, сейчас уже не помню, вроде бы проблема была с синхронизацией самой субд был связан. Вот ишью на гитхабе, тут с такой же проблемой столкнулись https://github.com/mapbox/node-sqlite3/issues/437

Thanks everyone for your tips. It appears there's no I/O perf problem on my side, I found a way to do inserts faster - instead of executing 'insert into' query 500 times for each row separately, I glue the SQL together and insert 500 rows in a single query. Only then the code is able to approach 20K-30K inserts per second, which is more or less what 'raw' sqlite would be able to handle. So the naive/default way of doing this in nodejs is not very optimal - I wonder how the asynchronous implementation for nodejs deals with the fact that sqlite doesnt allow concurrent write operations.

ну вот все правмльно сказали. пачками надо вставлять)

Это, мягко говоря, очень странно. Любой дамп содержит в себе "копи на много-много записей", а их загрузка широко применяется. Т.е., возможно, это был какой-то bug, а следовать "обжёгся на молоке, дует и на воду" не стоит, IMHO. ;)

я в итоге к тому же пришел

В том-то и дело, что дамп содержит(ал) несколько команд копи на ОЧЕНЬ большие (по количеству записей) таблицы.

Если Вам нужно вставить как можно быстрее, используйте COPY.
Если любопытно по другим способам, см. http://www.depesz.com/2007/07/05/how-to-insert-data-to-database-as-fast-as-possible/

Таки я был слеповат.

2020-07-22 08:36:32.718 GMT [2742] ERROR:  out of memory
2020-07-22 08:36:32.718 GMT [2742] DETAIL:  Cannot enlarge string buffer containing 0 bytes by 1418893915 more bytes.

Таки я был слеповат.

2020-07-22 08:36:32.718 GMT [2742] ERROR:  out of memory
2020-07-22 08:36:32.718 GMT [2742] DETAIL:  Cannot enlarge string buffer containing 0 bytes by 1418893915 more bytes.

Ну так вот вытащить бы этот запрос, и посмотреть (в psql) ошибку с максимальной детализацией.