Like everyone else, I'm going to offer tuning tips even thought that very much wasn't your point :-)

What you are describing sounds almost exactly like PRAMGA synchronous = FULL [1] (which is the default). That pragma controls when fsync occurs. Depending on your application, you might have got away with NORMAL or even OFF. Again depending on your application, you could have set journal_mode = OFF or increased the mmap_size (both also discussed on that page). Yes those are fairly magical hacks, but synchronous and journal_mode at least are things are always worth considering for a new SQLite database (and before building it with debugging symbols!).

Even without those tweaks, the really key thing is to use fairly large transactions. I'm surprised that alone wouldn't have got you decent performance.

One option you didn't have at the time but might help today is write-ahead mode [2] with journal_mode = WAL (but still presumably not as fast as journal_mode = OFF!). I believe the only reason it isn't enabled by default is for backwards compatibility. According to that article, it was introduced in 2010-07-21, and improved to better handle large transactions (>100MB) in 2016-02-15.

[1] https://www.sqlite.org/pragma.html#pragma_synchronous

[2] https://sqlite.org/wal.html

I've been there. It doesn't work. I've been applying all those tips and tricks, but it didn't improve performance that much, but I have lost data.

Sqlite is full of locks, and any writes are single-threaded stop-the-world, and there's no way around it. It's Sqlite's philosophy. Write-heavy databases just need something like MVCC, and Sqlite won't have that.

I think you make a point which is very true and valid but not relevant to this particular comment thread.

You are talking about multiple different processes/threads heavily and concurrently writing to a database. In that case, you're absolutely right, the point has come to switch to a client/server database like PostgreSQL or MySQL.

But the parent comment was not about that (or at least they didn't explicitly mention concurrency, and their mention of "a default setting that made spill-to-disk very aggressive" rather than locks suggested that concurrency wasn't the problem). They seemed to be talking about a single writer inserting at a high rate, which is something I'd expect to cope with very well with the right tricks (mostly batching multiple inserts in transactions - I acknowledge their comment that the defaults are unfortunate though). Yes even with a single process there are locks, but if a lock is uncontended then it is not normally a problem.

> the point has come to switch to a client/server database

Windows has an embedded NoSQL DB engine which is fine with concurrent writes and multi-versioning: https://en.wikipedia.org/wiki/Extensible_Storage_Engine

There’re disadvantages too. It does not implement SQL, the queries need to be done manually on top of various indices in these tables. The DB has much more than a single file. The API is way more complicated than sqlight. The databases are portable from older to newer versions of Windows with automatic upgrades, but not the other way.

The JET database continually corrupts itself. It’s terrible.

The Windows search functionality uses this database and the only way Microsoft was able to make this feature somewhat reliable is by having it run very thorough checks at startup and tossing the database and reindexing at the first hint of trouble.

I don’t know why it is so terribly unreliable but I know it is.

Interesting, I have opposite experience. Once I had lots of experience with AD and Exchange. Later I shipped desktop software based on ESENT, and supported it for a few years. Was generally happy with the technology.

Are you sure these issues are caused by the DB engine? Other possibilities include your PC (like interference with crappy AV software), or Microsoft’s code of these search indexing services.

This happens on large amounts of PCs and I don’t think the way Microsoft uses its own database engine should be an excuse for it corrupting its data.

I think the difference is that Windows Search is always running, including when the computer crashes, is turned off unexpectedly or doesn’t complete waking up from sleep or hibernation. I think JET just isn’t that robust against that. It’s quite conceivable people manually closed your software when they shut down their computer.

I remember Exchange failing the same way if its database disks suddenly disappear due to network issues.

> Microsoft uses its own database engine

Microsoft is large and software quality varies. For instance look a Skype, they failed to use their own GUI frameworks and are using Electron i.e. Google Chrome to paint a few controls.

> I think JET just isn’t that robust against that.

I think it is, it's mentioned everywhere:

https://docs.microsoft.com/en-us/windows/win32/extensible-st...

https://stackoverflow.com/a/2369220/126995

Okay, ‘according to the documentation’ it probably is. In reality it isn’t.

Like they say,

In theory, there’s no difference between theory and practice. In practice, there is.

Here’s a list of things that can go wrong (here in the context of domain controllers):

https://docs.microsoft.com/en-us/troubleshoot/windows-server...

Some of them are just the unavoidable hardware failures, for others they suggest ‘Deploy the OS on server-class hardware’. Or the always helpful ‘restore from backup’. Not quite reasonable for a database containing a search index on a consumer device.

> Here’s a list of things that can go wrong (here in the context of domain controllers)

That article was written because DC is a business-critical infrastructure. Here’s a comparable one about Oracle: https://docs.oracle.com/cd/A87860_01/doc/server.817/a76965/c...

NTFS: https://docs.microsoft.com/en-us/previous-versions/windows/i...

> Not quite reasonable for a database containing a search index on a consumer device.

Before I switched to MS Outlook, I was using Windows live mail (now discontinued) as an e-mail client for a decade or so, it used ESENT for everything.

When I run process explorer and search for “esent.dll”, it finds a dozen of system services using ESENT databases, many of them critical like CryptSvc.

I try to buy good hardware, but that’s not server-grade components. I don’t use ECC RAM nor a UPS, and I suffer from brief power outages couple times a year. If ESENT would be corrupting databases when the power is turned off suddenly, I would have noticed.

Your experiment with a sample size of 1 proves, with an uncertainty of 100%, that the database never corrupts.

Experiments with a larger sample size show a different result.

> Sqlite is full of locks, and any writes are single-threaded stop-the-world, and there's no way around it. It's Sqlite's philosophy.

SQLite is not the silver bullet of the DB world, but it's extremely useful in certain scenarios. Will you need a DB for a desktop app? Ditto. Want a portable file to carry some complex data structure? Ditto. Want to test your webapp during development, or provide a single-user version for users? Ditto.

Concurrent writing, multiple user, multiple producer scenarios need something bigger. MySQL Embedded, MySQL, Posgres, MSSQL, Oracle... List goes on.

SQLite makes databases accessible and useful in much more scenarios. I've hated databases until I've found SQLite, because I simply didn't see the point of running a big server which is designed to handle much bigger data sets to store 250KB of text tables.

SQLite is extremely underrated IMHO.

There's a way around it which is actively worked on apparently:

https://www.sqlite.org/src/doc/begin-concurrent/doc/begin_co...

Why would stop-the-world affect performance when you are doing batch inserts?

Stopping the database is even a common trick when bulk inserting into "real" databases.

I think they are referring to a OLTP type scenario where you have lots of inserts and reads, but the reads only look at a couple of rows at a time, often only a single row. MVCC is good for such cases because you can avoid the overhead of taking lots of locks but still get fine grained concurrency control.

You are right that the Sqlite approach actually works quite well for bulk operations since you only require a single lock. However, it's usually still better for bulk inserts without updates to use fine grained locking or MVCC since you can often avoid acquiring any locks at all beyond the basic ones guarding fundamental DB data structures (these aren't locks as far as SQL is concerned since they cannot cause deadlock, it's a big pet peeve of mine when people think lock-free = no use of mutexes).

As a side note, don't do the following pattern: "BEGIN TRAN; INSERT INTO T ...; SELECT max(id) FROM T; COMMIT;". I used to do this, but this is a very bad habit that may be incorrect (assuming that id is an autoincrementing column). It's only correct on systems with true serializability, when you have opted into full serializability, and where such systems consider autoincrementing IDs to be part of serializability. When I tested this, Postgres and MSSQL handled this as expected while MySQL allowed the select to return a different row. I just tested Sqlite, and it does seem to work there regardless of WAL since it only allows concurrent readers plus a single writer. Use last_insert_rowid() or the equivalent for your database[1].

[1]: https://sqlite.org/lang_corefunc.html#last_insert_rowid

> As a side note, don't do the following pattern: "BEGIN TRAN; INSERT INTO T ...; SELECT max(id) FROM T; COMMIT;". I used to do this, but this is a very bad habit that may be incorrect (assuming that id is an autoincrementing column). It's only correct on systems with true serializability, when you have opted into full serializability, and where such systems consider autoincrementing IDs to be part of serializability.

And even when it work, it'll create a lot of unnecessary conflicts.

> Use last_insert_rowid() or the equivalent for your database[1].

RETURNING is the best approach for that in postgres (although lastval() also works). https://www.postgresql.org/docs/devel/sql-insert.html

According to several heavy sqlite users on HN, performance is improved if you do locking at the application layer. No idea if this is true.

See what you need to do is stand up a Redis cache cluster in front of your app, and then a cassandra cluster to cache the writes and compact them, before synchronizing the data with your single-page SQLite datastore as the master data.

Obviously for your SQL queries we crank up a large SparkSQL cluster.

- AWS architects

You can thank me later.

Do you get more thanks than just telling people to use MySQL than your 3 layer complicated solution?

I'm sure they were being sarcastic.

One trick of `pragma journal_mode = WAL` is that even with synchronous normal, SQLite syncs when you close the last connection [1]. So re-using a connection is key to getting the full benefits if you're executing commands from a single thread/process.

[1] https://sqlite.com/wal.html#the_wal_file

Out of curiosity, just how many rows were you trying to insert, for this to be a problem? My memory is a bit fuzzy, but on SQLite even standard INSERT statements can scale to hundreds of thousands per second, if you do them in one transaction. Just curious about the scenario here.

A need little trick is to explicitly state “begin transaction “ and “end transaction” in my app. Not sure how general use this is.

This is exactly how I accomplished high performance in sqlite too.

I'm surprised more people don't use transactions in sqlite given transactions are a staple of using any enterprise RDBMS.

Once you travel code boundaries (classes, functions, whatever) transaction management gets a bit hairy.

The question "prove this program reliably closes the transaction I started" starts to become equivalent to "prove this program halts"

Obviously they are useful tools and heavily used, but it's not like they are a zero-overhead feature.

This is quite a generalised point so difficult to really respond to but some frameworks do provide a mechanism to check if a transaction has been closed. The stateless nature of web development does also make working with transactions easier as you have a clear entry and exit point of each request. However I appreciate not all uses of RDBMSs are stateless, let alone web applications. But a similar approach can sometimes be applied with static stateful applications where you can calve up the transactional operations into logical code paths that are easier to mentally visualise. As always though, it really depends on how your code is structured and the specific problems you're trying to solve.

Where you know code boundaries are an issue I've found functional designs tend to work a little better than OOP with regards to managing transactions but a lot of that could just be down to how my brain is wired (while I'm not a FP evangelist I do tend to favour breaking code down to stateless functions rather than stateful classes).

It's fair to say spaghetti code will be a problem on most reasonably mature code bases but there are approaches and frameworks that help somewhat with managing transactions across code boundaries -- just as there are tools that make working with transactions harder. But in my experience there are much harder problems to solve than working with transactions.

> but it's not like they are a zero-overhead feature.

Is there such thing as a zero overhead feature? (I say this semi-flippantly).

I mean I have to deal with this crap at $dayjob but I genuinely can't believe of the terrible code I see that borrows a resource (connection, transaction, file handle) and then only the happy path gives it back. I desperately wish that languages would make this a compile error if all code paths don't lead to the resource being freed.

The only thing that should ever stop you from returning a resource is a malicious scheduler.

> I desperately wish that languages would make this a compile error if all code paths don't lead to the resource being freed.

You might want to take a look at rust.

I can see the obvious parallels between memory management lifetimes and transaction management, does rust have explicit features for extending lifetimes to resources besides memory?

Yes, it works for files, network connections and database transactions[1].

To span a transaction over two functions, it has to be assigned to some variable and the lifetime of this variable is tracked by the compiler.

[1] https://docs.rs/sqlx/0.3.0/sqlx/struct.Transaction.html

You don’t need to solve the halting program, you just need a way to construct programs that halt (or close the connection), which is way easier.

Many languages have some sort of `finally` or `with` construct tailored for this use case.

Remember, we’re code writers, not arbitrary discriminators.

I actually avoided transactions in MSSQL when I could because it escalates locks real quick. Which is a death knell for a busy system.

Eh? They exist, that doesn’t mean anyone actually uses them.

My point is that using transactions should be drilled into people who work on databases and where consistency is a requirement because transactions turn multiple complex SQL requests into one atomic operation:

http://db4beginners.com/blog/relationaldb-transaction/

Oh, I agree. It’s just that my recent experiences have included mostly people foreign to both transactions and foreign keys.

I've never worked at a place that doesn't use them. What if the 3rd insert in a series fails and consequently writes the wrong thing down on the 4th with an update?

That's just my experience so I guess it may be meaningless but I'm surprised to hear it may not be often used.

> I've never worked at a place that doesn't use them.

Consider yourself lucky then. I know of a place that doesn’t use transactions in a homegrown ERP solution, of all things.

I've seen all kinds of wonders during my life so this one is no surprise. If however someone is doing stupid things it is their problem. They're free to complain to themselves.

