artifact ID column sequence overflowing

[Mark-Simulacrum]

I used a short (i16) for the artifact ID, thinking "we only have a couple thousand artifacts, we're not overflowing a ~35k integer anytime soon". It turns out that postgres ended up skipping a bunch of artifact IDs, so despite having ~2500 rows we have already exceeded the limit. I temporarily bumped the sequence down manually, but that's a short-term fix at best.

I think we need to run some sort of compaction, but every option I've seen so far is going to take a long time to run against the production database and is likely to cause locks or other problems -- in particular, https://dba.stackexchange.com/questions/111823/compacting-a-sequence-in-postgresql seems to not contain anything easily viable for us.

A possible alternative is to switch the column to an integer ID, but that seems likely to just offset the problem -- I am surprised to see how quickly we hit the 35k limit; maybe we're doing something wrong.

I am also somewhat annoyed that there doesn't appear to be a good way to convince postgres to locate the next empty slot in the column (i.e., finding unused integers). That would be just fine for our use case.

cc @rust-lang/infra -- I am inexperience with postgres or databases in general, would be happy to hear advice on how to best deal with this problem. The database schema is mostly visible in the migrations, and I am happy to run specific queries if it helps.

I manually tried to migrate a few rows by resetting the sequence and then doing update artifact set id = default where id = XXX; but that takes a long time and seems to lock out some queries (not quite sure, site went down though I think).

Edit: here is a CSV dump of the artifact table: https://gist.github.com/Mark-Simulacrum/e38783c6cb7e93bfc220cfd37c00aedd

[carols10cents]

Hey Mark, Jake linked me to this issue because we had to deal with this at one of my previous jobs -- we luckily noticed we were going to overflow an i32 ID column a few weeks before it happened but had to migrate it 😂 I have a little time to give you some directions to investigate and I might have some time to actually do some work to help in the next few days.

I'd say the first step would be change this to an i32. Even if the code is doing something wrong, this'll buy you a LOT more time to deal with the actual problem rather than spending your time manually resetting the sequence. Bits are cheap, your time is expensive :)

Given that there's only 2.5k rows that would need migrating (not sure how many foreign key usages of this ID there are?), I think it should be pretty quick to lock the table and change the type of the column-- I'd try this on a local database copy to see.

If it does take unacceptably long (not sure what the uptime requirements are for this exactly), then I think we would need to do this in separate steps that should all be quick and non-locking:

• Add a new column named like id_new with type i32 that's not any sort of key and can be NULL
• Add a trigger or code that any insert into this table adds the same value to id_new as id
• Copy all the values in id to id_new
• Put non-null and unique constraints on id_new
• Change the primary key to id_new and change all foreign key constraints to use id_new
• Change all code to use id_new instead of id
• Delete id
• Follow the same steps again if you want the column name to be id instead of id_new

Here are some links:

• https://stackoverflow.com/questions/29075413/change-primary-key-in-postgresql-table
• https://www.postgresql.org/docs/12/sql-altertable.html The "Notes" section in particular about what takes a long time and/or locks the table
• https://www.rainforestqa.com/blog/2014-06-27-zero-downtime-database-migrations
• https://rollout.io/blog/rails-migrations-zero-downtime/

Please let me know what you think and if this raises more questions!

[Mark-Simulacrum]

Okay, with roughly 5 hours of migrations, I think we're on i32 columns now. @carols10cents -- do you have any ideas on debugging why we're so sparsely using the space? I would have expected this if we had multiple concurrent writers, but we really have only 1, maybe 2 sometimes.

[carols10cents]

Sorry for the delay-- the most likely reason, as I understand it, is rolled back transactions. I see the artifact_id function that does the inserting into artifacts, and I see that function being called within a transaction here, but I don't immediately see why that transaction would be rolled back (nor am I sure that's the only place this is called within a transaction).

I have to go, but I'll take another look later.... does that sound possible though?

[Mark-Simulacrum]

That function is called often indeed, though usually it doesn't actually insert (we very rarely, essentially never, delete artifacts) -- and having only 2.5k means it succeeded that many times roughly over the last 6 months or so.

That said, maybe "ON CONFLICT DO NOTHING" is actually causing the counter to increment, then we detect the conflict on the name column, and end up exiting early? It looks like this answer indicates yes. Presumably if we switch the order to select first, and then failing that insert (still with an on conflict do nothing, in case of concurrent inserts), this would be resolved?

[carols10cents]

Looks like you're right! I tried making a table just like artifacts with:

create table artifact( 
    id integer primary key generated always as identity,
    name text not null unique, 
    date timestamptz,
    type text not null
);

Then I did two inserts with the same name, then one with a different name:

# insert into artifact (name, date, type) values ('hi', now(), 'try') on conflict do nothing returning id;
 id 
----
  1
(1 row)

INSERT 0 1
# insert into artifact (name, date, type) values ('hi', now(), 'try') on conflict do nothing returning id;
 id 
----
(0 rows)

INSERT 0 0
# insert into artifact (name, date, type) values ('bye', now(), 'try') on conflict do nothing returning id;
 id 
----
  3
(1 row)

INSERT 0 1

So id 2 is gone! I think your plan to try selecting first, then insert with on conflict nothing, sounds good.

[Mark-Simulacrum]

Great! Ok, I've deployed a change that should fix this then -- select id from artifact order by id; currently has this tail:

 46012
 46066
 46384
 46470
 46562
 46613
 46629
 46656
 46895
 46913
 47003
 47295
 47665
 47755
(2774 rows)

Hopefully the next few IDs that get inserted end up at roughly +1 increments. (Maybe a bit more if we do end up with concurrent inserts but that seems really unlikely).

[Mark-Simulacrum]

Okay, this seems to have fixed it:

 47755
 47772
 47773
 47774
 47775
 47776
 47777
 47778
 47779
 47780
 47781
 47782
 47783
 47784
 47785
 47786
 47787
 47788
 47789
 47790
 47791
 47792
 47793
 47794
 47795
 47796
 47797
 47798
 47799
 47800
 47801
 47802
 47803
 47804
 47805
 47806
 47807
 47808
 47809
 47810
 47811
 47812
 47813
 47814
 47815
 47816
 47817
 47818
 47819
 47820
 47821
 47822
 47823
 47824
(2827 rows)

Thanks @carols10cents! Hopefully it doesn't pop up again...