Thread: Any justification for sequence table vs. native sequences?
I just stumbled across this table in a database developed by a collegue: field_name | next_value | lock ------------+-------------+-------- id_alert | 500010 | FREE id_page | 500087 | FREE id_group | 500021 | FREE These "id_" fields correspond to the primary keys on their respective tables. Instead of making them of type serial, they are of bigints with a NOT NULL constraint, and the sequence numbers are being managed by the application (not the database.) I googled around a bit trying to find an argument either in favour of or against this approach, but didn't find much. I can't see the advantage to this approach over using native PostgreSQL sequences, and it seems that there are plenty of disadvantages (extra database queries to find the next sequence number for one, and a locking mechanism that doesn't play well with multiuser updates for two.) Can anyone comment on this? Has anyone ever had to apply a pattern like this when native sequences weren't sufficient? If so, what was the justification? Thanks, -- ------------------------------------------------------------------------ *Doug Gorley* | doug.gorley@gmail.com <mailto:doug.gorley@gmail.com>
On 08/18/2009 01:14 PM, Doug Gorley wrote: > I just stumbled across this table in a database > developed by a collegue: > > > field_name | next_value | lock > ------------+-------------+-------- > id_alert | 500010 | FREE > id_page | 500087 | FREE > id_group | 500021 | FREE > > > These "id_" fields correspond to the primary keys > on their respective tables. Instead of making > them of type serial, they are of bigints with a > NOT NULL constraint, and the sequence numbers are > being managed by the application (not the database.) > > I googled around a bit trying to find an argument > either in favour of or against this approach, but > didn't find much. I can't see the advantage to > this approach over using native PostgreSQL sequences, > and it seems that there are plenty of disadvantages > (extra database queries to find the next sequence > number for one, and a locking mechanism that doesn't > play well with multiuser updates for two.) > > Can anyone comment on this? Has anyone ever had to > apply a pattern like this when native sequences > weren't sufficient? If so, what was the justification? One justification I can see is if there would otherwise be an unmanageably large number of individual sequences. I have an app in which there is a table containing "things" that have a type code. There can be an arbitrary number of type codes and in practice may be many dozens. Each "thing" also has a user-visible id number which users normally assign sequentially within each type. The app currently creates a sequence for each type and uses them to provide a default values for the id numbers. I am considering changing this to something like you describe. In my case there is a low insert rate so contention (which I read is the biggest problem with this approach) should not be an issue.
Doug Gorley <doug.gorley@gmail.com> wrote: > > I just stumbled across this table in a database > developed by a collegue: > > > field_name | next_value | lock > ------------+-------------+-------- > id_alert | 500010 | FREE > id_page | 500087 | FREE > id_group | 500021 | FREE > > > These "id_" fields correspond to the primary keys > on their respective tables. Instead of making > them of type serial, they are of bigints with a > NOT NULL constraint, and the sequence numbers are > being managed by the application (not the database.) > > I googled around a bit trying to find an argument > either in favour of or against this approach, but > didn't find much. I can't see the advantage to > this approach over using native PostgreSQL sequences, > and it seems that there are plenty of disadvantages > (extra database queries to find the next sequence > number for one, and a locking mechanism that doesn't > play well with multiuser updates for two.) > > Can anyone comment on this? Has anyone ever had to > apply a pattern like this when native sequences > weren't sufficient? If so, what was the justification? The only reason I can think to add that much complexity is to ensure gap-free sequences, which Postgres' internal sequences do _not_ guarantee. And yes, it's pretty much guaranteed to be slower than built in sequences, with blocking when multiple threads want a sequence all at the same time. I'm rather concerned by the third column, as I'm not sure what his implementation approach is, and I'm concerned that he's using a home-brewed locking mechanism instead of using table locks. -- Bill Moran http://www.potentialtech.com
Bill Moran <wmoran@potentialtech.com> writes:
> And yes, it's pretty much guaranteed to be slower than built in sequences, with
> blocking when multiple threads want a sequence all at the same time.
It's also going to create a vacuum bottleneck unless the insert rate is
quite low, because each ID assignment will create another dead row in
the sequence management table.
> I'm rather concerned by the third column, as I'm not sure what his implementation
> approach is, and I'm concerned that he's using a home-brewed locking mechanism
> instead of using table locks.
Indeed, that looks a bit scary/pointless. You could at least use
SELECT FOR UPDATE to lock the rows.
regards, tom lane