Tangentially - it may be their problem, but I hesitate to say it's their fault. I'm continually dismayed at how spotty and superficial education about how to use an RDBMS can be. Even in formal education on the subject.

It does not take PhD and rocket science for one to figure that sometimes operations must be bunched and executed with success/failure as a single unit. That would come as a business requirements. For curious person it would not take much to do some search on a subject and discover and read about transactions.

You hardly ever need transactions if you track validity explicitly in your schema.

Suppose T1 has a 1-many relationship with T2. Declare in your assumptions that any rows in T2 with no corresponding valid row in T1 are not valid.

Additionally, have an is_valid field on T1 so selecting all valid data from T2 is done with "select * from T2 inner join T1 on T2.t1id = T1.id where T1.is_valid".

To insert data, insert a row into T1 first but initially have is_valid be false. Then insert all necessary data into T2. Finally, do an update and change the original row in T1 to have is_valid be true.

For deletions to T1, just do an update and set is_valid to false. Thanks to the validity logic, this has the effect of also invalidating all T2 rows.

Updates are trickier, but you can allow them to work without transactions by having two ids for for every table. The first id is the one we worked with before which is used for joins. The second id is used by applications to look for explicit records. Therefore, just never do any updates aside from the one setting is_valid to true (which is really storage logic and not application logic). Instead, just insert a new row into T1 whenever you want to update something in T1. The final update now just needs to flip the is_valid bit for the old row and the new row and will also need to verify that the old row is valid as well as any other rows the current update relies on (basically need to turn it into complex CAS).

All of this is pretty messy, but it does let you have CRUD without any transaction support from your DB. Also, even if your DB has transactions, this scheme has the advantage of being lock-free so your application cannot deadlock.

If you have many updates/deletes, you can do garbage collection either by allowing the GC to use a transaction or by changing adding in a check for insertions to T1 that verify the number of associated rows in T2 before setting is_valid.

Unfortunately, while updates and inserts with GC can still be lock-free, they are not wait-free since an insert or update can fail. If you never do updates or GC though, this is actually wait-free and guarantees that every create, read, and delete operation will succeed in the absence of hardware/network failures.

Still, this overhead probably isn't worth it unless you already need to track the history explicitly for auditing or something. At the company where we used this, we didn't have an is_valid row, we had valid_from and valid_to which were timestamps.

Same as @mikeyjk! Can you point toward more information on this topic? The applications I'm dealing with in my day job are constantly plagued by deadlocks in the database surrounding bulk transactions. I've theorized that a "T1 1-many T2" refactoring of the tables - like what you've described - might be a solution , but haven't settled on how to implement. Would absolutely love to learn more on this esoteric, hard-to-google topic.

I guess I'm not sure how you can get away without needing transactionality for these extra columns / is_valid columns

All of this sounds like a lot of additional work just to provide some of the functionality transactions provide out of the box. And I say "some" because you've not addressed rollbacks.

That said, it's still an interesting thought experiment so glad you shared.

Wow, this has broken my brain but has really piqued my interest - thanks. Do you have any reading on this topic / strategy to help a mouth breather like me out?

My sentiments exactly!

Not using transaction is just very bad practice. If people are using wrong approach to solve the task they should not complain about results.

Another trick if you have multiple process (users) accessing the DB and you don't want to lock the DB for a long time, is to insert into a temp table (possibly with a transaction, although the time savings is not as dramatic with temp tables). Then copy the temp table to the main one (insert into ... from ...). The advantage is lets say you are reading in a bunch of items from something else, that will take a chunk of time more than just the DB time. So by going to a temp table you aren't locking the target table for anyone else while gathering the data. Then combine this with flushing the temp table every X rows or X seconds, and you have a number of efficient updates to the table without long lock times.

Also have WAL mode on to get multi-user access going.

This surprises me as I thought SQLite locks are db-level, not table-level. Is this not the case?

If you have wal-mode enabled then the automatic locks are table level. So one process can be updating one table and another one can work on a different one. Also, you only need to enable wal mode on the DB once (pragma journal_mode=wal), it "sticks" for each connection.

In my application that uses SQLite (Snebu backup), as data comes in (as a TAR format stream) I have one process extracting the data and metadata, then serializing the metadata to another process that owns the DB connection. This process dumps the metadata to a temp table, then every 10 seconds "flushes" the metadata to the various tables that it needs to go to. This way I can easily have multiple backups going simultaneously, as each process spends a small amount of time (relatively) flushing the data to the permanent tables, and a greater part of the time compressing and writing backup data to the disk vault directory.

