Hacker News Reader

Comment by TedDoesntTalk | Hacker News Reader

Doesn't Postgres have its own set of unique of problems?

I’ve encountered far fewer and nothing treacherous like the “you lost data but we didn’t tell you about it” stuff MySQL is famous for.

Indeed, the worst of which is you can't do in-place upgrades - upgrading cluster versions is an insanely complex ride.

I routinely upgrade versions - on AWS RDS this is just a click or two in my case.

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_...

When you say "complex ride" what does that mean?

I am not sure if that procedure covers major versions (13 -> 14 for example), but if it does, they are ahead of the curve. On GCP, you have to backup the database and restore the backup to a new database. On self-hosted installs, you have to pick one of these processes: https://www.postgresql.org/docs/14/upgrading.html

It does. It’s really easy to outsource.

To confirm, RDS does support in place major version upgrade for postgres.

They do an in_place upgrade using pg_upgrade I believe. They do have to regenerate system tables - they also automate I think some updates to system statistics. But for very large databases this really is the way to go I think.

They do suggest a backup before you run this in case there are problems, most folks have periodic backups anyways, but the backup is not used.

> When you say "complex ride" what does that mean?

On managed postgresql services like RDS it's easy, yes, because Amazon does all of the nasty work for you and has already seen all the kinks you may run into.

If you are on your own and using Docker, you essentially need a third-party image that has the old and new binaries (https://github.com/tianon/docker-postgres-upgrade). Good f...ing luck if you dare run PostgreSQL inside a Kubernetes environment, because adapting that guide to that is a can of worms large enough to make anyone squirm.

If you are on your own and are using distribution packages, it's more complicated (https://blog.samuel.domains/blog/tutorials/from-stretch-to-b...).

If you are on your own and use postgres from source, well... have fun, there's a reason why I prefer using distribution-maintained packages when possible.

Fun fact InnoDB in place updates have plenty of caveats too: must be last column, cannot have non-default on-update/delete clauses. And if you workaround with any table-rename solutions like pt-online-schema-change then you might crash your server. (Some MySQL 8 versions crash on table renames.)

EDIT: Oops, guess you meant version updates not DDL. Leaving comment for posterity.

Postgres doesn't have a way to change the order of columns. Of course you could make a view, but my code doesn't use a view and I don't really want to make one just for when I want to check the database.

Wouldn't a single application of a temp table be sufficient?[0]

0: https://brianchildress.co/reorder-columns-postgres/

If that’s the biggest problem you have with your database, be happy.

Out of curiosity, how does this cause a problem for you? I’ve been using SQL databases heavily since the mid-90s and I’m not sure I’ve ever been in a situation where I had to use SELECT * but couldn’t either process the results by column name or have the client read in whatever order needed. I strongly favor only selecting the fields I care about, however, so I’m assuming there’s a case you’ve encountered.

Reply on news.ycombinator.com