CODEWITHSUNDEEP
sqldatabasepostgresqldatabase-design
user8530752
user8530752

Reputation:

Postgresql column type performance

So, lets say I need to create a table. One of the columns will contain an array of strings e.g. fruits someone bought. Now, I need to perform lookups like who bought which fruit e.g. apple, etc. Now, I can choose two data types for the column:

  1. Json type

    With this approach, the column will store JSON object and use POSTGRESQL json operators to check existence of a fruit.

  2. string type

    With this, we concatenate every fruits e.g. "apple orange grapes". To check existence we can use LIKE function or any other string matching function.

Though I've not benchmarked myself, I wanted to know from the experts which would be the best approach. Lets assume for any row the number of fruits wont get any bigger than 200. We also get updates but those are not that frequent. We will most probably get more queries than updates.

Upvotes: 0

Views: 384

Answers (1)

GMB
GMB

Reputation: 222722

I would suggest to normalize your design. Instead of storing multiple values in a single column (either as JSON or as a delimited string), I would recommend creating another table, called a bridge table, where each user/fruit will be stored on a separate row.

create table users (
    user_id serial primary key,
    name text
);

create table fruits (
    fruit_id serial primary key,
    name text
);

create table users_fruits (
    user_id  int references users(user_id)   on delete cascade,
    fruit_id int references fruits(fruit_id) on delete cascade
);

This is the canonical way to store a many-to-many relationship in a relational database (one user may have several fruits, and one fruit may belong to multiple users).

Then, you can easily and efficiently look up all users that have fruit "apple" with a query like:

select u.*
from users u
where exists (
    select 1
    from users_fruits uf
    inner join fruits f on f.fruit_id = uf.fruit_id
    where uf.user_id = u.user_id and f.name = 'apple'
)

Upvotes: 3

Related Questions