11 ms·

If you haven't tried SQLite, please do. For years I ignored SQLite and used MySQL (it does the job) but once you see how fast SQLite is, and advantages of havin

If you haven't tried SQLite, please do. For years I ignored SQLite and used MySQL (it does the job) but once you see how fast SQLite is, and advantages of having a DB contained in a single file... just go play around with SQLite instead of ignoring it for years like me. It's neat.

alexchamberlain 6y ago
The main challenge there is how to you ensure your database is resilient to machine or datacentre outages? ie what happens if the 1 server with the database is in a datacentre that loses Internet connectivity?
- zdkl 6y ago
  SQlite "merely" assumes that the problems that come with distributed systems are handled at the application layer. You'll have to solve those problems for yourself, sure, but in practice I have rarely (I think never actually) had dataloss through a fault of sqlite. Also, did you know you can use in-memory instances (and share them across threads!) with the right incantation? And that you can backup your on-disk instance to an in-memory one, do your expensive transactions without hitting the disk then backup the modified instance right back to disk, even in-place if you want! Sqlite is amazing when you don't expect the DB to do replication or failover on its own.
  - krab 6y ago
    There is always DRBD as well. You can make replication a lower-layer problem. Not that it's without drawbacks.
  - iveqy 6y ago
    No I did not know that, I've looked for a long time for a way to convert a sqlite3 database to an in memory database and then back again. Do you mean that there's support in sqlite3 for this? Could you point me in the right direction?
    - Multicomp 6y ago
      We both learned something new today. Looks like this is what you want in combination with using an in memory database. I've been doing a handrolled in memory cache layer to speed data access, but with this, I can just call the db directly and then periodically sync to disk, redis rdb style. Sqlite is a staggeringly good piece of technology! https://www.sqlite.org/backup.html https://www.sqlite.org/backup.html
    - beagle3 6y ago
      In Python you just have to open the special file name “:memory:” to get a memory-based db. I don’t remember what the raw SQLite incantation is (or if it’s different). Also, pay attention to “ATTACH” - it’s the way to use multiple databases (file and/or memory) while still letting SQLite handle it all (e.g. join a memory db to a file db, insert result into 3rd file db - all without having to look at records in your own code)
      - folmar 6y ago
        It's the same in plain sqlite.
  - cm2187 6y ago
    In other words it is just a regular object you serialize from time to time...
- gravypod 6y ago
  Some say replication is an application layer concern, not a serialization concern. I don't agree or disagree but it's something I've heard. I've seen sqlite used as a cross language data frame solution. Store it in s3 and it's resilient if you are read only.
- CGamesPlay 6y ago
  I feel like the sibling comments here are basically just saying "yep, that's the main challenge!" without providing useful tips. I personally haven't used it, but I'm aware that this library exists to help resolve this challenge. https://litestream.io https://litestream.io
  - rapnie 6y ago
    And as it happens this featured on HN just 4 days ago: https://news.ycombinator.com/item?id=26103776 https://news.ycombinator.com/item?id=26103776
- thatwasunusual 6y ago
  https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite
  - tptacek 6y ago
    rqlite looks neat. I'd be interested in hearing any major success stories about it.
- mantap 6y ago
  Just open your SQLite database in read-only mode :) SQLite works really well for static or semi-static data. For example, a blog where you have a small number of users writing and many users reading from the DB. If the authors are content to use one server to edit the DB then you can easily push that DB to the servers handling the reads.
  - johannes1234321 6y ago
    Yes this can work, however you are mostly relying on the operating system's file system cache for speed. Other databases will try harder to keep their own cache. But true, there is lots of room where SQLite works nicely.
- lrem 6y ago
  TBF: you don't. The moment you care about any shortcoming of SQLite, move away. One of the cool things about it, is that SQLite is very lax about what it accepts (mostly in the datatype area). You can write your SQL statements targeting whatever database you think you'll move to later and they'll work while you're still on SQLite. I believe having this migration work seamlessly towards PostreSQL is one of the advertised features.
- pjc50 6y ago
  I would say that's a case for using a proper replicated RDBMS if you need that level of replication. Sqlite is not a hammer for all occasions.
millstone 6y ago
Good observations from a MySQL perspective. Any thoughts from the other end, where the alternatives are JSON or XML or ZIP? SQLite tries hard to convince you to use it as an application file format, but it looks like a giant black box of overkill: why incorporate its 200k SLOC when the alternatives are a fraction of the size?
- Scarbutt 6y ago
  How do you create structure data with ZIP? why incorporate its 200k SLOC when the alternatives are a fraction of the size? Performance, ACID and a superior declarative query language.
  - millstone 6y ago
    ZIP files are not a database: they are more like a directory hierarchy. But maybe all I need is named blobs: no query language parser, optimizer, indexing, etc. SQLite positions itself as an improvement over ZIP for application file formats: https://www.sqlite.org/appfileformat.html https://www.sqlite.org/appfileformat.html . But minzip is so much smaller, easier to understand, debug and ship. So why use SQLite for an app if ZIP suffices?
    - setr 6y ago
      If you’re talking about like cbr archives, you’re right. It’s comparing against usages like word/excel, which store a bunch of XML in an archive and call it a day. If you’re not reading and writing out application state, then yes, you don’t need something to manage your non-existent state
- realdense 6y ago
  Depends on your use case. XML and JSON are great for applications with simple data stores, having done this myself. But if you foresee a need for complex queries or locking and threads then SQLite might be a good choice.
- ak217 6y ago
  JSON/XML quickly stop being alternatives as soon as you need any sort of index, a memory-mapped/on-disk data structure that doesn't have to be loaded into memory, transactional or even just incremental writes. ZIP is not even directly comparable.
hans_castorp 6y ago
I will re-consider it, once they have proper data types and data type checking.
- jraph 6y ago
  This aspect will probably never change: > Flexible typing is considered a feature of SQLite, not a bug. Nevertheless, we recognize that this feature does sometimes cause confusion and pain for developers who are acustomed to working with other databases that are more judgmental with regard to data types. In retrospect, perhaps it would have been better if SQLite had merely implemented an ANY datatype so that developers could explicitly state when they wanted to use flexible typing, rather than making flexible typing the default. But that is not something that can be changed now without breaking the millions of applications and trillions of database files that already use SQLite's flexible typing feature. https://sqlite.org/quirks.html#flexible_typing https://sqlite.org/quirks.html#flexible_typing
  - rini17 6y ago
    It is possible to create check constraints that do the type checking. Actually, if they shipped some predefined ones and said "if you want strong type checking do this", like, syntax to automatically populate the table columns with type check constraints, it would be IMO perfectly backward compatible.
  - darksaints 6y ago
    At the very least we could get strict versions of data types, or some sort of key word used in the ddl to specify strict typing.
    - attilakun 6y ago
      This might help: https://dba.stackexchange.com/a/222271 https://dba.stackexchange.com/a/222271
- RedShift1 6y ago
  When I started a project with SQLite, the available data types struck me as odd (coming from MySQL), but as I learned I started asking the question: are there any more fundamental data types other than null, int, real, text and blob? For example dates are just a facade for an integer of some kind, JSON is really just text adhering to certain formatting rules, booleans are usually stored as some kind of byte anyway so why not drop that abstraction? With this limited set of datatypes it really makes you think harder about the data you are processing, because in the end all your data is one of these types anyway.
jjoonathan 6y ago
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.
hnaccy 6y ago
I reach for SQLite if I need persisted state for a local application or custom file format but why use it for things that may need more write concurrency like web server? Postgres is basically just as easy to use and backup.
- Uberphallus 6y ago
  SQLite loses most of its edge in concurrent write scenarios, but its read performance is difficult to beat. A lot of it comes from what TFA says: there's no network roundtrip, but a function call. Even in a local machine, a unix socket query will carry at least a couple of system calls with potential context switches, and that makes regular RDBMS lag behind when you do tons of sequential and small queries. Of course, when you have large results or complex queries that eat a bigger chunk of the time cake and that technical advantage wanes. After that, which RDBMS has the performance lead is largely workload-dependent.
