Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

How not to change PostgreSQL column type

notso.boringsql.com

Post reply on HN
Print view View original

11–20 of 31 posts

Re: How not to change PostgreSQL column type

#11

Post by dewey »

vrosas wrote: post #10

I’m probably alone here and this goes against the HN consensus but, as great a piece of tech Postgres is, I’ve often found it to be a premature optimization to pick as the main DB for newer companies. If you don’t know what your data models are, you’re going to end up in worlds of pain constantly running schema migrations like the OP describes.

I'd say Postgres is very often the right choice for newer company as it's well understood, easy to operate and you know you don't have to switch to a new DB because the vendor got acquired / merged / shutdown after 2 years or is going through growing pains and deprecations themselves.

If you give your schema a good thought (The one place where you shouldn't rush and take shortcuts at the beginning) and for example use jsonb columns and later move data out of it if you notice you need to query on it more performantly you can get very far.

The pain of data model migrations is also usually not that big if the company isn't very large and has a lot of legacy yet.

Re: How not to change PostgreSQL column type

#12

Post by j-cheong »

Changing a PostgreSQL column type without following the author's instructions and just running the following command is VERY anti-pattern. Confused why people do this in the first place.

ALTER TABLE table_name ALTER COLUMN column_name [SET DATA] TYPE new_data_type

>you need to make sure the source system has enough disk space to hold the WAL files for a long enough time

if the asynchronous replication process has an external buffer instead of the WAL, then it addresses this issue

Re: How not to change PostgreSQL column type

#13

Post by beeboobaa3 »

vrosas wrote: post #10

I’m probably alone here and this goes against the HN consensus but, as great a piece of tech Postgres is, I’ve often found it to be a premature optimization to pick as the main DB for newer companies. If you don’t know what your data models are, you’re going to end up in worlds of pain constantly running schema migrations like the OP describes.

> premature optimization

I'm extremely curious to hear what you consider a better/simpler choice. At least postgres gives you the tools to do schema migrations, and if you're operating at a scale where such migrations become a problem (i.e. probably not for a while) you really ought to know what you're doing.

Re: How not to change PostgreSQL column type

#14

Post by unclebucknasty »

vrosas wrote: post #10

I’m probably alone here and this goes against the HN consensus but, as great a piece of tech Postgres is, I’ve often found it to be a premature optimization to pick as the main DB for newer companies. If you don’t know what your data models are, you’re going to end up in worlds of pain constantly running schema migrations like the OP describes.

It's not Postgres, specifically, as much as any SQL or non-schemaless database, right?

And if we're saying that's a problem, then sounds like we're going back into the NoSQL debates from a decade ago.

Hopefully not.

I think it's better to understand your schema as much as possible, and have a sane process for applying changes when needed. Defining a schema forces you to think about what you're building.

OTOH, the idea that developers on a project are just going to throw whatever new attributes they need into a document as they go along is a recipe for bugs and inefficiency. Also, near-instant technical debt, as early work frequently survives longer than anticipated.

You also don't completely escape data changes without pain when using a NoSQL database. If for instance you change a string to an int you'd still need to figure out what to do with existing data, either via conversion or handling in-code.

Re: How not to change PostgreSQL column type

#15

Post by paulryanrogers »

miohtama wrote: post #6

For ” How to Safely Change a PostgreSQL Column Type” (new column, trigger, etc). Is there a particular reason PostgreSQL cannot set up column migration itself out-of-the-box? I have used PSQL for many years and it is always a bit cumbersome to do the column migrations manually, even though the process itself is quite mechanical.

So Postgresql and MySQL can both do full table copies as needed. And their locking has gotten better. They also can do more changes in place. Yet still too often they'll need exclusive locks which blocks even reads.

For very big tables it's often better to manually add a column, backfill then drop-swap.

Re: How not to change PostgreSQL column type

#16

Post by chatmasta »

j-cheong wrote: post #12

Changing a PostgreSQL column type without following the author's instructions and just running the following command is VERY anti-pattern. Confused why people do this in the first place. ALTER TABLE table_name ALTER COLUMN column_name [SET DATA] TYPE new_data_type >you need to make sure the source system has enough disk space to hold the WAL files for a long enough time if the asynchronous replication process has an…

> Confused why people do this in the first place

Probably because every tutorial on the Internet, along with the docs, recommends doing it this way. All the gotchas are buried in the footnotes.

Re: How not to change PostgreSQL column type

#17

Post by mattashii »

j-cheong wrote: post #12

Changing a PostgreSQL column type without following the author's instructions and just running the following command is VERY anti-pattern. Confused why people do this in the first place. ALTER TABLE table_name ALTER COLUMN column_name [SET DATA] TYPE new_data_type >you need to make sure the source system has enough disk space to hold the WAL files for a long enough time if the asynchronous replication process has an…

> Confused why people do this in the first place.

Because you lose a significant amount of performance if you start adding NULL and variable-length columns just because you're afraid of a table rewrite.

Because the resulting table will not have had 1 table of update-induced bloat at the end of the operation.

Because you can be sure the modification is applied atomically and you as the user can be sure the migration from A to B goes through as expected or has a graceful rollback to the old data, rather than getting stuck or failures halfway through the migration.

Because toasted data from DROP-ed columns is not removed from storage with the DROP COLUMN statement, but only after the row that refers to that toasted value is updated or deleted.

...

Every column you "DROP" remains in the catalogs to make sure old tuples' data can be read from disk. That's overhead you now will have to carry around until the table is dropped. I'm not someone who likes having to carry that bloat around.

Re: How not to change PostgreSQL column type

#18

Post by [deleted] »

paulryanrogers wrote: post #15
miohtama wrote: post #6

For ” How to Safely Change a PostgreSQL Column Type” (new column, trigger, etc). Is there a particular reason PostgreSQL cannot set up column migration itself out-of-the-box? I have used PSQL for many years and it is always a bit cumbersome to do the column migrations manually, even though the process itself is quite mechanical.

So Postgresql and MySQL can both do full table copies as needed. And their locking has gotten better. They also can do more changes in place. Yet still too often they'll need exclusive locks which blocks even reads. For very big tables it's often better to manually add a column, backfill then drop-swap.

[deleted]

Re: How not to change PostgreSQL column type

#19

Post by dgan »

j-cheong wrote: post #12

Changing a PostgreSQL column type without following the author's instructions and just running the following command is VERY anti-pattern. Confused why people do this in the first place. ALTER TABLE table_name ALTER COLUMN column_name [SET DATA] TYPE new_data_type >you need to make sure the source system has enough disk space to hold the WAL files for a long enough time if the asynchronous replication process has an…

Not everyone has billion rows in their tables ¯\_(ツ)_/¯

Re: How not to change PostgreSQL column type

#20

Post by evanelias »

Thaxll wrote: post #9

This is pretty much how pt-online-schema-change from Percona works. Good old MySQL days...

My thoughts exactly. It's surprising that external online schema change tools for Postgres have only become a thing fairly recently! The only two I'm aware of are:

* pgroll: Written in Golang, first commits June 2023. https://github.com/xataio/pgroll

* pg-osc: Written in Ruby, first commits Dec 2021. https://github.com/shayonj/pg-osc

Meanwhile over in the MySQL and MariaDB ecosystem, external OSC tools have been around for quite some time, starting with oak-online-alter-table over 15 years ago. The most popular options today are pt-online-schema-change or gh-ost, but other battle-tested solutions include fb-osc, LHM, and the latest entry Spirit.

Post reply on HN