Performance impact of converting Integer to Smallint for low-cardinality fields

[Aldo Caruso]

Hello,

I have a table with a column containing only three distinct values (-1, 0, and 1). This field was originally defined as an INTEGER (4 bytes), though a SMALLINT (2 bytes) would have been sufficient.

As Firebird disallows a direct ALTER COLUMN TYPE in this context to prevent potential data loss, I am considering the standard workaround: creating a temporary SMALLINT column, migrating the data, dropping the original column, and renaming the new one. This process would also require re-evaluating all dependent stored procedures and triggers.

The table contains 250,000 records. Theoretically, switching to SMALLINT would save approximately 500 KB of space. Given that the total database size is 1.4 GB, I would like to ask the community:

• Is there a measurable benefit to disk I/O, memory footprint, or query performance for a dataset of this scale?

• Beyond "architectural elegance," is the overhead of refactoring dependencies (SPs/Triggers) and the metadata churn worthwhile for a 500 KB reduction?

I look forward to your insights.

Thanks in advance.

Aldo Caruso

[Gerdus van Zyl]

Having done something similar I found the difference in performance very small.
So I would not recommend it until you have other changes that would require a SP/Trigger refactor anyway.
If this column is something like an active/inactive/disabled status column and you are running firebird 5+ I had some good experimental results with a partial index for active records only.