The title likely sums up my question, but to be clear, I'm specifically thinking RDBs.
I have no intention of doing this, I've just been curious and haven't found a clear answer in my attempts at googling it.
If it is or is not true could the reason be explained? (if not too much of a big ask)
Thanks for anyone willing to share insight into this.
An index stores a copy of the data for the fields the index is defined on. Typically, the standard index uses a B-Tree data structure to organize that copy of the data. The clustered index (or clustering the table data around an index - depending on the database system you're using) is a special kind of index which instead of storing a copy of the data, is actually the table itself organized into a B-Tree data structure.
Tables with no indexes (and more specifically no clustered indexes) are stored in a Heap data structure. A Heap is an unordered dynamically allocated collection of data that is highly liable to fragmentation on data changes. A B-Tree is an ordered tree collection of data that is less liable to fragmentation issues on changes. See this StackOverflow answer for much more in depth information on the differences and pros vs cons of Heaps vs B-Trees.
Going back to my first paragraph, to keep in mind that an index is a literal copy of the data (except the clustered index), means there is work when the data is written to the table to persist that data to every index. If your table has 100 columns, and ergo had 100 indexes, then when a new record is inserted into the table, 100 additional write operations would need to occur. This obviously has some performance implications (especially in the context of locking and blocking). Additionally, since a B-Tree data structure is what's used to store data for an index, there's an O(Log2(n)) insert time to locate where in the B-Tree to insert the new record.
A table with no indexes will only need to undergo a single write operation, and since it would be a Heap data structure storing the data, the insertion time would be O(1), a very fast constant insertion time, because the data is just inserted at the end of the Heap being that it's an unordered data structure. Clearly, from a write perspective, no indexes are most optimal.
But from a read perspective, Heaps have a O(n) search time, because they are unordered (essentially randomly ordered). B-Trees have a O(Log2(n)) search time because they are ordered, which is significantly faster than Heaps (O(n)). So indexes help improve performance when locating specific records of data, especially for read queries.
So there's a trade-off of performance losses between maintaining too many indexes vs not having enough or the proper indexes to serve your queries.
As mustaccio's analogy points out, there is definitely a difference between packing all of your clothes for vacation than packing no clothes. Let's pretend you brought 5 suitcases with you, that each stored a different color of clothing, and you packed each article of clothing you're bringing with you in order of size. Your suitcases are the indexes. The articles of clothing are the data. To sort your entire wardrobe in each suitcase would take a long time (many writes to the indexes). And now after you've done that, let's say you go out and buy a new shirt in 5 different colors because of how much you liked that shirt. Now you have to pack 5 copies of that shirt in each respective suitcase by color (5 more writes). Lot of work.
But now let's say while on vacation, you decide on the first day you want green socks and blue jeans. Well your clothes are all already sorted by color and by size (socks being the smallest, jeans being the largest in this example). So it would be very quick for you to locate those specific articles of clothing (reading for the data).
Conversely, if you had a single suitcase that you just threw all your clothes into, it would be very quick for you to pack your clothes up (writing to a Heap). But then it would take a very long time to locate those green socks and blue jeans from that unorganized suitcase (reading from a Heap).
It's usually more common (though not always, depending on your use case) to read a specific record from the table more frequently than you write / update that specific record. So some indexes (after careful consideration) generally make sense to have on your tables.
