title: Are postgres JSON indexes efficient enough compared with classic normalized tables?
body: <p>Current Postgresql versions have introduced various features for JSON content, but I'm concerned if I really should use them - I mean, there is not yet "best practice" estabilished on what works and what doesn't, or at least I can't find it.</p>
  
  <p>I have a specific example - I have a table about objects which, among other things, contains a list of alternate names for that object. All that data will also be included in a JSON column for retrieval purposes. For example (skipping all the other irrelevant fields).</p>
  
  <pre><code>create table stuff (id serial primary key, data json);
  insert into stuff(data) values('{"AltNames":["Name1","Name2","Name3"]}')
  </code></pre>
  
  <p>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 (<a href="https://stackoverflow.com/questions/18404055/index-for-finding-element-in-json-array">Index for finding an element in a JSON array</a>, for example). However, SHOULD it be done that way or is it a perverse workaround that's not recommended?</p>
  
  <p>The classic alternative, of course, is to add an additional table for that one-to-many relation, containing the name and a foreign key to the main table; the performance of that is well understood. However, that has it's own disadvantages as then it means either data duplication between that table and JSON (with possible integrity risk); or creating that JSON return data dynamically at every request, which has it's own performance penalty.</p>