Postgresql column type performance

Question

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:

Json type

With this approach, the column will store JSON object and use POSTGRESQL json operators to check existence of a fruit.
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.

None of the approaches is good. Go the way @GMB suggests, it's by far the best.Further to that the bridge table may have extra attributes - time of purchase, quantity, price etc. — Stefanov.sm
– Stefanov.sm, Commented Nov 11, 2020 at 11:59

GMB · Accepted Answer · 2020-11-11 10:38:30Z

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'
)

Collectives™ on Stack Overflow

Postgresql column type performance

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