Postgres JSONB table querying structure?

Question

So I'm building a table/database in Postgres using its JSONB feature. My question is about how to structure the table/query against it. Is it faster to include a separate relational column featuring a foreign key relating to something like users? Or just store a user in the JSON data and include that in your JSON query.

An example:

|   userID   |           json         |
|     1      |     { 'userID': 1,     |
|            |       'something': 5 } |

Or simply:

|         json        |
|   { 'userID': 1,    |
|    'something': 5 } |

CraigKerstiens · Accepted Answer · 2016-04-04 19:14:08Z

1

It depends a bit if you want a true foreign key or not. You can't actually create a foreign get to json->userId. If you want to be able to index against it for fast joins, you can absolutely create a functional index on json->userId or you can create a GIN index on the entire document.

If you do actually want to create a foreign key and have appropriate constraints there then it's worth materializing it out to an actual column.

answered Apr 4, 2016 at 19:14

CraigKerstiens

5,9891 gold badge27 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres JSONB table querying structure?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related