# What is the best way to store large lists of user statistics?

I am trying to figure out the best way to store user statistics data in a table. For each user property, there can be anywhere from one to a couple thousand integer values (realistically, these would would average to a number in the hundreds). I know that PostgreSQL allows columns to contain `integer[]` values, but is this really the best solution when I could potentially be storing a a couple hundred or a thousand integers in one cell?

Here is example table with a few of the user properties I might store in it:

``````| UserStatistics                                                                                      |
|-----------------------------------------------------------------------------------------------------|
| user_id | user_song_likes | user_album_likes | user_artist_likes | user_followers | user_following  |
|-----------------------------------------------------------------------------------------------------|
| 1       | [12,56,34,11,7] | [1,154,32,33,84] | [5,14,54,43,10,2] | [55,63,23,2,4] | [43,78,2,4,22]  |
|-----------------------------------------------------------------------------------------------------|
| 2       | [1,2,3,4,87,89] | [62,71,4,6,12,2] | [13,17,35,76,1,2] | [91,17,7,87,9] | [21,17,79,98,1] |
|-----------------------------------------------------------------------------------------------------|
| 3       | [45,53,42,19,2] | [7,14,144,67,93] | [13,14,2,23,3,73] | [11,56,5,12,1] | [34,91,23,10,5] |
|-----------------------------------------------------------------------------------------------------|
``````

In this example, the `user_song_likes` array will realistically hold hundreds of values (I scaled down the array for the sake of this example). So will the `user_album_likes` array. `user_artist_likes` will most likely come close to a hundred values, but it will definitely surpass that for some users. `user_followers` will average a between 20 and 80 values, but there are going to be a few select users with tens of thousands of followers. `user_following` is going to average between 100 and 2,000 values for almost every user.

All this considered, I think it would be very inefficient to store user statistics with `integer[]` columns. What are my alternative options? What would be a reasonably efficient way to store this data?

• Have you considered a separate table for user_song_likes etc? Feb 7, 2021 at 19:34
• normalizing the database, helps you pon the long run
– nbk
Feb 7, 2021 at 20:20
• @Lennart I am not seeing how separate table would help. Each cell would still have to contain multiple values, unless I somehow created a new column for every liked song. Feb 7, 2021 at 21:09
• No, each time a user likes a song that would be represented as a row. (1,12),(1,56),.. I was curious whether you considered it and discarded it, and the reason for it Feb 7, 2021 at 21:50
• Hi, and welcome to the forum! Under no circumstances should you have comma separated lists of anything in an RDBMS. Datums should be atomic - search on 1st Normal Form. In an RDBMS, "things" (users, songs, artists) are represented by tables. You can represent the relationships between them using Associative Entities - linking/joining tables (again, search). Any other way is not good! User likes Artist - have a u_likes_a table with a user_id field and an artist_id field! Feb 7, 2021 at 21:58

As others have mentioned, I'd look to normalize the data in your `UserStatistics` table. You could do something stupid simple as a generic `Likes` table and a `Follows` table (don't take my poor names, I'm sure you can come up with better ones :).

The `Likes` table would have its own primary key, and a foreign key field referencing `user_id`, and it could have a column called `like_id` and a column called `like_type`. The `like_id` would store one row per ID from the `user_song_likes`, `user_album_likes`, and `user_artist_likes` columns of your previous schema, and the `like_type` would represent if it's of type `song`, `album`, or `artist`.

You could then implement the same idea for your `Follows` table (e.g. store one row per ID of each `follower` and `following` in a generic `follow_id` column with a `follow_type` column to determine which is which). Using a more normalized schema (even though this one is generic to keep things simple) is a more relational form of storing your data and will be more efficient for querying.

• I see. I had to research what it means to "normalize" a database, but once I understood 4th normal form I was able to see how this helps. +1 Feb 8, 2021 at 20:48
• @CardinalSystem Sorry, I almost always link a good doc on it whenever I drop that word, but forgot to this time. Will update my answer when I get a chance for future readers, but glad you understand it now. :)
– J.D.
Feb 8, 2021 at 23:34

``````CREATE TABLE user_song_likes
( user_id... NOT NULL REFERENCES users (user_id)
, song_id... NOT NULL REFERENCES songs (song_id)
- - if order is important a timestamp can be used
, PRIMARY KEY (user_id, song_id)
);
``````

You may have valid objections against a relational design like that, I was curious whether you considered it and rejected it.

For such a design you may want to have a look at a columnar store, I have not used one in postgres but I Googled and found Citus and Swarm64. Perhaps there are others as well.

• It will definitely be easier to add or remove a song from a relational design like this. You can add an index for song_id, and an index for user_id to easily get songs a user likes, or all users that like a song...not so easy to do that with the OP example design. Apr 1, 2023 at 19:31

I would do something like this, personally:

``````    --holds stats (song, artist, etc.)
create table stat(
stat_id serial primary key,
name text
);

--holds values per stat
create table stat_value(
stat_value_id serial primary key,
stat_id int references stat(stat_id),
value text
);

--users
create table user(
user_id int primary key,
name text
--etc...
);

--relates users to stat values
create table user_stat(
user_stat_id bigserial primary key,
user_id int references user (user_id),
stat_id int references stat (stat_id), --not really necessary, but would speed things up if you didn't want to scan all stat_values for which stat it is
stat_value_id int references stat_value (stat_value_id)
);

``````

This would allow you to:

• maintain referential integrity
• not have to create a table per stat (though you may want to)
• quickly search all users of a given stat
• quickly search all stats of a given user
• more easily add and remove data (contrasted with scanning user by user and updating arrays with all previous values +/- a value)