CODEWITHSUNDEEP
postgresql
Matas Kairaitis
Matas Kairaitis

Reputation: 326

Data type for storing long lists

What is the correct way of storing large lists in PostgreSQL?

I currently have a "user" table, where the "followers" column stores a list of the followers that that user has. This list is stored in JSON, and every time the server wants to add a new user to that list, it retrieves it from the database, appends the new user, and then replaces the old list with the new list.

The problem is that these lists tend to get quite lengthy, which might affect performance. Is it possible to simply append to the list directly via SQL without retrieving it and rewriting it later?

Upvotes: 0

Views: 1727

Answers (2)

Schien
Schien

Reputation: 3903

Use a separate table for followers. The table should have at least two columns: userid and followerid. And it's good practice to have a primary key for this table as well, so let's give it a "ufid".

You can do a select to get all the elements and compute the JSON string if your application needs it. But do not work with JSON or any other string representation of the list, as it defeats the purpose of a relational database.

To add a new follower, simply add a new record to the follower table with the userid; deleting and update are also done on the record level without working with the "other records".

Upvotes: 1

mike.k
mike.k

Reputation: 3447

If followers is a list of integers which are primary keys to their accounts, make it an integer array int[]. If they are usernames or other words, go with a string array character varying[].

To append to an array column you can do this:

UPDATE the_table SET followers = followers || new_follower WHERE id = user;

Upvotes: 0

Related Questions