title: Using dynamic schema with postgres
body: <p>I have to decide how I want to implement dynamic schema. I will try to put some more description where my problem is. </p>
  
  <p>I use the postgres DB in latest version and app is written in Ruby and Hanami. I have requirement to implement view where I will display list of users in our application (table view). Here comes first requirement that it needs to contain dynamic data. For example user has name and birth_date and I store this data in Users table. In other table I store metrics related to user like number of views and comments. This is only part of data. I have couple more tables related to my User and I need to join them into one view. It doesn't sound very dynamic. I know. But I have another requirement. I want to allow users to add custom column to my table view. </p>
  
  <p>At this point we have table with following predefined columns:</p>
  
  <pre><code>name, birth_date, number_of_views, number_of_comments 
  </code></pre>
  
  <p>and we can add some custom ones.</p>
  
  <p>Now It becomes dynamic. It is not the end. I need to add filtering on this columns. So for example if given user has columns I mentioned earlier I should allow to filter on all of them (predefined and custom). I am trying to investigate how I should implement this feature.</p>
  
  <p>Now the question part :) I am thinking about how I should store this data. I am thinking about storing it as a JSONB column in my postgres. Scale of this is about 5kk of users. So this mean 5kk rows in this table. Not so much. I am wondering about filtering this data using dynamic filters. I tried to make some benchmarks for 5kk table and it worked well if I used pagination (without indexes). For querying whole dataset it took like 10sec so wasn't perfect.</p>
  
  <p>Questions</p>
  
  <ol>
  <li>Is JSONB good idea? I am not sure If I can define proper indexes if I will filter dynamically on this data (every query can be different). How to define indexes for this scenario?</li>
  <li>Is a good idea to store predefined columns as normal columns in table and only custom ones in JSONB? Will it give me any profit?</li>
  <li>I should I use postgres to that? I don't believe that postgres isn't good enough for this scale and I prefer to not change tool.</li>
  </ol>
  
  <p>Thanks for all answers. I don't have huge experience with dynamic schemas so every tip can be usefull ;P</p>