[Semaphor]

Relevant for applications as well, when a table only has a few thousand entries, a scan is not the end of the world and not even an outage in waiting.

I agree with you that one should seek when possible as part of normal query optimization, but depending on your data, it could also just easily be something you can live with forever.

[hinkley]

You can’t control the growth rate of your tables, you can only estimate it. When we design for reliability we want to remove single cause failures and make a best effort to reduce dual cause failures. We definitely don’t want two failures from a single cause.

What reason might the lack of indexes suddenly become a critical issue? And what other things might you be scrambling to deal with at the same time? Tables might fill quickly when a favorable review comes in, or some world even results in churn in your system.

Just make the damned index. You Are Going to Need It. And what’s the harm in making it?

[Semaphor]

Depending on your application, you can very accurately estimate it. And in a case I had yesterday, it involved stringy numbers because of a third party system, so I could indeed add a computed persisted column that converts our number to a VARCHAR, add a 9th index with a lot of fields on that computed column and then save… almost nothing compared to just scanning 6k rows.

[magicalhippo]

We had one such table for years. The one day I get an emergency call from support, big customer don't get their responses and it's critical for their workflow.

After some digging I found the service generating the responses got killed due to being unresponsive.

Turns out our customer got a new client which caused them to suddenly generate 100x as much data as others in this module. And that caused a lot more data in a table that joined this non-indexed table.

So everything was working, it was just the performance went over a cliff in a matter of days due to the missing index.

Added the required index and it's been humming ever since.

I've had similar experiences, and so these days I'm very liberal with indexes.

We have read-heavy workloads, if you mostly insert then sure be conservative.

[ahoka]

“And what’s the harm in making it?“

Increased storage and slower inserts?

[hinkley]

In a table you think isn’t growing?? No.

[magicalhippo]

As usual, there are well-qualified exceptions. If you are very certain the table scan can't hurt, sure. But in my experience, an index wouldn't hurt any in those cases.