[PostgreSQL] What data type to choose?

There is a simple task in the table is based on data for different types of entities. For example, there is a comments table, and it stores the comments for posts, photos, videos.

The table has two fields that are responsible for the entity entity_id is the entity id and entity_type – entity type (post, photo, video).

Field entity_id – itself an int (bigint NOT NULL)
But field entity_type makes me doubt. Now I made them 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. No better to do entity_type -
small int? How would int index easier to build, and on the other hand restricts the possible enum options.

If a big win in performance between enum and int small?
June 26th 19 at 14:08
4 answers
June 26th 19 at 14:10
Some you have no such enum. enum stowage of such person:
Entity_enum_type CREATE TYPE as ENUM ('post', 'image', 'video');

Well and accordingly in the table will be of type enum_type entity_enum_type .
https://www.postgresql.org/docs/9.6/static/datatyp...
Here's the enum is on disk 4 bytes for the value, clear and very effective.
This is the PostgreSQL implementation to version 9.2 in which there is no ENUM type.
In any case this is a string field with a limited version of the input. Even if we build an index on the ENUM type you gave, it would be more productive smallint? - zakary.Lockman47 commented on June 26th 19 at 14:13
Yah. in 9.1 enum - mina50 commented on June 26th 19 at 14:16
: Yes, I did not. To version 9. But the question remains..
Even if we build an index on the ENUM type you gave, it would be more productive smallint?
- zakary.Lockman47 commented on June 26th 19 at 14:19
I will say more in 8.3 was enum.
And performance you need to look specifically at your ( probably ancient ) version which is faster. General considerations - avoid unnecessary constraints ( aka system-level triggers ) is a good thing, and whether 2-bytovy type much faster 4-BYTOVOGO ( enum ) is unknown. My crystal ball tells me that in the real case there would be approximately a tie, but for the enum, we get type-safe.

In any case this is a string field

The numeric field. That you for convenient display whatever string is in storage properties are not affected. - herman.Labadie commented on June 26th 19 at 14:22
: The indices in principle is not very effective for data with low selectivity. For example, if you have thousands of records 600 will be of type "post", the index search all of these records is likely to be less efficient than no index.
However, in each case, you need to check.
The examples in the article - Markus_Langworth commented on June 26th 19 at 14:25
: thanks for the reply - zakary.Lockman47 commented on June 26th 19 at 14:28
June 26th 19 at 14:12
Make a table of entity types. Each entity type will have its own id. The table objects let the number of entity type.
That is, you are inclined to believe that you need to use int? - zakary.Lockman47 commented on June 26th 19 at 14:15
Yes. - mina50 commented on June 26th 19 at 14:18
: thanks for the reply - zakary.Lockman47 commented on June 26th 19 at 14:21
June 26th 19 at 14:14
> If there is a big win in performance between enum and int small?
There is a prize in the logic of the approach with enum type is convenient to use as an entity in the features.
June 26th 19 at 14:16
You can still unite entity_id and entity_type in one column (bigint),
if you want joint, you can use the unique ID in the entire database (prefix ID),
also, it will be convenient to refer to objects from other places just one number, without specifying the type, since the sewn in ID.
Interesting idea. Thank you - zakary.Lockman47 commented on June 26th 19 at 14:19

Find more questions by tags Data typesPostgreSQL