I've been working with this for the past 8 years or so, and have picked up a few tricks on keeping as much as possible batched up in transactions, but also keeping the transaction times short relative to other operations. So far seems to work out fairly well.

Note, that in addition to journal_mode=wal, you need to have a busy handler defined that infinitely retries transactions with a 250 ms delay between each retry.

Edit: On further review of the docs, I'm not sure if wal mode enables table-level locking, it may be that when writing to a temp table, that temp tables are part of a separate schema (or are otherwise separate from the main DB) -- which makes sense, as temp tables are only visible to the process that owns them. So a temp table can be locked in a transaction, while the rest of the DB is writable.

It seems like the locking is page-level in "wal" and "wal2" modes:

> Usually, SQLite allows at most one writer to proceed concurrently. The BEGIN CONCURRENT enhancement allows multiple writers to process write transactions simultanously if the database is in "wal" or "wal2" mode, although the system still serializes COMMIT commands.

> When a write-transaction is opened with "BEGIN CONCURRENT", actually locking the database is deferred until a COMMIT is executed. This means that any number of transactions started with BEGIN CONCURRENT may proceed concurrently. The system uses optimistic page-level-locking to prevent conflicting concurrent transactions from being committed.

> When a BEGIN CONCURRENT transaction is committed, the system checks whether or not any of the database pages that the transaction has read have been modified since the BEGIN CONCURRENT was opened. In other words - it asks if the transaction being committed operates on a different set of data than all other concurrently executing transactions. If the answer is "yes, this transaction did not read or modify any data modified by any concurrent transaction", then the transaction is committed as normal. Otherwise, if the transaction does conflict, it cannot be committed and an SQLITE_BUSY_SNAPSHOT error is returned. At this point, all the client can do is ROLLBACK the transaction.

The page also mentions:

> The key to maximizing concurrency using BEGIN CONCURRENT is to ensure that there are a large number of non-conflicting transactions. In SQLite, each table and each index is stored as a separate b-tree, each of which is distributed over a discrete set of database pages. This means that:

> Two transactions that write to different sets of tables never conflict

Source: https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begi...

+1. I was not aware of the 'stickiness' of the wal pragma. Thank you for the tip. I will be defaulting to this going forward. For a quick confirmation, I used:

  $ f=$(mktemp)
  $ sqlite3 $f "pragma journal_mode"
  delete
  $ sqlite3 $f "pragma journal_mode=wal
  wal
  $ sqlite3 $f "pragma journal_mode"
  wal

Edit: struggling to format code markup correctly on my mobile.

+1, thanks for this and your previous comment. Have you written up your approach in any more detail? I'd love to reference it.

The code is on github.com/derekp7/snebu if you want to take a look. The next task on my list is to write up developer / contributor documentation on how the whole thing is put together along with various tricks (and simplified demonstration code).

But specifically, look in "snebu-main.c" that is where the opendb function is (so you can see the pragma statements), and there is a busy_retry function that gets referenced (all it does is sleep for .1 seconds). I believe that you don't need the busy-retry function, if you use the built-in busy handler, but I'm not really sure and don't want to take a chance and break working code.

For the temp tables, look in snebu-submitfiles.c -- the function at the top handles the DB operations, one towards the bottom handles the non-DB tar file consumption operations, and there is a circular queue in the middle to handle buffering so the data ingestion can keep going while the data is getting flushed (these three run as separate processes). I should learn threads, as there may be more flexibility in that, but not comfortable enough with thread programming yet.

Thanks so much! The phrase "unreasonably helpful" comes to mind. Also, kudos for the extraordinarily high-quality documentation in snebu!

> A need little trick is to explicitly state “begin transaction “ and “end transaction” in my app.

That is exactly what the parent comment already said:

> if you do them in one transaction

The commands you stated are exactly how to do (multiple) things in a transaction.

Indeed, without this, performance would seem quite lacklustre. I believe it's very common in the SQLite community.

Why would I do a hundred thousand insertions in a single transaction in a crud app?

I think parent means that for some careful selection of N (where N > 1) insertions per bulk transaction you can scale up to hundreds of thousands of insertions per second, rather than putting hundreds of thousands of insertions in a single transaction.

No, the opposite. In SQLite, starting and ending transactions that write things to the db is a relatively expensive operation, and running queries outside transaction is effectively the same as running each of them in an independent transaction.

