All we need is an easy explanation of the problem, so here it is.
I have a massive table that is about to run out of positive integers for the autoincrementing integer primary key. I’d like to buy myself a few years to fix the underlying architectural issues. I think I can do this by altering the sequence to
Are there gremlins here I don’t know about that could cause bad performance issues? We’re not ordering anywhere in our queries off of the ID, but I’m wondering if there’s somthing about how the data is physicaly stored on the disk that could cause me trouble.
My Proposed Solution
alter sequence alert_send_id_seq minvalue -2147483646; alter sequence alert_send_id_seq restart with -2147483646;
CREATE TABLE public.alert_send ( id integer NOT NULL, user_id integer, date timestamp without time zone, sent boolean ); CREATE SEQUENCE public.alert_send_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER TABLE ONLY public.alert_send ALTER COLUMN id SET DEFAULT nextval('public.alert_send_id_seq'::regclass); ALTER TABLE ONLY public.alert_send ADD CONSTRAINT alert_send_pkey PRIMARY KEY (id);
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
No other issues. Using negative values is the fastest way to buy time when you running out of int range. Rarely used in DBA practice because it is difficult to ensure that there are no queries for which sorting by id is important or that the application itself will not be surprised by negative values.
The internal format of an integer type in postgresql is the ordinary int32 type from the C language, without further complication.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