91DarioDev 91DarioDev - 3 years ago 149
SQL Question

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

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?

Answer Source

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).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download