7 ms·

SQLite vs Postgres for a local database (on disk, not over the network): who wins? (Each in their most performance oriented configuration)

• RedShift1 4y ago
  SQLite is always going to win in that category just from the fact that there are less layers of code to be worked through to execute a query.
  • remram 4y ago
    Latency-wise maybe, but throughput can be more important for a lot of applications or bigger databases. I say "maybe" because even there, SQLite is much more limited in terms of query-planning (very simple statistics) and the use of multiple indexes. That's assuming we're talking about reads, PostgreSQL will win for write-heavy workloads.
  • electroly 4y ago
    As long as you turn it into a throughput race instead of a latency race, PostgreSQL can definitely win. SQLite has a primitive query builder and a limited selection of query execution steps to choose from. For instance, all joins in SQLite are inner loop joins. It can't do hash or merge joins. It can't do GIN or columnstore indexes. If a query needs those things, PostgreSQL can provide them and can beat SQLite.
    • ac2u 4y ago
      out of interest, what columnstore indexes are available to postgres? Would be happy to find out that I'm missing something. I know citus can provide columnar tables but I can't find columnar indexes for regular row-based tables in their docs. (use case of keeping an OLTP table but wanting to speed up a tiny subset of queries) Closest thing I could find was Swarm64 for columnar indexes but it doesn't seem to be available anymore.
  • sophacles 4y ago
    > just from the fact that there are less layers of code to be worked through This is not an invariant. I've seen be true, and I've seen it be false. Sometimes that extra code is just cruft yes. Other times though it is worth it to set up your data (or whatever) to take advantage of mechanical sympathies in hot paths, or filter the data before the expensive processing step, etc.
    • RedShift1 4y ago
      I'm not talking about extra code, I'm talking about _layers_ of code. With PostgreSQL you're still sending data over TCP/IP or a UNIX socket, and are copying things around in memory. Compare that to SQLite that runs in the memory space of the program, thus no need for copying and socket traffic. There's just less middlemen (middlepersons?) with SQLite that are unavoidable with PostgreSQL. So less layers = less interpreting/serialization/deserialization/copying/... = higher performance. I will even argue that even if the SQLite query engine is slightly less efficient than PostgreSQL, you're still winning because of less memory copying going around.
      • fuckstick 4y ago
        > less interpreting/serialization/deserialization/copying/... = higher performance Unfortunately for many database workloads you are overestimating the relative cost of this factor. > even if the SQLite query engine is slightly less efficient than PostgreSQL And this is absurd - the postgresql query engine isn't just "slightly" more efficient. It is tremendously more sophisticated. People using a SQL datastore as a glorified key-value store are not going to notice - which seems to be a large percentage of the sqlite install base. It's not really a fair comparison.
        • ok_dad 4y ago
          With SQLite, though, you could reasonably just skip doing fancy joins and do everything in tiny queries in tight loops because SQLite is literally embedded in your app’s code. You can be careless with SQLite in ways you cannot with a monolithic database server because of that reason. I still agree there are use cases where a centralized database is better, but SQLite is a strange beast that needs a special diet to perform best.
• lvass 4y ago
  SQLite. The most performant configuration is unsuited to most usage, and may lead to database corruption on a system crash.
  • rafale 4y ago
    Should have said the most performance oriented setting that's also safe from data corruption.
    • lvass 4y ago
      Then it depends on the usage. You'd likely need to run with synchronous mode on, and even on WAL, multiple separate write transactions is a issue. If you don't have many writes or buffer them into not many transactions, SQLite is the most performant.
• thomascgalvin 4y ago
  This is basically the exact use case SQLite was designed for; PostgreSQL is a marvel, and at the end of the day presents a much more robust RDBMS, but it's never going to beat SQLite at the thing SQLite was designed for.
• samatman 4y ago
  Postgres obviously. Sorry, just thought I'd buck the trend and assume a very write-heavy workload with like 64 cores. If you don't have significant write contention, SQLite every time.
  • innocenat 4y ago
    Where is write contention coming from if it's operated locally?
    • Thaxll 4y ago
      SQLite is "single" threaded for writes.
    • d3nj4l 4y ago
      ... you can get tons of requests on a server?
      • dinosaurdynasty 4y ago
        Redis has the same limitation (only one transaction at a time) and is used a lot for webapps. It solves this by requiring full transactions up front. The ideal case for sqlite for performance is to have only a single process/thread directly interacting with the database and having other process/threads send messages to and from the database process.
      • innocenat 4y ago
        But that isn't "locally"?
  • ledgerdev 4y ago
    Here's sqlite doing 100 million inserts in 33 seconds which should fit into nearly every workload, though it is batched. https://avi.im/blag/2021/fast-sqlite-inserts/ https://avi.im/blag/2021/fast-sqlite-inserts/ So write contention from multiple connections is what you're talking about, versus a single process using sqlite?
