AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
Postgresql non sequential primary key8/31/2023 ![]() ![]() That means that after recovering from a crash, the sequence may have skipped some values. Rather, the first call logs a value 32 numbers ahead of the current value, and the next 32 calls to nextval don’t log anything. Since writing WAL impacts performance, not each call to nextval will log to WAL. Now end the database session and start a new one:Īs with all other objects, changes to sequences are logged to WAL, so that recovery can restore the state from a backup or after a crash. Subsequent calls to nextval use those cached values, and there is no need to access the sequence.Īs a consequence, these cached sequence values get lost when the database session ends, leading to gaps: Then the first call to nextval in a database session will actually fetch that many sequence values in a single operation. To work around that, you can define a sequence with a CACHE clause greater than 1. Gaps in sequences caused by cachingĮven though nextval is cheap, a sequence could still be a bottleneck in a highly concurrent workload. Rolling back sequence values would reduce concurrency and complicate processing. After all, a sequence should not be the bottleneck for a workload consisting of many INSERTs, so it has to perform well. This intentional behavior is necessary for good performance. The second statement was rolled back, but the sequence value 2 is not, forming a gap. INSERT INTO be_positive (value) VALUES (314) INSERT INTO be_positive (value) VALUES (-99) ĮRROR: new row for relation "be_positive" violatesĬheck constraint "be_positive_value_check" INSERT INTO be_positive (value) VALUES (42) SELECT pg_get_serial_sequence('be_positive', 'id') the identity column is backed by a sequence: Id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, This little example shows how a gap forms in a sequence: ![]() Such cases will leave unused “holes” in the sequence of assigned values. For example an INSERT with an ON CONFLICT clause will compute the to-be-inserted tuple, including doing any required nextval calls, before detecting any conflict that would cause it to follow the ON CONFLICT rule instead. This is true even if the surrounding transaction later aborts, or if the calling query ends up not using the value. To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back that is, once a value has been fetched it is considered used and will not be returned again. As the documentation tells us, that is not the case for sequence values: We are used to the atomic behavior of database transactions: when PostgreSQL rolls a transaction back, all its effects are undone. This article shows the causes of sequence gaps, demonstrates the unexpected fact that sequences can even jump backwards, and gives an example of how to build a gapless sequence. Occasionally, gaps in these primary key sequences can occur – which might come as a surprise to you. I wrote about auto-generated primary keys in some detail in a previous article. Most database tables have an artificial numeric primary key, and that number is usually generated automatically using a sequence. ![]() Gap-less gapless index performance postgresql primary key rollback sequence ![]()
0 Comments
Read More
Leave a Reply. |