Counting in Postgres is often thought to be slow, but it doesn’t necessarily have to be. This rundown provides the ways you can count from exact to approximate, what trade-offs are, and some guide of when you should consider each.
If you have large text columns and are looking to index you may want to think twice, or at least dig into see what your options are. By default they can be quite large and a few small tactics can greatly reduce their footprint.
Postgres full-text search isn’t quite as specialized as some other FTS only systems, but can be powerful. If you are using it, here’s some handy tips on how to write the same query but achieve much different performance.
Joins are one of the powerful features of SQL, and while outer joins aren’t as common as inner ones in certain cases they’re especially useful. Here’s a quick look at how they work in a distributed fashion with Citus.