Working with big PostgreSQL databases

Photo by PostgreSQL on Wikimedia

Strategies

ALTER TABLE my_table ADD COLUMN new_column VARCHAR(256) NOT NULL DEFAULT ‘Hello’
ALTER TABLE my_table ADD COLUMN new_column VARCHAR(256) NULL
CREATE INDEX my_index_idx ON my_table(my_column)
CREATE INDEX CONCURRENTLY my_concurrent_index_idx ON my_table(my_column)
— Create a temporary table with required data
SELECT id, 12345678 as value_to_update_to
INTO my_table_with_update_data
FROM my_table
where my_column > 800000;
— Use a temporary table to update data
WITH rows_to_update AS (
SELECT id, value_to_update_to
FROM my_table_with_update_data
)
UPDATE my_table SET my_column = rows_to_update.value_to_update_to
FROM rows_to_update
WHERE my_table.id = rows_to_update.id;
— Delete the temporary table
DROP TABLE my_table_with_update_data;

Conclusion

Software Engineer, specialising in Java, Kotlin, Spring and Docker