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

PostgreSQL 17

postgresql.org

Post reply on HN
Print view View original

141–150 of 153 posts

Re: PostgreSQL 17

#141

Post by frankramos »

kiwicopple wrote: post #99
ellisv wrote: post #36

Earlier quoted context omitted.

> I'm a huge fan of FDW's Do you have any recommendations on how to manage credentials for `CREATE USER MAPPING ` within the context of cloud hosted dbs?

in supabase we have a “vault” utility for this (for example: https://fdw.dev/catalog/clickhouse/#connecting-to-clickhouse ). Sorry I can’t make recommendations for other platforms because i don’t want to suggest anything that could be considered unsafe - hopefully others can chime in

Just upgraded Supabase to a Pro account to try FDW but there doesn’t seem to be solid wrappers for MySQL/Vitess from Planetscale. This would help a ton of people looking to migrate. Does anyone have suggestions?

Re: PostgreSQL 17

#142

Post by peiskos »

kiwicopple wrote: post #4

Another amazing release, congrats to all the contributors. There are simply too many things to call out - just a few highlights: Massive improvements to vacuum operations: "PostgreSQL 17 introduces a new internal memory structure for vacuum that consumes up to 20x less memory." Much needed features for backups: "pg_basebackup, the backup utility included in PostgreSQL, now supports incremental backups and adds the pg…

A bit off topic, can someone suggest how I can learn more about using databases(postgres specifically) in real world applications? I am familiar with SQL and common ORMs, but I feel the internet is full of beginner level tutorials which lack this depth.

Re: PostgreSQL 17

#143

Post by bigger_cheese »

systems wrote: post #121
bigger_cheese wrote: post #120

Earlier quoted context omitted.

I'm sure it can work but I've found it hard to slot into our use cases. Power BI's trending is one thing I have really struggled with the limitations it has. For one of my use cases (industrial plant data) we often want to trend timeseries data on a common xaxis with uniquely scaled yaxis. i.e Temperature, Pressure, Flow Rate, Fill height etc all have differently scaled yaxis but common xaxis. Something like this exa…

sorry weird question, did you keep changing this link? was one of the link on powerbi.tips can you put that back, for some reason my browser history lost it , or something weird happened, not sure what it is

I edited the link once to a graph that better illustrated what I meant both results came from a google image search.

Re: PostgreSQL 17

#144

Post by Superfud »

peiskos wrote: post #142
kiwicopple wrote: post #4

Another amazing release, congrats to all the contributors. There are simply too many things to call out - just a few highlights: Massive improvements to vacuum operations: "PostgreSQL 17 introduces a new internal memory structure for vacuum that consumes up to 20x less memory." Much needed features for backups: "pg_basebackup, the backup utility included in PostgreSQL, now supports incremental backups and adds the pg…

A bit off topic, can someone suggest how I can learn more about using databases(postgres specifically) in real world applications? I am familiar with SQL and common ORMs, but I feel the internet is full of beginner level tutorials which lack this depth.

For PostgreSQL, the manual is extremely well written, and is warmly recommended reading. That should give you a robust foundation.

Re: PostgreSQL 17

#145

Post by jeltz »

ktosobcy wrote: post #140
jeltz wrote: post #135

Earlier quoted context omitted.

It would be a major hassle to do so since it would mean keeping around major parts of the code for all old versions of PostgreSQL. Possible to do? Yes. A hassle for the dev team? Yes, a huge one. Worth it? Doubtful.

> It would be a major hassle to do so since it would mean keeping around major parts of the code for *all old versions of PostgreSQL.* WTF? It was only implied to be able to do migration between major versions, not all of them (which doesn't make any sense). I don't expect PostgreSQL changing that significantly between major version the upgrade would be such a huge hassle… TBH PostgreSQL seem to be the only tool/soft…

Patches welcome since you obviously know more than me about the code base. And I obviously meant only major versions when I said all versions. PostgreSQL releases one new major version per year so that adds up quickly.

> I don't expect PostgreSQL changing that significantly between major version the upgrade would be such a huge hassle…

PostgreSQL would need code which transforms between the old AST to the new AST for example to support views and check constraints so PostgreSQL would need to keep around a version of the AST for every major version of PostgreSQL plus the code to deparse it. It would also need to keep around the code to read every version of the catalog tables (it partially does this in pg_dump, but only partially since parts of pg_dump is implemented using server side functions).

There are also likely a bunch more complications caused by the pluggable nature of PostgreSQL: e.g. custom plan nodes which might make the whole project a non-starter.

So this would at least mean basically a total rewrite of pg_dump plus maintaining 5 to 10 versions of the AST. But likely much more work since I have likely forgot a bunch of stuff which needs to change. A huge project which increases the maintenance burden for a relatively small gain.

> TBH PostgreSQL seem to be the only tool/software that has such a bonkers upgrade path between major versions…

This is caused mostly by PostgreSQL being heavily extensible unlike its competitors.

Re: PostgreSQL 17

#146

Post by tianzhou »

systems wrote: post #14
gigatexal wrote: post #7

Earlier quoted context omitted.

So from one expensive vendor to another? Your boss seems smart. ;-) What’s the rationale? What do you gain?

Well, from one VERY expensive vendor, to another considerably less expensive vendor Also, MSSQL have few things going for it, and surprisingly no one seem to be even trying to catch up - Their BI Stacks (PowerBI, SSAS) - Their Database Development (SDK) ( https://learn.microsoft.com/en-us/sql/tools/sql-database-projects/sql-database-projects?view=sql-server-ver16 ) The MSSQL BI stack is unmatched , SSAS is the top st…

>> I really wish someone will implement DB Projects (dacpac) for Postgresql

Bytebase did https://www.bytebase.com/docs/change-database/synchronize-sc...

Re: PostgreSQL 17

#147

Post by tianzhou »

koolba wrote: post #65
pphysch wrote: post #48

Earlier quoted context omitted.

> And finally the GEM that is database projects, you can program your DB changes declaratively, there is nothing like this in the market and again, no one is even trying Automatic migration tools have essentially been doing this for a while (e.g. Django migrations). But I agree it would be nice if Postgres had better support built in.

Database migrations to arrive at a target state are an interesting beast. It’s not just the destination that matters, it’s how you get there. The naive approach of applying whatever DDL you need to turn database schema A into B is not necessarily the one anybody would actually want to run, and in larger deployments it’s most definitely not the one you’d want to run. Intermediate availability and data migration go wel…

(Bytebase co-founder) Bytebase implements this https://www.bytebase.com/docs/change-database/synchronize-sc...

Re: PostgreSQL 17

#148

Post by justinclift »

ktosobcy wrote: post #122
dewey wrote: post #40

Earlier quoted context omitted.

Maybe https://github.com/pgautoupgrade/docker-pgautoupgrade is interesting for you.

I heard about that project but it still somewhat convoluted. Imagine being able to simply use "postgres:latest" or better yet "postgres:15" and switch to "postgres:16" and it would just update (like any other minor version does, or any other db, like mysql, does)

Yeah, we've offered to make that happen but there's been zero response:

https://github.com/docker-library/postgres/issues/37#issueco...

Btw, what do you find convoluted about our project (pgautoupgrade)?

It started out as super simple like you mention above... but it also started out using Alpine images so didn't work so well for people coming from Debian based ones. We've added support for Debian based ones now too, but that's introduced more complexity to the README (as has other options). o_O

Re: PostgreSQL 17

#149

Post by justinclift »

olavgg wrote: post #46
levkk wrote: post #35

Earlier quoted context omitted.

pg_upgrade is a bit manual at the moment. If the database could just be pointed to a data directory and update it automatically on startup, that would be great.

I agree, why is this still needed? It can run pg_upgrade in the background.

When you say "in the background" what are you meaning?

Unless something has radically changed with this last release, then the PostgreSQL database needs to be offline while pg_upgrade is running.

Re: PostgreSQL 17

#150

Post by justinclift »

jeltz wrote: post #145
ktosobcy wrote: post #140

Earlier quoted context omitted.

> It would be a major hassle to do so since it would mean keeping around major parts of the code for *all old versions of PostgreSQL.* WTF? It was only implied to be able to do migration between major versions, not all of them (which doesn't make any sense). I don't expect PostgreSQL changing that significantly between major version the upgrade would be such a huge hassle… TBH PostgreSQL seem to be the only tool/soft…

Patches welcome since you obviously know more than me about the code base. And I obviously meant only major versions when I said all versions. PostgreSQL releases one new major version per year so that adds up quickly. > I don't expect PostgreSQL changing that significantly between major version the upgrade would be such a huge hassle… PostgreSQL would need code which transforms between the old AST to the new AST for…

> A huge project which increases the maintenance burden for a relatively small gain.

No, it would be for a huge gain for the people who run PostgreSQL. Every long running PostgreSQL installation use has to go through the process and/or potential downtime figuring out a (potentially forced) upgrade every few years.

Instead of that, PG being able to transparently (or at least automatically) upgrade major versions as needed would remove that absolutely huge pain point.

Other databases have recognised this as being a major problem, then put the time and effort into to solving it. The PostgreSQL project should too. :)

Post reply on HN