How far can you get with a single Postgres instance on a single machine? I know things like cockroach and citus existence but generally Postgres isn’t sharded as far as I know.
You can scale up that one machine a lot. If you start with a normal sized machine you have a lot of overhead in increasing ram/cpu on that machine (eg you could start with say 16 cores and 100G ram or less and scale up to like 2TB ram and 64/128 cores). There’s also runway for scaling things by eg shooting down certain long-running queries that cause performance problems or setting up read replicas.
So even if you’re a bit worried about scaling it, you can at least feel the problems are far away enough that you shouldn’t care until later.
We are serving a several tens of TB database with tens of thousands of daily user doing very heavy queries on a single machine that did cost us $15k a few years ago (we have fallbacks and replication and whatnot don't worry). The same machine also has java services. You can really do a lot on today's machines.
That's complicated based on workload, etc. A single PG node will obviously never scale to Google or Facebook levels.
Attend a PG conference and you will run into plenty of people running PG with similar use cases(and maybe similar loads) to you.
I can say we run a few hundred concurrent users backed by PG on a small to medium sized VPS without issues. Our DB is in the 3 digit GB range on disk, but not yet TB range.
So even if you’re a bit worried about scaling it, you can at least feel the problems are far away enough that you shouldn’t care until later.