4 ms·

In the past I've had huge speedups by moving simple single-table databases that had grown a bit (e.g., time series data) from sqlite to postgres. Insert perform

In the past I've had huge speedups by moving simple single-table databases that had grown a bit (e.g., time series data) from sqlite to postgres. Insert performance is also quite bad forcing you to write applications with extra caching layers to be able to do a bunch of inserts at once. Sqlite is great for many applications but it's speed is somewhat oversold. I really wish the postgres engine was embeddable into applications easily.

hizanberg 7y ago
Obviously if it was embeddable it wouldn’t be as fast and be subject to the same design limitations as SQLite.
- pedrocr 7y ago
  There's nothing about being embeddable that's a limitation. All I really want is a lib I can link to that gives me an entry point to launch the database against a given folder, with some of the tweaks the postgres config file has, and that gives me a connection back. That entry point can launch however many threads it wants and behave exactly like postgres itself. After that my app can connect to a standard postgres and take advantage of all the features and performance. We've been moving so much into web apps that optimizing for desktop apps like these is not a big concern these days. Sqlite ends up being used in that niche but it's extremely easy to run into it's limitations even in very simple personal usage. It's fine for what it is, an alternative to open(), but there are plenty of use cases where optimizing for fast open is not relevant. Here's an example of where I'd really like postgres to replace sqlite: https://github.com/pedrocr/syncer/ https://github.com/pedrocr/syncer/ I've had to add an extra caching layer because of sqlite INSERT performance. Given that I've already had to code around limitations and the schema is simple I will probably end up using a simpler non-SQL database in the future instead.
  - hizanberg 7y ago
    > There's nothing about being embeddable that's a limitation. An always-running in-memory server process is going to be a lot better at concurrency than multiple embeddable libraries trying to take locks out on the same file on disk.
    - pedrocr 7y ago
      Read what I wrote. What I want is always-running in-memory. Being embeddable doesn't mean you need to have the sqlite model where several processes access the same database. None of the sqlite uses I've had required that at all.
quickquestion42 7y ago
EDIT: Deleted a controversial comment about the often praised SQLite test suite not actually being public and about locking behaviour in SQLite. References are [1] and [2]. [1] https://www.sqlite.org/th3.html https://www.sqlite.org/th3.html [2] https://beets.io/blog/sqlite-nightmare.html https://beets.io/blog/sqlite-nightmare.html
- rathel 7y ago
  If I understand you correctly, there is another test suite in addition to the one you can download freely?
  - liability 7y ago
    The TH3 test suite is proprietary: >"The TH3 test harness is a set of proprietary tests, written in C that provide 100% branch test coverage (and 100% MC/DC test coverage) to the core SQLite library. The TH3 tests are designed to run on embedded and specialized platforms that would not easily support TCL or other workstation services. TH3 tests use only the published SQLite interfaces. TH3 consists of about 69.4 MB or 948.1 KSLOC of C code implementing 44753 distinct test cases. TH3 tests are heavily parameterized, though, so a full-coverage test runs about 1.7 million different test instances. The cases that provide 100% branch test coverage constitute a subset of the total TH3 test suite. A soak test prior to release does hundreds of millions of tests. Additional information on TH3 is available separately." https://sqlite.org/testing.html https://sqlite.org/testing.html
  - Tomte 7y ago
    > The TH3 test harness is a set of proprietary tests https://www.sqlite.org/testing.html#harnesses https://www.sqlite.org/testing.html#harnesses
- liability 7y ago
  D. Richard Hipp has always been pretty up front about his business model. He provides sqlite for free, public domain, and does not typically entertain third party contributions to keep the licensing clean. Additionally his business sells proprietary extensions and support for sqlite. Having that test suite uniquely positions them to do so, and due to this business model they expect to have the funding to continue providing a public domain sqlite to the world for decades to come.
liability 7y ago
I once tried out some RSS aggregator/reader that required me to install and set up postgres for no good reason other than it's what the developer was accustomed to. Huge pain in the ass, particularly since simply using sqlite would have been perfectly adaquate (does a RSS reader really need concurrent writes? Really?) For any software meant to be used on a non-technie's desktop, anything other than an embedded 'zero config' arrangement is a nonstarter.
- pedrocr 7y ago
  This is exactly true, which is why embedding postgres would be great. It's very easy to hit the performance limitations of sqlite and then be stuck with the choice of either poor performance or forcing users to setup postgres like you experienced.
- aitchnyu 7y ago
  Amarok, the kitchen sink music player had an option of connecting to a Mysql server back in 2007. IIRC it used a slower embedded DB out of the box.
