TLDR: Scale PostgreSQL effectively via internal solutions, addressing issues like lock contention, advisory locks, foreign keys, index bloat, TOAST, and multi-tenancy. Optimize performance with strategies such as short transactions, counter caching, and partitioning. Use schema designs favoring append-only patterns and implement zero-downtime upgrades. Focus on monitoring and appropriate indexing to maintain efficiency as the system grows.
https://www.shayon.dev/post/2025/40/scaling-with-postgresql-without-boiling-the-ocean/