CODEWITHSUNDEEP
jsonperformancepostgresqlindexing
Andi Keikha
Andi Keikha

Reputation: 1316

Postgres Decision, JSON or an extra column?

I need to know which one is faster for retrieving an activity log and I couldn't find any on the comparison: Assume all the columns that needs to be searched on has btree indexing, and all the keys inside the json object that needs searching has GIN indexing.

case 1:
    ActivityID (int), actorID(int), action(string), object(string), Datetime(date), type(string)

case 2:
    ActivityID (int), actorID(int), Datetime(date), type(string), Log(json or hstor) {action="..", object=".."}

case 3:
    ActivityID (int), Datetime(date), Log(json or hstor) {actorID:"..", action="..", object="..", type=".."}

case 4:
    ActivityID (int), Log(json or hstor) {Datetime="..", actorID:"..", action="..", object="..", type=".."}

How can I decide which one to use? Querying for a bunch of records that has a specific Type or actorID in which one is faster? Is it faster when it's a key in the json object, or when it's an indexed separate column?

Upvotes: 1

Views: 542

Answers (1)

Arash.m.h
Arash.m.h

Reputation: 355

Choosing between JSON and a column, should be a more high level decision than just performance. I assume when someone decides to use JSON they have to use it because the classic columns were too much of hassle.

Postgres is a relational database after all so other than performance there are lots of limitations when using jsonb or hstore (think about joins and foreign keys)

Postgres's JSON is not the same as MongoDB's the later is not relational at all.

Back to your question,I didn't have a chance to actually do the test, but I can say the JSON's index performance in Postgres is at most as good as the columns.

Edit:

Also read this.

Upvotes: 1

Related Questions