We can use same logic for both types (string/bigint) to generate consecutive ids (e.g. like https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c). This way both are naturally sortable.

So, let's say we have a value 30722102503933550.

What is the real performance difference if we would leave it as bigint (30722102503933550::bigint) vs using as a string ("30722102503933550"::text) for a primary key.


The question of performance can only be assessed if you know the exact SQL statement. There is no way to answer it in general.

That said, you should go with bigint. A bigint takes 8 bytes of storage, while a text with the value in your question requires 18 bytes (1 byte compressed TOAST header). This becomes particularly relevant if you want an index on the column.

Besides, comparing numbers is faster than comparing strings.

Not the answer you're looking for? Browse other questions tagged or ask your own question.