If you need to do a lot of inserts (or updates, etc), the slowest possible way to do them is to do them outside of a transaction. The fastest way to do them is to wrap them all into a single transaction.

> If you need to do a lot of inserts (or updates, etc), the slowest possible way to do them is to do them outside of a transaction. The fastest way to do them is to wrap them all into a single transaction.

This doesn't seem to contradict the comment you're replying to. They're suggesting wrapping operations into transactions in batches e.g. (just making some numbers up) if you have 100,000 inserts maybe you'd do 100 transactions of 1000 inserts each. I wouldn't call that "the opposite" of your one mega-transaction suggestion. In fact I'd expect it to still have most or all of the speed benefit of using one single transaction, or potentially even be slightly faster.

Oh fascinating, you actually put hundreds of thousands of statements in a single SQLite transaction in an online CRUD app (as opposed to offline processing)? I've never done more than a couple hundred and even then usually they're "logically batched," both because I'm worried about forcing unnecessary read to write transaction promotions for concurrent reads and thereby increasing busy errors, but also because that affects durability to have a transaction open that long (it's not great to let your HTTP response hang for a second before responding as you keep your transaction open).

For serialized writers in any system I'm sure keeping a transaction open as long as possible is the ideal case for throughput, but there's other problems with that in a CRUD app no?

Well, that was my original question to the the parent poster, too. You can achieve 100k+ /sec, but they went looking for better performance, apparently.

But to answer your question at face value, one common use for SQLite is as a file format for complex desktop apps, or perhaps a game save format for certain types of games (mostly the non-realtime types). One great advantage to this is that if you use a DB migration library, you ensure backwards compatibility with previous versions of your saved files. However, it's easy to imagine a fair bit of data getting inserted into such a new file each time it is created. It might only be 5000 rows, but I'd prefer to not have to wait 5 seconds just to persist it, if a single change can make it 0.05 seconds.

db seeding with dummy data, migration, analytics db sync etc.

These are all one time/one a year operations.

You still don't want to wait 5 hours to let your inefficient query complete.

because bulk kinda implies that you want all-or-nothing :)

I've recently had my Python script generate about 100GB of data and inserting them into SQLite was _far_ from being the bottleneck.

I suppose OP was committing too often or even had autocommit on.

Then it wouldn't actually haven been a case of "bulk insert" at all.

How many rows? What is the "fast" you expect?

My main app sync data across ERPs and their main case is batch loading of data. This mean that I need to nearly mirror a SQL Server/Oracle/Cobol/Firebase/Etc database into sqlite, clean it, then upload to postgresql.

I have more troubles fast loading into PG than sqlite (not saying I don't have them in the past!) and sqlite is very very fast to me.

I stumbled upon a similar issue last week.

I was building a small Rails project that has mostly reads, but here and there it has inserts that can have 1000+ entries with related models. Things seem fine when clicking around, but when I used jMeter to test what is the capability of the server, I found it terrible with simultaneous requests.

Adding two workers made things even worse, and this is with ~10% writes and the remaining going to reads.

I quickly changed the db to postgres for comparison purposes, and from 3-4 requests per second with sqlite, it jumped to 60-70 as it did scale linearly with the number of workers.

I am by no means expert in database optimisations, but out of the box this behaviour was somewhat limiting the usability of sqlite for a webapp.

Postgres is pretty bad at bulk insert. You need to use COPY INTO and create an in memory CSV file or mess around with unnest() to get decent performance.

`copy to` yes. That's what bulk insert is. How can having the feature make postgres bad at it? "in memory csv" no. You don't need to create an in memory csv file in order to load in bulk. Your language binding should provide a way to stream data into a `copy to` process I can't imagine how unnest is related to bulk loading. It's a query feature to unnest arrays.

The relationship between bulk loading and unnest is as follows.

A single insert of an array using an unnest can insert many rows. The performance is worse than copy to, but in the same ballpark. But there are use cases where you'd like to bulk load through a stored procedure for a variety of reasons, and now calling the procedure with an array and using unnest internally is a straight win.

Thanks for sharing! Did you have performance benefits using PostgreSQL out of the box, or did you have to tune specific settings? How much effort did you have to spend to do this migration?

Did you use transactions? I noticed a significant performance increase after wrapping blocks of queries with BEGIN TRANSACTION and COMMIT TRANSACTION.