I am designing a postgresql database for an online card game. I want to provide users with the option to access their play history - users can see log of their games.

The data I want to save has the following structure:

Player Ids | game_data
A,B,C,D    | ..game log..
A,D,E,F    | ..game log..
D,C,A      | ..game log..
D,A        | ..game log..

Each game can have up to 22 participants, so Max number of players is 22, Min number of players is 2.

So far I have about 100M records. Every day I add about 500K records. I have about 500K Players. Player-ID is a 32 byte string (MD5).

I want players to be able to access their game_data, so I want to select game_log by Player-Id. I need to do it as fast as possible. What would be the best way to do it with Postgres? I would prefer to save all these data in a single table.

So far I am considering two approaches:

Approach 1

Make a field of JSON type and save all Player's in in JSON-Array and query JSON in a SELECT statement.

Approach 2

Make 22 fields in a table for each player (If there is no player field is NULL) and make an ugly query over all fields.

So far I don't like any of these approaches. Is there a better method for this?

  • 1
    Thank you for declaring it. Still, it is generally discouraged to post identical questions on multiple SE sites. I suggest you delete this one, since the duplicate on SO already has answers. You can always migrate the other one here, since it would be appropriate on dba.SE. Just flag it for migration. – Erwin Brandstetter Nov 6 '14 at 1:53