performance type varchar(1) or smallint to store s

Question

I'll store a status from 0 to 7 and I want to know which is the better type field to store, considering performance and space on Postgres database: varchar(1) or smallint.

By the way, is there any difference to set a field varchar(1) or varchar(100), still talking about performance and space?

Answer 1

In my opinion you're fighting the wrong battle. You're worried about the performance impact of storing an integer instead of a single character field, which in my opinion is short-sighted thinking. The actual impact on performance of an integer vs. a single character is trivial, and I doubt this can be measured meaningfully. In my experience it's more important to reduce the cognitive loading on the developers and users of the system, and thus it's better to use character fields which are long enough to contain a reasonable description of the status instead of numeric values or single character abbreviations. Not having to remember what 1, 2, 'A', or 'X' mean is very helpful. Instead of these abbreviated values I suggest using easy-to-understand values such as 'READY', 'ACTIVE', 'PROCESSED', 'CANCELLED', etc.

As to the second part of the question - not really. There might be some trivial amount of time to move the longer string, but it's trivial unless you're talking about millions of values.

Best of luck.

Answer 2

While I agree with Bob Jarvis that this is really premature optimisation, I'll try to focus on the question as asked.

You're neglecting the most important choices. Your choices include:

• smallint
• enum
• "char"
• character and character varying

You could use an enumerated type. This is only really OK so long as you expect never to remove valid values, since PostgreSQL currently doesn't support deleting values from enum types.

Alternately, you could use the "char" data type. Yes, the quotes matter. It's a single character, like the C data type char. Without quotes char turns into character(1) at parse time.

varchar and character aren't really ideal for this because they're variable-width types with header overheads etc.

By the way, is there any difference to set a field varchar(1) or varchar(100), still talking about performance and space?

No. This is answered (many times) in other questions.