What you're describing would probably have been equally possible with Postgres from 20 years ago, running on an average desktop PC from 20 years ago. (Or maybe even with SQLite from 20 years ago, for that matter.)

Don't get me wrong, Postgres has gotten a lot better since 2006. But most of the improvements have been in terms of more advanced query functionality, or optimizations for those advanced queries, or administration/operational features (e.g. replication, backups, security).

I take it as meaning use Postgres until there's a reason not to. ie build for the scale / growth rate you have not "how will this handle the 100 million users I dream of." A simpler tech stack will be simpler to iterate on.

I personally think reading manuals and tuning is a comparably low risk form of software development.

Oh no, not a company hiring a team of specialist in a core technology you need! What next, paying them a good wage? C'mon, it's so much better to get a bunch of random, excuse me, "specialized" SaaS tools that will _surely_ not lead to requiring five teams of specialists in random technologies that will eventually be discontinued once Google acquires the company running them.

OK but seriously, yeah sometimes "specialized" is good, though much less rarely than people pretend it to be. Having specialists ain't bad, and I'd say is better than telling a random developer to become a specialist in some cloud tech and pretending you didn't just end up turning a - hopefully decent - developer into a poor DBA. Not to mention that a small team of Postgres specialists can maintain a truly stupendous amount of Postgres.

And TBH, developers are pretty dumb not to realize that the tech tools monoculture is a way for business folks to make us easily replaceable... If all companies use the same tech, it turns us into exchangeable commodities which can easily be replaced and sourced across different organizations.

Look at the typical React dev. They have zero leverage and can be replaced by vibe coding kiddies straight out of school or sourced from literally any company on earth. And there are some real negatives to using silver bullet tools. They're not even the best tools for a lot of cases! The React dev is a commodity and they let it happen to them. Outsmarted by dumb business folks who dropped out of college. They probably didn't even come up with the idea; the devs did. Be smarter people. They're going to be harvesting you like Cavendish.

So many times when trying to just go for simplicity with SQLite it takes me like one working day until I run up against enough annoyances to where resolving those is more work than setting up the "set up and forget" postgres instance.

Granted, this is for personal stuff... but "Postgres packaged for low maintenance" is present in a lot of OS package managers! Even for smaller data analysis work SQLite perf leads _loads_ to be desired (once had QGIS struggling with a sqlite DB... pg made everything mostly instant. Indices etc... but stuff I _couldn't easily get with sqlite_)

If SQLite works for you that's great, I do think it's worth it for people to _try_ to do simple pg setups to understand just how painful it is to use pg (for me: not that high)

Second, when I need a local save file. Sometimes small local apps are better served by a save file and they save file might as well have an extensible format that I can update as I go. This is more rare but still can be useful.

The first use case is very powerful. A temporary SQL database that can be blown away with zero trace of it is great. And the ability to run complex queries on it can really help.

But 99% of the time I just use Postgres. It works, it has sane defaults, it is crazy extensible, and it has never not met my needs, unlike Oracle or MySQL.

i personally like postgres myself for just about all use cases that must be shared with others (app with more than one client that might be providing CRUD updates or anything really that demands a central data store). ive used sqlite a couple times with WAL to try and make a small app shared between 2-3 people who all would contribute updates thru it but it wasnt ideal. for postgres so many features/extensions its concurrent writes are fast as hell and if you just want to one-shot a solution then you cant go wrong, but it's ofc not the same as sqlite setup.

i think a lot of the pain with postgres is just learning to effectively be a knowledgeable db admin of sorts. its somewhere between being a competent devops guy and a dbadmin expert all in one. if you're actually doing some kind of production deployment it is kinda scary hoping you've got everything set up right. even supabase which makes this whole process trivial to get going requires an understanding of not-always-understood security premises that just make things spooky.

lot of words to say i dont get much out of these discussions tbh. theres just too many use cases and variables in everyones working/hobby lives im not sure that there is a proverbial bottom to any of it. some will use sqlite and some will use postgres and some will use some weird thing no ones heard of because they're afraid to rawdog sql and just want immediate graphql capability to be the main mode of data retrieval. some will show up here and talk about why you need redis in the middle.

its too much noise so i just keep using postgres because its free and boring and fast. end of the day i just want to make stuff people can use. it's a hard endeavor to do well alone, if you dont have a team of other experts who can help you put all the puzzle pieces together on how to deploy things the right way and also add pieces like redis or whatever... it's just a lot. it's hard to find where to get started. sqlite is the only solution that really by nature of what it is seems to champion the lonely developer, but the tradeoffs are big if you're trying to make something that should get used by many people.

Having to look up and spend time fixing permissions every time itself makes using Postgres for simple uses difficult for me but if you're using it ad hoc, any tips?

And Turso: https://turso.tech/

1) A single person, doing a ton of heavy lifting, leaves, or worse, turns it over, or silently sells out to a nefarious person.

