0

I'm developing a Rails app that always create a new record and then delete if the user decide not to save it. So I just wonder whether ...

  1. My DB will ever run out of ID given the auto_increment behavior.
  2. Would deleted row's ID be reused again?

Edit: My Database is MySQL.

Ken Ratanachai S.
  • 3,307
  • 34
  • 43
  • 1
    Yes, you could run out (e.g., https://hackernoon.com/the-night-the-postgresql-ids-ran-out-9430a2dbb895) but there are ways around it. I don't recall if PG will re-use deleted IDs. – Dave Newton Dec 03 '18 at 22:54
  • 1
    @DaveNewton Newer Rails will use `bigserial` so running out is highly unlikely but not impossible. And PostgreSQL sequences are monotonically increasing so old values won't be reused. – mu is too short Dec 03 '18 at 23:00
  • Thank you all for your responses. My DB is currently MySQL. – Ken Ratanachai S. Dec 03 '18 at 23:19
  • 1
    You could look at a table directly in the database (i.e. using the `mysql` CLI tool) to see what type `id` is, then check the limit of that in the MySQL docs. While you're in the MySQL docs, read up on `auto_increment` to see how that deals with deleted IDs but I doubt they'll be reused. – mu is too short Dec 03 '18 at 23:38
  • @muistooshort Thanks! It's BIGINT and it would takes thousand years to reach the limit! (According to https://stackoverflow.com/a/9743313/267693) – Ken Ratanachai S. Dec 04 '18 at 02:49

2 Answers2

0

It depends on the DB you use and type of the ID field. If you use postgreql and serial type, the limit is 2147483647

https://stackoverflow.com/a/17755518/580346

mrzasa
  • 22,895
  • 11
  • 56
  • 94
0

Starting from Rails 5.1 Primary keys for both MySQL and Postgres has been changed to BIGINT, which means the largest ActiveRecord ID would be 9223372036854775807.

So even if your system creates new ID every millisecond it would still take 292,471,208 years to use up the BIGINT. Therefore, in short, the answer is NO.

Ken Ratanachai S.
  • 3,307
  • 34
  • 43