3 ms·

Awesome! Only a little over a billion more to go before GitHub’s very own OpenAPI Spec can start overflowing int32 on repositories too, just like it already doe

Awesome! Only a little over a billion more to go before GitHub’s very own OpenAPI Spec can start overflowing int32 on repositories too, just like it already does for workflows run IDs! https://github.com/github/rest-api-description/issues/4511 https://github.com/github/rest-api-description/issues/4511

bartread 1y ago
The company where I did my stint as CTO I turned up, noticed they were using 32-bit integers as primary keys on one of their key tables that already had 1.3 billion rows and, at the rate they were adding them, would overflow on primary key values within months… so we ran a fairly urgent project to upgrade the IDs to 64-bit to avoid the total meltdown that would have ensued otherwise.
- hobs 1y ago
  heh, that's happened at at least 5 companies I have worked at - go to check the database, find - currency as floats, hilarious indexes, integers gonna overflow, gigantic types with nothing in them.
  - rudasn 1y ago
    I bet you haven't seen indeces on decimals though! Fun times :)
    - azophy_2 1y ago
      Just curious as someone with limited experience on this. Whats wrong with it? decimal is consistent & predictable (compared than float), so it shouldn't be that big of a deal right? CMIIW
      - rudasn 1y ago
        Yeah, not a big deal but completely useless nonetheless as you would never really query your table for just the one decimal column (eg the price) but a couple more (eg the category and the price) so you'd have a multi-column index on those columns. The index on just the price column never gets used.
        cwbriscoe 1y ago
        What if you wanted to select "top 100 most expensive products" or number of products between $0.01 and $10, $10.01 and $100, $100.01 and $1000? Sure you could do a full table scan on your products table on both queries but an index on price would speed both queries up a lot if you have a lot of products. Of course you have to determine if the index would be used enough to make up for the extra time on index update when the price changes or products are added or deleted.
        erulabs 1y ago
        Cheap solution, sure, add an index. But you're asking an OLAP question question of an OLTP system. Questions like that are best asked at least of an out-of-production read replica or better an analytics db.
        robertlagrant 1y ago
        I don't really understand this - what is an out of production read replica? Why wouldn't it just go to a production read replica? And what is an "analytics db" in this context?
        gkalin59 1y ago
        You stream CDC events to have a 1 to 1 read replica in something like Snowflake/Databricks where you can run all kinds of OLAP workflows on this analytics db replica.
        robertlagrant 1y ago
    - hobs 1y ago
      They'd certainly need decimals in the first place, but yeah I have seen indexes on every column, multiple times, I have seen indexes such that the sum of their size are 26 times the size of the original data... that's actively being written to.
- gchamonlive 1y ago
  What are the challenges of such projects? How many people are usually involved? Does it incur downtimes or significant technical challenges for either the infrastructure or the codebase?
  - jiggawatts 1y ago
    Not the original commenter, but I've read through half a dozen post-mortems about this kind of thing. The answer is: yes. There's challenges and sometimes downtime and/or breaking changes are inevitable. For one, if your IDs are approaching the 2^31 signed integer limit, then by definition, you have nearly two billion rows, which is a very big DB table! There are only a handful of systems that can handle any kind of change to that volume of data quickly. Everything you do to it will either need hours of downtime or careful orchestration of incremental/rolling changes. This issue tends to manifest first on the "biggest" and hence most important table in the business such as "sales entries" or "user comments". It's never some peripheral thing that nobody cares about. Second, if you're using small integer IDs, that decision was probably motivated in part because you're using those integers as foreign keys and for making your secondary indexes more efficient. GUIDs are "simpler" in some ways but need 4x the data storage (assuming you're using a clustered database like MySQL or SQL Server). Even just the change from 32-bits to 64-bits doubles the size of the storage in a lot of places. For 2 billion rows, this is 8 GB more data minimum, but is almost certainly north of 100 GB across all tables and indexes. Third, many database engines will refuse to establish foreign key constraints if the types don't match. This can force big-bang changes or very complex duplication of data during the migration phase. Fourth, this is a breaking change to all of your APIs, both internal and external. Every ORM, REST endpoint, etc... will have to be updated with a new major version. There's a chance that all of your analytics, ETL jobs, etc... will also need to be touched. Fun times.
    - lcnPylGDnU4H9OF 1y ago
      > For one, if your IDs are approaching the 2^31 signed integer limit, then by definition, you have nearly two billion rows Just wanted to nitpick this; this is not actually definitively true. A failed insert in some systems will increment the counter and deleting rows usually does not allow the deleted ID to be re-used (new inserts use the current counter). Of course, that is beside the point: the typical case of a table approaching this limit is a very large table.
- cyberax 1y ago
  The same story happened inside Amazon.
- darkwater 1y ago
  Lived that with a MySQL table. The best thing is that the table was eventually dismissed (long after the migration) because the whole data model around it was basically wrong.
neomantra 1y ago
A couple weeks ago there was some Lua community issues because LuaRocks surpassed 65,535 packages. There was a conflict between this and the LuaRocks implementation under LuaJIT [1] [2], inflicting pain on a narrow set of users as their CI/CD pipelines and personal workflows failed. It was resolved pretty quick, but interesting! [1] https://github.com/luarocks/luarocks/issues/1797 https://github.com/luarocks/luarocks/issues/1797 [2] https://github.com/openresty/docker-openresty/issues/276 https://github.com/openresty/docker-openresty/issues/276

