11 ms·

Counterpoint: I over-used SQLite because it was the first database I encountered and spent waaaay longer working around its shortcomings than I eventually spent

Counterpoint: I over-used SQLite because it was the first database I encountered and spent waaaay longer working around its shortcomings than I eventually spent porting to postgres. Long version: I couldn't get bulk insert performance above absolutely miserable levels. I tried tricks like deleting and recreating indices but without luck. The perf tooling wasn't there to quickly figure out where the problem was (this was 10 years ago, not sure if things have improved) so I wound up building a version of SQLite with debug symbols and profiling it with a C profiler. The problem turned out to be a default setting that made spill-to-disk very aggressive and basically guaranteed that any workflow like mine would grind along with miserable slowness and no outward indication of what to do about it. I found an email thread where someone in effectively the same situation made some constructive suggestions and got turned away on the principle that even casual users ought to just know performance knobs like this one. Yikes. I am probably munging some of the details, but it made me angry enough to learn postgres and port my code over despite having a fix for my immediate problem.

danielbarla 6y ago
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.
- prox 6y ago
  A need little trick is to explicitly state “begin transaction “ and “end transaction” in my app. Not sure how general use this is.
  - danielbarla 6y ago
    Indeed, without this, performance would seem quite lacklustre. I believe it's very common in the SQLite community.
  - hnlmorg 6y ago
    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.
    - Aeolun 6y ago
      Eh? They exist, that doesn’t mean anyone actually uses them.
      - hnlmorg 6y ago
        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/ http://db4beginners.com/blog/relationaldb-transaction/
        Aeolun 6y ago
        Oh, I agree. It’s just that my recent experiences have included mostly people foreign to both transactions and foreign keys.
      - mikeyjk 6y ago
        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.
        konha 6y ago
        > 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.
        6y ago
  - derekp7 6y ago
    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.
    - uh_uh 6y ago
      This surprises me as I thought SQLite locks are db-level, not table-level. Is this not the case?
      - derekp7 6y ago
        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.
  - quietbritishjim 6y ago
    > 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.
- fctorial 6y ago
  Why would I do a hundred thousand insertions in a single transaction in a crud app?
  - cztomsik 6y ago
    because bulk kinda implies that you want all-or-nothing :)
  - dwohnitmok 6y ago
    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.
    - Tuna-Fish 6y ago
      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.
      - dwohnitmok 6y ago
        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?
lrem 6y ago
I've recently had my Python script generate about 100GB of data and inserting them into SQLite was _far_ from being the bottleneck.
- bhaak 6y ago
  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.
hiq 6y ago
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?
quietbritishjim 6y ago
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 https://www.sqlite.org/pragma.html#pragma_synchronous [2] https://sqlite.org/wal.html https://sqlite.org/wal.html
- pornel 6y ago
  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.
  - quietbritishjim 6y ago
    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.
mamcx 6y ago
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.
imtringued 6y ago
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.
- mvc 6y ago
  `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.
  - btilly 6y ago
    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.
wedesoft 6y ago
Did you use transactions? I noticed a significant performance increase after wrapping blocks of queries with BEGIN TRANSACTION and COMMIT TRANSACTION.
elAhmo 6y ago
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.

danielbarla 6y ago

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.

prox 6y ago
A need little trick is to explicitly state “begin transaction “ and “end transaction” in my app. Not sure how general use this is.
- danielbarla 6y ago
  Indeed, without this, performance would seem quite lacklustre. I believe it's very common in the SQLite community.
- hnlmorg 6y ago
  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.
  - Aeolun 6y ago
    Eh? They exist, that doesn’t mean anyone actually uses them.
    - hnlmorg 6y ago
      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/ http://db4beginners.com/blog/relationaldb-transaction/
      - Aeolun 6y ago
        Oh, I agree. It’s just that my recent experiences have included mostly people foreign to both transactions and foreign keys.
    - mikeyjk 6y ago
      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.
      - konha 6y ago
        > 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.
        6y ago
- derekp7 6y ago
  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.
  - uh_uh 6y ago
    This surprises me as I thought SQLite locks are db-level, not table-level. Is this not the case?
    - derekp7 6y ago
      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.
- quietbritishjim 6y ago
  > 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.
fctorial 6y ago
Why would I do a hundred thousand insertions in a single transaction in a crud app?
- cztomsik 6y ago
  because bulk kinda implies that you want all-or-nothing :)
- dwohnitmok 6y ago
  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.
  - Tuna-Fish 6y ago
    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.
    - dwohnitmok 6y ago
      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?

lrem 6y ago

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

bhaak 6y ago
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.

hiq 6y ago

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?

quietbritishjim 6y ago

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 https://www.sqlite.org/pragma.html#pragma_synchronous [2] https://sqlite.org/wal.html https://sqlite.org/wal.html

pornel 6y ago
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.
- quietbritishjim 6y ago
  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.

mamcx 6y ago

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.

imtringued 6y ago

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.

mvc 6y ago
`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.
- btilly 6y ago
  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.

wedesoft 6y ago

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

elAhmo 6y ago

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.