Does it matter to convert integer primary key into equivalent string?

Question

In a PostgreSQL DB, I have a sortable bigint primary key, which is a bit verbose in terms of readability.

I want to encode it as base36 or base64, like using char(n). In theory the number of bytes should be the same as before, so it does not take more space. Is that correct?

Are there other concerns? For example sorting, collation?

Answer 1

There are potential performance differences and sorting differences (collation dependent) per this DBA.StackExchange answer and it's linked answers such as this one. I highly recommend reading through all the information referenced in that answer.

These sections of the above linked articles are specifically relevant:

There is no performance difference among these three types [char, varchar, text]...

Short answer: integer is faster than varchar or text in every aspect.

Therefore integer (and BIGINT) is more performant of a data type, even of equal data length.

Answer 2

You could keep the bigint PK, but for user interaction-related purposes (if that is why you want to reduce the length of the values) you could introduce a generated column that would return a string of characters representing your PK value in the desired form.

ALTER TABLE
  YourTable
ADD
  MyBigintIDInBaseN text
  GENERATED ALWAYS AS MyDecimalToBaseNConversionFunction(MyBigintID) STORED
;

The conversion function would need to be a custom function (because I do not believe PostgreSQL offers a built-in one for this task), marked as IMMUTABLE so that it can be used in a generated column expression.

That way you would continue using the bigint PK internally, including as a sorting criterion where necessary and as a reference target for other tables' FKs. For display purposes, however, you would use the computed column.

If your custom representations of PKs need to be used as query arguments as well, it would be a good idea to create an index on the computed column to help the performance.