Better data type to store a long ID

Question

I'm working on a project using Rails. In this app, we use OAuth to enable Facebook and Google sign-up/log-in. I want to store the various providers (FB or Google) and user IDs associated with these providers in a separate table/model that will be related to the user.

My question is the next one, the user IDs provided by FB and Google are really long numbers (Google's is 21 digits). Would it make sense to store it as an integer or better store it as a string? Which are the pros-cons of those two options?

I'm using Postgres 10.14.

Answer 1

You cannot store that many digits in a Postgres integer column. bigint allows a maximum of 19 digits (9223372036854775801 being the biggest positive number: 2^63-1).

While the number is all digits, the most efficient, exact data type is numeric. The manual:

The actual storage requirement is two bytes for each group of four decimal digits, plus three to eight bytes overhead.

21 decimal digits stored as numeric will occupy 18 bytes in RAM and 15 bytes on disk. See:

• What is the overhead for varchar(n)?
• Lookup performance of Numeric vs String