• ergocoder 4y ago
  Functionality-wise, SQLite's dialect is really lacking...
  • simonw 4y ago
    Is it the SQL dialect there lacking or is it the built-in functions? I agree that SQLite default functionality is very thin compared to PostgreSQL - especially with respect to things like date manipulation - but you can extend it with more SQL functions (and table-valued functions) very easily.
    • ergocoder 4y ago
      Depends on what easily means. Sqlite can't do custom format date parsing and regex extract. How do we extend something like this? If we go beyond a simple function to window function, I imagine it would be even harder. At this point, we nlmight as well use postgres.
      • polyrand 4y ago
        Adding user-defined functions to SQLite is not difficult, and the mechanism is quite flexible. You can create extensions and load them when you create the SQLite connection to have the functions available in queries. I wrote a blog post explaining how to do that using Rust, and the example is precisely a `regex_extract` function [0]. If you need them, you also have a "stdlib" implemented for Go [1] and a pretty extensive collection of extensions [2] [0]: https://ricardoanderegg.com/posts/extending-sqlite-with-rust/ https://ricardoanderegg.com/posts/extending-sqlite-with-rust... [1]: https://github.com/multiprocessio/go-sqlite3-stdlib https://github.com/multiprocessio/go-sqlite3-stdlib [2]: https://github.com/nalgeon/sqlean https://github.com/nalgeon/sqlean
        • ergocoder 4y ago
          Wow this is helpful. I'm using sqlite for some of my projects and always bothered that some functions are missing. WITH RECURSIVE is too mind bending. This seems like I can add a lot more functions to it, not just regex extract. Came here to complain and learned something useful.
• nikeee 4y ago
  The documentation offers some advice on this: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
• bob1029 4y ago
  >most performance oriented configuration I am 99% sure SQLite is going to win unless you actually care about data durability at power loss time. Even if you do, I feel I could defeat Postgres on equal terms if you permit me access to certain ring-buffer-style, micro-batching, inter-thread communication primitives. Sqlite is not great at dealing with a gigantic wall of concurrent requests out of the box, but using a little bit of innovation in front of SQLite can solve this problem quite well. The key is resolve the write contention outside of the lock that is baked into the SQLite connection. Writing batches to SQLite on a single connection with WAL turned on and Sync set to normal is pretty much like operating at line speed with your IO subsystem.
  • deleted 4y ago
    [deleted]
  • prirun 4y ago
    > I am 99% sure SQLite is going to win unless you actually care about data durability at power loss time. SQLite will handle a power loss just fine. From https://www.sqlite.org/howtocorrupt.html https://www.sqlite.org/howtocorrupt.html: "An SQLite database is highly resistant to corruption. If an application crash, or an operating-system crash, or even a power failure occurs in the middle of a transaction, the partially written transaction should be automatically rolled back the next time the database file is accessed. The recovery process is fully automatic and does not require any action on the part of the user or the application." From https://www.sqlite.org/testing.html https://www.sqlite.org/testing.html: "Crash testing seeks to demonstrate that an SQLite database will not go corrupt if the application or operating system crashes or if there is a power failure in the middle of a database update. A separate white-paper titled Atomic Commit in SQLite describes the defensive measure SQLite takes to prevent database corruption following a crash. Crash tests strive to verify that those defensive measures are working correctly. It is impractical to do crash testing using real power failures, of course, and so crash testing is done in simulation. An alternative Virtual File System is inserted that allows the test harness to simulate the state of the database file following a crash."
• kpgaffney 4y ago
  I think the (unsatisfying) answer is "it depends". There's a huge amount of diversity in database workloads, even among the workloads served by SQLite as we mention in the paper. For read-mostly to read-only OLTP workloads, read latency is the most important factor, so I predict SQLite would have an edge over PostgreSQL due to SQLite's lower complexity and lack of interprocess communication. For write-heavy OLTP workloads, coordinating concurrent writes becomes important, so I predict PostgreSQL would provide higher throughput than SQLite because PostgreSQL allows more concurrency. For OLAP workloads, it's less clear. As a client-server database system, PostgreSQL can afford to be more aggressive with memory usage and parallelism. In contrast, SQLite uses memory sparingly and provides minimal intra-query parallelism. If you pressed me to make a prediction, I'd probably say SQLite would generally win for smaller databases. PostgreSQL might be faster for some workloads on larger databases. However, these are just guesses and the only way to be sure is to actually run some benchmarks.