Should I use Postgres' bigserial for records in a new application?

Question

I'm working on a new application which stores it's data in a PostgreSQL database. The kind of data amongst other stuff is meter records from some measuring devices. There can be hundreds and thousands of those devices per customer and all normally provide few records per day. Newer records replace older ones by simply deleting the older ones and get inserted as new records with new ids. What my company doesn't know is how many customers with how many measuring devices and records per day we will really need to store.

As this is a new application, I would like to ask for your advice regarding using serial vs. bigserial as the ID for the meter records. Is there any noticable performance downgrade on current x64 CPUs or such? Storage for the ID field should increase by a factor of 2, right? Any obvious reasons why not to use bigserial?

Thanks!

Answer 1

As far as performance is concerned there is no difference - assuming that PostgreSQL has been properly compiled to take advantage of x86_64 platform. For example, adding two 32-bit ints takes the same time as adding 64-bit ints. However, if it was compiled for plain x86 (32-bit), there will be some (marginal?) drop in performance because it somehow needs to emulate 64-bit arithmetic using only 32-bit registers.

In a nutshell: make sure you use 64-bit version of PostgreSQL and you should be good to go!

Answer 2

There is no significant performance problems with bigserial datatype as sequence and primary key of a table. For details read documentation and older question about subject.

BTW: if you have 10000 devices and each device produce 10 records per day, integer is enough for 60 years so I think integer is enough ;-)