Using SMALLINT for id column is a bad idea
... unless you enjoy your app stopping working at some point.
Don’t be a cheap-ass.
Saving 2 bytes on an id field by using a SMALLINT instead of an INT is like skipping the foundation when building a house to save on concrete costs.
On a 10,000 user base, you saved 20kB. Congrats. (That’s $0.0000025 in the most expensive storage type at AWS).
On a 100,000 user base, you saved… Nothing because the SMALLINT can’t store values up 65,535 so congrats again, so the whole system collapsed.
While, with an INT, you can store up to 4 billions values. and if you need more, the BIGINT is here for you with up to 2^64 - 1 values.
Hopefully, this specific problem is very simple to solve, see the image below. However, sometimes, early optimisations like this one can impact the whole architecture and are difficult to solve.
The lesson here is to always take into account the magnitude of the impact of your decisions. Why impose constraints on a system when it offers little to no benefits?
Don’t be a cheap-ass.
