I am endlessly fascinated with content tagging systems

There is no good solution for the cultural problem that a written language is somehow unsuitable for end users. but personally I have spent way too many hours trying to make a search interface only to realize at the end that not only is my interface complicated and hard to use it still has only a fraction of the descriptive power a sql query has. At times I am tempted to make full use of the built in database permissions and let the user just type queries directly. but this suggestion is always vetoed.

People usually go with GIN indexes, that can be used on the contains, overlaps or equals comparisons.

Many enterprise databases add a memo field called "Comments" to almost every table. Clients very often end up coming up with their own guidelines about how to embed various information in the comments fields that the primary structure is missing. Looking over how clients are using the "comments" fields is a great way to discover new things that should be formally incorporated into the structure of your data architecture. Similarly with tags.

Look at tags as a starting point for adding a bit of loose structure to the frontiers of your data architecture. Mix them in with more structured data architecture. Be ready to "graduate" tags up to the next level of structure when it becomes appropriate. Stop worrying about how to make tagging perfect and embrace it for what it is: an easy way to get started on modeling the parts of the domain that you haven't spent a long time thinking about yet. A good way to understand how users want to use your system. Something you're always revisiting, cleaning up, and using as a source of inspiration. If you see some tags getting out of hand, don't try to improve your tagging system; instead take what those tags are trying to represent and add more structured fields and queries for them. This pipeline of less to more structure should be constantly playing out in a healthy, evolving system.

They have tag aliases, meta-tags and so-called "tag implications".

The last one is basically sub-tags but with more flexibility and dead simple to implement: if A implicates B, then tagging an image with A will automatically tag it with B. So you can tag "American Male Novelist", and then the system will automatically add "American", "Male", "Novelist", "Writer", etc. (after such implications were added).

It much easier than Wikipedia's categories, but Wikipedia's way is of course intentional because categories is meant to have a stronger hierarchy than mere tags.