5 ms·

I hope you've found https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite https://stackoverflow.com/questions/1711631/impr

• jgalt212 1y ago
  yes, but they punt on this issue: CREATE INDEX then INSERT vs. INSERT then CREATE INDEX i.e. they only time INSERTs, not the CREATE INDEX after all the INSERTs.
  • deleted 1y ago
    [deleted]
• zeroq 1y ago
  Yes! That was actually quite helpful. For my use case (recreating in-memory from scratch) it basically boils down to three points: (1) journal_mode = off (2) wrapping all inserts in a single transaction (3) indexes after inserts. For whatever it's worth I'm getting 15M inserts per minute on average, and topping around 450k/s for trivial relationship table on a stock Ryzen 5900X using built-in sqlite from NodeJS.
  • vlovich123 1y ago
    Would it be useful for you to have a SQL database that’s like SQLite (single file but not actually compatible with the SQLite file format) but can do 100M/s instead?
    • zeroq 1y ago
      Not really. I tested couple different approaches, including pglite, but node finally shipped native sqlite with version 23 and it's fine for me. I'm a huge fan of serverless solutions and one of the absolute hidden gems about sqlite is that you can publish the database on http server and query it extremely efficitent from a client. I even have a separate miniature benchmark project I thought I might publish, but then I decided it's not worth anyones time. x]
    • pdimitar 1y ago
      Which database might that be? I have been looking for replacement of SQLite for years -- admittedly, not very actively, embedded databases are just a hobby obsession and my life did not allow me much leisure time in the last years -- and still couldn't find one. The written-in-Rust `sled` database is more like a key-value store and I had partial successes with it but it's too much work making a KV store a relational database.
      • zeroq 1y ago
        Honestly I don't see much use for yet-another-sqlite. The premise of having 100M/s writes instead of 500k/s sounds bit unrealistic, but at the same time, while simply importing tuples and completely ignoring stuff like foreign keys, I'm only utilizing one core. I had on my todo list an experiment to run these imports in paralell into different databases and then merging them somehow, but I ran out of time. Again, 10Gb sqlite is quite large. On the other hand, I think the adoption and the fact that you can take your db basicially anywhere and it will run out of the bat is something you can't ignore. I was briefly looking at pglite but I don't really see benefits apart from a niche use case when you really need that compatibility with big brother. And then sqlite has so many hidden gems, like the scenario where you can use a sqlite file hosted on http like a remote database! I can post my 10Gb database on S3 and run count(*) on main table and it will only take like 40kb of bandwidth.
        • pdimitar 1y ago
          > Honestly I don't see much use for yet-another-sqlite. Agreed. I want something better than SQLite, something that learns from it and upgrades it further. > On the other hand, I think the adoption and the fact that you can take your db basicially anywhere and it will run out of the bat is something you can't ignore. Absolutely. That's why I am soon finishing my Elixir -> Rust -> SQLite library since I happen to believe most apps don't even need a dedicated DB server. > I was briefly looking at pglite but I don't really see benefits apart from a niche use case when you really need that compatibility with big brother. I would probably easily pay 1000 EUR next month if I could have SQLite with PostgreSQL's strict schema. That's the one weakness of SQLite that I hate with a passion. I know about strict mode. I am using it. Still not good enough. I want "type affinity" gone forever. It's obviously a legacy feature and many people came to rely on it. Hence I concluded that SQLite will never change and something newer should arrive at one point. Though how do you beat the (likely) millions of tests that SQLite has? You don't... but we have to start somewhere. > And then sqlite has so many hidden gems, like the scenario where you can use a sqlite file hosted on http like a remote database! I can post my 10Gb database on S3 and run count() on main table and it will only take like 40kb of bandwidth.* Admittedly I never saw the value in that, to me that just seems like you are having a remote database again, at which point why not just go for PostgreSQL which is stricter and has much less surprises. But that's my bias towards strictness and catching bugs at the door and not 10 km down the road.
• o11c 1y ago
  It's worth noting that the data in that benchmark is tiny (28MB). While this varies between database engines, "one transaction for everything" means keeping some kind of allocations alive. The optimal transaction size is difficult to calculate so should be measured, but it's almost certainly never beneficial to spend multiple seconds on a single transaction. There will also be weird performance changes when the size of data (or indexed data) exceeds the size of main memory.
• gibibit 1y ago
  Hilarious, 3000+ votes for a Stack Overflow question that's not a question. But it is an interesting article. Interesting enough that it gets to break all the rules, I guess?
  • detaro 1y ago
    It's a (quite old) community wiki post. These do (and especially did back then) work and are treated differently.