bartread 1y ago

The company where I did my stint as CTO I turned up, noticed they were using 32-bit integers as primary keys on one of their key tables that already had 1.3 billion rows and, at the rate they were adding them, would overflow on primary key values within months… so we ran a fairly urgent project to upgrade the IDs to 64-bit to avoid the total meltdown that would have ensued otherwise.

hobs 1y ago
heh, that's happened at at least 5 companies I have worked at - go to check the database, find - currency as floats, hilarious indexes, integers gonna overflow, gigantic types with nothing in them.
- rudasn 1y ago
  I bet you haven't seen indeces on decimals though! Fun times :)
  - azophy_2 1y ago
    Just curious as someone with limited experience on this. Whats wrong with it? decimal is consistent & predictable (compared than float), so it shouldn't be that big of a deal right? CMIIW
    - rudasn 1y ago
      Yeah, not a big deal but completely useless nonetheless as you would never really query your table for just the one decimal column (eg the price) but a couple more (eg the category and the price) so you'd have a multi-column index on those columns. The index on just the price column never gets used.
      - cwbriscoe 1y ago
        What if you wanted to select "top 100 most expensive products" or number of products between $0.01 and $10, $10.01 and $100, $100.01 and $1000? Sure you could do a full table scan on your products table on both queries but an index on price would speed both queries up a lot if you have a lot of products. Of course you have to determine if the index would be used enough to make up for the extra time on index update when the price changes or products are added or deleted.
        erulabs 1y ago
        Cheap solution, sure, add an index. But you're asking an OLAP question question of an OLTP system. Questions like that are best asked at least of an out-of-production read replica or better an analytics db.
        robertlagrant 1y ago
        I don't really understand this - what is an out of production read replica? Why wouldn't it just go to a production read replica? And what is an "analytics db" in this context?
        gkalin59 1y ago
        You stream CDC events to have a 1 to 1 read replica in something like Snowflake/Databricks where you can run all kinds of OLAP workflows on this analytics db replica.
        robertlagrant 1y ago
  - hobs 1y ago
    They'd certainly need decimals in the first place, but yeah I have seen indexes on every column, multiple times, I have seen indexes such that the sum of their size are 26 times the size of the original data... that's actively being written to.
gchamonlive 1y ago
What are the challenges of such projects? How many people are usually involved? Does it incur downtimes or significant technical challenges for either the infrastructure or the codebase?
- jiggawatts 1y ago
  Not the original commenter, but I've read through half a dozen post-mortems about this kind of thing. The answer is: yes. There's challenges and sometimes downtime and/or breaking changes are inevitable. For one, if your IDs are approaching the 2^31 signed integer limit, then by definition, you have nearly two billion rows, which is a very big DB table! There are only a handful of systems that can handle any kind of change to that volume of data quickly. Everything you do to it will either need hours of downtime or careful orchestration of incremental/rolling changes. This issue tends to manifest first on the "biggest" and hence most important table in the business such as "sales entries" or "user comments". It's never some peripheral thing that nobody cares about. Second, if you're using small integer IDs, that decision was probably motivated in part because you're using those integers as foreign keys and for making your secondary indexes more efficient. GUIDs are "simpler" in some ways but need 4x the data storage (assuming you're using a clustered database like MySQL or SQL Server). Even just the change from 32-bits to 64-bits doubles the size of the storage in a lot of places. For 2 billion rows, this is 8 GB more data minimum, but is almost certainly north of 100 GB across all tables and indexes. Third, many database engines will refuse to establish foreign key constraints if the types don't match. This can force big-bang changes or very complex duplication of data during the migration phase. Fourth, this is a breaking change to all of your APIs, both internal and external. Every ORM, REST endpoint, etc... will have to be updated with a new major version. There's a chance that all of your analytics, ETL jobs, etc... will also need to be touched. Fun times.
  - lcnPylGDnU4H9OF 1y ago
    > For one, if your IDs are approaching the 2^31 signed integer limit, then by definition, you have nearly two billion rows Just wanted to nitpick this; this is not actually definitively true. A failed insert in some systems will increment the counter and deleting rows usually does not allow the deleted ID to be re-used (new inserts use the current counter). Of course, that is beside the point: the typical case of a table approaching this limit is a very large table.
cyberax 1y ago
The same story happened inside Amazon.
darkwater 1y ago
Lived that with a MySQL table. The best thing is that the table was eventually dismissed (long after the migration) because the whole data model around it was basically wrong.

neomantra 1y ago

A couple weeks ago there was some Lua community issues because LuaRocks surpassed 65,535 packages. There was a conflict between this and the LuaRocks implementation under LuaJIT [1] [2], inflicting pain on a narrow set of users as their CI/CD pipelines and personal workflows failed. It was resolved pretty quick, but interesting! [1] https://github.com/luarocks/luarocks/issues/1797 https://github.com/luarocks/luarocks/issues/1797 [2] https://github.com/openresty/docker-openresty/issues/276 https://github.com/openresty/docker-openresty/issues/276