bob1029 7y ago
I would be curious about your particular implementation with SQLite. When enabling WAL, I am able to saturate a NVMe disk on which the the database file resides. In the single-node case, I have yet to see SQLite (when optimized) run slower than any other SQL implementation. I do agree that SQLite with 100% defaults will easily be overtaken by Postgres, and that if your use case requires that more than 1 node persist transactional data you should definitely be using Postgres. We are currently using SQLite (C#/System.Data.Sqlite.Core/WAL=on/synchronous=normal) in production for multiple customers as the principal store of transactional business data and application state. We have had literally zero downtime or performance concerns as a consequence of SQLite being used for managing this data. The longer this goes on, the more I am led to believe that using a client-server database system for any single-node persistence scenario is never a good idea. The part that I really like is that I don't have to install anything on the customer's box along with our software. We use a self-contained deployment of .NET Core, and it brings the SQLite engine along for the ride.
- pedrocr 7y ago
  The code for my most recent case is here: https://github.com/pedrocr/syncer/ https://github.com/pedrocr/syncer/ The db code itself is here: https://github.com/pedrocr/syncer/blob/24d70fd452aa6b1463090f8c81178f736cd80739/src/backingstore/metadatadb.rs https://github.com/pedrocr/syncer/blob/24d70fd452aa6b1463090... WAL is being used but that's not enough to get a mixed INSERT/SELECT to not completely starve reads without first caching INSERT and batching it. >In the single-node case, I have yet to see SQLite (when optimized) run slower than any other SQL implementation. I do agree that SQLite with 100% defaults will easily be overtaken by Postgres, and that if your use case requires that more than 1 node persist transactional data you should definitely be using Postgres. This is single node with WAL. If there's something I've missed I'd love to see it (or get a PR). But the research I did when I wrote the code seemed to indicate this was a known limitation of sqlite.
  - bob1029 7y ago
    Is there a reason you are managing locking at the application layer on the SQLite connection object? https://github.com/pedrocr/syncer/blob/24d70fd452aa6b1463090f8c81178f736cd80739/src/backingstore/metadatadb.rs#L108 https://github.com/pedrocr/syncer/blob/24d70fd452aa6b1463090... This is going to absolutely destroy your throughput. I am not sure about go, but in .Net, the underlying SQLite connection instance is inherently thread-safe and can be shared across execution contexts. If you are unable to share a single SQLite connection instance between multiple logical parallel execution contexts in go, I would recommend just creating a new SQLite connection in each case where you need one (e.g. at the beginning of every method currently performing a lock on connection). The only thing you need to remember with this approach is that the synchronous=normal pragma needs to be set each time you new up a connection. This is an extremely fast operation (doesn't touch disk IIRC), so you shouldn't worry too much about any extra overhead here.

hizanberg 7y ago

Obviously if it was embeddable it wouldn’t be as fast and be subject to the same design limitations as SQLite.

pedrocr 7y ago
There's nothing about being embeddable that's a limitation. All I really want is a lib I can link to that gives me an entry point to launch the database against a given folder, with some of the tweaks the postgres config file has, and that gives me a connection back. That entry point can launch however many threads it wants and behave exactly like postgres itself. After that my app can connect to a standard postgres and take advantage of all the features and performance. We've been moving so much into web apps that optimizing for desktop apps like these is not a big concern these days. Sqlite ends up being used in that niche but it's extremely easy to run into it's limitations even in very simple personal usage. It's fine for what it is, an alternative to open(), but there are plenty of use cases where optimizing for fast open is not relevant. Here's an example of where I'd really like postgres to replace sqlite: https://github.com/pedrocr/syncer/ https://github.com/pedrocr/syncer/ I've had to add an extra caching layer because of sqlite INSERT performance. Given that I've already had to code around limitations and the schema is simple I will probably end up using a simpler non-SQL database in the future instead.
- hizanberg 7y ago
  > There's nothing about being embeddable that's a limitation. An always-running in-memory server process is going to be a lot better at concurrency than multiple embeddable libraries trying to take locks out on the same file on disk.
  - pedrocr 7y ago
    Read what I wrote. What I want is always-running in-memory. Being embeddable doesn't mean you need to have the sqlite model where several processes access the same database. None of the sqlite uses I've had required that at all.

quickquestion42 7y ago

EDIT: Deleted a controversial comment about the often praised SQLite test suite not actually being public and about locking behaviour in SQLite. References are [1] and [2]. [1] https://www.sqlite.org/th3.html https://www.sqlite.org/th3.html [2] https://beets.io/blog/sqlite-nightmare.html https://beets.io/blog/sqlite-nightmare.html

rathel 7y ago
If I understand you correctly, there is another test suite in addition to the one you can download freely?
- liability 7y ago
  The TH3 test suite is proprietary: >"The TH3 test harness is a set of proprietary tests, written in C that provide 100% branch test coverage (and 100% MC/DC test coverage) to the core SQLite library. The TH3 tests are designed to run on embedded and specialized platforms that would not easily support TCL or other workstation services. TH3 tests use only the published SQLite interfaces. TH3 consists of about 69.4 MB or 948.1 KSLOC of C code implementing 44753 distinct test cases. TH3 tests are heavily parameterized, though, so a full-coverage test runs about 1.7 million different test instances. The cases that provide 100% branch test coverage constitute a subset of the total TH3 test suite. A soak test prior to release does hundreds of millions of tests. Additional information on TH3 is available separately." https://sqlite.org/testing.html https://sqlite.org/testing.html
- Tomte 7y ago
  > The TH3 test harness is a set of proprietary tests https://www.sqlite.org/testing.html#harnesses https://www.sqlite.org/testing.html#harnesses
liability 7y ago
D. Richard Hipp has always been pretty up front about his business model. He provides sqlite for free, public domain, and does not typically entertain third party contributions to keep the licensing clean. Additionally his business sells proprietary extensions and support for sqlite. Having that test suite uniquely positions them to do so, and due to this business model they expect to have the funding to continue providing a public domain sqlite to the world for decades to come.

liability 7y ago

I once tried out some RSS aggregator/reader that required me to install and set up postgres for no good reason other than it's what the developer was accustomed to. Huge pain in the ass, particularly since simply using sqlite would have been perfectly adaquate (does a RSS reader really need concurrent writes? Really?) For any software meant to be used on a non-technie's desktop, anything other than an embedded 'zero config' arrangement is a nonstarter.

pedrocr 7y ago
This is exactly true, which is why embedding postgres would be great. It's very easy to hit the performance limitations of sqlite and then be stuck with the choice of either poor performance or forcing users to setup postgres like you experienced.
aitchnyu 7y ago
Amarok, the kitchen sink music player had an option of connecting to a Mysql server back in 2007. IIRC it used a slower embedded DB out of the box.

bob1029 7y ago

I would be curious about your particular implementation with SQLite. When enabling WAL, I am able to saturate a NVMe disk on which the the database file resides. In the single-node case, I have yet to see SQLite (when optimized) run slower than any other SQL implementation. I do agree that SQLite with 100% defaults will easily be overtaken by Postgres, and that if your use case requires that more than 1 node persist transactional data you should definitely be using Postgres. We are currently using SQLite (C#/System.Data.Sqlite.Core/WAL=on/synchronous=normal) in production for multiple customers as the principal store of transactional business data and application state. We have had literally zero downtime or performance concerns as a consequence of SQLite being used for managing this data. The longer this goes on, the more I am led to believe that using a client-server database system for any single-node persistence scenario is never a good idea. The part that I really like is that I don't have to install anything on the customer's box along with our software. We use a self-contained deployment of .NET Core, and it brings the SQLite engine along for the ride.

pedrocr 7y ago
The code for my most recent case is here: https://github.com/pedrocr/syncer/ https://github.com/pedrocr/syncer/ The db code itself is here: https://github.com/pedrocr/syncer/blob/24d70fd452aa6b1463090f8c81178f736cd80739/src/backingstore/metadatadb.rs https://github.com/pedrocr/syncer/blob/24d70fd452aa6b1463090... WAL is being used but that's not enough to get a mixed INSERT/SELECT to not completely starve reads without first caching INSERT and batching it. >In the single-node case, I have yet to see SQLite (when optimized) run slower than any other SQL implementation. I do agree that SQLite with 100% defaults will easily be overtaken by Postgres, and that if your use case requires that more than 1 node persist transactional data you should definitely be using Postgres. This is single node with WAL. If there's something I've missed I'd love to see it (or get a PR). But the research I did when I wrote the code seemed to indicate this was a known limitation of sqlite.
- bob1029 7y ago
  Is there a reason you are managing locking at the application layer on the SQLite connection object? https://github.com/pedrocr/syncer/blob/24d70fd452aa6b1463090f8c81178f736cd80739/src/backingstore/metadatadb.rs#L108 https://github.com/pedrocr/syncer/blob/24d70fd452aa6b1463090... This is going to absolutely destroy your throughput. I am not sure about go, but in .Net, the underlying SQLite connection instance is inherently thread-safe and can be shared across execution contexts. If you are unable to share a single SQLite connection instance between multiple logical parallel execution contexts in go, I would recommend just creating a new SQLite connection in each case where you need one (e.g. at the beginning of every method currently performing a lock on connection). The only thing you need to remember with this approach is that the synchronous=normal pragma needs to be set each time you new up a connection. This is an extremely fast operation (doesn't touch disk IIRC), so you shouldn't worry too much about any extra overhead here.