26 ms·

Many small queries are efficient in SQLite

gopalv 6y ago
> For a 50-entry timeline, the latency is usually less than 25 milliseconds. There are a lot of good ideas which don't scale down to a smaller single-core system. For example, there's a CROSS JOIN in that example, which is just fine at the row-counts we might encounter on sqlite. The general low-scale workloads + the in-process operation (the SQL query is a function call, not RPC), means that sqlite can avoid needing the complex query to compress the user intent into a single round-trip. This doesn't apply in general & the article doesn't claim to do so. In the past, I've had production systems which are blocked on mysql_parse() rather than the actual disk IO (anyone remember the mysql Drizzle client side optimizations & the Dynamic SQL extensions?). The simplicity of 1+N is basically a "simple enough and it always works", plus in a few case we can actually use a different projection for the N queries. As a side note to Fossil, I've gone spelunking into the SVN repos with sqlite (there's a .db inside your .svn/ if you still use it) & what you'd end up using is a lot of 1+N queries (ACTUAL_NODE).
- nice2meetu 6y ago
  CROSS JOIN in sqlite3 is used to force the order of the tables in the join. Note the query has a WHERE condition so is the same as an INNER JOIN https://www.sqlite.org/optoverview.html#manual_control_of_query_plans_using_cross_join https://www.sqlite.org/optoverview.html#manual_control_of_qu...
- throwaway912598 6y ago
  From a live table (there are about 10 of these in the database): sqlite> select count(*) from <redacted>; 49862386 Curious what row count you expect in non-SQLite databases on a regular basis.
senderista 6y ago
You can have a client/server database which is just as efficient as a single-process embedded database like SQLite, if that database uses shared memory.
- jbverschoor 6y ago
  Exactly. I have the feeling there is just a little bit too much sqlite-hype going on lately.
Laminary 6y ago
If you haven't tried SQLite, please do. For years I ignored SQLite and used MySQL (it does the job) but once you see how fast SQLite is, and advantages of having a DB contained in a single file... just go play around with SQLite instead of ignoring it for years like me. It's neat.
- alexchamberlain 6y ago
  The main challenge there is how to you ensure your database is resilient to machine or datacentre outages? ie what happens if the 1 server with the database is in a datacentre that loses Internet connectivity?
  - zdkl 6y ago
    SQlite "merely" assumes that the problems that come with distributed systems are handled at the application layer. You'll have to solve those problems for yourself, sure, but in practice I have rarely (I think never actually) had dataloss through a fault of sqlite. Also, did you know you can use in-memory instances (and share them across threads!) with the right incantation? And that you can backup your on-disk instance to an in-memory one, do your expensive transactions without hitting the disk then backup the modified instance right back to disk, even in-place if you want! Sqlite is amazing when you don't expect the DB to do replication or failover on its own.
    - krab 6y ago
      There is always DRBD as well. You can make replication a lower-layer problem. Not that it's without drawbacks.
    - iveqy 6y ago
      No I did not know that, I've looked for a long time for a way to convert a sqlite3 database to an in memory database and then back again. Do you mean that there's support in sqlite3 for this? Could you point me in the right direction?
      - 6y ago
ThePadawan 6y ago
> The Appropriate Uses For SQLite page says that dynamic pages on the SQLite website typically do about 200 SQL statements each. This has provoked criticism from readers. Examples: > "200 SQL statements is a ridiculously high number for a single page" > "For most sites, 200 queries is way, way, way too much." > "[This is] bad design" Funnily enough, at a recent workplace the guideline was "if it only does 200 queries, you've optimized it enough".
- PurpleFoxy 6y ago
  I can relate to this. It’s so easy to write a some code that fires off 10 queries but it’s obscured by the ORM and then it’s in an index listing which isn’t paginated and eventually some user does things weird and it ends up with 1000 items so this one listing runs 10,000 queries.
  - TeMPOraL 6y ago
    In my experience, it doesn't take the user doing anything weird - all it takes is to use ORM enough in the project, and suddenly you have hundreds of separate queries being run to fetch something trivial, which could be done in one or two queries of hand-written SQL.
    - matthewmacleod 6y ago
      In my experience, those "one or two queries of hand-written SQL" quickly balloon out of control into a litany of crimes, because the assumption they will be better relies on having the kind of developer who can easily write "one or two queries of hand-written SQL" but are somehow incapable of reading the documentation or using an ORM correctly.
      - wtetzner 6y ago
        Writing SQL is so much easier than understanding ORMs. ORMs tend to be very complex, because the problem they're trying to solve requires that complexity. Now maybe my perspective is biased too much, since the only ORM I've really used is Hibernate. But in that experience, I learned that Hibernate brings a ton of complexity with no real value. Seems like a high price to pay to avoid learning SQL. The sweet spot seems to be using a library like JOOQ, which lets you write queries, as well as safely construct queries at runtime.
LunaSea 6y ago
The limited type system and loose data validation always prevented me of using SQLite. It's a nice data store but a poor database in my opinion.
- hans_castorp 6y ago
  > The limited type system and loose data validation always prevented me of using SQLite. This. "limited type system" is actually an exaggeration in my opinion. The "data types" seem to only serve documentation purposes. You can always store any value in any column regardless of the "data type"
  - tpetry 6y ago
    Wait? Your can for example put an unicode string into integer or number columns? That can‘t be true? I mean, really? ...
    - ludwigschubert 6y ago
      Yeah. Execute program has a short and sweet explanation with in-browser demonstration: https://www.executeprogram.com/courses/sql/lessons/no-type-enforcement-in-sqlite https://www.executeprogram.com/courses/sql/lessons/no-type-e...
    - IshKebab 6y ago
      It is. They even consider it a feature. You don't actually need to specify column types at all. You can just leave them out. Or make up entirely new types. Foreign key constraints are also not enforced by default but there is at least an option to turn that on.
    - hans_castorp 6y ago
      > Wait? Your can for example put an unicode string into integer or number columns? Yes https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=4634e3821676ed28e27c06d7c9c0af46 https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=4634e3821676ed...
    - 6y ago
curiousmindz 6y ago
An important reminder is to use the right tool for the job. SQLite is indeed amazing at what it does, and fits with many scenarios where it isn't often considered, but it isn't a silver bullet. If you have a scenario where you must have multiple machines (for redundancy or resiliency), then SQLite may not be the "best" choice. However, if these machines were needed for performance reason, you may find that a single well-written SQLite-based solution can be performant enough to run on a single machine (while reaping all the benefits of its simpler approach).
- nine_k 6y ago
  Creating read replicas has become easy with SQLite, thanks to Litestream (recently on HN: https://news.ycombinator.com/item?id=26103776 https://news.ycombinator.com/item?id=26103776). What is really hard with SQLite is efficient concurrent updates. But you don't need them very often.
  - pbowyer 6y ago
    What I've never got to grips with is the "use a single writer with SQLite" advice. That seems doable with a long-running application server, but if your application boots up on every request (like PHP, Python & Ruby applications generally do) how do you do this, when you have multiple simultaneous users? It feels like SQLite is missing a separate gatekeeper-binary to act as the single writer: the database server as it were.
    - liuliu 6y ago
      SQLite uses file lock to coordinate with many processes. Your performance will be similar to use single writer if we ignore the repeated DB open / close due to the app boot on every request. I am not sure about real-world DB open / close cost. Because it is considered good practice to always use some kinds of SQLite connection pool if you need many readers / writers. Didn't get a chance to try.
    - jononor 6y ago
      With Python at least an application server with long-running processes is the most common way to deploy, for example with gunicorn. Though typical config will have green threads and/or multiple worker processes, so I am not quite sure how one the single-writer is enforced.
skc 6y ago
It's ridiculously tempting to try and use SQLite for everything. It's so ridiculously useful and capable.
- sgt 6y ago
  Your smart phone most likely has it already.
  - dmlittle 6y ago
    I might be mistaking things here but I believe that Apple's CoreData and Android's equivalent are both powered by SQLite under the hood. Messages on macOS stores all your message in a SQLite file under ~/Library/Messages/chat.db
    - duskwuff 6y ago
      macOS and iOS both use SQLite pervasively. There's tons of little databases all over. Chrome uses SQLite heavily for history, cookies, bookmarks… etc. So does Firefox. SQLite is everywhere. :)
      - regularfry 6y ago
        It's everywhere to the extent that I find it hard to believe it's not lurking somewhere on a default Windows install. I would very much like to be able to say to people "do not use excel for that. It will make you cry" and not have the response "corporate IT won't let me install anything better."
        duskwuff 6y ago
        It definitely exists within Edge, as that's Chromium-based. Not sure it's available in a standalone form, though.
        regularfry 6y ago
snidane 6y ago
If you're using sqlite, but for a static website, you might consider using duckdb. It's still evolving, but it has huge potential. It's also an embedded single file database like sqlite, but stores data in columns. Duckdb to sqlite is like redshift to postgres. Hopefully they will add compression for string columns, so that host your entire static site from a single file in a very efficient way. https://duckdb.org/ https://duckdb.org/ https://youtu.be/PFUZlNQIndo https://youtu.be/PFUZlNQIndo
- scary-size 6y ago
  It literally says OLAP on their website. Why would I use this for a static website?
  - snidane 6y ago
    If you have a static website with a lot of data behind it, eg. a map visualizing some larger dataset, then OLAP database will be actually a very good backing storage.
    - setr 6y ago
      That seems like an oddly specific thing to assume when suggesting it for use by static websites generally — afaict most static sites would see no distinct benefit from columnar storage. That said, SQLite for OLAP is very neat.
teekert 6y ago
Hmm, NextCloud always warns against sqlite (but it is the default in the Linux Server IO docker image. It would be very easy, and nice, if it turned out sqlite was fine. It makes backing up easier, and would clean up my docker-compose.yaml.
- jiofih 6y ago
  Cloud volumes are usually IOPS-challenged and have shitty durability guarantees. If you’re going this route you should use a VPS or dedicated server instead.
  - teekert 6y ago
    It's in my basement on a Corei3 with a decent Samsung nvme ssd.
    - jiofih 6y ago
      You said docker. Doesn’t matter where it’s physically running, just that the persistence guarantees are not the same for containers.
      - teekert 6y ago
        I was addressing the "IOPS-challenged and have shitty durability guarantees". I don't use cloud volumes, so this shouldn't be a problem. I guess I was hoping for someone to tell me sqlite is fine in my case ;)
        jiofih 6y ago
        Ah, “shitty durability guarantees” comes from the container orchestration, not any downside of cloud hardware. I mixed up two likely reasons :)
ithrow 6y ago
If you put for example postgresql on equal footing, application and database in the same server (over unix sockets), then I guess 200 SQL statements is not a problem either for the timeline example.
- catmanjan 6y ago
  Wouldn't it still be over the network/IPC, unlike SQLite?
  - unnouinceput 6y ago
    No. It will be internal port to port connection. And it also depends on the driver. You can definitely have a crappy SQLite driver for your obscure OS that can absolutely take a lot of time for those 200 queries.
    - jononor 6y ago
      It is still IPC (inter-process communication) even though it wont go over any network. Probably still order of magnitude more overhead than a function call, but quite possibly it will be acceptable.
      - unnouinceput 6y ago
        None is stopping you to write a PGSQL driver that uses memory-mapped files and then your calls are as fast as a function call. Like I said, it all depends on the driver.
jokoon 6y ago
Question : I'm building a 3d procedural geometry generator and I need to store and index it by tiles for hundreds of kilometers. I'm curious if it can be efficient to store polygonal data in a database instead of a dedicated file format. I'm never sure how to properly copy raw struct binary data from sql, directly in ram. I think avoiding parsing the text output result in better performance, but I'm not entirely sure...
- oblio 6y ago
  Blob? https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html
- ashkankiani 6y ago
  Storing small blobs is a use case where SQLite often excels. By small, I mean in the range of <400kB. That is, if those are usually write-once, read-only. I can't remember the paper right now, but one of the results was that if you are repeatedly overwriting rows, then the performance will degrade gradually (until you VACUUM, presumably). I've used SQLite for this quite often, since it's convenient to store additional metadata that can be queried with these blobs. I know of a person who uses an SQLite database for storing emojis in a chat client, as well. You can open blobs directly as binary data, as well. Almost like files. See sqlite_blob_open and friends. The question you should ask, though, is what is your access/write patterns? Once you know where SQLite excels, you can accurately assess whether it fits your use-case. Without knowing more details, I can't say one way or another.
- eerikkivistik 6y ago
  3dcitydb with PostGIS on Postgres is pretty neat for those kinds of operations.
- yarcob 6y ago
  It sounds like PostGIS might be a good fit. It gives you a lot of tools for accessing and indexing geometry data, and it can scale to huge sizes. Parsing text formats is unlikely to be a bottleneck, accessing the data is more likely to be the bottleneck. The correct data structure (schema) and indexes are going to make the biggest difference. If you do complex computations on the data, and it fits in RAM, you'll probably see the best performance if you first load the data into data structures optimized for the computation first, and it's less important how you store the data on disk.
_flux 6y ago
I can imagine that. However, if you ever need to move out from SQLite to anything that is behind the network, the performance will tank—and if you've really taken advantage of this fast round-trip, refactoring them into fewer larger queries may turn out to be quite a task.
yread 6y ago
Indeed. In my app using SQLite I log every query that takes longer than 5ms (the db is a few GBs, largest table about 1m rows). It logs very little
tutfbhuf 6y ago
You exchange network access against filesystem access, but what if your filesystem is a network device?
- config_yml 6y ago
  SQLite recommends not using network storage: > You are advised to avoid using SQLite on a network filesystem in the first place, since performance will be slow https://sqlite.org/atomiccommit.html#_broken_locking_implementations https://sqlite.org/atomiccommit.html#_broken_locking_impleme...
  - tutfbhuf 6y ago
    That's a serious limitation since many VMs in datacenters have block devices attached over network.
    - striking 6y ago
      I think this is true for any RDBMS, though. If your block device has high latency, you will see bad perf when the DB tries to fsync.
      - tutfbhuf 6y ago
        But the block device latency is usually a order of magnitude lower (microseconds) than the network latency between the database and its clients (milliseconds), except when all clients are located in the same datacenters.
    - stefanfisk 6y ago
      ”Network filesystems” means stuff like NFS. Networked block devices should generellt be fine AFAIK.
- madpata 6y ago
  You exchange network+filesystem access against only filesystem acess. The server needs to store your DB somehow too.
slmjkdbtl 6y ago
sqlite pages are all pleasure to read, all the badass statements reminds me of the classic: SQLite does not compete with client/server databases. SQLite competes with fopen().
kumarvvr 6y ago
For anything but the smallest ones, I would prefer to use PostgreSQL. SQLite has too many quirks, and I sincerely feel it messes our thought process when working on Postgres or the like. As a file format, it works awesome, but it has its uses and limits.
pachico 6y ago
I've been running it in production successful for years. I have a read-only service that exposes Geonames gazetteer using SQLite and it's simply beautiful. I can deploy both code and dataset at the same time.
mcv 6y ago
I've been thinking lately that, in the unlikely event that I'll ever write a big computer game, I'd like to use SQLite as the save file. Too many games take ages to save or load the state, whereas updating a database with every state change seems much more painless. I don't have any experience with SQLite, but this article makes it sound like my idea would work very well.
- unnouinceput 6y ago
  Saving is usually done almost instantly because you just write a file. Loading is another beast on itself because the game engine needs to prepare the world. That's where it lies the most time, not in reading a file that has like couple MB on disk. Sure, go with SQLite if you want as a save, it won't matter anyway in the big picture.
Aeolun 6y ago
Eh, we used to do 20k mysql queries on a single page like 10 years ago and it worked well enough to not notice at first. Of course eventually we “fixed” that, and things got faster, but it was still impressive.
- enriquto 6y ago
  > we used to do 20k mysql queries on a single page As a non-web developer, this is something that surprises me greatly. Why does loading a single page ever need to do a sql query? Can't you most often just generate all the possible static pages that you want to serve? Is your db content really changing every second?
  - unnouinceput 6y ago
    e-commerce sites.
    - enriquto 6y ago
      That's exactly what I was thinking about. For example, last weekend I was looking at the products offered by an online bike shop. There were at most a few thousand pages showing product lists with prices. Some of these pages took more than a second (!) to load. What the hell is going on? What's the server doing all that time? That should be instantaneous. It can be solved by a small folder of static html pages, re-generated when stock changes a few times per week. I'm not a web developer so maybe what I say does not make sense and sounds stupid. But I'm just astonished by what process this happens to be a complicated problem.
      - fogihujy 6y ago
        Based on the numerous e-commerce web sites I see on a daily basis, it's usually one or more of the following: * Inefficient, disabled, or non-existing caching. * Random SEO plug-ins/add-ons doing things like queries on non-indexed data or insanely convoluted JOINs. * Logging every single HTTP request into a single table that nobody ever remembers to truncate. * Multiple queries for each page in order to recommend similar products in the page footer or on some widget. * Checking whether the visitor is logged in, and if there's anything in the basket yet. * Checking whether there's an active discount for the current user/product combination. It's really quite ridiculous. The worst offenders are WordPress/WooCommerce sites. Dedicated e-commerce solutions usually fare better.
reacharavindh 6y ago
I have only worked with PostgreSQL and when the wife(Accounting Professor) needed a DB to teach their students SQL, I heartily took her down the SQLite path because of its simplicity. It may not have been the best decision as I see her now fighting with its warts like 1. No type checking! Declare a column as int in DDL, and insert a text, SQLite happily stores it without an error :-( 2. No full outer join... instead my wife taught the students the standard SQL syntax and told them to use UNION with SQLite because it does not support that syntax. This is where it gets annoying. I can’t recollect, but there were issues with views as well which sounded really weird. I now think the choice of SQLite is more nuanced than I believed it to be.
- m1sta_ 6y ago
  Duckdb is like sqlite but with the postgres SQL parser.
  - masklinn 6y ago
    Does it also have the features? Like a returning clause? Or is it similarly gimped and restricted to a half-assed thread-incoherent function? edit: I'll take the downvotes as a no, and apparently last_insert_rowid somehow has fans.
- grandinj 6y ago
  try h2database.com if you don't mind Java, has pretty full SQL support (except for weak support for CTEs) Disclaimer: H2 dev
  - alimbada 6y ago
    H2 is great. Thanks for your work on it. We used it on my last project as an in memory database for running interaction tests between Dropwizard resources and the database in our CI pipeline. We had Liquibase running migrations during the setup for each test and DbUnit setting up test data.
  - listenallyall 6y ago
Ziinc 6y ago
I just started work on a simple state management tool based on SQLite [0], utilising SQL.js under the hood. Just wrote it last week too! It intends to be much easier to learn than redux and mobx, but more powerful than a react's context. In theory, it should support most of redux's ecosystem too, such as reselect, though I have yet to create examples for it. [0]: https://github.com/ziinc/memlite https://github.com/ziinc/memlite
thefrog 6y ago
Okay. And?
simonw 6y ago
An interesting consequence of this characteristic of SQLite is that it makes it a really good fit for GraphQL. GraphQL has the N+1 queries problem pretty much baked into it. You can work around it to a certain extent with a lot of effort implementing data loader patterns, but someone will still likely be able to cow up with a query that triggers a cascade of SELECTs. Or, because "many small queries are efficient in SQLite", you could use that instead. I built a Datasette plugin that exposes a GraphQL API to an existing SQLite database and it works so well that I switched from being a GraphQL skeptic to a GraphQL advocate. https://simonwillison.net/2020/Aug/7/datasette-graphql/ https://simonwillison.net/2020/Aug/7/datasette-graphql/ Here's an example nested GraphQL query that executes 32 select queries. It's pretty snappy: https://datasette-graphql-demo.datasette.io/graphql?query=%7B%0A%20%20repos(first%3A%2010%2C%20search%3A%20%22datasette%22%2C%20sort_desc%3A%20stargazers_count)%20%7B%0A%20%20%20%20totalCount%0A%20%20%20%20pageInfo%20%7B%0A%20%20%20%20%20%20endCursor%0A%20%20%20%20%20%20hasNextPage%0A%20%20%20%20%7D%0A%20%20%20%20nodes%20%7B%0A%20%20%20%20%20%20full_name%0A%20%20%20%20%20%20description%0A%20%20%20%20%20%20stargazers_count%0A%20%20%20%20%20%20created_at%0A%20%20%20%20%20%20owner%20%7B%0A%20%20%20%20%20%20%20%20name%0A%20%20%20%20%20%20%20%20html_url%0A%20%20%20%20%20%20%7D%0A%20%20%20%20%20%20issues_list(first%3A%203)%20%7B%0A%20%20%20%20%20%20%20%20totalCount%0A%20%20%20%20%20%20%20%20nodes%20%7B%0A%20%20%20%20%20%20%20%20%20%20title%0A%20%20%20%20%20%20%20%20%20%20created_at%0A%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%20%20%7D%0A%20%20%20%20%7D%0A%20%20%7D%0A%7D%0A https://datasette-graphql-demo.datasette.io/graphql?query=%7...
- tehlike 6y ago
  hasura does fairly good query optimization using postgres json_agg, that's my preferred way - if more graphql engines did the same that'd be amazing.
- rtorr 6y ago
  It does not have that problem baked into it since it relies on resolvers. It would have to be a choice to have that problem.
  - ubercore 6y ago
    A straightforward implementation certainly would have that problem. You have to put a decent amount of effort in to reduce query count to fill a nested query graph.
orware 6y ago
As an aside (or perhaps related note), over this past weekend I was doing my first real-world usage of SQLite, since I typically rely on conventional databases for my day-to-day work. I've read the article shared here in this thread in the past and read about the usefulness of SQLite generally, particularly for read-heavy situations, so I thought it would be a nice experiment to pull in the "Have I Been Pwned Password" List and import it into the SQLite and see how fast it could be queried. At first I didn't find too many resources out there of the same thing having been done, but after searching some more throughout the weekend I did find out some nice nuggets of information that helped (in particular, I learned that adding the "WITHOUT ROWID" option at the end of a SQLite CREATE TABLE statement will help to reduce the overall database size considerably). The desire to put things into SQLite for the "Have I Been Pwned Password" actually stemmed from a misinterpretation of the API page since it seemed at first that the API was not free (although I had thought it was), since one of the first things I read was the need for a $3.50/mo charge. Later on, I then figured out that the password checking API is still free, it's just the other pieces of the API that require the API Key / monthly charges (doh!). If you run a query against Troy's API, it's pretty damn fast all things considered, so I'll likely revert to using his API directly, but I was thinking of uploading the SQLite versions to some cloud hosting somewhere and potentially share it with others if anybody is interested. On to some quick stats collected over the weekend (using an import process powered by some PowerShell): 50 million took ~6 hours 22 minutes to complete (with ROWID): Start Time: Sunday, February 14, 2021 9:12:29 AM End Time: Sunday, February 14, 2021 3:34:46 PM 50 million took ~5 hours 46 minutes to complete (without ROWID): Start Time: Sunday, February 14, 2021 7:51:38 PM End Time: Monday, February 15, 2021 2:05:30 AM File size comparison: 5.28 GB (50 million rows, with ROWID) 2.61 GB (50 million rows, without ROWID) Search Time comparison: Example performance of Select-String (line-by-line search): TotalMilliseconds : 216245.7246 Example performance of the SQL query: TotalMilliseconds : 20.8543 I then ended up finding this implementation of a Binary Search option (rather than the naive approach Select-String takes above) that Chris Dent put together here: https://www.indented.co.uk/powershell-file-based-binary-search/ https://www.indented.co.uk/powershell-file-based-binary-sear... Example performance of this approach was more in line with the SQLite database: TotalMilliseconds : 40.3749 (For these runs comparing against the Binary Search option, the SQLite query ran in): TotalMilliseconds : 4.9491 All in all, now that I have discovered the Binary Search option that one would be workable without needing to do anything at all (except to make sure to download the "Ordered By Hash" version of the hashed password list) and use it directly, although I believe once the import has completed of the full password file into SQLite (using the WITHOUT ROWID option) the file size for the database should be fairly close to the actual txt file size (the text file size is 25.1 GB currently with close to 630 million lines in it...based on the smaller batch conversions to SQLite, I think the full import should be in the low 30 GB range). Tagging along with the top comment in this thread however, I do see what they mean about insert performance since I did try a few experiments there as well, but went ahead and kept things with the 10k batch inserts at a time (I think with individual inserts I was calculating things to potentially take 216 days or something ridiculuous, and switching to batch inserts made it go down to about 3 days, but based on some comments I read elsewhere over the weekend, even that seemed like it might be kind of high...at the moment I'm trying a full import outside of my PowerShell script using DBeaver's functionality for doing so, so I'll see if it completes faster...seems like it might since based on the current database's size it seems like it's more than 50% complete and it's probably only been about 12 hours).
marvinalone 6y ago
One of your queries is `SELECT uid FROM user WHERE cap LIKE '%s%';`. I can only assume your users table is tiny. How does it do with 1M users?
sbierwagen 6y ago
My only complaint about SQLite is that on https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html it reads: >REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number. When I read this I parsed 8-byte as 8-bit, and made several embarrassingly incorrect design decisions when creating a table as a result.

> For a 50-entry timeline, the latency is usually less than 25 milliseconds. There are a lot of good ideas which don't scale down to a smaller single-core system. For example, there's a CROSS JOIN in that example, which is just fine at the row-counts we might encounter on sqlite. The general low-scale workloads + the in-process operation (the SQL query is a function call, not RPC), means that sqlite can avoid needing the complex query to compress the user intent into a single round-trip. This doesn't apply in general & the article doesn't claim to do so. In the past, I've had production systems which are blocked on mysql_parse() rather than the actual disk IO (anyone remember the mysql Drizzle client side optimizations & the Dynamic SQL extensions?). The simplicity of 1+N is basically a "simple enough and it always works", plus in a few case we can actually use a different projection for the N queries. As a side note to Fossil, I've gone spelunking into the SVN repos with sqlite (there's a .db inside your .svn/ if you still use it) & what you'd end up using is a lot of 1+N queries (ACTUAL_NODE).

nice2meetu 6y ago
CROSS JOIN in sqlite3 is used to force the order of the tables in the join. Note the query has a WHERE condition so is the same as an INNER JOIN https://www.sqlite.org/optoverview.html#manual_control_of_query_plans_using_cross_join https://www.sqlite.org/optoverview.html#manual_control_of_qu...
throwaway912598 6y ago
From a live table (there are about 10 of these in the database): sqlite> select count(*) from <redacted>; 49862386 Curious what row count you expect in non-SQLite databases on a regular basis.

senderista 6y ago

You can have a client/server database which is just as efficient as a single-process embedded database like SQLite, if that database uses shared memory.

jbverschoor 6y ago
Exactly. I have the feeling there is just a little bit too much sqlite-hype going on lately.

Laminary 6y ago

If you haven't tried SQLite, please do. For years I ignored SQLite and used MySQL (it does the job) but once you see how fast SQLite is, and advantages of having a DB contained in a single file... just go play around with SQLite instead of ignoring it for years like me. It's neat.

alexchamberlain 6y ago
The main challenge there is how to you ensure your database is resilient to machine or datacentre outages? ie what happens if the 1 server with the database is in a datacentre that loses Internet connectivity?
- zdkl 6y ago
  SQlite "merely" assumes that the problems that come with distributed systems are handled at the application layer. You'll have to solve those problems for yourself, sure, but in practice I have rarely (I think never actually) had dataloss through a fault of sqlite. Also, did you know you can use in-memory instances (and share them across threads!) with the right incantation? And that you can backup your on-disk instance to an in-memory one, do your expensive transactions without hitting the disk then backup the modified instance right back to disk, even in-place if you want! Sqlite is amazing when you don't expect the DB to do replication or failover on its own.
  - krab 6y ago
    There is always DRBD as well. You can make replication a lower-layer problem. Not that it's without drawbacks.
  - iveqy 6y ago
    No I did not know that, I've looked for a long time for a way to convert a sqlite3 database to an in memory database and then back again. Do you mean that there's support in sqlite3 for this? Could you point me in the right direction?
    - 6y ago

ThePadawan 6y ago

> The Appropriate Uses For SQLite page says that dynamic pages on the SQLite website typically do about 200 SQL statements each. This has provoked criticism from readers. Examples: > "200 SQL statements is a ridiculously high number for a single page" > "For most sites, 200 queries is way, way, way too much." > "[This is] bad design" Funnily enough, at a recent workplace the guideline was "if it only does 200 queries, you've optimized it enough".

PurpleFoxy 6y ago
I can relate to this. It’s so easy to write a some code that fires off 10 queries but it’s obscured by the ORM and then it’s in an index listing which isn’t paginated and eventually some user does things weird and it ends up with 1000 items so this one listing runs 10,000 queries.
- TeMPOraL 6y ago
  In my experience, it doesn't take the user doing anything weird - all it takes is to use ORM enough in the project, and suddenly you have hundreds of separate queries being run to fetch something trivial, which could be done in one or two queries of hand-written SQL.
  - matthewmacleod 6y ago
    In my experience, those "one or two queries of hand-written SQL" quickly balloon out of control into a litany of crimes, because the assumption they will be better relies on having the kind of developer who can easily write "one or two queries of hand-written SQL" but are somehow incapable of reading the documentation or using an ORM correctly.
    - wtetzner 6y ago
      Writing SQL is so much easier than understanding ORMs. ORMs tend to be very complex, because the problem they're trying to solve requires that complexity. Now maybe my perspective is biased too much, since the only ORM I've really used is Hibernate. But in that experience, I learned that Hibernate brings a ton of complexity with no real value. Seems like a high price to pay to avoid learning SQL. The sweet spot seems to be using a library like JOOQ, which lets you write queries, as well as safely construct queries at runtime.

LunaSea 6y ago

The limited type system and loose data validation always prevented me of using SQLite. It's a nice data store but a poor database in my opinion.

hans_castorp 6y ago
> The limited type system and loose data validation always prevented me of using SQLite. This. "limited type system" is actually an exaggeration in my opinion. The "data types" seem to only serve documentation purposes. You can always store any value in any column regardless of the "data type"
- tpetry 6y ago
  Wait? Your can for example put an unicode string into integer or number columns? That can‘t be true? I mean, really? ...
  - ludwigschubert 6y ago
    Yeah. Execute program has a short and sweet explanation with in-browser demonstration: https://www.executeprogram.com/courses/sql/lessons/no-type-enforcement-in-sqlite https://www.executeprogram.com/courses/sql/lessons/no-type-e...
  - IshKebab 6y ago
    It is. They even consider it a feature. You don't actually need to specify column types at all. You can just leave them out. Or make up entirely new types. Foreign key constraints are also not enforced by default but there is at least an option to turn that on.
  - hans_castorp 6y ago
    > Wait? Your can for example put an unicode string into integer or number columns? Yes https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=4634e3821676ed28e27c06d7c9c0af46 https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=4634e3821676ed...
  - 6y ago

curiousmindz 6y ago

An important reminder is to use the right tool for the job. SQLite is indeed amazing at what it does, and fits with many scenarios where it isn't often considered, but it isn't a silver bullet. If you have a scenario where you must have multiple machines (for redundancy or resiliency), then SQLite may not be the "best" choice. However, if these machines were needed for performance reason, you may find that a single well-written SQLite-based solution can be performant enough to run on a single machine (while reaping all the benefits of its simpler approach).

nine_k 6y ago
Creating read replicas has become easy with SQLite, thanks to Litestream (recently on HN: https://news.ycombinator.com/item?id=26103776 https://news.ycombinator.com/item?id=26103776). What is really hard with SQLite is efficient concurrent updates. But you don't need them very often.
- pbowyer 6y ago
  What I've never got to grips with is the "use a single writer with SQLite" advice. That seems doable with a long-running application server, but if your application boots up on every request (like PHP, Python & Ruby applications generally do) how do you do this, when you have multiple simultaneous users? It feels like SQLite is missing a separate gatekeeper-binary to act as the single writer: the database server as it were.
  - liuliu 6y ago
    SQLite uses file lock to coordinate with many processes. Your performance will be similar to use single writer if we ignore the repeated DB open / close due to the app boot on every request. I am not sure about real-world DB open / close cost. Because it is considered good practice to always use some kinds of SQLite connection pool if you need many readers / writers. Didn't get a chance to try.
  - jononor 6y ago
    With Python at least an application server with long-running processes is the most common way to deploy, for example with gunicorn. Though typical config will have green threads and/or multiple worker processes, so I am not quite sure how one the single-writer is enforced.

skc 6y ago

It's ridiculously tempting to try and use SQLite for everything. It's so ridiculously useful and capable.

sgt 6y ago
Your smart phone most likely has it already.
- dmlittle 6y ago
  I might be mistaking things here but I believe that Apple's CoreData and Android's equivalent are both powered by SQLite under the hood. Messages on macOS stores all your message in a SQLite file under ~/Library/Messages/chat.db
  - duskwuff 6y ago
    macOS and iOS both use SQLite pervasively. There's tons of little databases all over. Chrome uses SQLite heavily for history, cookies, bookmarks… etc. So does Firefox. SQLite is everywhere. :)
    - regularfry 6y ago
      It's everywhere to the extent that I find it hard to believe it's not lurking somewhere on a default Windows install. I would very much like to be able to say to people "do not use excel for that. It will make you cry" and not have the response "corporate IT won't let me install anything better."
      - duskwuff 6y ago
        It definitely exists within Edge, as that's Chromium-based. Not sure it's available in a standalone form, though.
        regularfry 6y ago

snidane 6y ago

If you're using sqlite, but for a static website, you might consider using duckdb. It's still evolving, but it has huge potential. It's also an embedded single file database like sqlite, but stores data in columns. Duckdb to sqlite is like redshift to postgres. Hopefully they will add compression for string columns, so that host your entire static site from a single file in a very efficient way. https://duckdb.org/ https://duckdb.org/ https://youtu.be/PFUZlNQIndo https://youtu.be/PFUZlNQIndo

scary-size 6y ago
It literally says OLAP on their website. Why would I use this for a static website?
- snidane 6y ago
  If you have a static website with a lot of data behind it, eg. a map visualizing some larger dataset, then OLAP database will be actually a very good backing storage.
  - setr 6y ago
    That seems like an oddly specific thing to assume when suggesting it for use by static websites generally — afaict most static sites would see no distinct benefit from columnar storage. That said, SQLite for OLAP is very neat.

teekert 6y ago

Hmm, NextCloud always warns against sqlite (but it is the default in the Linux Server IO docker image. It would be very easy, and nice, if it turned out sqlite was fine. It makes backing up easier, and would clean up my docker-compose.yaml.

jiofih 6y ago
Cloud volumes are usually IOPS-challenged and have shitty durability guarantees. If you’re going this route you should use a VPS or dedicated server instead.
- teekert 6y ago
  It's in my basement on a Corei3 with a decent Samsung nvme ssd.
  - jiofih 6y ago
    You said docker. Doesn’t matter where it’s physically running, just that the persistence guarantees are not the same for containers.
    - teekert 6y ago
      I was addressing the "IOPS-challenged and have shitty durability guarantees". I don't use cloud volumes, so this shouldn't be a problem. I guess I was hoping for someone to tell me sqlite is fine in my case ;)
      - jiofih 6y ago
        Ah, “shitty durability guarantees” comes from the container orchestration, not any downside of cloud hardware. I mixed up two likely reasons :)

ithrow 6y ago

If you put for example postgresql on equal footing, application and database in the same server (over unix sockets), then I guess 200 SQL statements is not a problem either for the timeline example.

catmanjan 6y ago
Wouldn't it still be over the network/IPC, unlike SQLite?
- unnouinceput 6y ago
  No. It will be internal port to port connection. And it also depends on the driver. You can definitely have a crappy SQLite driver for your obscure OS that can absolutely take a lot of time for those 200 queries.
  - jononor 6y ago
    It is still IPC (inter-process communication) even though it wont go over any network. Probably still order of magnitude more overhead than a function call, but quite possibly it will be acceptable.
    - unnouinceput 6y ago
      None is stopping you to write a PGSQL driver that uses memory-mapped files and then your calls are as fast as a function call. Like I said, it all depends on the driver.

jokoon 6y ago

Question : I'm building a 3d procedural geometry generator and I need to store and index it by tiles for hundreds of kilometers. I'm curious if it can be efficient to store polygonal data in a database instead of a dedicated file format. I'm never sure how to properly copy raw struct binary data from sql, directly in ram. I think avoiding parsing the text output result in better performance, but I'm not entirely sure...

oblio 6y ago
Blob? https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html
ashkankiani 6y ago
Storing small blobs is a use case where SQLite often excels. By small, I mean in the range of <400kB. That is, if those are usually write-once, read-only. I can't remember the paper right now, but one of the results was that if you are repeatedly overwriting rows, then the performance will degrade gradually (until you VACUUM, presumably). I've used SQLite for this quite often, since it's convenient to store additional metadata that can be queried with these blobs. I know of a person who uses an SQLite database for storing emojis in a chat client, as well. You can open blobs directly as binary data, as well. Almost like files. See sqlite_blob_open and friends. The question you should ask, though, is what is your access/write patterns? Once you know where SQLite excels, you can accurately assess whether it fits your use-case. Without knowing more details, I can't say one way or another.
eerikkivistik 6y ago
3dcitydb with PostGIS on Postgres is pretty neat for those kinds of operations.
yarcob 6y ago
It sounds like PostGIS might be a good fit. It gives you a lot of tools for accessing and indexing geometry data, and it can scale to huge sizes. Parsing text formats is unlikely to be a bottleneck, accessing the data is more likely to be the bottleneck. The correct data structure (schema) and indexes are going to make the biggest difference. If you do complex computations on the data, and it fits in RAM, you'll probably see the best performance if you first load the data into data structures optimized for the computation first, and it's less important how you store the data on disk.

_flux 6y ago

I can imagine that. However, if you ever need to move out from SQLite to anything that is behind the network, the performance will tank—and if you've really taken advantage of this fast round-trip, refactoring them into fewer larger queries may turn out to be quite a task.

yread 6y ago

Indeed. In my app using SQLite I log every query that takes longer than 5ms (the db is a few GBs, largest table about 1m rows). It logs very little

tutfbhuf 6y ago

You exchange network access against filesystem access, but what if your filesystem is a network device?

config_yml 6y ago
SQLite recommends not using network storage: > You are advised to avoid using SQLite on a network filesystem in the first place, since performance will be slow https://sqlite.org/atomiccommit.html#_broken_locking_implementations https://sqlite.org/atomiccommit.html#_broken_locking_impleme...
- tutfbhuf 6y ago
  That's a serious limitation since many VMs in datacenters have block devices attached over network.
  - striking 6y ago
    I think this is true for any RDBMS, though. If your block device has high latency, you will see bad perf when the DB tries to fsync.
    - tutfbhuf 6y ago
      But the block device latency is usually a order of magnitude lower (microseconds) than the network latency between the database and its clients (milliseconds), except when all clients are located in the same datacenters.
  - stefanfisk 6y ago
    ”Network filesystems” means stuff like NFS. Networked block devices should generellt be fine AFAIK.
madpata 6y ago
You exchange network+filesystem access against only filesystem acess. The server needs to store your DB somehow too.

slmjkdbtl 6y ago

sqlite pages are all pleasure to read, all the badass statements reminds me of the classic: SQLite does not compete with client/server databases. SQLite competes with fopen().

kumarvvr 6y ago

For anything but the smallest ones, I would prefer to use PostgreSQL. SQLite has too many quirks, and I sincerely feel it messes our thought process when working on Postgres or the like. As a file format, it works awesome, but it has its uses and limits.

pachico 6y ago

I've been running it in production successful for years. I have a read-only service that exposes Geonames gazetteer using SQLite and it's simply beautiful. I can deploy both code and dataset at the same time.

mcv 6y ago

I've been thinking lately that, in the unlikely event that I'll ever write a big computer game, I'd like to use SQLite as the save file. Too many games take ages to save or load the state, whereas updating a database with every state change seems much more painless. I don't have any experience with SQLite, but this article makes it sound like my idea would work very well.

unnouinceput 6y ago
Saving is usually done almost instantly because you just write a file. Loading is another beast on itself because the game engine needs to prepare the world. That's where it lies the most time, not in reading a file that has like couple MB on disk. Sure, go with SQLite if you want as a save, it won't matter anyway in the big picture.

Aeolun 6y ago

Eh, we used to do 20k mysql queries on a single page like 10 years ago and it worked well enough to not notice at first. Of course eventually we “fixed” that, and things got faster, but it was still impressive.

enriquto 6y ago
> we used to do 20k mysql queries on a single page As a non-web developer, this is something that surprises me greatly. Why does loading a single page ever need to do a sql query? Can't you most often just generate all the possible static pages that you want to serve? Is your db content really changing every second?
- unnouinceput 6y ago
  e-commerce sites.
  - enriquto 6y ago
    That's exactly what I was thinking about. For example, last weekend I was looking at the products offered by an online bike shop. There were at most a few thousand pages showing product lists with prices. Some of these pages took more than a second (!) to load. What the hell is going on? What's the server doing all that time? That should be instantaneous. It can be solved by a small folder of static html pages, re-generated when stock changes a few times per week. I'm not a web developer so maybe what I say does not make sense and sounds stupid. But I'm just astonished by what process this happens to be a complicated problem.
    - fogihujy 6y ago
      Based on the numerous e-commerce web sites I see on a daily basis, it's usually one or more of the following: * Inefficient, disabled, or non-existing caching. * Random SEO plug-ins/add-ons doing things like queries on non-indexed data or insanely convoluted JOINs. * Logging every single HTTP request into a single table that nobody ever remembers to truncate. * Multiple queries for each page in order to recommend similar products in the page footer or on some widget. * Checking whether the visitor is logged in, and if there's anything in the basket yet. * Checking whether there's an active discount for the current user/product combination. It's really quite ridiculous. The worst offenders are WordPress/WooCommerce sites. Dedicated e-commerce solutions usually fare better.

reacharavindh 6y ago

I have only worked with PostgreSQL and when the wife(Accounting Professor) needed a DB to teach their students SQL, I heartily took her down the SQLite path because of its simplicity. It may not have been the best decision as I see her now fighting with its warts like 1. No type checking! Declare a column as int in DDL, and insert a text, SQLite happily stores it without an error :-( 2. No full outer join... instead my wife taught the students the standard SQL syntax and told them to use UNION with SQLite because it does not support that syntax. This is where it gets annoying. I can’t recollect, but there were issues with views as well which sounded really weird. I now think the choice of SQLite is more nuanced than I believed it to be.

m1sta_ 6y ago
Duckdb is like sqlite but with the postgres SQL parser.
- masklinn 6y ago
  Does it also have the features? Like a returning clause? Or is it similarly gimped and restricted to a half-assed thread-incoherent function? edit: I'll take the downvotes as a no, and apparently last_insert_rowid somehow has fans.
grandinj 6y ago
try h2database.com if you don't mind Java, has pretty full SQL support (except for weak support for CTEs) Disclaimer: H2 dev
- alimbada 6y ago
  H2 is great. Thanks for your work on it. We used it on my last project as an in memory database for running interaction tests between Dropwizard resources and the database in our CI pipeline. We had Liquibase running migrations during the setup for each test and DbUnit setting up test data.
- listenallyall 6y ago

Ziinc 6y ago

I just started work on a simple state management tool based on SQLite [0], utilising SQL.js under the hood. Just wrote it last week too! It intends to be much easier to learn than redux and mobx, but more powerful than a react's context. In theory, it should support most of redux's ecosystem too, such as reselect, though I have yet to create examples for it. [0]: https://github.com/ziinc/memlite https://github.com/ziinc/memlite

thefrog 6y ago

Okay. And?

simonw 6y ago

An interesting consequence of this characteristic of SQLite is that it makes it a really good fit for GraphQL. GraphQL has the N+1 queries problem pretty much baked into it. You can work around it to a certain extent with a lot of effort implementing data loader patterns, but someone will still likely be able to cow up with a query that triggers a cascade of SELECTs. Or, because "many small queries are efficient in SQLite", you could use that instead. I built a Datasette plugin that exposes a GraphQL API to an existing SQLite database and it works so well that I switched from being a GraphQL skeptic to a GraphQL advocate. https://simonwillison.net/2020/Aug/7/datasette-graphql/ https://simonwillison.net/2020/Aug/7/datasette-graphql/ Here's an example nested GraphQL query that executes 32 select queries. It's pretty snappy: https://datasette-graphql-demo.datasette.io/graphql?query=%7B%0A%20%20repos(first%3A%2010%2C%20search%3A%20%22datasette%22%2C%20sort_desc%3A%20stargazers_count)%20%7B%0A%20%20%20%20totalCount%0A%20%20%20%20pageInfo%20%7B%0A%20%20%20%20%20%20endCursor%0A%20%20%20%20%20%20hasNextPage%0A%20%20%20%20%7D%0A%20%20%20%20nodes%20%7B%0A%20%20%20%20%20%20full_name%0A%20%20%20%20%20%20description%0A%20%20%20%20%20%20stargazers_count%0A%20%20%20%20%20%20created_at%0A%20%20%20%20%20%20owner%20%7B%0A%20%20%20%20%20%20%20%20name%0A%20%20%20%20%20%20%20%20html_url%0A%20%20%20%20%20%20%7D%0A%20%20%20%20%20%20issues_list(first%3A%203)%20%7B%0A%20%20%20%20%20%20%20%20totalCount%0A%20%20%20%20%20%20%20%20nodes%20%7B%0A%20%20%20%20%20%20%20%20%20%20title%0A%20%20%20%20%20%20%20%20%20%20created_at%0A%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%20%20%7D%0A%20%20%20%20%7D%0A%20%20%7D%0A%7D%0A https://datasette-graphql-demo.datasette.io/graphql?query=%7...

tehlike 6y ago
hasura does fairly good query optimization using postgres json_agg, that's my preferred way - if more graphql engines did the same that'd be amazing.
rtorr 6y ago
It does not have that problem baked into it since it relies on resolvers. It would have to be a choice to have that problem.
- ubercore 6y ago
  A straightforward implementation certainly would have that problem. You have to put a decent amount of effort in to reduce query count to fill a nested query graph.

orware 6y ago

As an aside (or perhaps related note), over this past weekend I was doing my first real-world usage of SQLite, since I typically rely on conventional databases for my day-to-day work. I've read the article shared here in this thread in the past and read about the usefulness of SQLite generally, particularly for read-heavy situations, so I thought it would be a nice experiment to pull in the "Have I Been Pwned Password" List and import it into the SQLite and see how fast it could be queried. At first I didn't find too many resources out there of the same thing having been done, but after searching some more throughout the weekend I did find out some nice nuggets of information that helped (in particular, I learned that adding the "WITHOUT ROWID" option at the end of a SQLite CREATE TABLE statement will help to reduce the overall database size considerably). The desire to put things into SQLite for the "Have I Been Pwned Password" actually stemmed from a misinterpretation of the API page since it seemed at first that the API was not free (although I had thought it was), since one of the first things I read was the need for a $3.50/mo charge. Later on, I then figured out that the password checking API is still free, it's just the other pieces of the API that require the API Key / monthly charges (doh!). If you run a query against Troy's API, it's pretty damn fast all things considered, so I'll likely revert to using his API directly, but I was thinking of uploading the SQLite versions to some cloud hosting somewhere and potentially share it with others if anybody is interested. On to some quick stats collected over the weekend (using an import process powered by some PowerShell): 50 million took ~6 hours 22 minutes to complete (with ROWID): Start Time: Sunday, February 14, 2021 9:12:29 AM End Time: Sunday, February 14, 2021 3:34:46 PM 50 million took ~5 hours 46 minutes to complete (without ROWID): Start Time: Sunday, February 14, 2021 7:51:38 PM End Time: Monday, February 15, 2021 2:05:30 AM File size comparison: 5.28 GB (50 million rows, with ROWID) 2.61 GB (50 million rows, without ROWID) Search Time comparison: Example performance of Select-String (line-by-line search): TotalMilliseconds : 216245.7246 Example performance of the SQL query: TotalMilliseconds : 20.8543 I then ended up finding this implementation of a Binary Search option (rather than the naive approach Select-String takes above) that Chris Dent put together here: https://www.indented.co.uk/powershell-file-based-binary-search/ https://www.indented.co.uk/powershell-file-based-binary-sear... Example performance of this approach was more in line with the SQLite database: TotalMilliseconds : 40.3749 (For these runs comparing against the Binary Search option, the SQLite query ran in): TotalMilliseconds : 4.9491 All in all, now that I have discovered the Binary Search option that one would be workable without needing to do anything at all (except to make sure to download the "Ordered By Hash" version of the hashed password list) and use it directly, although I believe once the import has completed of the full password file into SQLite (using the WITHOUT ROWID option) the file size for the database should be fairly close to the actual txt file size (the text file size is 25.1 GB currently with close to 630 million lines in it...based on the smaller batch conversions to SQLite, I think the full import should be in the low 30 GB range). Tagging along with the top comment in this thread however, I do see what they mean about insert performance since I did try a few experiments there as well, but went ahead and kept things with the 10k batch inserts at a time (I think with individual inserts I was calculating things to potentially take 216 days or something ridiculuous, and switching to batch inserts made it go down to about 3 days, but based on some comments I read elsewhere over the weekend, even that seemed like it might be kind of high...at the moment I'm trying a full import outside of my PowerShell script using DBeaver's functionality for doing so, so I'll see if it completes faster...seems like it might since based on the current database's size it seems like it's more than 50% complete and it's probably only been about 12 hours).

marvinalone 6y ago

One of your queries is `SELECT uid FROM user WHERE cap LIKE '%s%';`. I can only assume your users table is tiny. How does it do with 1M users?

sbierwagen 6y ago

My only complaint about SQLite is that on https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html it reads: >REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number. When I read this I parsed 8-byte as 8-bit, and made several embarrassingly incorrect design decisions when creating a table as a result.