Postgres performance implications of using negative integer space for IDs

All we need is an easy explanation of the problem, so here it is.

The Problem

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 min(integer).

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;

Table Definition

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.

Method 1

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 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply