Comes down to your data model. If its inherently relational it's still the best play. Scale and performance are much more tractable problems than integrity and consistency. One you can measure and be sure of, the other you need a Phd to fully understand all the edge conditions that need covering.

There are some pretty decent NoSQL stores now for simpler access patterns. As long as you stay away from nonsense like MongoDB and stick to real databases like Cassandra/ScyllaDB/BigTable/etc you will do fine.

These stores are a fraction as flexible as PostgreSQL/MySQL but do allow scale-out storage and fast primary key lookups and scans. Good for when the size of your data is well in excess of 1TB+ and you don't need anything complex or consistency.