与classic 规范化表相比，postgres JSON 索引是否足够高效

I will need some queries in the form "list all objects where one of altnames is 'foobar'." The expected table size is on the order of a few million records. Postgres JSON queries can be used for that, and it can also be indexed (Index For Finding Element in JSON array, for example). However, SHOULD it be done that way or is it a perverse workaround that's not recommended?

It can be done that way but that doesn't mean that you should. In some sense, the best practice is well documented already (see e.g. using hstore vs using XML vs using EAV vs using a separate table) with a new datatype which, for all intents and practical purposes (besides validation and syntax), is no different from prior unstructured or semi-structured options.

换句话说，这是一头新妆的老猪.

JSON offers the ability to use inverted search tree indexes, in the same way as hstore, array types and tsvectors do. They work fine, but keep in mind that they're primarily designed for extracting points in a neighborhood (think geometry types) ordered by distance, rather than for extracting a list of values in lexicographical order.

To illustrate, take the two plans that Roman's answer outlines:

• 执行index scan的一个直接遍历磁盘页面，按照索引指示的顺序检索行.
• The one that does a bitmap index scan starts by identifying every disk page that might contain a row, and reads them as they appear on disk, as if it was (and in fact, precisely like) doing a sequence scan that skips useless areas.

回到你的问题:如果你使用Postgres表作为巨大的JSON存储，凌乱和超大的inverted tree indexes确实会提高应用程序的性能.但它们也不是一颗灵丹妙药，在处理瓶颈时，它们也不会让你达到正确的关系设计.

归根结底，这与您决定使用hstore或EAV时得到的结果没有什么不同:

1. 如果它需要一个索引(即它经常出现在where子句中，或者更重要的是出现在join子句中)，您可能希望数据位于单独的字段中.
2. If it's primarily cosmetic, JSON/hstore/EAV/XML/whatever-makes-you-sleep-at-night works fine.