How to use Postgres for everything

I agree. Postgres as the only piece of your data-layer? Yes. Postgres as your application and business-logic layer? No, thanks.

Postgres is inherently multi-tenant. Have separate logic DBs in one physical instance, connect using roles with minimal permissions, expose views (and materialized views!) for querying so you can mutate the underlying tables without requiring applications to change.

Great repo. I love Postgres too. Use it for everything until there's an actual bottleneck

That works but for a more robust solution you could look at https://pgbackrest.org

> Let's ship first and worry about this, much much much much later. Overarchitecting stuff makes life hard; coming into companies that have 40 servers running with everything architected for 1000000 engineers and billions of visitors while in reality there aren't even 2 users and there is 1 overworked engineer.

Even if you try to draw boundaries between different bits of the system, you are unlikely to end up with 40 servers, not even close. The average system wouldn't even have 40 separate use cases for PostgreSQL or even the need for 40 different dependencies.

However, if you do split it up early, you more realistically would have something along the lines of:

  * your main database - the main business domain stuff goes here
  * key-value storage/cache - decoupled from the main instance, because of the decoupling nobody will be tempted to put business domain specific columns here but keep it generic
  * message queue - for when processing some data takes a bunch of resources but during peak load you need to register a bunch of stuff quickly and process it when you get the capacity
  * blob storage - to not make the main database bloat a whole bunch, but to keep any binary stuff in a separate instance, provided you don't need S3 compatibility
  * auth - the actual user data, assuming you use Keycloak or something like it
  * metrics - all of the APM stuff, like from Apache Skywalking or PostgreSQL

Give or take 2-3 services, all of which can run off of a Docker Compose stack locally or with the container management platform of your choice (not even Kubernetes necessarily, but simpler ones like Hashicorp Nomad or even Docker Swarm, using the Compose format). All of which can have their backups be treated similarly, similar approaches to clustering, all of which have similar applicable tools, all of which have similar libraries for integration with any apps and all of which can be granularly inspected in regards to how they perform and scaled as needed.

It's arguably better than a single large instance that ends up with 300 tables eventually, has 100 GB of data in the shared test environment and you wouldn't know where to start in regards to making a working local environment if you join a legacy org that isn't using OCI containers and giving each dev a local environment. The single large deployment will rot faster than multiple ones. How many you actually need? Depends on what you do, it wasn't that long ago that GitLab decided to split their singular DB into multiple parts: https://about.gitlab.com/blog/2022/06/02/splitting-database-... (which also shows that you can get pretty far with a single schema as well, to anyone who wants a counter argument, though they did split in the end)

Realistically, if you're doing a personal or small project, everything can be in the same instance because it'll probably never go that far, but I've also seen both monolithic codebases (that are deployed as singleton apps, e.g. not even horizontal scaling) and what shoving all of the data in a single database leads to (regardless of whether it's PostgreSQL or Oracle), it's never pleasant. I'd at the very least look in the direction of splitting out bits of a larger system based on the use case, not even DDD, just like "here's the API and DB instance that process file uploads, they are somewhat decoupled from our business logic in regards to products and shopping carts and all that".

Now, would I personally always use PostgreSQL? Not necessarily, since there are also benefits to Redis/Valkey, MinIO, RabbitMQ and others, alongside good integrations with lots of frameworks/libraries that just work out of the box, as opposed to you needing to write a bunch of arguably awkward SQL for PostgreSQL. But the idea of using fewer tools like PostgreSQL for different use cases (that they are still good for) seems sound to me as well.