27

I am using postgresql, and was wondering how large

id INTEGER PRIMARY KEY

can get compared to 

id SERIAL PRIMARY KEY

In java an int is 4 bytes (32 bits) so it can get up to 2,147,483,647. Is this the case in postgresql? If so does that mean I cannot go past 2,147,483,647 rows?

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
abden003
  • 1,325
  • 7
  • 24
  • 48

2 Answers2

55

Here is a handy chart for PostgreSQL:

Name        Storage Size    Description                       Range
smallint    2 bytes         small-range integer               -32768 to +32767
integer     4 bytes         usual choice for integer          -2147483648 to +2147483647
bigint      8 bytes         large-range integer               -9223372036854775808 to 9223372036854775807
smallserial 2 bytes         small autoincrementing integer    1 to 32767
serial      4 bytes         autoincrementing integer          1 to 2147483647
bigserial   8 bytes         large autoincrementing integer    1 to 9223372036854775807

Source

Your assessment is right, you'd run out of unique ID's if you used a data type that was insufficient.

aneroid
  • 12,983
  • 3
  • 36
  • 66
Hart CO
  • 34,064
  • 6
  • 48
  • 63
  • 5
    .. but if you use a `bigint` (or `bigserial`, which is the same thing) [you really, really won't run out](http://stackoverflow.com/questions/13132939/what-happens-when-i-exhaust-a-bigint-generated-key-how-to-handle-it/13133035#13133035). – Craig Ringer Jul 20 '13 at 13:17
4

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases)

A bigserial is 8 bytes long. If that is not enough it is possible to use the 128 bits uuid:

create table t (
    id uuid primary key
);
insert into t (id)
select uuid_generate_v1mc();
select * from t;
                  id                  
--------------------------------------
 916bf7e6-f0c2-11e2-8d14-d372d5ab075f

The uuid_generate_v1mc function is provided by the uuid-ossp module

The main advantage of the uuid functions is that they generate an id that is very likely to be unique among different systems. A serial will have collisions between those systems.

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • 1
    And here I thought the trillion record tables I deal with were large, can't imagine needing more than nine quintillion ID's. – Hart CO Jul 19 '13 at 22:56
  • 4
    Heh... people afraid of exhausing a `bigint` key [just haven't done the maths](http://stackoverflow.com/questions/13132939/what-happens-when-i-exhaust-a-bigint-generated-key-how-to-handle-it/13133035#13133035). `uuid` keys can be very handy for distributed systems but are unnecessary for key exhaustion issues.. – Craig Ringer Jul 20 '13 at 13:18
  • @CraigRinger due to the birthday paradox bigint can have conflicts between keys long before it is "exhausted". UUIDs have 128 bits to avoid that problem. – Paul A Jungwirth Jun 24 '15 at 17:57
  • 4
    @PaulAJungwirth If you are generating keys at random, yes. With BIGSERIAL sequences, no. – Craig Ringer Jun 24 '15 at 21:41