Скачать или смотреть How many rows of data are deemed untenable and require a switch to a JSON column

Hello everyone! I hope this video has helped solve your questions and issues. This video is shared because a solution has been found for the question/problem. I create videos for questions that have solutions. If you have any other issues, feel free to reach out to me on Instagram:   / ky.emrah  

Below, you can find the text related to the question/problem. In the video, the question will be presented first, followed by the answers. If the video moves too fast, feel free to pause and review the answers. If you need more detailed information, you can find the necessary sources and links at the bottom of this description. I hope this video has been helpful, and even if it doesn't directly solve your problem, it will guide you to the source of the solution. I'd appreciate it if you like the video and subscribe to my channel!How many rows of data are deemed untenable and require a switch to a JSON column

Context
To describe the situation I find myself in currently, let's take the 3 tables relevant to this:
CREATE TABLE steam_user (
id SERIAL PRIMARY KEY,
username TEXT NOT NULL,
steam_id TEXT NOT NULL,
profile_url TEXT NOT NULL,
avatar_url TEXT NOT NULL
);

CREATE TABLE achievement (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
display_name TEXT NOT NULL,
description TEXT NOT NULL,
icon_url TEXT DEFAULT '' NOT NULL,
categories SMALLINT[] DEFAULT '{}' NOT NULL
);

CREATE TABLE achievements_on_steam_users (
id SERIAL PRIMARY KEY,
steam_user_id INTEGER NOT NULL, /* foreign key */
achievement_id INTEGER NOT NULL, /* foreign key */
achieved BOOLEAN NOT NULL DEFAULT FALSE,
unlock_time TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00'
);

CREATE TABLE steam_user (
id SERIAL PRIMARY KEY,
username TEXT NOT NULL,
steam_id TEXT NOT NULL,
profile_url TEXT NOT NULL,
avatar_url TEXT NOT NULL
);

CREATE TABLE achievement (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
display_name TEXT NOT NULL,
description TEXT NOT NULL,
icon_url TEXT DEFAULT '' NOT NULL,
categories SMALLINT[] DEFAULT '{}' NOT NULL
);

CREATE TABLE achievements_on_steam_users (
id SERIAL PRIMARY KEY,
steam_user_id INTEGER NOT NULL, /* foreign key */
achievement_id INTEGER NOT NULL, /* foreign key */
achieved BOOLEAN NOT NULL DEFAULT FALSE,
unlock_time TIMESTAMP NOT NULL DEFAULT '1970-01-01 00:00:00'
);

I'm pulling achievements for a specific set of games from the Steam API and then connecting them together in the achievements_on_steam_users junction table.
achievements_on_steam_users
The problem
achievements_on_steam_users gains over 1000 rows every time i pull achievement data for a user. This will happen once every time a new user's data is queried on the site. I pull the state of all achievements from steam and then save it to my database.
achievements_on_steam_users
Use Case
One of the pages required me to query all of these entries for one specific user. Optimally I'd prefer to be able to JOIN this query with the achievement table. Furthermore I would like to support a case, where users can reset their achievement data (on Steam). This is why I would prefer to have rows with the achieved = FALSE state in them and just update as I go, instead of only adding completed achievements to this list, that I'd then have to delete.
JOIN
achievement
achieved = FALSE
These are the two main queries that I'm worrying about:
SELECT *
FROM achievements_on_steam_users
WHERE steam_user_id = $1 /* some id */

SELECT *
FROM achievements_on_steam_users
WHERE steam_user_id = $1 /* some id */




QUERY PLAN




Seq Scan on achievements_on_steam_users (cost=0.00..25.60 rows=1328 width=21)


Filter: (steam_user_id = 1)






QUERY PLAN




Seq Scan on achievements_on_steam_users (cost=0.00..25.60 rows=1328 width=21)


Filter: (steam_user_id = 1)





QUERY PLAN



QUERY PLAN

QUERY PLAN


Seq Scan on achievements_on_steam_users (cost=0.00..25.60 rows=1328 width=21)


Filter: (steam_user_id = 1)



Seq Scan on achievements_on_steam_users (cost=0.00..25.60 rows=1328 width=21)

Seq Scan on achievements_on_steam_users (cost=0.00..25.60 rows=1328 width=21)

Filter: (steam_user_id = 1)

Filter: (steam_user_id = 1)
SELECT *
FROM achievements_on_steam_users
INNER JOIN achievement ON achievements_on_steamSource of the question:
https://stackoverflow.com/questions/7...

Question and source license information:
https://meta.stackexchange.com/help/l...
https://stackoverflow.com/