[BUG]indexing issue after FastAPI deployment

[ramyaragupathy]

As reported by Ralph on Slack:

The project numbers 18650 through to 19145 appeared to have been lost and will not be used. Is this correct?
19146 and 19147 have been created
Then 19148 through to 19178 are lost
19179 is created. 19180 is not created, 19181 is created
Then 19182 through to 19211 are lost
19212 is created
Then 19213 through to 19244 are lost
19245 is created
Then 19246 through to 19277 are lost
19278 and 19279 are created.
Is this correct or is something unusual happening?

I was doing a search for new projects and all of these returned a Project not found
Usually the next project is allocated the next available number but as you can see there are large gaps between the numbers.
495 projects between 18649 and 19146
31 projects between 19147 and 19179
29 projects between 19181 and 19212
31 projects between 19212 and 19244
31 projects between 19245 and 19278

18649 created 2025-04-21
then nothing until (gap of 495)
119146 created 2025-04-28
then nothing until (gap of 31)
19179 created 2025-04-28
then nothing until (gap of 29)
19212 (in DRAFT)
then nothing until (gap of 31)
19245 created 2025-04-28
then nothing until (gap of 31)
19278 created 2025-04-29

18783 then a gap of 66 to
18849 then a gap of 33 to
18882 and 18883 then a gap of 33 to
18948 then a gap of 33 to
18981

cc @prabinoid @manjitapandey @nischalstha9 @royallsilwallz @dakotabenjamin

[prabinoid]

I think the issue arises from the postgres and scaling rather than from the application level. PostgreSQL internally “pre‑logs” batches of 32 sequence values in the WAL (Write‑Ahead Log) for efficiency. Those pre‑logged values are lost on an unclean shutdown or restart, so the very next nextval() call appears to jump by ~32. Subsequent calls then return +1 as the in‑memory batch is consumed. These jumps cannot be eliminated by changing CACHE at the SQL level.

• WAL‑prelogged batches of 32
  PostgreSQL hard‑codes a constant SEQ_LOG_VALS = 32 in its source. On the first nextval() after a checkpoint (or restart/crash), it writes a single WAL record reserving the next 32 values—even if your sequence’s CACHE is 1—so that it need not WAL‑log every individual call. If the server then stops (whether a crash, restart, or manual pg_ctl restart), any unused values in that batch are simply abandoned.

When Aurora Serverless scales by pausing/resuming or resizing ACUs, both operations internally stop the PostgreSQL process (losing the in‑memory prelog batch) and then restart it—just like a failover and hence causing the same ~32‑ID jump when the serverless cluster scales down or scales back up. Also in Aurora, this behavior can additionally occur when sequence pages are evicted from the buffer cache. Increasing the parameter shared_buffers, may decrease the frequency at which these gaps occur, however there is no way to guarantee a true "gapless" assignment of sequence numbers. See aws repost

cc: @nischalstha9 @spwoodcock

Some References:
postgres, github, postgres git, aws repost, stackoverflow

[prabinoid]

I force‑killed the containers and saw the next last_value jump by ~32; then I performed a normal shutdown and restart and the sequence stayed exactly where it left off, incrementing by 1 as expected.

[prabinoid]

Tried in postgres to confirm the behaviour.

Screen.Recording.2025-05-09.at.13.33.30.mov

[spwoodcock]

This is some excellent research and debugging @prabinoid - well done!

I think we have probably observed similar behaviour in all our tools that use auto-incrementing IDs 😅

I don't think the problem matters much at the end of the day though - it might be a bit strange for users to see IDs skip numbers, but it doesn't be a huge deal (it's a bit pedantic 😆)

In terms of actual fixes:

• I imagine correctly shutting down would solve this. But I assume these gaps were caused by infra issues out of our hands over time? A SIGTERM should be sent to the postgres container, which would correctly clear the WAL and other shutdown processes in postgres.
• Or, this could be fixed by switching to UUID based project ID (actually used in DroneTM I believe), but I'm not sure which is more 'user-friendly': 8f699543-7aaa-4bbf-a213-2326c8bbf759 vs 12344 followed by 12366

[ramyaragupathy]

Adding further comments from Sam, Ralph and Patrik for further analysis:

there might be also issue with team #numbers ? Last week new team had number #504 today another new team is #735 .

just tested and this issue also exists for campaign numbers, the only campaign since the new TM has been '2025 Colombia Mapatón Nacional' and the campaign ID increased by increment of 33 from prior campaign 'OMH-ESA Climate Campaign 2025' (see screenshot)
It does not seem to exist for organization IDs, I just created a new organization and only went up by an increment of one.

confirm that the same is happening with teams numbers from starting from 438
438 (33 to) 471 (33 to) 504 (33 to) 537 (33 to) 570 (66 to) 636 (33 to) 669 (33 to) 702 (33 to) 735

cc @prabinoid @nischalstha9 @dakotabenjamin @manjitapandey

[spwoodcock]

Adding further comments from Sam, Ralph and Patrik for further analysis:

there might be also issue with team #numbers ? Last week new team had number #504 today another new team is #735 .

just tested and this issue also exists for campaign numbers, the only campaign since the new TM has been '2025 Colombia Mapatón Nacional' and the campaign ID increased by increment of 33 from prior campaign 'OMH-ESA Climate Campaign 2025' (see screenshot)
It does not seem to exist for organization IDs, I just created a new organization and only went up by an increment of one.

confirm that the same is happening with teams numbers from starting from 438
438 (33 to) 471 (33 to) 504 (33 to) 537 (33 to) 570 (66 to) 636 (33 to) 669 (33 to) 702 (33 to) 735

cc @prabinoid @nischalstha9 @dakotabenjamin @manjitapandey

Yep that makes sense - all related to what @prabinoid researched above 😅

Is it a big deal to the users?
(there is no data issue or anything here, and its unlikely we will run out of available IDs)

We could consider converting the IDs to entirely random integers or something, if it helps. But no doubt that would have other implications (shared links etc would all become invalid)

[spwoodcock]

Possible solution could be to create a manual trigger before insert, instead of using nextval()

CREATE OR REPLACE FUNCTION projects_id_seq() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
begin
  NEW.id := (SELECT COALESCE(MAX(id), 0) + 1 FROM projects);
  RETURN NEW;
end
$$;

Probably not the best of ideas though, as it could cause race conditions on multiple inserts and will reduce insert speed slightly.

That said, I stick by what I said above: gaps in primary keys are normal and safe (due to WAL or rollback). They are quite common too. This is purely an aesthetic thing, so it depends on how bothered users are to decide on what we do 😄