[bob1029]

I can assure you that I live on the same planet as everyone else posting here.

Whether or not I could perform this miracle depends entirely on your specific use cases. Many people who have this sort of reaction are coming from a place where there is heavy use of the vendor lock-in features such as SSIS and stored procedures.

If you are ultimately just trying to get structured business data to/from disk in a consistent manner and are seeking the lowest latency and highest throughput per request, then SQLite might be what you are looking for.

The specific core counts or other specifications are meaningless. SQLite scales perfectly on a single box, and if you have some good engineers you might even be able to build a clustering protocol at the application layer in order to tie multiple together. At a certain point, writing your own will get cheaper than paying Microsoft for the privilege of using SQL Server.

[SNACKeR99]

This is a great answer. The details REALLY matter. One of my best early tech success stories was rewriting a SQL query that took 27 hours to one that took ~5 seconds. This was running on a very large Oracle cluster. They had poured more and more money into hardware and licensing trying solve this. In the end, it was a matter of turning a cursor-based query into a set-based query.

[zimpenfish]

In some respects, I think the constraints of something like SQLite can focus people's attention on making things work properly rather than throwing hardware at the problem.

I can think of a couple of places I've worked where they had simple problems that could have been solved by some thinking and coding but instead were solved* by more expensive hardware.

[bob1029]

This is precisely my favorite part of SQLite. The constraints (aka lack of features) is what makes it so compelling. We experienced some serious revelations going down this path. The biggest thing from a devops perspective is that you can push very consolidated releases. There is no need to install anything for SQLite to function.

For instance, we use .NET Core Self-Contained Deployments combined with SQLite. As a result, we ship a zip file containing the dotnet build artifacts to a blank windows or linux host and have a working application node within a matter of seconds. The databases will be created and migrated automatically by our application logic, and the host is automatically configured using OS interop mechanisms.

So, when you really look at it, the constraints imposed upon us by SQLite encouraged us to completely sidestep the containerization game. Our "container" is just a single platform-specific binary path that has all of its dependencies & data contained within.

Without SQLite, we would have to have some additional process for every environment that we stand up. This is where the container game starts to come in, and I feel like its a bandaid to a problem that could have been avoided so much further down in the stack (aka SQLite vs MySQL/SQLServer/Postgres). Sure, there are applications where you absolutely must use a hosted solution for one reason or another, but for many (most) others where you do not, it's really the only thing stopping you from having a single process/binary application distribution that is absolutely trivial to install and troubleshoot. You literally just zip up prod bin path and load it on a developer workstation to review damages. 100% of the information you need will be there every time. No trying to log into a SQL server or losing track of which trace folder is for what issue # and sql dump. It keeps things very well organized at all levels. We can just launch the production app with --console to see its exact state at the time of the copy operation and then attach debuggers, etc.

[mumblemumble]

The last time I reduced database server load by 70% by just spending a week tuning indexing strategies and ill-performing queries, nobody thanked me. Sure, a new database server costs way more than a week of my time, but that is completely beside the point.

The point is that I robbed someone of the chance to buy a shiny new computer.

[zimpenfish]

> The last time I reduced database server load by 70% [...] nobody thanked me.

It's crazy. I could maybe understand if there's a time crunch where it's quicker and easier to get more hardware in order to make a sale that'll keep the company alive (which I have experienced once) but that's maybe 1% of the cases.

Anyway, in lieu of their gratitude, I offer my thanks because I appreciate the effort.

[BFatts]

That's a fantastic point. Most people in technology these days look at a problem and immediately think things like "more hardware" or "cloud deployment" all to get scalability. Scalability can come in forms other than throwing lots of money at an issue... Oftentimes, money can be saved if one throws more intelligence at the problem. :)

[nicoburns]

Is SQLite likely to be faster than postgres? In terms of ease of use / admin overhead I consider them mostly equivalent. I thought the main problem with SQLite was it was slow tih concurrent writers. Whereas the "bigger" SQL databases have code that allows concurrent writes.

[catalogia]

The issue with SQLite and concurrent writers isn't that it's slow, it's that it just can't do it. WAL mode lets you have as many readers as you want concurrent with a single writer, but it doesn't give you multiple concurrent writers. If you really need concurrent writes, use PostgreSQL or another RDBMS.

In my experience, SQLite is likely to be faster when you have lots of reading. Being in-process gives SQLite a natural advantage in read-heavy situations.

[bob1029]

WAL mode is how you address this problem with SQLite.

See: https://www.sqlite.org/wal.html

"Write transactions are very fast since they only involve writing the content once (versus twice for rollback-journal transactions) and because the writes are all sequential. Further, syncing the content to the disk is not required, as long as the application is willing to sacrifice durability following a power loss or hard reboot."

[JAlexoid]

As long as you don't mind volatile memory issues, that is.

[itroot]

I think there are a lot of places where sqlite can outperform postgres... This is read-heavy and latency-critical apps, where additional hop is costly, for example.