Not sure how you worked that out from the most terrible map I've seen where everything is a "slightly greener shade of green" to disambiguate it from the other green bits, but kudos!
When your internet speed map looks like a topographical map, you need to adjust your colours.
Because you can set up a rudimentary queueing system in MySQL/PostgreSQL very quickly these days. And it scales really well for small to medium sized applications!
I maintain a web application with a few hundred daily users and with the following table I have never had any problems:
CREATE TABLE `jobs` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`queue` VARCHAR NOT NULL,
`payload` JSON NOT NULL,
`created_at` DATETIME NOT NULL,
PRIMARY KEY `id`,
INDEX `queue`
);
Using MySQL's LOCK and UNLOCK I can ensure the same job doesn't get picked up twice.
All in all, it's a very simple solution. And simple is better!
Worked at a place where the lead engineer was adamant a graph database was the solution. After two years of trying to get it launched, the project failed spectacularly.
Another team got given the rewrite and build it in six months using MySQL.
Yes PostgreSQL (and others) are a better choice in these scenarios. I think the point is that a lot of applications/systems might not even need this separation since a single server would be able to handle the load. In this case a local SQLite database could be a serious performance enhancement.
A lot of factors play into this and it certainly does not work in every case. But I recently got to re-write an application at work in that way and was baffled how simple the application could be if I did not outright overengineer it from the start.
That is just anecdata. But my guess is that this applies to a lot of applications out there. The choice is not between PostgreSQL/MySQL and SQLite, but between choosing a single node to host your application or splitting them between multiple servers for load balancing or other reasons. So the choice is architectural in nature.
> I've never seen SQLite used in a setup which multiple machines connect to the same database over a network.
If you actually mean "database server", i.e., SQL is going over the wire, I don't see why you'd ever structure things that way. You lose both SQLite's advantages (same address space, no network round-trip, no need to manage a "database server") and also lose traditional RDBMS advantages (decades of experience doing multiple users, authentication, efficient wire transfer, stored procedures, efficient multiple-writer transactions, etc).
Assuming that it's the worker / job server which is primarily issuing SQL queries, what you'd do is move the data to the appropriate server and integrate SQLite into those processes. (ETA: Or to think about it differently, you'd move anything that needs to issue SQL queries onto the "database server" and have them access the data directly.) You'd lose efficient multiple-writer transactions, but potentially get much lower latency and much simpler deployment and testing.
If you need replication on the app level, SQLite doesn't make sense, because it's not built for networks.
But...
Many projects won't ever need that. A bare metal machine can give you dozens of cores handling thousands of requests per second, for a fraction of the cost of cloud servers. And a fraction of the operational complexity.
Single point of failure is really problematic if your system is mission critical. If not, most apps can live with the possibility of a few minutes of downtime to spin up a fail over machine.
Turso (https://turso.tech/) offers a solution in that scenario. The advantage with SQLite being that each machine has a local copy of the database (optionally I think) for reads so it’ll be extremely fast, and writes happen to one primary database but are abstracted and replicated.
In a nutshell, if you have a database that will have multiple instances talking to it - you are better off with a client-server database, like Postgres, MariaDB, SQL Server, Oracle, etc.
SQlite, generally speaking, is a FANTASTIC local database solution, like for use in an application.
What's the point of this? If you have multiple applications on multiple systems accessing the same DB, it seems to make more sense to just use PostgreSQL, since it's specifically designed for concurrent operation like this, instead of trying to handle this in your own custom backend code.
If you have multiple applications on different servers communicating with the same database, then yes you would need to run a database such as PostgreSQL.
If you run a single application on a server that needs a database you might want to consider SQLite, regardless of your needs for concurrency/concurrent writes.
Having an API in front of a database for full control of what is available is extremely common.
> trying to handle this in your own custom backend code
It's not writing some extra custom code, it's simply locating all of the code which interacts directly with your database on one host. Splitting up where your code is so that what would be function calls in some places if everybody interacted with the database are instead API calls. This kind of organizational decision is not at all unusual.
And if you're using SQLite it's probably because your application is simple and you should have some pushback anyway on people trying to mAkE iT WEBScaLE!! (can I still make this joke or has everybody forgotten?)
A lot of premature optimizers get very worried about concurrency and scalability on systems which will never ever have concurrent queries or need to be scaled at all. I remember making fun of developers running "scalable" Hadoop nonsense on their enormous clusters which cost more than my yearly salary to run by reimplementing their code with cut and grep on my laptop at a 100x speedup.
I've worked places where a third of our cloud budget was running a bunch of database instances which were not even 5% utilized because folks insisted on all of these database benefits which weren't ever going to be actually needed.
It's not a particularly unusual situation: it's very common for a database to effectively be entirely owned by an application which manages its own constraints on top of that database. In that circumstance sqlite is pretty interchangable with other databases.
It’s not unusual but is never performant. Adding an api layer and network hops on what should be a database shard or view is why enterprise software sucks so much ass.
Why does the api take 3s to respond? Well it needs to call 6 other apis all of which manager their own data. The problem compounds over time. APIs are not the way to solve cross organization data concerns.
You’ve fully misunderstood what I said. When you have 500 applications, the graph of calls for how any one api resolves will go deep. Api1 calls 2 calls 3 and so on.
Vs creating an organization wide proper way to share and manage data.
The number of applications doesn't need to create depth in the API layer. They're not related. If I have a service that sends emails, whether I have one or a thousand applications calling it doesn't matter.
From the webapp angle check out, for example, pocketbase.io which is an open source Go supabase style backend which wraps sqlite. They have benchmarks etc available.
This might sound like a stupid question, but I'll ask anyway:
What benefit does encryption at rest solve for something which is never intended to actually rest?
For example, a MySQL database powering a web application is expected to be alive and responding to requests 24/7. It's never really intended to be at rest.
So what benefit does encryption at rest bring? Won't a hacker be attempting to take data when it's online (and therefore not resting)?
Your RAID reports an issue with one of the disks. You disable it and have the DC staff swap it. What happens to the disk? It might be resold. It might be put into the next server. It might land in an office drawer with a label "to be wiped".
> What benefit does encryption at rest solve for something which is never intended to actually rest?
That's the point of the article, luckily enough. "Disk Encryption is important for disk disposal and mitigating hardware theft, not preventing data leakage to online attackers."
> So what benefit does encryption at rest bring? Won't a hacker be attempting to take data when it's online (and therefore not resting)?
"At rest" is used to contrast with "in flight", where the data is being transferred between computers. So data "in flight" is protected by HTTPS etc, once it has been transferred it is "at rest" on the destination computer (even if that computer is still online).
Intention becomes victim to reality easily, often with no input.
I haven't read the post yet, but I feel like expecting a perfectly well-defined model is kind of in bad faith. Defense in depth is established, FDE is one tool among many.
By focusing on the hacker we forget about the person who may get the equipment downstream; procedures/processes fail, and so on.
It's preparing for the unknowns. Sounds like paranoia? That's the job! Defending against human nature - malice, forgetfulness, etc.
I guess I'll close with this: you're the only one who can make your security assessment. What's important, what's at risk, and so on. It's trade-offs all the way down.
I should clarify that I see the value of encryption at rest for something like an employee laptop, which could be left at a bar (while powered off) by accident.
I just don't get the value of it for always online servers.
You can remove storage devices from online servers, without interruption. Said devices will contains data that could be "lost" that way. Hence: encryption at rest.
An intruder gains access to an API box, and could try to read sensitive data from a DB. But the interesting fields are encrypted, and the key is somewhere in RAM. Not impossible to exfiltrate, but takes much longer time and more skill, thus cannot be made an unattended malware payload. Also, a key for one customer won't give access to data of other customers, even if the common database access credentials are obtained.
I've written a whitepaper about our encryption at rest at work, which includes a bit of considerations about the threats considered.
The first important part is: Encryption at rest protects higher levels of the stack from access by lower levels of the stack. If your attack is working at an application level - e.g. you have a database connection - encryption at rest is no tool to deal with this. Encryption at rest is more about protecting the database from an attacker with physical access.
The second consideration is: There will always be a tradeoff between availability and security when dealing with encryption at rest. Manually decrypting a system is very secure, but if that system goes offline at 3am, it'll be offline until the device is decrypted. Automatically decrypting a system using Clevis/Tang, TPM, Bitlocker and such gives you more availability but could make it possible for an attacker to access your data if they have sufficient control.
And that's the third consideration: If an attacker has sufficient control, they can defeat automated decryption of encryption at rest, and they may have ways to start attacking manual decryption of encryption at rest as well. Like, you might be able to start looking at memory contents, disk writes and start doing some differential cryptoanalysis to attack encrypted data and such.
But with all of these three together, you arrive at the goal and security level we have formulated for our encryption at rest:
Our encryption at rest is supposed to defend us against employees of our hosters getting access to one or two of our virtual or physical storage devices. If they have access to one or two storage devices, they must not be able to access customer data on the devices.
Naturally, the question is: But what happens if they have more drives?
Well, the simple answer from the whitepaper is: That's a problem for the lawyers.
Handling 1-2 of our drives is entirely arguable as daily business. Swapping drives via remote hands or dismissing dedicated servers with 2 drives at a specific hoster happens a lot and then they handle 1-2 drives, and our goal ensures they cannot gain access to customer data then.
However, if a datacenter tech starts pulling 3 or more drives and starts analyzing data on them together, that's outside of normal operational procedures and we can start considering that an attack and start sueing them. Or they are being directed by law enforcement. Both are issues for the legal teams though.
At least that's our view. Encryption at rest works in a very different set of circumstances and mindset than other security topics in a software stack.
My thought here is that not all of the data in the database is being accessed at the same time, so the un-accessed data is "at rest". Is that correct, or am I barking up the wrong tree?
Assuming full-disk encryption is in use (LUKS, TrueCrypt/VeraCrypt, BitLocker, etc.), there is enough information held in RAM to decrypt the entire disk. If the attacker gains access to a privileged user, or at least to a user allowed to read the file system (such as the user running the database), they can exfiltrate the unencrypted contents of the disk, regardless of what the DB software is actively accessing.
