I am designing a postgres database for scientific data. It has about 30-40 tables, many of which are lookup tables with less than 10 records. The bulk of the data are concentrated in just a few tables with ~ 1,000,000 records that reference the lookup tables with foreign keys. There will be web user interface that accesses the data via a GraphQL API.
I always use the same INT data type for all primary and foreign key types. Is there merit in optimizing the primary keys on the lookup tables as SMALLINT to conserve space on the big tables (and perhaps a modest overall performance)? Or is it best to just keep all key columns the same data type to reduce complexity and confusion for humans and software accessing the data?
(The lookup data could change over time so I can't use ENUM.)