Saying there are cases where full scan is better than an index is really just describing an edge case where the default index implementation is non-optimal.
There are edge cases where default implementation of an index will not result in good performance in some use cases. For example, write heavy loads where the updated tuple values are frequently changing position in the index constantly. However, the solution is rarely "no index".
A solution is to throttle the index update by an acceptable delta through a non-default implementation.
A solution is to develop your own application-side index.
A solution is a custom PSQL function that interacts with an index in a more complicated way.
Maybe a non-default implementation is not priority right now, e.g. if scale is low. But it is always good to think ahead and to have a plan and to log the tech debt and track it responsibly so there are no surprises later. Don't build on shifty foundations.
There are edge cases where default implementation of an index will not result in good performance in some use cases. For example, write heavy loads where the updated tuple values are frequently changing position in the index constantly. However, the solution is rarely "no index".
A solution is to throttle the index update by an acceptable delta through a non-default implementation.
A solution is to develop your own application-side index.
A solution is a custom PSQL function that interacts with an index in a more complicated way.
Maybe a non-default implementation is not priority right now, e.g. if scale is low. But it is always good to think ahead and to have a plan and to log the tech debt and track it responsibly so there are no surprises later. Don't build on shifty foundations.