2

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.)

Improve this question
4
  • when you can use smallint which means you have not that much rows, take it, as bigger the tables ge, so slower they get. you can't use int for 1 millionen rows – nbk Dec 11 '20 at 0:12
  • 1
    Hi, and welcome to the forum! SMALLINT is fine if you'll never have more than 32767 lookup values. – Vérace Dec 11 '20 at 0:54
  • @nbk INT ranges from -2147483648 to +2147483647. Should be fine for 1 million rows? It's moot because the large tables all use compound keys, not unique identifiers. – narmaps Dec 11 '20 at 16:56
  • it was only a typo it should tinyint instead of int in the last sentence – nbk Dec 11 '20 at 17:43
2

It's a little subjective, and there's certainly nothing wrong with reduced complexity by defaulting to INT (since it covers most use cases and there's almost no tangible difference in performance) but generally if your tables are going to be so small in record count, using SMALLINT is a good idea from a best practice standpoint. SMALLINT gets you +-32767 rows (for a total of 65,534). So it is definitely sufficient for your small tables.

When deciding on the size for a datatype, generally planning for anywhere between twice as much up to a magnitude more of the max amount values you think is possible is a decent rule of thumb, depending on how confident you are in that rough estimation.

At the end of the day, if 25% of your tables have 1,000,000 rows in them, switching one of the smaller tables to SMALLINT instead of INT will only save you about 20 MB of space overall (which is peanuts). If you did it for all your small tables, then at most you're saving about half a gigabyte of space. There's probably better optimizations to go after currently and you can let sleeping dogs lie. Moving forward if you want to take SMALLINT into better consideration on new tables, it's not a bad idea.

Improve this answer
3
  • 2
    I've always thought it''s a pity that PostgreSQL doesn't have the UNSIGNED type that MySQL has, then SMALLINT could be ~ 64K! – Vérace Dec 11 '20 at 0:59
  • Thanks @J.D. The tables with > million records use a four column compound key. So the savings would really add up if all four keys were 50% smaller. Also, these large tables are the area of the system that will grow the most over upcoming years. – narmaps Dec 11 '20 at 16:59
  • @narmaps No problem! "What's a lot of space" is subjective and dependent on a case by case basis, but nowadays disk space is super cheap, especially when talking about anything under 100 GB. I re-read what I wrote and it's a little confusing but what I actually calculated was if all of your large tables had every small tables key as a foreign key in it, for 10 large tables and 30 small tables, out of 40 total tables. In other words 10 large tables * 1,000,000 rows * 2 bytes saved per small tae key * 30 small table keys = 600,000,000 bytes = 600 MB saved (in the absolute worst case). – J.D. Dec 11 '20 at 17:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.