Is there any significant performance difference when using string vs bigint as a primary key in Postgres (Aurora)? 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 (&quot;30722102503933550&quot;::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.