271–280 of 330 posts
It started out life as a disposable engine for a decoy missile and so the engineers took a very lightweight approach to it and kept the costs down. It would later be adapted to be used for more permanent aircrafts and ended up being one of GE's most successful and longest serving engines.
Earlier quoted context omitted.
I would expect the performance of SQLite for queries against an index to outperform MySQL and PostgreSQL in all cases - because SQLite eliminates the need for network overhead by essentially executing those queries directly as a C function call. So no matter how optimized MySQL and PostgreSQL are, SQLite will run rings around them for basic SELECT queries.
Running against a unix socket doesn't seem to make that a selling point for SQLite.
Earlier quoted context omitted.
The risk is as I had written previously that it takes some effort to move away from a db to another when the need arises when I see no benefit in choosing SQLite in the beginning. I'm not a professional db engineer but one point is that there doesn't seem to be a way to create functions in SQLite which would mean creating triggers on various tables can cause excessive amount of duplicate code. If I rely on PostgreSQL…
That's not a risk, that's just an inefficiency further down the line (migrating data from sqlite to a "real" database can indeed be quite a chore, but far less so if you formalized your schema and constraints beforehand, so that a migration mostly involves exporting to SQL, rewriting where needed, and then importing into whatever dbms you end up using later on in the lifetime of your project). When we're talking abou…
When I realized SQLite would store any type of data in any kind of column type, it was obvious SQLite is different from others. They only added strict types about a year ago but scared me enough not to use it.
And how is SQLite any less secure? You can flat out copy its entirety using pg_dumpall. I'm not talking about security.
Earlier quoted context omitted.
What is the point of using SQLite under a web service? I thought people complained how MySQL sucks and PostgreSQL rocks for being right and SQLite was nowhere near being right or performant. (Things seem to be getting better with strict column types these days.) I've recently migrated a smallish service from MySQL to PostgreSQL and figured it's quite a work if you're not careful writing by the SQL standard which mean…
SQLite is far faster than Postgres or MySQL, however, the price you pay for this is having a single writer thread, and it's a library incorporated into your process, not a shared DB. It's faster because those other features of a server have a cost as well, particularly the cost of write arbitration. SQLite is fine when all your load can be served by a single backend process on a single machine. The moment you need mu…
Earlier quoted context omitted.
The Consider SQLite post mentions that one of SQLite’s in the past decade as “WAL mode (enabling concurrent reads and writes)”. Does this mean that the official advice to avoid SQLite for concurrent writes [1] is no longer a big concern? [1]: https://www.sqlite.org/whentouse.html
I think the way it's worded in that SQLite documentation page is still accurate: > SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writers queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that…
This may become an issue with large run-time datasets even in read-only shared access scenarios.
Here's an all-time great post about why you might consider SQLite in production with data about performance: https://blog.wesleyac.com/posts/consider-sqlite I use SQLite in production for my SaaS[1]. It's really great — saves me money, required basically no setup/configuration/management, and has had no scaling issues whatsoever with a few million hits a month. SQLite is really blazing fast for typical SaaS workloads…
The reason I decided against it is that it doesn’t have proper stored procedures. I use them a lot in PGSQL. They result in far fewer lines of code.
They also have the benefit of significantly reducing round trip calls to the database, which is one of the key advantages of SQLite.
But having used stored procedures for years, I can no longer bear the thought of writing SQL code in a host language, so I’m going to stick with PG for the time being.
Would be great to see something similar in SQLite; there are other advantages such as the single file database, that would work well in a microservice environment.
I'm using SQLite for a small personal project that's live in production and so far I love it (both for its simplicity in development and for its performance). But I've run into on prod that didn't exist in dev on my MacBook M1, and I'm curious if anyone has any suggestions: My app is basically quiet and serves requests in the dozens (super easy to run on a tiny instance), but for a few hours a day it needs to run sev…
How do you deal with the downtime. I also find myself constantly resizing instances (although for data analysis where I sometimes need 80+ cores only for a few hours), and the constant restarting and reloading drives me crazy Maybe for sqlite because it's stored in ssd, it's only 1-2 minutes to shutdown+change parameter+restart if it's scripted?
Earlier quoted context omitted.
Take this with a huge grain of salt because I am by no means an expert, but I currently am working on some scripts that import a few million rows into SQLite. I am using bash and the sqlite command line. I was getting a lot of concurrent write errors from sqlite (bear in mind i am only doing inserts of separate rows so in theory there is never an actual conflict), so I tried using WAL mode. It actually resulted in mo…
are you ingesting the data under 1 transaction? This is a common SQLite issue as writes aren't slow but transactions are. By default 1 write = 1 txn but you can put millions of writes into 1 txn and get many orders of magnitude speedup
What I learned from that though is that I would never use it for actual business software, no matter the scale. The fact that it doesn't have proper timestamp support is enough by itself to be crippling.