9/14/2023 0 Comments Psql add column![]() ![]() It is due to a binary incompatibility of both data types - the data is stored on a disk in sequential blocks and there is no free space in between to extend it to the larger data type. There is a catch - a change like this forces the database engine to rewrite the entire table. Wait! If you run this on your production database you and your users will probably have a bad time. In order to do that PostgreSQL has a DDL query for that, right? ALTER TABLE ALTER COLUMN TYPE BIGINT In PostgreSQL case that would be BIGINT - on 8 bytes it has a range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 possible values - over 9 quintillion - that has to be enough. ![]() We needed to change this column’s data type to allow us to store larger numbers. This still looks like a big number - over 2 billion records - but over time we recently started running into a problem that some high volume tables were getting dangerously close to this limit and we had to act. Since this is used for an auto incremented value starting at 1 we consider only the positive part. Taking a space of 4 bytes this allows us to store a range of -2,147,483,648 to +2,147,483,647 integers. At the beginning of the project nobody anticipated the sheer amount of data that will eventually be stored and all the table’s primary key ID columns were set to INT as a sort of go-to default data type that is usually used for this column. Over the years the platform grew a lot both in features and user base and together with that so did our database tables. ![]() Column migration from INT to BIGINT in PostgreSQLĪs a primary data storage for the transactional data for the Zemanta One platform we use PostgreSQL for its robustness, flexibility and time proven stability. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |