201–210 of 238 posts
Earlier quoted context omitted.
> This is exactly a great point. When data size goes to a billion rows, Postgres is tough. MongoDB just works without issue. Is it though ? Maybe 5-10 years ago it was.
It is still true that vanilla Postgres doesn’t scale well beyond multiple machines. There are extensions that help, though.
Earlier quoted context omitted.
It is still true that vanilla Postgres doesn’t scale well beyond multiple machines. There are extensions that help, though.
My point is that you can handle a billion of rows on a single PostgreSQL instance.
Earlier quoted context omitted.
> if you use an ORM that supports both SQLite and Postgres you'll have the option to upgrade if your site brings in enough traffic I'll never understand this idea that Postgres and SQLite are somehow interchangeable when the time is right. My database and Postgres are _literally_ the core definition of everything that my application does. My app is written in Rust, but that doesn't matter because it's a _Postgres_ ap…
I don't think people often switch from Postgres to SQLite, it's probably more common (and much easier) to prototype with SQLite lite first and then switch. If by referential integrity you just mean FK constraints, you can turn that on in sqlite3. I think SQLite is pretty good for a lot of use cases. An Axum/sqlite CRUD app should be able to handle at least few hundred requests per second on a medium powered box, whic…
Almost all statements about MongoDB are wrong. > You know exactly what your app needs to do, up-front No one does. Mongodb still perfectly fits. > You know exactly what your access patterns will be, up-front This one also no one knows when they start. We successfully scaled MongoDB from a few users a day to millions of queries an hour. > You have a known need to scale to really large sizes of data This is exactly a g…
> When data size goes to a billion rows, Postgres is tough. MongoDB just works without issue.
Joins are tough at a billion rows in Postgres. PK lookups and simple index queries of the type mongo is good at Postgres is generally good at too. The main thing mongo has over postgres is ease of sharding if one is looking to scale horizontally.
The "SQLite is just a file" thing is actually an advantage. The example of a website is actually a pretty poor one, since any website that needs to scale beyond a single box has many options. The two easiest ones are: - Mix static and dynamic content generation (and let's face it, most websites are mostly static from a server perspective) - Designate a writer node and use any of the multiple SQLite replication featur…
> if you use an ORM that supports both SQLite and Postgres you'll have the option to upgrade if your site brings in enough traffic I'll never understand this idea that Postgres and SQLite are somehow interchangeable when the time is right. My database and Postgres are _literally_ the core definition of everything that my application does. My app is written in Rust, but that doesn't matter because it's a _Postgres_ ap…
Earlier quoted context omitted.
Fully agree. At that level, the justification for using MongoDB usually boils down to not wanting to deal with table schemas or SQL. In both cases, there are better alternatives.
I’ve heard stuff like this from supposedly senior people - if we use mongo we can just store anything, we don’t need to think about a schema (also you can only store short strings in an SQL database)
Earlier quoted context omitted.
Oof, you're right. Still within the performance profile of a raspberry pi though, even if it's no longer off by an order of magnitude So I think my point still stand: that number is as low as you can get for any rdbms.
TBH we are at around 15M queries per hour. I am sure our customers don't want us to run on RPi. Btw, it's not only query but billion+ rows which are also there.
But the number was so low I couldn't help but point out that this was more likely to convince me that mongo is a joke then a usable database
> If you see a college student or fresh grad using MongoDB stop them. They need help. They have been led astray. I like this sentence way more than I should.
Exactly, SSPL is such a trojan horse, and God knows how they will change the license in the future
A lot of these OSS projects provided a commercial offering in the form of SaaS. Yet AWS/GCP/Azure can just take the OSS project, not contribute anything, and reap all the profit.
AFAICS, these licenses are only intended to defend against the cloud providers, not against companies just using the product commercially and internally.
The "SQLite is just a file" thing is actually an advantage. The example of a website is actually a pretty poor one, since any website that needs to scale beyond a single box has many options. The two easiest ones are: - Mix static and dynamic content generation (and let's face it, most websites are mostly static from a server perspective) - Designate a writer node and use any of the multiple SQLite replication featur…
You don't need to maintain, secure and tweak postgres any more than you would with SQLite. Just install it and it'll work. Postgres backup is a single command. And actually you're supposed to create sqlite backups with special command as well, if you're copying a file, you're doing it wrong. I really don't see any cons with Postgres over SQLite for server applications.
Of course you need to maintain postgres.
Major version upgrades are not automatic, you can't just install a newer binary/library version and start it as you can for SQLite. You need to shut down the DB and run `pg_upgrade`, or write manual full export-import scripts with `pg_dump`/`pg_dumpall`/`pg_restore`/`psql`.
And good luck deciding between the different format options, as some of them are unsupported across some of these tools, some cannot export and reimport the full database cluster, there's no idempotent "just import this snapshot" operation (point-in-time restore), lack of progress reporting, etc.
Here are some notes I on the topic:
# Note on Postgres backups
#
# Unfortunately, postgres backup+restore is not straightforward.
#
# * Backups created with `pg_dumpall`, which create an .sql file,
# cannot simply be used for point-in-time recovery.
# They need to be restored with `psql` (not `pg_restore`),
# which errors if the data already exists.
# * You could probably tell it to ignore errors, but naturally it'll just
# run through `INSERT ...`, so it's not a proper point-in-time recovery,
# because it doesn't remove data newer than the backup as expected.
# * To use `pg_restore` (which can ignore existing data, re-creating
# everything with the `--clean` flag), you need to use `pg_dump`
# (not `pg_dumpall`), which cannot backup *all* databases,
# only a single given one.
# * Further, `pg_restore` does not accept `--format=plain` SQL backups
# (the default created by `pg_dump`). Only the non-plain backups are
# accepted, which are less readable for a human to determine whether
# a given backup is the one desired to restore based on the data.
#
# As a result, we aim for restoration using `pg_restore --clean`,
# backing up only the `postgres` database using `pg_dump -d postgres`.
# This works for us because we currently store all our tables in the
# `postgres` database.
# We use `--format=tar` because it is a plain text format, which
# * deduplicates better than compressed formats, and
# * allows a human to `grep` in plain text for desired contents.
And why can't I just have postgres-as-a-library to link into my binary, like I can do with SQLite?
You also can't just run postgres as root (e.g. in a container), and have to set up UNIX users to work around that, because postgres has it hardcoded to avoid running as root. This, too, you don't need to do with SQLite.
Also, postgres is harder to secure.
You need to either use TCP and ensure that other UNIX users on the same system can't just connect, or use UNIX Domain Sockets which have a 108 char path length restriction [1] (which is of course not documented in postgres's docs [2]), so it will suddenly break your CI when its path changes from
/var/lib/jenkins/workspace/my-branch-name/postgres/sockets/.s.PGSQL.5432
/var/lib/jenkins/workspace/my-longer-branch-name-for-additional-cool-feature-12456/postgres/sockets/.s.PGSQL.5432
Postgres is still my DB of choice, but it would be very misleading to say that it needs no maintenance and just works.
[1]: https://serverfault.com/questions/641347/check-if-a-path-exc...
[2]: https://www.postgresql.org/docs/current/runtime-config-conne...