Would we run out of INTEGER ID's in PostgreSQL for frequent operations?

Question

Our PostgreSQL tables and sequences use INTEGER for ID's.

According to this, the limits are https://www.postgresql.org/docs/9.6/static/datatype-numeric.html

integer     4 bytes     typical choice for integer  -2147483648 to +2147483647

The application is a timeline scheduler similar to Outlook that drops and re-creates appointments on every Add / Edit / Delete. Whenever you make a change, you can introduce new Gaps in your timeline or delete old Gaps, which we are also saving as regular appointments.

Therefore, every common Add/Edit/Delete operation has the potential to use up 5-6 new IDs (we're saving immediately as an Ajax operation), not just 1. Before a day's worth of appointments is finalized, you can end up wasting about 30-50 IDs due to the additional Gap ("fake"-appt.) persistence, which is a business requirement.

Question: Would we quickly run out of INTEGER ID's? If we do, is it easy to adjust an existing PostgreSQL DB to use BIGINT for ID's and sequences?

Answer 1

If we do, is it easy to adjust an existing PostgreSQL DB to use BIGINT for ID's and sequences

Sequences always use bigint internally, so you only need to adjust the columns using the sequence.

However altering a column from integer to bigint requires a rewrite of the table as the storage of the column changes (integer and bigint are fixed-width data types). This in turn requires an exclusive lock on the table which probably is easier if you stop access to the database. How long that will take depends on the size of the table (number of columns and number of rows).

Would we quickly run out of INTEGER IDs

Assuming you "burn" one sequence number per second, 24 hours a day, 7 days a week, you will run out of integers in roughly 68 years (twice as long if you allow negative numbers). That should give you enough time to plan for the downtime.