10

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!

Thorsten Schöning
  • 3,501
  • 2
  • 25
  • 46

2 Answers2

5

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!

Piotr Miś
  • 981
  • 5
  • 6
  • One additional interesting statement I didn't recognize before in the docs for CREATE SEQUENCE: "Sequences are based on bigint arithmetic[...]On some older platforms[...]sequences use regular integer arithmetic[...]" So 32 Bit integers really seem to be some kind of legacy anyway. – Thorsten Schöning Aug 28 '14 at 06:04
3

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

Community
  • 1
  • 1
1ac0
  • 2,875
  • 3
  • 33
  • 47
  • 3
    Why not just use 64-bit integer and be safe for ages? :) The only price you pay is some memory, which is cheap anyway. – Piotr Miś Aug 27 '14 at 16:56
  • @uraf you are right, memory is cheap but ssd discs at the moment aren't ;-) – 1ac0 Aug 27 '14 at 17:01
  • It's not just those 10 records per day, I thought especially of the gaps because of rollbacked transactions during errors and such. Of course those shouldn't happen frequently at all... It's just about a gut feeling. :-) – Thorsten Schöning Aug 27 '14 at 20:45