Multiple rows or column array I am working on a personal project where I need to be able to store and retrieve game statistics for a bunch of players and support very fast lookup on each player id. My current design (with unnecessary details omitted) looks something along the lines of

games: player1's score &gt; player2's score &gt; player3's score &gt; player4's score
+--------------------------+--------------------+--------------------+--------------------+--------------------+
| id BIGSERIAL PRIMARY KEY | player1 VARCHAR(8) | player2 VARCHAR(8) | player3 VARCHAR(8) | player4 VARCHAR(8) |
+--------------------------+--------------------+--------------------+--------------------+--------------------+
| 1                        | playerA            | playerB            | playerC            | playerD            |
+--------------------------+--------------------+--------------------+--------------------+--------------------+
| 1                        | playerE            | playerF            | playerE            | playerA            |
+--------------------------+--------------------+--------------------+--------------------+--------------------+
| 1                        | playerF            | playerB            | playerC            | playerE            |
+--------------------------+--------------------+--------------------+--------------------+--------------------+

player_games:
+-----------------------+-------------------+------------------+
| id SERIAL PRIMARY KEY | player VARCHAR(8) | gameid BIGSERIAL |
+-----------------------+-------------------+------------------+
| 1                     | asdf              | 1                |
+-----------------------+-------------------+------------------+
| 2                     | asdf              | 2                |
+-----------------------+-------------------+------------------+
| 3                     | fdsa              | 1                |
+-----------------------+-------------------+------------------+
| ...                   | ...               | ...              |
+-----------------------+-------------------+------------------+


and I will do a player lookup along the lines of

SELECT * FROM games WHERE id IN (SELECT gameid FROM player_games WHERE player='&lt;player&gt;')


Since I will be inserting tens of thousands of games per day, I am looking for ways to efficiently store data in player_games. The other alternative I am considering is to use an array, so instead we will have something along the lines of

player_games:
+-------------------------------+---------------------+
| player VARCHAR(8) PRIMARY KEY | gameids BIGSERIAL[] |
+-------------------------------+---------------------+


and I will do a lookup with

SELECT * FROM games WHERE id IN unnest(SELECT gameids FROM player_games WHERE player='&lt;player&gt;')


Which option is the better option here, and in the case of the first, is it beneficial to have an index on the player column? I will be batch inserting roughly 4000 rows per hour (90000 rows per day) into player_games after populating the historical data.
Given the information in the question I would start out with something like:

CREATE TABLE players
( player char(8) not null primary key
, additional attributes );

CREATE TABLE games
( game_id int not null primary key
, additional attributes );

CREATE TABLE player_games (match?)
( player char(8) not null
     references players (player)
, game_id int not null
     references games (game_id)
, participant_no smallint not null
,    constraint ... check (participant_no between 1 and 4)
,    primary key (player, game_id)
,    unique (game_id, participant_no) )

CREATE TABLE results 
( game_id int not null
, player char(8) not null
, score ... not null
,     foreign key (game_id, player)
      references player_games (game_id, player)


Example of queries that can easily be answered

Which games has a player participated in?

SELECT game_id 
FROM player_games
WHERE player = ?


JOIN with games if you need more info from each game

Which players participated in a game?

SELECT player 
FROM player_games
WHERE game_id = ?


JOIN with players if you need more info from each player

Order the players from game X according to there score:

SELECT player, score
FROM results
WHERE game_id = X
ORDER BY score