Adding an index on tags.name is likely not very useful on its own but adding an index with an uniqueness constraint to prevent duplicates is probably a good idea. Assuming there are just a few different tags records (<1000) then a sequential scan is generally either insignificantly slower or even faster than an index scan.

>Not the subsequent years of maintenance.

I'd much rather maintain this example than a code base replicating the same behavior without an ORM.