After running the above, the tables look as follows (\dt+
):
List of relations
Schema | Name | Type | Owner | Size | Description
--------+---------------+-------+-------+-------+-------------
public | users_no_id | table | phil | 58 MB |
public | posts_no_id | table | phil | 12 MB |
public | users_with_id | table | phil | 62 MB |
public | posts_with_id | table | phil | 10 MB |
The sum of the tables:
- no_id total: 70MB
- with_id total: 72MB
The indexes (\di+
):
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+----------------------------+-------+-------+---------------+---------+-------------
public | users_no_id_pkey | index | phil | users_no_id | 57 MB |
public | posts_no_id_pkey | index | phil | posts_no_id | 2208 kB |
public | posts_no_id_username_idx | index | phil | posts_no_id | 4480 kB |
public | users_with_id_pkey | index | phil | users_with_id | 21 MB |
public | users_with_id_username_idx | index | phil | users_with_id | 44 MB |
public | posts_with_id_pkey | index | phil | posts_with_id | 2208 kB |
public | posts_with_id_user_id_idx | index | phil | posts_with_id | 2808 kB |
The sum of indexes:
- no_id total: 64MB
- with_id total: 66MB
After this, I increased the posts count to 1000000, leading to the following:
Tables:
List of relations
Schema | Name | Type | Owner | Size | Description
--------+---------------+-------+-------+--------+-------------
public | users_no_id | table | phil | 58 MB |
public | posts_no_id | table | phil | 103 MB |
public | users_with_id | table | phil | 62 MB |
public | posts_with_id | table | phil | 89 MB |
- no_id total: 161MB
- with_id total: 151MB
Indexes:
Schema | Name | Type | Owner | Table | Size | Description
--------+----------------------------+-------+-------+---------------+-------+-------------
public | users_no_id_pkey | index | phil | users_no_id | 57 MB |
public | posts_no_id_pkey | index | phil | posts_no_id | 18 MB |
public | posts_no_id_username_idx | index | phil | posts_no_id | 48 MB |
public | users_with_id_pkey | index | phil | users_with_id | 21 MB |
public | users_with_id_username_idx | index | phil | users_with_id | 44 MB |
public | posts_with_id_pkey | index | phil | posts_with_id | 18 MB |
public | posts_with_id_user_id_idx | index | phil | posts_with_id | 24 MB |
- no_id total: 123MB
- with_id total: 107MB
With lower row counts the no id approach uses less data, also because the id approach requires an additional unique index. Once row count gets bigger, the id approach uses more data, but still not as much as I had expected.
E.g. the index on the foreign key for the no id approach is only double the size of the id appraoch. Also, the posts table in the no id appraoch stores the full username on each row, but is still only ~16% bigger.