They're most useful with small memory-only tables that have very frequent lookups. For example, in a lot of systems, you might store the validation rules for each field in a table. Or you might have a set of autocomplete options. Or you may have a table with the set of currently logged-in sessions. These tables have row counts that measure in the hundreds, not millions, and the whole table can fit within a couple pages.

For something like this, the cost of reindexing is trivial, and the full data is usually in a .sql file somewhere, so even if there's data loss it takes all of about 10 seconds to restore it.

Why would you do this instead of hardcoding this in code, storing it in a local hashtable, or putting it in Redis/Memcached? Bunch of reasons. These tables can be shared across all clients of the database, so if you have multiple apps (in different languages!) that require common validation, they can all make a SQL query instead of recoding the logic. If you don't otherwise need Redis/Memcached, you can avoid adding that dependency to your stack. You can use foreign key constraints to ensure that the values in real data tables match the valid values in the validation table. You can throw an admin interface on top of the tables and let customers edit the set of available options, rather than forcing all changes to go through the developers.