How not to change PostgreSQL column type • Hacker News Board

Re: How not to change PostgreSQL column type

#21

Post by mattashii » Tue, May 07, 2024, 8:41 PM UTC

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.

> Is there a particular reason PostgreSQL cannot set up column migration itself out-of-the-box?

People haven't asked hard enough to the right people, I suppose. PostgreSQL is an open-source project, where wanted and needed features are supplied by willing individuals and companies, and vetted by the community so that the code base remains in good quality.

I just suppose no-one has bothered yet with implementing ALTER TABLE CONCURRENTLY to the point that it's been accepted by the community, though another reason might be because the induced metadata churn (only 1600 distinct user-defined columns available in each table at most) might become problematic with ADD COLUMN ... DROP COLUMN.

Re: How not to change PostgreSQL column type

#22

Post by junto » Tue, May 07, 2024, 8:49 PM UTC

Microsoft SQL Server has a similar issue but also similarly when adding a new column to a table, which I found out by accident when trying to deploy a migration that had worked fine in our non-production environment stages.

Adding the column as not nullable and setting a default is the absolute killer.

A work around when you still want to have a default is to do a three pass migration. First add the column as nullable and no default, then run an update to set the default on all rows, and finally change it to not nullable and set the default constraint.

It has a surprising difference in speed.

Re: How not to change PostgreSQL column type

#23

Post by xboxnolifes » Tue, May 07, 2024, 9:44 PM UTC

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'm confused by the use of premature optimization here. What exactly are you suggesting is the better default choice?

Re: How not to change PostgreSQL column type

#24

Post by yen223 » Tue, May 07, 2024, 10:46 PM UTC

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…

The unsafe ALTER COLUMN is one step.

The safe option is four steps minimum.

It's not hard to see why people would be tempted by the unsafe option.

Re: How not to change PostgreSQL column type

#25

Post by jordanthoms » Wed, May 08, 2024, 6:37 AM UTC

unclebucknasty wrote: post #14

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 ide…

Not quite any - in CockroachDB, all schema changes are non-blocking: https://www.cockroachlabs.com/docs/stable/online-schema-chan... . Yugabyte seems to be getting there with them also.

Still risks involved in migrations (mostly from the migration executing too quickly and creating high load in the cluster - the admission control system should have reduced this) and we have extra review steps for them, but it's been very useful to be able to migrate large tables without any extra application-level work.

Re: How not to change PostgreSQL column type

#26

Post by sgarland » Wed, May 08, 2024, 1:00 PM UTC

dewey wrote: post #11

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 us…

> give your schema a good thought

> use jsonb columns

These two statements are mutually exclusive in most cases. If you want JSON, don’t use a relational database.

IME, the “we’ll normalize this later” event never occurs.

Re: How not to change PostgreSQL column type

#27

Post by sgarland » Wed, May 08, 2024, 1:04 PM UTC

unclebucknasty wrote: post #14

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 ide…

> Defining a schema forces you to think about what you're building.

YES. Thank you. Sit down with pencil and paper, write down a table name, and start putting attributes into it. Then define a PK, and ask yourself if every attribute is directly related to the PK (a user named foo has an id of 1, and lives in country bar). Repeat. Then ask yourself how you’d join the tables. If you find that something _could_ be represented as a join, but isn’t, consider doing so.

Re: How not to change PostgreSQL column type

#28

Post by dewey » Wed, May 08, 2024, 1:26 PM UTC

sgarland wrote: post #26

dewey wrote: post #11

Earlier quoted context omitted.

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 us…

> give your schema a good thought > use jsonb columns These two statements are mutually exclusive in most cases. If you want JSON, don’t use a relational database. IME, the “we’ll normalize this later” event never occurs.

Not really, it doesn't mean you put everything into a jsonb field. It could mean that for example if you have some user specific settings you just drop them in a jsonb on the user itself instead of building a schema with mapping tables, permissions etc. as you don't know yet which options you want to support. Once thing stabilize you can pull it out of there.

From my personal experience this works really well and is a nice balance between a strict schema, and still allowing some flexibility for experiments.

Re: How not to change PostgreSQL column type

#29

Post by sgarland » Wed, May 08, 2024, 3:13 PM UTC

dewey wrote: post #28

sgarland wrote: post #26

Earlier quoted context omitted.

> give your schema a good thought > use jsonb columns These two statements are mutually exclusive in most cases. If you want JSON, don’t use a relational database. IME, the “we’ll normalize this later” event never occurs.

Not really, it doesn't mean you put everything into a jsonb field. It could mean that for example if you have some user specific settings you just drop them in a jsonb on the user itself instead of building a schema with mapping tables, permissions etc. as you don't know yet which options you want to support. Once thing stabilize you can pull it out of there. From my personal experience this works really well and is…

This will work, but you risk referential integrity violations, as well as performance problems at scale.

The main issue is what I said in “IME…” – tech debt builds, and people never want to go back and fix it. Just upsize the hardware, easy-peasy.

I would rather see a wide table with a bunch of bools indicating various options, personally. When that gets annoying or unperformant, split it out into logical groups. Color schemes can be their own table, email marketing preferences can be their own table, etc.

Re: How not to change PostgreSQL column type

#30

Post by dewey » Fri, May 10, 2024, 8:26 AM UTC

sgarland wrote: post #29

dewey wrote: post #28

Earlier quoted context omitted.

Not really, it doesn't mean you put everything into a jsonb field. It could mean that for example if you have some user specific settings you just drop them in a jsonb on the user itself instead of building a schema with mapping tables, permissions etc. as you don't know yet which options you want to support. Once thing stabilize you can pull it out of there. From my personal experience this works really well and is…

This will work, but you risk referential integrity violations, as well as performance problems at scale. The main issue is what I said in “IME…” – tech debt builds, and people never want to go back and fix it. Just upsize the hardware, easy-peasy. I would rather see a wide table with a bunch of bools indicating various options, personally. When that gets annoying or unperformant, split it out into logical groups. Col…

Which is precisely the caveat I mentioned 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.

If you put data inside where you want the database to enforce integrity...then it's the wrong place for the data. If you are getting problems on scaling, you are relying on data in jsonb columns for heavy queries which you should not. In that case it should've been moved out already.

As always it's about tradeoffs and being pragmatic. There's no 100% way of saying jsonb is always the wrong choise, or always the right choice. You still have to be smart about when to reach for it.

> I would rather see a wide table with a bunch of bools indicating various options, personally. When that gets annoying or unperformant, split it out into logical groups. Color schemes can be their own table, email marketing preferences can be their own table, etc.

The point is to exactly avoid this kind of overhead when you have zero paying customers, as that's premature optimization. Of course from a pure data model perspective it's nice, but from a business perspective you don't need that until it hurts and you have to split it out.