[PostgreSQL] What data type to choose?

Question

PostgreSQL

There is a simple task, the table contains data for different types of entities. For example, there is a table of comments, and it stores comments for posts, photos, videos.
The table has two fields that are responsible for the entity entity_id - this is the id of the entity, and entity_type - the type of entity (post, photo, video).
The entity_id field is naturally int (bigint NOT NULL)
But the entity_type field makes me doubt it. Now I made them of type enum (character varying(255) NOT NULL) CONSTRAINT comments_entity_type_check CHECK (entity_type::text = ANY (ARRAY['post'::character varying, 'image'::character varying, 'video'::character varying]::text[]))
But I doubt the performance of this approach. Isn't it better to make entity_type -
small int? As though with int and it is simpler to build an index, and on the other hand enum limits possible options.
Is there a big performance gain between enum and small int?

Answer 1

Some kind of enum you don't have. the enum of a healthy person is:
Well, respectively, in the enum_type table there will be an entity_enum_type type.
https://www.postgresql.org/docs/9.6/static/datatyp...
Here is an enum that takes 4 bytes per value on disk, is clear and very efficient.

Answer 2

Make a table of entity types. Each entity type will have its own id. Next in the object table, let's go to the entity type number.

Answer 3

> Is there a big performance gain between enum and small int?
There is a gain in logic from the enum approach, it is convenient to use the type as an entity in functions.

Answer 4

You can also combine entity_id and entity_type into one column (bigint),
if you need to join, you can use unique ids throughout the database (prefix ids),
it will also be convenient to refer to objects from other places with just one number, without specifying the type, i.e. to. the type is sewn into the id.