Skip to content
Advertisement

What is the default limit for auto generated primary Column(Integer) in SQLAlchemy with FastAPI and MYSQL?

Currently, I’m using Python’s fastAPI with SQLAlchemy and MYSQL. I’ve defined an auto-generated primary column as shown below. What’s the maximum number that this column can hold? Currently, the DB grows by 10k records per day, and I clear 70% of it on the weekends. The number of records will always stay constant, but I want to know the maximum limit that ‘id’ can hold before breaking down. This ‘id’ column is important to me, for sorting the results, and I don’t want it to overflow somehow.

class SampleData(SampleTable):
__tablename__ = 'demotable'

id = Column(Integer, primary_key=True)
col1 = Column(String(100))
col2 = Column(String(100))

What’s the maximum value that the column ‘id’ can ever hold?

Advertisement

Answer

The maximum value of a signed integer in MySQL (as well as most other programming languages) is 232-1, or 2147483647.

This is documented: https://dev.mysql.com/doc/refman/8.0/en/integer-types.html

If you use 10k id values per day, that will overflow in 214748 days, or just short of 588 years.

If that’s not enough, then make your id column a BIGINT. Then it will last until after Earth’s Sun envelops the Earth.

3 People found this is helpful
Advertisement