The fact that the performance is so close between a database that requires NO SERVER and one that requires a dedicated server + ops management is a signal to me that all projects should start on SQLite unless there is a compelling reason not to.
Plus it seems to be a readonly workload, something for which posgres is not ultra relevant (not that it doesn't work, but...).
If you might want to use multiple app instances connected to a shared DB, I would say it's probably easier to just use a local postgres container for dev and a managed cloud DB. Really not that much effort and you get automatic backups.
If you plan to never use a shared DB, SQLite is great though.
I think this is what it comes down to for me too. Yes there might be some use cases that really benefit from Postgres features like GIS and the native JSON type, but ultimately, for most use-cases, it's going to hinge on whether or not you ever expect to need to scale any part of the system horizontally, whether that's the frontends or the DB itself.
Or a proper UUID type, materialized views, enforced data-constraints, lateral joins, queries that consider multiple indexes, etc, etc.
It's not like there's just a couple of niches where PostgreSQL's features distinguish it from SQLite. SQLite is appropriate for very simple DB use-cases where you aren't too bothered by silent data inconsistency/corruption, but not a whole lot beyond that. It's a good little piece of software, but it hardly relegates PostgreSQL to "Just GIS things" or "Just JSON use-cases".
SQLite is also appropriate for use cases where you are bothered by silent data inconsistency/corruption, it's just that often the hardware running PostgreSQL (or any "serious" DBMS, relational or not) is usually less prone to random inconsistency/corruption (non-interruptible redundant power sources, well cooled environment, running lower but more stable clock speeds on everything, ECC RAM, raid disk array, corruption-resistant FS).
If you run PostgreSQL on the cheapest consumer hardware, expect random corruption of the database when power runs out several times in a row.
Recent SQLite versions have STRICT mode where they forbid that.
https://www.sqlite.org/stricttables.html
> or too-long data in a too-short column
You can use a constraint for that:
CREATE TABLE test (
name TEXT NOT NULL CHECK(length(name) <= 20)
) STRICT;For most business applications used by more than one user there are usually some expectations that data will be retained and the application will be available.
Still, PGSQL requires some level of setup/maintenance: create user, permissions, database, service with config/security effort. If there is a way to run PGSQL as lib and say to it: just store your data in this dir without other steps, I am very interested to learn.
- [1] https://aws.amazon.com/rds/
- [2] https://azure.microsoft.com/en-us/services/postgresql/#overv...
yeah, premium is very large if I want to do a lot of data crunching (lots of iops, cores and ram is needed), plus latency/throughput to those services will be much higher than to my local disk through PCIE bus.
Steps will be:
- sudo apt install postgres (more steps are needed if you are not satisfied with OS default version)
- sudo su postgres
- psql -> create db, user, grant permissions
- modify hba.conf file to change connection permissions
- modify postgresql.conf to change settings to your liking because defaults are very out of touch with modern hardware
Another options would be to build your docker image, but I am not good with this.
- sudo apt install sqlite3 # you're going to want the client
- sudo chmod myapp db.sqlite3
- sudo chmod myapp . # make sure you have directory write permissions for WAL/journal
- sudo -u myapp sqlite3 db.sqlite3 'PRAGMA journal_mode=WAL;'
Nothing is ever single-command easy in all cases.
I am familiar with Java and H2, you just say in config somewhere: store db in this directory, and you are all set.
I think there is no such route currently for PGSQL.
Which isn't bad. This is a pretty low bar to clear.
(All that said, I am of the opinion that separating data storage from application logic is better for operations, for security, and for disaster recovery unless you desperately need to prioritize avoiding network latency above everything else, so this is all pretty moot to me.)
I think usual way to do it is say having shared library file like sqlite.so in some directory or installed through OS package manager, and thin API binding for specific runtime in form of runtime package (e.g. npm).
> you desperately need to prioritize avoiding network latency above everything else, so this is all pretty moot to me
Goal here is easy distribution of embedded DB. Many apps are doing this with SQLite, for example Dropbox client. Many other examples: https://www.sqlite.org/famous.html
> you could conceivably run postgres on your app server
So what you've been saying has been taken in the context of packing sqlite or Postgres next to your web application, which has pretty limited use cases, and reads really weirdly in that light.
But there is another approach: one self contained binary/artifact which can include embedded DB too, and be launched by one command.
That seems pretty... odd on their part. I know that Ruby gems in particular can and do include C sources, so there's no reason it couldn't ship sqlite3.c (or even the various .c files that get combined into sqlite3.c); if you're able to install Nokogiri (i.e. you have a usable C toolchain), then you should be able to compile sqlite3.c no problem.
Meanwhile, Python ships with an "sqlite3" module in its standard library (last I checked), so if you have Python installed at all then you almost certainly already have SQLite's library installed.
I think the thinking is that packing The Entire Library rather than your necessary bindings is overkill/duplication/etc etc. - it's the static versus dynamic argument in a sense, even though you may be dynamically linking while you pack your own library. Duplication versus OS-controlled security patching? Admittedly when most deployables end up being Docker containers (for good or for ill) that's less important, but most of these tools are from a distinctly earlier era of deployment tools.
It does, but it still compiles bindings for it. My point is that if you're able to compile C code at all (including Nokogiri's bindings to libxml2), then strictly requiring some external libsqlite3.so is unnecessary - because it's pretty dang simple to just compile sqlite3.c alongside whatever other C code you're compiling.
> And I think--don't quote me, Python isn't my thing and I only looked briefly--that Python on Ubuntu requires the `python-sqlite` APT package, which depends on both `python` and `libsqlite0`
Right, and my point there is that if you've installed Python, then in doing so you've already installed SQLite (because your package manager did so automatically, because it's a dependency), so you don't need to run an additional command to install SQLite.
I don't want stand-alone client for app which customer downloaded from my site.
Also, docker adds complexity itself.
docker run -it -e POSTGRES_PASSWORD -p 5432:5432 postgres:12
And bam, you have a server
Like:
FROM postgres:14.3
COPY pg/pg-config.sql /docker-entrypoint-initdb.d/000-pg-config.sql
COPY pg/schemas.sql /docker-entrypoint-initdb.d/010-schemas.sql
COPY pg/meddra-tables.sql /docker-entrypoint-initdb.d/020-meddra-tables.sql
# ...
RUN chmod a+r /docker-entrypoint-initdb.d/*
ENV POSTGRES_USER=myapp
ENV POSTGRES_PASSWORD=xxx
ENV POSTGRES_DB=mydb
docker exec postgres psql -h localhost -p 5432 -U ${DBADMINUSER} -c "some SQL statement"
Also, I usually change hba.conf file too, to disallow outside connections.
If you need all this customization then did SQLite really fit the bill in the first place?
I need to change config for prod pgsql instance, because default config performance wise is not necessary suitable for serious prod machines.
> If you need all this customization then did SQLite really fit the bill in the first place?
I am not familiar with SQLite, but somehow familiar with Java H2 DB, which has similar idea. You can embed it and all configs into self contained java jar file together with your app and no moves in host OS is needed.
> RDBMSs are there to enforce constraints on data.
If you did not ask SQLite to enforce type constraints on relations (with STRICT), it will work in the relaxed, backwards-compatible expected behavior of previous versions of SQLite.
That being said, if you want actual validation, you probably need more complex CHECK expressions anyways with your business rules, and those work by default on any database.
https://www.sqlite.org/stricttables.html
* unless you're a madman that runs things with "PRAGMA ignore_check_constraints = false;" enabled or equivalent; in that case, no DB can help you.That does not mean it is applicable to all databases use-cases, but production-grade it undoubtedly is.
By your example, a bank should use SQLite because it is deployed widely.
You have to look where they are deployed and the use cases. They also come with dramatically different tooling which make sa huge difference. More tooling does not mean that a full RDMS is a better choice:
It is just about what you need, what use case you need to cover and what industry you are in.
Widely-used constraints like UNIQUE are production-quality in SQLite.
If you’re writing something to run in an embedded/client app environment, then yeah why would you use Postgres for your one machine? You could, but it’ll add a lot of moving parts you’ll never need and probably don’t want (like remotely accessible network ports you’ll need to secure)
It seems to me (or at least, I'm hoping this is happening) that the pendulum is swinging back to simple deployments and away from AWS/Cloud/K8s for every tiny little app.
One thing I'd love to see is that the 'default' MVP deployment is your code + SQLite running on a single VM. That will scale well into "traction" for the vast majority of applications people are building.
It runs faster than 90% of webapps on the internet.
Good resume fodder though I guess.
I have zero experience with this but I am very curious how people do it in sqlite.
What does matter however, is enforcing parametrized queries everywhere. Unless all the db handles you pass to the client handling code are read-only, chaos will ensure from the DDL permissions.
Why is it superior to put all of the (bespoke) access control logic in the server side bridge rather than use what's available in the database (accessed by the bridge, not the client)?
I have been watching like a hawk for 6 months but I haven't stumbled upon a clear reason why this is done, except for "it helps source code db portability".
For a multiorg/multiuser application this seems like the crucial distinction between sqlite and postgresql.
Again I have no experience here, talk to me like I'm stupid (I really am!).
Within a single org, multiuser approach, there are 2 big problems that I remember with attempting to shoehorn DB auth into application auth:
* assuming you use a connection pool, you might run out of TCP connections/ports if you need to handle too much stuff;
say for example that your load balancer need 3 application nodes behind it - you will need 2 (connections per user) x 3 (application nodes) connections just to handle a user - 6 connections/user. That will eat your database connection limit very fast, for no good reason.
* assuming you don't use a connection pool, you now have horrible latency on every connection (bad) and need to handle plain text passwords (assuming you use scram-sha-256), or md5 non-replay-resistant hashes of user passwords in, either sent in every client request, or in a shared session system. No matter what you pick, you have a security disaster in the making (very bad).
sqlite looks like great technology to me (as is postgresql) but I am a bit of a fanatic for keeping the overall system as understandable as possible, so these questions are important (for me, I'm stupid).
Preemptive scaling never really works and most projects never scale enough to warrant more than one server (unless you write very inefficient code).
We once did an app ages ago where the database was used to create a materialized view of code (PHP) + web pages for everyone and everything. We then rsynced that to 6 machines. This is ancient times, but this thing FLEW -> click click click click you could fly through the pages. It was a read heavy workload, but still, even the front-end (just hand coded) was somehow faster than all the latest and greatest going on these days.
Postgres and SQLite are very different in several aspects, e.g.
- types and enforcing type safety
- handling concurrency and transactions (available isolation levels)
- functions / procedures
- array support
Postgres can be much more valuable than just using it for "relational data storage". Its features can be a good reason for choosing it from the beginning.
A migration is all but expected. Might as well make things easy on yourself for the first year and start with SQLite.