6 ms·

Excuse my ignorance but isn't the whole point of SQLite that it's embedded, not clint-server?

• Kinrany 4y ago
  Ideally you want both an embedded database, a library for manipulating requests understood by the database, and a few libraries for exposing the same interface over different networks.
  • deleted 4y ago
    [deleted]
• bob1029 4y ago
  For us, a major point of SQLite is that we can execute queries in ~100uS when deployed on top of NVMe storage. Everything being in the same process is 50% of the value proposition for our product. Once you involve a network and another computer, it gets a lot slower unless you are doing RDMA-style shenanigans with very expensive hardware. The other 50% of the value proposition is simplified operations in a B2B setting.
  • MuffinFlavored 4y ago
    > For us, a major point of SQLite is that we can execute queries in ~100uS when deployed on top of NVMe storage. Postgres can't achieve these query times?
    • bob1029 4y ago
      How long does it take to round trip through the network stack of 2 machines in a typical datacenter?
      • MuffinFlavored 4y ago
        I thought we'd be comparing apples-to-apples with Postgres running locally in this case?
        • bob1029 4y ago
          Ok - How many microseconds does it take to round trip SELECT 1 to localhost on a typical cloud VM? You are still using the network stack and involving multiple processes.
          • Spivak 4y ago
            People really do be forgetting how unbelievably fast doing things in-process is. It’s why people suffer the complexity of threads instead multiple processes with ipc - like the network.
            • bob1029 4y ago
              Agreed. In-process is where you get things like instruction-level parallelism. I think our university programs have failed us the most in this regard. ILP should be drilled into every student's brain as the penultimate performance hack. Once you start separating everything into a bunch of different buckets/systems/processes, none of these advantages can be explored anymore. In some cases, ILP can give you a 100x improvement in performance. That's serial throughput. This is the reason many developers consider bit manipulation/hacking to be essentially free.
          • linuxdude314 4y ago
            There is no “round trip” and no network stacked involved in querying a local Postgres instance (web app on same VM as db). Attention is better spent designing an appropriate schema than worrying about the network being too slow to run a single query (if it is, you’ve already failed).
          • justinclift 4y ago
            > Ok - How many microseconds does it take to round trip SELECT 1 to localhost on a typical cloud VM? As a data point, when PG is on the same box as the querying app, you can run the connection over a unix domain socket instead of going over localhost. In theory (!), that's supposed to have reduced latency + higher bandwidth too. It's been a bunch of years since I last saw benchmarks about it though, so things might have changed a bit in the meantime (no idea).
      • bawolff 4y ago
        But this article is about using sqlite over a web socket so both would pay that price.
      • jbverschoor 4y ago
        Use a unix file socket for the connection, or just embed postgresql :-) The nice thing about sqlite, i.m.o., is mainly that it is just one file.
    • irq-1 4y ago
      A Dev once told me that running SQL Server and IIS (Microsoft webserver) on the same machine would allow shared memory. Just pass a pointer from db to web; no copying of the data. Postgres doesn't do this AFAIK.
      • JohnBooty 4y ago
        Purely FYI for those wondering: yeah, this is the default when connecting to MSSQL from the same box. It's not an IIS thing per se. Any app can do this with the appropriate connection string. https://learn.microsoft.com/en-us/sql/tools/configuration-manager/shared-memory-properties?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/tools/configuration-ma... https://learn.microsoft.com/en-us/sql/tools/configuration-manager/creating-a-valid-connection-string-using-shared-memory-protocol?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/tools/configuration-ma... It's been ages since I worked in Microsoft land, but this is one of the reasons why MS shops often scale up instead of out. Imagine a single box with a dozens of beefy cores and a couple hundred gigs of RAM running both IIS and SQL. That's... a significant amount of power. It's also a potentially massive blast radius for security oopsies, but I don't know the practical history of how secure it has or hasn't been, or how it integrates with things like clustering etc.
        • bob1029 4y ago
          I've personally found that one computer is substantially easier to keep an eye on than many computers.
          • JohnBooty 4y ago
            Yeah. And a lot of businesses just don't need the high availability that a cluster of lesser boxen can provide. If they need to have a few hours of downtime each month to install patches on the one-and-only Big Ass Server, that's not necessarily a problem. Oh, and even an idiot can admin the thing. (Not a literal idiot, but it's about the simplest admin experience possible) I honestly think a lot of businesses would honestly be better off with this kind of simplified "scale up" architecture. The downsides are clear and it's not the right fit for everything, obviously. But I see the IT world defaulting to these complicated 95384-piece AWS instances "Because that's how Netflix does it" or whatever and it's like... you're not Netflix and hardly anybody is.