How to implement a simple primary key on an id field in postgres

Question

I've been digging through stackover flow and it seems odd that a powerful database management software such as postgres does not have a simple way to provide this.

The best solution I have so far is

id serial primary key,,

but in the event that you want to do an

INSERT VALUE ON CONFLICT DO NOTHING

(I'm adding this last part b/c postgres cannot do INSERT OR IGNORE), the sequence will count on even after insertions failed.

So The table you get might look something like:

id | name
1 | amy
5 | bob
12 | john
104 | thomas

where the in between indexes were failed insertions. The app works, but I find this annoying. Suggestions?

Suggestion? Get used to it. There is no problem with a serial primary key having gaps. Incidentally, this behavior is true of all databases that have such functionality. It would be very expensive (performance-wise) to remove those gaps. — Gordon Linoff
– Gordon Linoff, Commented Oct 5, 2017 at 0:37
The only responsibility of a primary key column is to uniquely identify a row. The actual value is meaningless. -4635 is as good a primary key value as 42 or 107 or anything else that is unique in that table — user330315
– user330315, Commented Oct 5, 2017 at 5:35
Get used to the speed this provides and learn to not be annoyed with performance improvements. — Joe Love
– Joe Love, Commented Oct 5, 2017 at 7:54
@GordonLinoff sqlite has insert or ignore, is that expensive? forgive me for not up to your speed. — user2167582
– user2167582, Commented Oct 5, 2017 at 14:58
@user2167582 . . . If you rollback a transaction after the sequence has been added you are going to get gaps. — Gordon Linoff
– Gordon Linoff, Commented Oct 5, 2017 at 23:26

LoztInSpace · Accepted Answer · 2017-10-05 01:26:36Z

2

To build on @Gordon Linoff 's comment, this behaviour is a vital part of scalability by allowing the DB to process inserts concurrently across connections.

Without it, to achieve the no gap scenario, every transaction would have to complete or fail in its entirety before the next ID could be assigned to any table. DBs that use the concept of a SEQUENCE would not be able to cache blocks of values, meaning every allocation of a sequence number (for whatever reason) would require a lock which in turn could block.

In both cases, in order to guarantee the next value, the DB cannot proceed until nothing else is pending. You'd basically end up with a giant queue of serialised inserts and they database would not scale, at least for inserts.

All databases work like this and this is the reason why. I hope that helps you understand, even if you still find it annoying. The only real way around it is to allocate a true sequence number after the event in a big batch or as you SELECT but you'll generally find out it's rarely if ever worth doing.

answered Oct 5, 2017 at 1:26

LoztInSpace

5,6971 gold badge17 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Craig Ringer Over a year ago

Right. You can do it, but it performs terribly, and it's not worth doing unless there's a compelling business need.

LoztInSpace Over a year ago

I don't even know if you can do it. The standard DB tools like sequences and identity columns are all built to be concurrent and therefore with gaps. SELECT MAX(id) doesn't work unless you somehow lock the table and even then Postgresql, like many databases, will read the last known version and carry on so you'd have the same problem. If you could, it would be a case of working really hard to make it do something you don't want to happen.

Craig Ringer Over a year ago

Sure you can, google "gapless sequence postgres". You use a counter table that you UPDATE ... SET id_counter = id_counter + 1 RETURNING id_counter in a subquery or function you substitute for nextval.

Mike Sherrill 'Cat Recall' Over a year ago

@CraigRinger is right. One kind of compelling business need for gapless sequences is in accounting: check numbers, invoice numbers, and probably some other things I can't think of at the moment.

Craig Ringer Over a year ago

@MikeSherrill'CatRecall' Definitely, there are appropriate uses. I kinda wish PostgreSQL had a canned helper for that. But people would inevitably just use it when they shouldn't then get confused by the resulting deadlocks and terrible performance, so making people implement it themselves isn't all bad.

Collectives™ on Stack Overflow

How to implement a simple primary key on an id field in postgres

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related