4

I've got a Ruby web app running on Heroku with a Postgres database. I've noticed the auto-incremented IDs have a gap in them around the time that the Postgres database was under "maintenance" by Heroku.

The gap is a one-time thing, and then the IDs increment by 1 again. Like so:

ID   |                 Created at |
2959 | 2016-02-14 21:07:05.149797 |
2960 | 2016-02-14 21:15:05.03284  |
2961 | 2016-02-14 22:59:19.634962 |
2994 | 2016-02-15 09:25:30.969881 |
2995 | 2016-02-15 09:44:38.49678  |
2996 | 2016-02-15 09:51:00.282624 |

The maintenance by Heroku happened in between the two records (2961 and 2994) being created, at 2016-02-15 04:00:00.

I've seen an explanation that a failed transaction will result in an ID being skipped over on the next successful commit, but I can't see anything in the application logs to indicate that any records were being created around the time.

james246
  • 1,884
  • 1
  • 15
  • 28
  • 1
    A failed transaction could do it, but so could a handful of other innocuous things. Are you worried that data has been lost, or just wondering why there could be gaps? – David Feb 23 '16 at 14:08
  • @David I don't think there's been any data loss (due to little/no traffic at that time), but just wondering what might've caused this behaviour – james246 Feb 23 '16 at 14:35
  • 1
    Not sure if this is helpful, but I myself just experienced this issue and in researching, found another similar [SO question](https://stackoverflow.com/questions/16976193/devise-user-id-skips-several-numbers-in-auto-increment). The interesting thing is, we all appear to be using Heroku & PostgreSQL _AND_ each of our respective gap counts is **exactly 33**. Mine from `171` to `204`. Unfortunately, I don't have any answers beyond that. – Eric Norcross Aug 10 '18 at 04:26
  • That's fascinating; I have discovered that in my app (Rails on Heroku with Postgres), I have "gaps" of **33** unused IDs every month for the last 7 months (the lifetime of the app). – Ollie Bennett Apr 07 '22 at 20:05

2 Answers2

3

See answer from heroku here and more on why this happens here.

mwal
  • 2,803
  • 26
  • 34
0

If your question is whether records were lost during that time, it'd take a code and log audit to figure out 1. how that could potentially happen and 2. whether it did happen.

If your question is why this happens or whether it should happen... that's down to implementation, on the part of both PostgreSQL and Heroku.

kungphu
  • 4,592
  • 3
  • 28
  • 37
  • Yes, it does seem to be some implementation detail of either how Heroku transition from their "follower" database back to the original database after maintenance has completed. Or perhaps something to do with Postgres itself. Either way, Heroku support assures me it's not a problem (and expected behaviour), so I'm just curious as to why it doesn't pick up where it left of in the sequence.. – james246 Feb 24 '16 at 16:17
  • 1
    It's certainly vexing, but then it's also a good reminder that 'autoincrement' IDs are intended not specifically *to* autoincrement, but rather to be unique-in-context identifiers that don't require the user's intervention for generation. As a potentially-useful relevant side note, for projects that need more reliable uniqueness, PostgreSQL's contrib package supplies [`uuid-ossp`](http://www.postgresql.org/docs/current/static/uuid-ossp.html), which -- among other things -- lets you specify UUID generation as a default for fields, including primary keys. – kungphu Feb 25 '16 at 00:57