2) A severe security vulnerability is found. If everyone is using postgres, everyone is vulnerable. Bonus points if the vulnerability is either not publicly disclosed or it is hard to fix.

3) Commercial/Government interests heavily influence and push the project into places that could make it vulnerable in any given way. This is absolutely a thing.

4) AI. No clarification here. Just use your imagination, with recent news regarding FFMPEG and other projects in mind.

PostgreSQL is good enough to get to medium sized with nearly every use case. Once you are there, you have the use case and the test data to test any alternative for it well, rather than trying to guess beforehand what you actually need.

The advice is basically "PostgreSQL is probably good enough for whatever you're building now, and you should only look for other solution once you are big enough that it stops being that"

Once upon a time, logical replication wasn't a thing, and upgrading major versions was a nightmare, as all databases in the chain had to be on the same major version. Upgrading big databases took days because you had to dump and restore. The MVCC bloat and VACCUM problem was such a pain in the ass, whereas with MySQL I rarely had any problems with InnoDB purge threads not able to keep up with garbage collecting historical row versions.

Lots of these problems are mitigated now, but the scars still sometimes itch.

With MySQl variants like percona xtradb setup can go from 1 instance to cluster to geo replicating cluster with minimal effort.

While vanilla postges for an equivalent setup is basically pulling teeth.

OTOH, if and only if you design your schema to exploit MySQL’s clustering index (like for 1:M, make the PK of the child table something like (FK, some_id)), your range scans will become incredibly fast. But practically no one does that.

So what did the VACUUM-free, undo-based MVCC storage engine project stall? https://wiki.postgresql.org/wiki/Zheap

Why is there no InnoDB for PostgreSQL?

(Maybe OrioleDB will avoid a similar fate.)

Run benchmarks that show that, for your application under your expected best-case loads, using Redis for caching instead of PostgreSQL provides a meaningful improvement.

If it doesn't provide a meaningful improvement, stick with PostgreSQL.

Just use memcache for query cache if you have to. And only if you have to, because invalidation is hard. It's cheap, reliable, mature, fast, scalable, requires little understanding, has decent quality clients in most languages, is not stateful and available off the shelf in most cloud providers and works in-clusetr in kubernetes if you want to do it that way.

I can't find a use case for Redis that postgres or postgres+memcache isn't a simpler and/or superior solution.

Just to give you an idea how good memcache is, I think we had 9 billion requests across half a dozen nodes over a few years without a single process restart.

But As soon as you go outside Postgres you cannot guarantee consistent reads within a transaction.

That’s usually ok, but it’s a good enough reason to keep it in until you absolutely need to.

I like Redis a lot, but for things in the start I'm not sure the juice is always worth the squeeze to get it setup and manage another item in teh stack.

Luckily, search is something that has been thought about and worked on for a while and there's lots of ways to slice it initially.

I'm probably a bit biased though from past experiences from seeing so many different search engines shimmed beside or into a database that there's often an easier way in the start than adding more to the stack.