3 ms·

One thing Postgres definitely doesn't do, and I wish it did, is embedded. This is usually the place that people use SQLite, for example, apps that are clients,

One thing Postgres definitely doesn't do, and I wish it did, is embedded. This is usually the place that people use SQLite, for example, apps that are clients, but still need to do substantial work on the client side. The problem is, SQLite does have a few limitations that it doesn't make obvious: some of the more complex SQL syntax that Postgres supports are missing, but more importantly, it can only ever do one write or read to the database predictably. (Don't believe me? Try this: https://gist.github.com/mrnugget/0eda3b2b53a70fa4a894 https://gist.github.com/mrnugget/0eda3b2b53a70fa4a894) I know that SQLite supports concurrent writes and reads technically, but it predictably leads to 'database is locked' issues. In practice you have to use it in a fashion that it only ever does one thing, read, or write. No multiple writes. No single write and multiple readers, no single write and single reader. Just read — or write. (Edit: WAL doesn't help with this either.) Or you're in a world of hurt. I'd love to be able to move to Postgres just to get away from that.

manigandham 8y ago
This is almost always a driver/wrapper problem, which seems to be the case here. SQLite supports multiple readers without issue that only block slightly when a write transaction is committed, and there's also WAL mode with even better concurrency that avoids locks altogether for reads.
- rolleiflex 8y ago
  I've had the same problem with Python, not just Go. If it's a driver problem, it's a very widespread one, which would point out to SQLite driver API being in need of some love if so many folks who write SQLite drivers are making the same mistake.
  - blattimwind 8y ago
    The Python driver is buggy-by-default. This has been documented in recent versions [1], and is kinda sane for the undo log, but not so good if you want to use snapshot isolation. I guess many bindings would make the same decision. Basically they delay the emission of BEGIN until the first DML/DDL is executed. If you fix this (which is easy), then WAL works just fine. Note that transactions, as is standard, span from BEGIN/COMMIT to COMMIT/ROLLBACK. See e.g. https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#transaction-isolation-level https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#t... and https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#pysqlite-serializable https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#p... [1] Really it's more like "encoded": https://docs.python.org/3/library/sqlite3.html#controlling-transactions https://docs.python.org/3/library/sqlite3.html#controlling-t... -- you already need to know the issue to understand that this section specifies the root cause.
    - rolleiflex 8y ago
      Interesting! The last time I used this with Python was 2014, and this, alongside the botched migration to 3 was the reason I moved on from Python to Go. Glad to hear things have been improving.
  - manigandham 8y ago
    SQLite has billions of global installs and is tested and certified under incredibly stringent conditions for use in applications like aircraft avionics. Unless there is serious evidence to the contrary, it's pretty much guaranteed that it's not the cause of the issue. How many drivers have you actually tested? There are 2 comments from 2016 on that thread saying other drivers do work fine.
coleifer 8y ago
> some of the more complex SQL syntax that Postgres supports are missing, but more importantly, it can only ever do one write or read to the database predictably I don't know...it's recently added upsert compatible with Postgres syntax as well as support for window functions. Additionally, sqlite has a sophisticated json extension. > it can only ever do one write or read to the database predictably WAL mode addresses this, allowing an arbitrary number of readers with a writer. For concurrent applications just use a dedicated write thread and enqueue writes...your example is contrived. Connection management, transaction scope management, etc, can all work in your favor. Writes occur in a few milliseconds, meaning you only need to hold the exclusive lock very briefly in most situations. More information here: http://charlesleifer.com/blog/going-fast-with-sqlite-and-python/ http://charlesleifer.com/blog/going-fast-with-sqlite-and-pyt...
- rolleiflex 8y ago
  The gist I linked is extremely basic, just insert and read, really, and it does use WAL by default. It'll still end up with database lock, and from there it's all shallow waters — reverting from a crashed database lock is not trivial and can cause data loss if handled improperly. Not all writes are couple milliseconds. I regularly have transactions that take 15< seconds, and it's not because the data size that's being input is large, it's just that the SQL is recursive and complex enough to warrant recursion (storing graph data).
justinclift 8y ago
As a data point with SQLite, there's a branch called the "Server Process Edition" which has support for 16 concurrent writers: https://www.sqlite.org/src/tree?ci=754ad35cd26da361 https://www.sqlite.org/src/tree?ci=754ad35cd26da361 https://www.sqlite.org/src/artifact/b98409c486d6f028 https://www.sqlite.org/src/artifact/b98409c486d6f028 Haven't yet played around with it myself, and I tend to expect it'd be more experimental than something to use in production.
combatentropy 8y ago
> it predictably leads to 'database is locked' issues Do you mean your application actually throws the error "database is locked," and dies without simply waiting for the lock to release? This behavior definitely goes against the grain of the documentation. From the SQLite website: "SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writers queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds." --- https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html I have to wonder, with the others here, whether the bug is in the driver, or just anywhere but SQLite. With such fragility it would have blown up by now. SQLite is ubiquitous: the iPhone, iTunes, Android, Chrome, Windows 10, McAfee, the Redhat Package Manager (RPM), commercial flight software, and many other pieces of software --- https://www.sqlite.org/famous.html https://www.sqlite.org/famous.html

