Обсуждение: One Sequence for all tables or one Sequence for each table?
Hi, if you define a SERIAL column postgresql's default is to generate a sequence for each SERIAL column (table_column_seq). But you can use one sequence for the whole database like this: CREATE dbsequence; CREATE TABLE one ( id int4 NOT NULL DEFAULT nextval('dbseq') ); CREATE TABLE two ( id int4 NOT NULL DEFAULT nextval('dbseq') ); One drawback: You reach the internal end of a sequence faster if you use your sequence for all tables. But this can be avoided if you use int8 datatype. Are there other drawbacks/benfits using one Sequence for each table or one sequence for all tables? kind regards, janning
Hi, I suppose the paralel work will be a problem if you are using one sequence for all tables. If you insert a large amount of rows in different tables there will be great slowdown because your sequence is the bottle neck of your database. All the inserts must read from it one by one. If you have many sequences (one for each table PK) every insert in a different table will use different sequence and this will improve performance. Kaloyan Iliev Janning Vygen wrote: >Hi, > >if you define a SERIAL column postgresql's default is to generate a sequence >for each SERIAL column (table_column_seq). But you can use one sequence for >the whole database like this: > >CREATE dbsequence; >CREATE TABLE one ( > id int4 NOT NULL DEFAULT nextval('dbseq') >); >CREATE TABLE two ( > id int4 NOT NULL DEFAULT nextval('dbseq') >); > >One drawback: You reach the internal end of a sequence faster if you use your >sequence for all tables. But this can be avoided if you use int8 datatype. > >Are there other drawbacks/benfits using one Sequence for each table or one >sequence for all tables? > >kind regards, >janning > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster > > > >
On Thu, Jun 02, 2005 at 12:58:33PM +0300, Kaloyan Iliev Iliev wrote: > Hi, > > I suppose the paralel work will be a problem if you are using one > sequence for all tables. If you insert a large amount of rows in > different tables there will be great slowdown because your sequence is > the bottle neck of your database. All the inserts must read from it one > by one. If you have many sequences (one for each table PK) every insert > in a different table will use different sequence and this will improve > performance. I don't know about this. Sequences are designed to be very efficient, they don't rollback and can be cached by backends. In several of the databases I setup, I sometimes arranged for sequences to start at different points so when you setup a foreign key there was no chance you linked it to the wrong table. This especially in cases where there might be confusion about which table links where. Using one serial for everything does this even better. As for performance, I think disk I/O is going to be an issue before getting sequence numbers will be... -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Вложения
Am Donnerstag, 2. Juni 2005 12:03 schrieb Martijn van Oosterhout: > On Thu, Jun 02, 2005 at 12:58:33PM +0300, Kaloyan Iliev Iliev wrote: > > Hi, > > > > I suppose the paralel work will be a problem if you are using one > > sequence for all tables. > > I don't know about this. Sequences are designed to be very efficient, > they don't rollback and can be cached by backends. > > In several of the databases I setup, I sometimes arranged for sequences > to start at different points so when you setup a foreign key there was > no chance you linked it to the wrong table. This especially in cases > where there might be confusion about which table links where. > > Using one serial for everything does this even better. As for > performance, I think disk I/O is going to be an issue before getting > sequence numbers will be... I guess i will use one sequence for all tables if there are now drawbacks. BTW: OIDs are using the same conecpt, don't they? And for me it makes sense to use a sequence only for getting a unique identifier and nothing else. even better if this identifier is unique among all tables. Thanks a lot for your opinions! regards janning