Remove defaulted id column once pkey has been changed

[theelderbeever]

Hindsight is 20-20 and we should have setup our database differently at the start however, I am looking for some possible suggestions on how to handle the following scenario.

We are using TimescaleDB (PostgreSQL 14.2). With this in mind we needed composite primary keys in order to make hypertables. Right now the way we have handled this was to

1. Create our tables via piccolo letting it generate the id column as the sequential pkey in the initial migration
2. Generate a new empty migration. In here we write raw SQL to drop the id pkey and generate new composite keys with multiple columns not including the id column. Then generate the hypertables. This works well enough.
3. The id column has generally just ridden along add seq values and not being used. Not ideal but so be it.
4. This morning we maxed out the int4 values for the default type in the seq
5. Currently, I attempting to fix this in prod by updating the type on the id to BIGINT however, as we have ~2 billion rows of data per table I am expecting this to take on the order of 12+ hours where the database is out of commission.

Okay so that is the problem above (fun one I know) but I am wondering is there a way to safely drop this id column from our tables via the ORM in such a way that piccolo stays happy? We aren't using these columns so they are just causing problems and taking up space at the moment. If I could go back I would have defined my own pkey in the initial migration but well... whoops.

[dantownsend]

It's a hard one. Piccolo tables needs some kind of primary key, and it doesn't currently have syntax to support composite primary keys.

I came across this article which might help you. It's Citus instead of Timescale, but they're similar enough. Even so, 2 billion rows still meant 20 minutes of downtime which might still be unacceptable.

It's kind of a hack, but you could set primary_key=True on one of your other columns - even though it's not an actual primary key, Piccolo will think it is, and should be happy.

class MyTable(Table):
    my_column = Varchar(primary_key=True)

When you run piccolo migrations new my_app --auto it will generate a migration. If you run it nothing will happen, as Piccolo doesn't currently know how to swap primary keys on a table.

This will only work though if you have no foreign keys pointing to the table - not sure if this is the case or not.

[theelderbeever]

Yeah we don't use FKs due to the style of our data they aren't necessary. And yep I read the same Citus article. Was worried piccolo may get frustrated during all of that but setting the primary_key=True hack may help there.