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

PostgreSQL 14

postgresql.org

Post reply on HN
Print view View original

61–70 of 293 posts

Re: PostgreSQL 14

#61

Post by olavgg »

buro9 wrote: post #23

Suppose I had a "friend" with a PostgreSQL 9.6 instance (a large single node)... what's the best way to upgrade to PostgreSQL 14?

This is one of the major pain points with PostgreSQL. Unless you absolutely need any new features and uptime is important, you can just continue using PostgreSQL 9.6 even thought it is EOL. https://www.postgresql.org/support/versioning/ It will most likely work great for many more years.

I wish future versions of PostgreSQL will have some backwards compatibility for old system/data tables/datastructures and be able to do live migration when running a newer release.

pg_upgrade will not work if the internal data structure for your data changes. It only recreates the system tables. https://www.postgresql.org/docs/14/pgupgrade.html

Re: PostgreSQL 14

#62

Post by tfeldmann »

mypastself wrote: post #30

Somewhat related, but does anybody have suggestions for a quality PostgreSQL desktop GUI tool, akin to pgAdmin3? Not pgAdmin 4, whose usability is vastly inferior. DBeaver is adequate, but not really built with Postgres in mind.

TablePlus

Re: PostgreSQL 14

#63

Post by skrebbel »

These changes look fantastic.

If I may hijack the thread with some more general complaints though, I wish the Postgres team would someday prioritize migration. Like make it easier to make all kinds of DB changes on a live DB, make it easier to upgrade between postgres versions with zero (or low) downtime, etc etc.

Warnings when the migration you're about to do is likely to take ages because for some reason it's going to lock the entire table, instant column aliases to make renames easier, instant column aliases with runtime typecasts to make type migrations easier, etc etc etc. All this stuff is currently extremely painful for, afaict, no good reason (other than "nobody coded it", which is of course a great reason in OSS land).

I feel like there's a certain level of stockholm syndrome in the sense that to PG experts, these things aren't that painful anymore because they know all the pitfalls and gotchas and it's part of why they're such valued engineers.

Re: PostgreSQL 14

#64

Post by dgb23 »

hackandtrip wrote: post #20

Any suggestions to learn and go deep in PostgreSQL for someone who worked mostly on NoSQL (MongoDB)? From the few days I have explored it, it is absolutely incredible, so congratulations for the work done and good luck on keeping the quality so high!

If you are a novice or even if just a bit rusty with relational databases I recommend:

- Grab a good book or two. The Art of PostgreSQL is one. There are _many_ others. Take a bit of time to find out what suits you. I typically like reading a more theoretical one and a more practical one (with actionable, concrete advice, actual code in it and so on).

- Get a nice tool/IDE/editor plugin that can interactively work with databases, create them, query them etc. Almost all of the editor utilities that you expect from general purpose programming also applies to programming with SQL.

- PostgreSQL is a very powerful, primarily relational database, but there is very little it cannot do. For example it is perfectly feasible to implement a document (JSON) store with it, and convenient to boot. There are also great extensions such as for temporal tables.

- There is a ton of advice, discussion etc. to find, especially around data modelling. Word of caution: Some of it is religious, at least slightly. The book I mentioned above too to a certain degree, but it is still a very good book. Realize that a relational DB can give you a ton of stuff in a performant, declarative manner. But not everything should live in the DB, nor is everything relational in the strictest sense. Be pragmatic.

Source: I've been diving deeper into this topic since a few years and still am. The more I learn and are able to apply, the more I understand why the relational paradigm is so dominant and powerful.

Re: PostgreSQL 14

#66

Post by apocalyptic0n3 »

thornygreb wrote: post #35
mypastself wrote: post #30

Somewhat related, but does anybody have suggestions for a quality PostgreSQL desktop GUI tool, akin to pgAdmin3? Not pgAdmin 4, whose usability is vastly inferior. DBeaver is adequate, but not really built with Postgres in mind.

I pay for jetbrains datagrip, worth every penny.

Seconded. DataGrip is terrific and supports every database type I have ever come into contact with. And it's all JDBC-based so you can add new connectors pretty easily (from within the app, no less. No fiddling with files necessary). I had to do that to do help on a proposal a few years ago for a project that had a Firebird database and Datagrip didn't natively support it.

Re: PostgreSQL 14

#67

Post by aeyes »

candiddevmike wrote: post #8

How does everyone do postgresql upgrades with the least amount of downtime?

Fastest non-intrusive way I know in RDS or any other environment which allows you to spin up a new box:

* Set up a replica (physical replication)

* Open a logical replication slot for all tables on your old master (WAL will start accumulating)

* Make your replica a master

* Upgrade your new master using pg_upgrade, run analyze

* On your new master subscribe to the logical replication slot from your old master using the slot you created earlier, logical replication will now replicate all changes that occurred since you created the slot

* Take down your app, disable logical replication, switch to the new master

You can do the upgrade with zero downtime using Bucardo Multi-Master replication but the effort required is much much higher and I'm not sure if this is really feasible for a big instance.

Re: PostgreSQL 14

#68

Post by johnthuss »

This looks like an amazing release! Here are my favorite features in order:

• Up to 2x speed up when using many DB connections • ANALYZE runs significantly faster. This should make PG version upgrades much easier. • Reduced index bloat. This has been improving in each of the last few major releases. • JSON subscript syntax, like column['key'] • date_bin function to group timestamps to an interval, like every 15 minutes. • VACUUM "emergency mode" to better prevent transaction ID wraparound

Re: PostgreSQL 14

#69

Post by assface »

paulryanrogers wrote: post #9
maxpert wrote: post #4

I converted from MySQL (before whole MariaDB and fork), and I've been happier with every new version. My biggest moment of joy was JSONB and it keeps getting better. Can we please make the connections lighter so that I don't have to use stuff like pgbouncer in the middle? I would love to see that in future versions.

FWIW Mysql 8 has gotten a lot better in standards compliance and ironing out legacy quirks, with some config tweaks. While my heart still belongs to PostgreSQL things like no query hints, dead tuple bloat (maybe zheap will help?), less robust replication (though getting better!), and costly connections dampens my enthusiasm.

> maybe zheap will help?

According to Robert Haas, the zheap project is dead.

Re: PostgreSQL 14

#70

Post by illuminated »

mypastself wrote: post #30

Somewhat related, but does anybody have suggestions for a quality PostgreSQL desktop GUI tool, akin to pgAdmin3? Not pgAdmin 4, whose usability is vastly inferior. DBeaver is adequate, but not really built with Postgres in mind.

PgModeler [0], cannot recommend it enough. You have to compile yourself the free version (it's open source - you pay if you want to directly download the precompiled binaries) and it's a bit of work if you want to include the query optimizer plugin, but there's documentation and GitHub issues with solutions already in place. Once you compile it for the first time and start using it, you'll keep doing it again with each new version. I haven't found a better Postgres tool yet.

edit: it's a cross-platform tool, supporting Linux, Mac and Windows (32/64)

[0]: https://pgmodeler.io/

Post reply on HN