postgres: id auto-increment with millions of rows per day. is it bad?

user_8372336 0
Viewed 1059

in my database i save million of rows per day in a table and i delete all the rows after 2 weeks because i don't need them anymore.

i was wondering if it's good or bad to add a primary key id auto increment or i will hit too high numbers.

the table contains only inserts anytime an user issues a command with columns like the id of the user, the timestamp and the command. so to add a primary key i guess i necessarily need to add an auto-increment that will soon become very very high.

should i avoid to add it considering the amount of inserts?

primary-key auto-increment postgresql sql database
2 Answers

The "auto-incrementing" functionality in Postgres is provided by the serial data types. These are described in the documentation.

If you are inserting millions of rows per day, then I would advise you to use bigserial. You won't have to worry about overflow or resetting the value.

user_11440350

It's not bad, unless it causes performance problems and you have proof that the auto increment is the source of those problems, which is unlikely.

Frankly, 1 million rows per day is peanuts. You'll be fine. If you expect your scenario to continue for more than 3 years, you should use serial8 (64-bit) instead of serial (32-bit).

user_2561960
Related