![]() In essence a PK couples two constraints - the data inside the column has to be unique and not null. Column constraintsĪs the next step we will do a couple of tricks that will later help us create a primary key (PK) constraint on this column. Īfter all the values are copied the original column and new column should both have the same data and thanks to the trigger it’s also staying in sync with the new incoming data. UPDATE SET new_id = id WHERE id BETWEEN 0 UPDATE SET new_id = id WHERE id BETWEEN 1000 UPDATE SET new_id = id WHERE id BETWEEN 2000. But it is ok because we copy the data in smaller quick transactions so the table is always accessible for other processes. This will be the most time consuming operation that can run for hours. Now we need to backfill the existing data - copy the values from the original column. ![]() id RETURN NEW END $ BODY $ LANGUAGE PLPGSQL CREATE TRIGGER set_new_id_trigger BEFORE INSERT OR UPDATE ON FOR EACH ROW EXECUTE PROCEDURE set_new_id () Backfill CREATE FUNCTION set_new_id () RETURNS TRIGGER AS $ BODY $ BEGIN NEW. ![]() The next two steps will help us set and keep consistent state with the original column - we create a procedure that will copy the value from the original column to a new column and this procedure will be automatically triggered on every new INSERT or UPDATE. ALTER TABLE ADD COLUMN new_id BIGINT Ĭreating a nullable column with no default value is a quick operation. The first thing we need to do is to create a new BIGINT column with a temporary name. To achieve the same goal we can split the process into a couple of independent operations that are by themselves very quick or don’t require such a lock. We had to come up with a different solution - one that would possibly allow us to mitigate the INT overflow problem without any downtime. At Zemanta we strive for maximum availability to not inconvenience our clients and such a large maintenance window was unacceptable. ![]() This operation and its lock can easily last a couple of hours (depending on the table size and hardware) and cause a complete downtime as other processes will not be able to acquire this resource. During the rewrite process it will acquire the very aggressive ACCESS EXCLUSIVE lock that essentially prevents any other operation on the table including reads. 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 |