- jraph 6y ago
  > Postgres is basically just as easy to use and backup. SQLite is way ahead on this: no daemon to run, no user / database to create, manage and administrate, no authentication to set, no socket connection to manage… backup is as easy as it gets: (copy one or two files). Postgres is still largely manageable of course.
  - johannes1234321 6y ago
    Well, backing up by copying doesn't neccissarily result in a consistent state if there are writes to the database. For that you have to use the SQLite `.backup` command (or using the backup API https://sqlite.org/c3ref/backup_finish.html https://sqlite.org/c3ref/backup_finish.html) after which the backup database has to be copied over to backup storage (or backup storage has to be mounted to the production system, which is dangerous)
    - beagle3 6y ago
      Use "rsync" instead of "cp". After it finishes copying, it will check to see if the file has changed since it started copying, and will restart the copy if so (with a limited number of retries). If copying the entire database is faster than your average update right, this will converge very quickly and will deliver a consistent copy. For many small applications, this is perfectly fine. It's not much harder to just "sqlite3 $file ".backup $backupfile"' (that's literally all it takes, and what you should do) and guarantee consistency. But it's nice to know that a simple "rsync" is sufficient for slowly-updating uses - e.g. And as for the other side of backup, you know -- restore -- sqlite shines brighter than everything else. You can just take a good copy and put it back. You can examine the file everywhere, on a read only system, etc - without configuring anything if needed.
phillc73 6y ago
If you like SQLite, then DuckDB[1] is probably worth looking at. Very similar in many ways, but DuckDB is a column-oriented rather than row, so does have some performance advantages. It is quite new, so I might not go all in for mission critical production yet, but it is worth exploring for analytics work. [1] https://duckdb.org/ https://duckdb.org/
- iagovar 6y ago
  DuckDB is amazing.
- sriku 6y ago
  Wow! Seems to be around for a while as well. Regret not finding it earlier.
- vslira 6y ago
  Question for ppl using DuckDB: are the use cases similar to what you'd use Apache Arrow, but with the benefit of working in SQL, or are they meaningfully different? I'm not currently using any of those, mind you, still on a pandas/dask* dataframe basis, but I'm trying to wrap my head around where the ecosystem is moving *I know Dask is already using Arrow behind the scenes
  - phillc73 6y ago
    I don't use Apache Arrow, so I'm in a poor position to compare it with DuckDB. My use case for DuckDB is effectively querying R dataframes with SQL. DuckDB has the functionality to register virtual tables, with data from existing dataframes. As I know SQL reasonably well, using DuckDB to query dataframes means I don't need to learn a bunch of new dplyr verbs or data.table constructs.There are some other R packages which also support this use case - sqldf and tidyquery are two I am aware of. Both of these follow a different approach, where they parse the SQL query. Using a DuckDB virtual table lets the database handle all of the SQL. I've found so far that through using DuckDB, performance is much better than sqldf and tidyquery, nowhere near as quick as data.table and can be quicker than dplyr, depending on query complexity. I haven't really looked at anything approaching big data sizes though.
Cthulhu_ 6y ago
I'm using SQLite at the moment; on the one side there's a 'legacy' (read: poorly written 2012) application, on the other there's the new and rebuilt version. The old one was not built very well, it does not use foreign keys or any kind of database constraints (it references other entries by name in a column of comma-separated values) and it runs like trash. But the performance problem is not in the dozen queries it runs to load the data, it's in the fact that it converts the query result to XML (via string concatenation, because of course) and that is converted to JSON; the conversion is at least 60% of each request. The other problem is that it writes and re-queries the data whenever you leave one of the hundreds of form fields in the application. I'm rebuilding the application in a modern tech stack, still using SQLite but properly this time, along with Go and React. API requests take 20-40ms instead of 300-1500ms, and there's much less of them. The main downside to using SQLite is that it does not support "proper" database migrations; you cannot alter a column. You can add columns to an existing table, but you can't change existing columns. The database abstraction I'm using at the moment, Gorm (a different subject entirely) work around this by moving stuff to a temp table, recreating the table with the updated columns and moving stuff back, I believe. Anyway TL;DR sqlite is not the bottleneck.
- srcreigh 6y ago
  SQLite did get support for renaming columns recently [1]. Anyways, the process you describe is also used in MySQL for doing online schema migrations. [2] "proper" database migrations cause downtime [1]: https://stackoverflow.com/questions/805363/how-do-i-rename-a-column-in-a-sqlite-database-table https://stackoverflow.com/questions/805363/how-do-i-rename-a... [2]: https://github.com/github/gh-ost https://github.com/github/gh-ost
NelsonMinar 6y ago
It's terrific right until you need multiple processes writing to the same database. It's no accident that SQLite is fast for many small queries; it's not doing a lot of the work required to, say, be a good database backend for multiple web frontends.
- deleted 6y ago
  [deleted]
- danenania 6y ago
  Yeah, I don’t really see sqlite as competing with postgres or mysql for this reason. It’s an alternative to the file system.
lisper 6y ago
I tried to switch from MySQL to SQLite for my Postfix/Dovecot installation but I ran into a very annoying problem: every now and then I lose an email because the "database is locked". This is a show-stopper for me because the problem happens very rarely, only once every couple of days, but it's catastrophic: when this happens, the incoming message is not bounced, it is actually lost. The only reason I even realized it was happening is because I noticed there were emails in the root account, which is the error-reporting mechanism of last resort. I've searched the web in vain for a solution. If you have any suggestions, I would love to be able to stick with sqlite, but at the moment I am about to begin migrating back to MySQL. :-(
- kevincox 6y ago
  This sounds like a problem with Postfix or Dovecot. Postfix shouldn't ack or Dovecot shouldn't delete the email until it is safely stored. That being said there are many use cases where having high availability is critical and in the face of multiple writers SQLite isn't the best option for that.
  - lisper 6y ago
    I don't think this is a multiple-writer problem. Postfix is only reading. I am running a milter that is writing, but I control the code for that so I have it set up to retry if it fails. So the error is being generated by postfix itself, and so it must be happening on a read (because that is all postfix does). I was hoping to find some kind of global switch that would make sqlite always wait for locks rather than throwing an error. But I've scoured the web for such a solution without success :-(
    - samatman 6y ago
      There is no such thing, to my knowledge. What can be done is registering a busy callback: https://www.sqlite.org/c3ref/busy_handler.html https://www.sqlite.org/c3ref/busy_handler.html I don't understand enough about your specific problem to know if this will actually help you, just sharing a tidbit I encountered working on a comparable issue.

alexchamberlain 6y ago

The main challenge there is how to you ensure your database is resilient to machine or datacentre outages? ie what happens if the 1 server with the database is in a datacentre that loses Internet connectivity?

zdkl 6y ago
SQlite "merely" assumes that the problems that come with distributed systems are handled at the application layer. You'll have to solve those problems for yourself, sure, but in practice I have rarely (I think never actually) had dataloss through a fault of sqlite. Also, did you know you can use in-memory instances (and share them across threads!) with the right incantation? And that you can backup your on-disk instance to an in-memory one, do your expensive transactions without hitting the disk then backup the modified instance right back to disk, even in-place if you want! Sqlite is amazing when you don't expect the DB to do replication or failover on its own.
- krab 6y ago
  There is always DRBD as well. You can make replication a lower-layer problem. Not that it's without drawbacks.
- iveqy 6y ago
  No I did not know that, I've looked for a long time for a way to convert a sqlite3 database to an in memory database and then back again. Do you mean that there's support in sqlite3 for this? Could you point me in the right direction?
  - Multicomp 6y ago
    We both learned something new today. Looks like this is what you want in combination with using an in memory database. I've been doing a handrolled in memory cache layer to speed data access, but with this, I can just call the db directly and then periodically sync to disk, redis rdb style. Sqlite is a staggeringly good piece of technology! https://www.sqlite.org/backup.html https://www.sqlite.org/backup.html
  - beagle3 6y ago
    In Python you just have to open the special file name “:memory:” to get a memory-based db. I don’t remember what the raw SQLite incantation is (or if it’s different). Also, pay attention to “ATTACH” - it’s the way to use multiple databases (file and/or memory) while still letting SQLite handle it all (e.g. join a memory db to a file db, insert result into 3rd file db - all without having to look at records in your own code)
    - folmar 6y ago
      It's the same in plain sqlite.
- cm2187 6y ago
  In other words it is just a regular object you serialize from time to time...
gravypod 6y ago
Some say replication is an application layer concern, not a serialization concern. I don't agree or disagree but it's something I've heard. I've seen sqlite used as a cross language data frame solution. Store it in s3 and it's resilient if you are read only.
CGamesPlay 6y ago
I feel like the sibling comments here are basically just saying "yep, that's the main challenge!" without providing useful tips. I personally haven't used it, but I'm aware that this library exists to help resolve this challenge. https://litestream.io https://litestream.io
- rapnie 6y ago
  And as it happens this featured on HN just 4 days ago: https://news.ycombinator.com/item?id=26103776 https://news.ycombinator.com/item?id=26103776
thatwasunusual 6y ago
https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite
- tptacek 6y ago
  rqlite looks neat. I'd be interested in hearing any major success stories about it.
mantap 6y ago
Just open your SQLite database in read-only mode :) SQLite works really well for static or semi-static data. For example, a blog where you have a small number of users writing and many users reading from the DB. If the authors are content to use one server to edit the DB then you can easily push that DB to the servers handling the reads.
- johannes1234321 6y ago
  Yes this can work, however you are mostly relying on the operating system's file system cache for speed. Other databases will try harder to keep their own cache. But true, there is lots of room where SQLite works nicely.
lrem 6y ago
TBF: you don't. The moment you care about any shortcoming of SQLite, move away. One of the cool things about it, is that SQLite is very lax about what it accepts (mostly in the datatype area). You can write your SQL statements targeting whatever database you think you'll move to later and they'll work while you're still on SQLite. I believe having this migration work seamlessly towards PostreSQL is one of the advertised features.
pjc50 6y ago
I would say that's a case for using a proper replicated RDBMS if you need that level of replication. Sqlite is not a hammer for all occasions.

millstone 6y ago

Good observations from a MySQL perspective. Any thoughts from the other end, where the alternatives are JSON or XML or ZIP? SQLite tries hard to convince you to use it as an application file format, but it looks like a giant black box of overkill: why incorporate its 200k SLOC when the alternatives are a fraction of the size?

Scarbutt 6y ago
How do you create structure data with ZIP? why incorporate its 200k SLOC when the alternatives are a fraction of the size? Performance, ACID and a superior declarative query language.
- millstone 6y ago
  ZIP files are not a database: they are more like a directory hierarchy. But maybe all I need is named blobs: no query language parser, optimizer, indexing, etc. SQLite positions itself as an improvement over ZIP for application file formats: https://www.sqlite.org/appfileformat.html https://www.sqlite.org/appfileformat.html . But minzip is so much smaller, easier to understand, debug and ship. So why use SQLite for an app if ZIP suffices?
  - setr 6y ago
    If you’re talking about like cbr archives, you’re right. It’s comparing against usages like word/excel, which store a bunch of XML in an archive and call it a day. If you’re not reading and writing out application state, then yes, you don’t need something to manage your non-existent state
realdense 6y ago
Depends on your use case. XML and JSON are great for applications with simple data stores, having done this myself. But if you foresee a need for complex queries or locking and threads then SQLite might be a good choice.
ak217 6y ago
JSON/XML quickly stop being alternatives as soon as you need any sort of index, a memory-mapped/on-disk data structure that doesn't have to be loaded into memory, transactional or even just incremental writes. ZIP is not even directly comparable.

hans_castorp 6y ago

I will re-consider it, once they have proper data types and data type checking.

jraph 6y ago
This aspect will probably never change: > Flexible typing is considered a feature of SQLite, not a bug. Nevertheless, we recognize that this feature does sometimes cause confusion and pain for developers who are acustomed to working with other databases that are more judgmental with regard to data types. In retrospect, perhaps it would have been better if SQLite had merely implemented an ANY datatype so that developers could explicitly state when they wanted to use flexible typing, rather than making flexible typing the default. But that is not something that can be changed now without breaking the millions of applications and trillions of database files that already use SQLite's flexible typing feature. https://sqlite.org/quirks.html#flexible_typing https://sqlite.org/quirks.html#flexible_typing
- rini17 6y ago
  It is possible to create check constraints that do the type checking. Actually, if they shipped some predefined ones and said "if you want strong type checking do this", like, syntax to automatically populate the table columns with type check constraints, it would be IMO perfectly backward compatible.
- darksaints 6y ago
  At the very least we could get strict versions of data types, or some sort of key word used in the ddl to specify strict typing.
  - attilakun 6y ago
    This might help: https://dba.stackexchange.com/a/222271 https://dba.stackexchange.com/a/222271
RedShift1 6y ago
When I started a project with SQLite, the available data types struck me as odd (coming from MySQL), but as I learned I started asking the question: are there any more fundamental data types other than null, int, real, text and blob? For example dates are just a facade for an integer of some kind, JSON is really just text adhering to certain formatting rules, booleans are usually stored as some kind of byte anyway so why not drop that abstraction? With this limited set of datatypes it really makes you think harder about the data you are processing, because in the end all your data is one of these types anyway.

jjoonathan 6y ago

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.

hnaccy 6y ago

I reach for SQLite if I need persisted state for a local application or custom file format but why use it for things that may need more write concurrency like web server? Postgres is basically just as easy to use and backup.

Uberphallus 6y ago
SQLite loses most of its edge in concurrent write scenarios, but its read performance is difficult to beat. A lot of it comes from what TFA says: there's no network roundtrip, but a function call. Even in a local machine, a unix socket query will carry at least a couple of system calls with potential context switches, and that makes regular RDBMS lag behind when you do tons of sequential and small queries. Of course, when you have large results or complex queries that eat a bigger chunk of the time cake and that technical advantage wanes. After that, which RDBMS has the performance lead is largely workload-dependent.
jraph 6y ago
> Postgres is basically just as easy to use and backup. SQLite is way ahead on this: no daemon to run, no user / database to create, manage and administrate, no authentication to set, no socket connection to manage… backup is as easy as it gets: (copy one or two files). Postgres is still largely manageable of course.
- johannes1234321 6y ago
  Well, backing up by copying doesn't neccissarily result in a consistent state if there are writes to the database. For that you have to use the SQLite `.backup` command (or using the backup API https://sqlite.org/c3ref/backup_finish.html https://sqlite.org/c3ref/backup_finish.html) after which the backup database has to be copied over to backup storage (or backup storage has to be mounted to the production system, which is dangerous)
  - beagle3 6y ago
    Use "rsync" instead of "cp". After it finishes copying, it will check to see if the file has changed since it started copying, and will restart the copy if so (with a limited number of retries). If copying the entire database is faster than your average update right, this will converge very quickly and will deliver a consistent copy. For many small applications, this is perfectly fine. It's not much harder to just "sqlite3 $file ".backup $backupfile"' (that's literally all it takes, and what you should do) and guarantee consistency. But it's nice to know that a simple "rsync" is sufficient for slowly-updating uses - e.g. And as for the other side of backup, you know -- restore -- sqlite shines brighter than everything else. You can just take a good copy and put it back. You can examine the file everywhere, on a read only system, etc - without configuring anything if needed.

phillc73 6y ago

If you like SQLite, then DuckDB[1] is probably worth looking at. Very similar in many ways, but DuckDB is a column-oriented rather than row, so does have some performance advantages. It is quite new, so I might not go all in for mission critical production yet, but it is worth exploring for analytics work. [1] https://duckdb.org/ https://duckdb.org/

iagovar 6y ago
DuckDB is amazing.
sriku 6y ago
Wow! Seems to be around for a while as well. Regret not finding it earlier.
vslira 6y ago
Question for ppl using DuckDB: are the use cases similar to what you'd use Apache Arrow, but with the benefit of working in SQL, or are they meaningfully different? I'm not currently using any of those, mind you, still on a pandas/dask* dataframe basis, but I'm trying to wrap my head around where the ecosystem is moving *I know Dask is already using Arrow behind the scenes
- phillc73 6y ago
  I don't use Apache Arrow, so I'm in a poor position to compare it with DuckDB. My use case for DuckDB is effectively querying R dataframes with SQL. DuckDB has the functionality to register virtual tables, with data from existing dataframes. As I know SQL reasonably well, using DuckDB to query dataframes means I don't need to learn a bunch of new dplyr verbs or data.table constructs.There are some other R packages which also support this use case - sqldf and tidyquery are two I am aware of. Both of these follow a different approach, where they parse the SQL query. Using a DuckDB virtual table lets the database handle all of the SQL. I've found so far that through using DuckDB, performance is much better than sqldf and tidyquery, nowhere near as quick as data.table and can be quicker than dplyr, depending on query complexity. I haven't really looked at anything approaching big data sizes though.

Cthulhu_ 6y ago

I'm using SQLite at the moment; on the one side there's a 'legacy' (read: poorly written 2012) application, on the other there's the new and rebuilt version. The old one was not built very well, it does not use foreign keys or any kind of database constraints (it references other entries by name in a column of comma-separated values) and it runs like trash. But the performance problem is not in the dozen queries it runs to load the data, it's in the fact that it converts the query result to XML (via string concatenation, because of course) and that is converted to JSON; the conversion is at least 60% of each request. The other problem is that it writes and re-queries the data whenever you leave one of the hundreds of form fields in the application. I'm rebuilding the application in a modern tech stack, still using SQLite but properly this time, along with Go and React. API requests take 20-40ms instead of 300-1500ms, and there's much less of them. The main downside to using SQLite is that it does not support "proper" database migrations; you cannot alter a column. You can add columns to an existing table, but you can't change existing columns. The database abstraction I'm using at the moment, Gorm (a different subject entirely) work around this by moving stuff to a temp table, recreating the table with the updated columns and moving stuff back, I believe. Anyway TL;DR sqlite is not the bottleneck.

srcreigh 6y ago
SQLite did get support for renaming columns recently [1]. Anyways, the process you describe is also used in MySQL for doing online schema migrations. [2] "proper" database migrations cause downtime [1]: https://stackoverflow.com/questions/805363/how-do-i-rename-a-column-in-a-sqlite-database-table https://stackoverflow.com/questions/805363/how-do-i-rename-a... [2]: https://github.com/github/gh-ost https://github.com/github/gh-ost

NelsonMinar 6y ago

It's terrific right until you need multiple processes writing to the same database. It's no accident that SQLite is fast for many small queries; it's not doing a lot of the work required to, say, be a good database backend for multiple web frontends.

deleted 6y ago
[deleted]
danenania 6y ago
Yeah, I don’t really see sqlite as competing with postgres or mysql for this reason. It’s an alternative to the file system.

lisper 6y ago

I tried to switch from MySQL to SQLite for my Postfix/Dovecot installation but I ran into a very annoying problem: every now and then I lose an email because the "database is locked". This is a show-stopper for me because the problem happens very rarely, only once every couple of days, but it's catastrophic: when this happens, the incoming message is not bounced, it is actually lost. The only reason I even realized it was happening is because I noticed there were emails in the root account, which is the error-reporting mechanism of last resort. I've searched the web in vain for a solution. If you have any suggestions, I would love to be able to stick with sqlite, but at the moment I am about to begin migrating back to MySQL. :-(

kevincox 6y ago
This sounds like a problem with Postfix or Dovecot. Postfix shouldn't ack or Dovecot shouldn't delete the email until it is safely stored. That being said there are many use cases where having high availability is critical and in the face of multiple writers SQLite isn't the best option for that.
- lisper 6y ago
  I don't think this is a multiple-writer problem. Postfix is only reading. I am running a milter that is writing, but I control the code for that so I have it set up to retry if it fails. So the error is being generated by postfix itself, and so it must be happening on a read (because that is all postfix does). I was hoping to find some kind of global switch that would make sqlite always wait for locks rather than throwing an error. But I've scoured the web for such a solution without success :-(
  - samatman 6y ago
    There is no such thing, to my knowledge. What can be done is registering a busy callback: https://www.sqlite.org/c3ref/busy_handler.html https://www.sqlite.org/c3ref/busy_handler.html I don't understand enough about your specific problem to know if this will actually help you, just sharing a tidbit I encountered working on a comparable issue.