manigandham 8y ago

This is almost always a driver/wrapper problem, which seems to be the case here. SQLite supports multiple readers without issue that only block slightly when a write transaction is committed, and there's also WAL mode with even better concurrency that avoids locks altogether for reads.

rolleiflex 8y ago
I've had the same problem with Python, not just Go. If it's a driver problem, it's a very widespread one, which would point out to SQLite driver API being in need of some love if so many folks who write SQLite drivers are making the same mistake.
- blattimwind 8y ago
  The Python driver is buggy-by-default. This has been documented in recent versions [1], and is kinda sane for the undo log, but not so good if you want to use snapshot isolation. I guess many bindings would make the same decision. Basically they delay the emission of BEGIN until the first DML/DDL is executed. If you fix this (which is easy), then WAL works just fine. Note that transactions, as is standard, span from BEGIN/COMMIT to COMMIT/ROLLBACK. See e.g. https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#transaction-isolation-level https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#t... and https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#pysqlite-serializable https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#p... [1] Really it's more like "encoded": https://docs.python.org/3/library/sqlite3.html#controlling-transactions https://docs.python.org/3/library/sqlite3.html#controlling-t... -- you already need to know the issue to understand that this section specifies the root cause.
  - rolleiflex 8y ago
    Interesting! The last time I used this with Python was 2014, and this, alongside the botched migration to 3 was the reason I moved on from Python to Go. Glad to hear things have been improving.
- manigandham 8y ago
  SQLite has billions of global installs and is tested and certified under incredibly stringent conditions for use in applications like aircraft avionics. Unless there is serious evidence to the contrary, it's pretty much guaranteed that it's not the cause of the issue. How many drivers have you actually tested? There are 2 comments from 2016 on that thread saying other drivers do work fine.

coleifer 8y ago

> some of the more complex SQL syntax that Postgres supports are missing, but more importantly, it can only ever do one write or read to the database predictably I don't know...it's recently added upsert compatible with Postgres syntax as well as support for window functions. Additionally, sqlite has a sophisticated json extension. > it can only ever do one write or read to the database predictably WAL mode addresses this, allowing an arbitrary number of readers with a writer. For concurrent applications just use a dedicated write thread and enqueue writes...your example is contrived. Connection management, transaction scope management, etc, can all work in your favor. Writes occur in a few milliseconds, meaning you only need to hold the exclusive lock very briefly in most situations. More information here: http://charlesleifer.com/blog/going-fast-with-sqlite-and-python/ http://charlesleifer.com/blog/going-fast-with-sqlite-and-pyt...

rolleiflex 8y ago
The gist I linked is extremely basic, just insert and read, really, and it does use WAL by default. It'll still end up with database lock, and from there it's all shallow waters — reverting from a crashed database lock is not trivial and can cause data loss if handled improperly. Not all writes are couple milliseconds. I regularly have transactions that take 15< seconds, and it's not because the data size that's being input is large, it's just that the SQL is recursive and complex enough to warrant recursion (storing graph data).

justinclift 8y ago

As a data point with SQLite, there's a branch called the "Server Process Edition" which has support for 16 concurrent writers: https://www.sqlite.org/src/tree?ci=754ad35cd26da361 https://www.sqlite.org/src/tree?ci=754ad35cd26da361 https://www.sqlite.org/src/artifact/b98409c486d6f028 https://www.sqlite.org/src/artifact/b98409c486d6f028 Haven't yet played around with it myself, and I tend to expect it'd be more experimental than something to use in production.

combatentropy 8y ago

> it predictably leads to 'database is locked' issues Do you mean your application actually throws the error "database is locked," and dies without simply waiting for the lock to release? This behavior definitely goes against the grain of the documentation. From the SQLite website: "SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writers queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds." --- https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html I have to wonder, with the others here, whether the bug is in the driver, or just anywhere but SQLite. With such fragility it would have blown up by now. SQLite is ubiquitous: the iPhone, iTunes, Android, Chrome, Windows 10, McAfee, the Redhat Package Manager (RPM), commercial flight software, and many other pieces of software --- https://www.sqlite.org/famous.html https://www.sqlite.org/famous.html