PostgreSQL 14 • Hacker News Board

Re: PostgreSQL 14

#41

Post by alberth » Thu, Sep 30, 2021, 1:56 PM UTC

kiwicopple wrote: post #28

alberth wrote: post #16

Earlier quoted context omitted.

Would you mind expanding on what's so appealing about Supabase (i.e. Firebase). I feel like I live in a cave because I haven't quite understood what problem Supabase/Firebase is solving for.

[supabase cofounder] While we position ourselves as a Firebase alternative, it might be simpler for experienced techies to think of us as an easy way to use Postgres. We give you a full PG database for every project, and auto-generated APIs using PostgREST [0]. We configure everything in your project so that it's easy to use Postgres Row Level Security. As OP mentions, we also provide a few additional services that y…

Thanks so much and really appreciate you taking the time to respond here.

I think it's fantastic to make deploying existing software/tools easier, and people are definitely willing to pay for the ease, curious though - what prevents the Postgres team from taking supabase contributions (since it's Apache 2.0) and including it in core Postgres?

Re: PostgreSQL 14

#42

Post by nicoburns » Thu, Sep 30, 2021, 1:56 PM UTC

yarcob wrote: post #38

nicoburns wrote: post #33

Earlier quoted context omitted.

Looks like the best approach might be to use in-place upgrade tool that ships with Postgres to upgrade to v10 (use Postgres v10 to perform this upgrade). From there you'd be able to create a fresh v14 instance and use logical replication to sync over the v10 database. Before briefly stopping writes while you swap over to the v14 database. EDIT: Looks like pg_upgrade supports directly upgrading multiple major versions…

Why would you first upgrade to PG 10?

Because version below 10 don't support logical replication. The alternative would be to use a 3rd party extension for replication, which may well be a good option but I don't have any experience with that so I can't really comment.

Re: PostgreSQL 14

#43

Post by paozac » Thu, Sep 30, 2021, 1:57 PM UTC

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 like Postico, but it is mac-only and not free.

Re: PostgreSQL 14

#44

Post by darksaints » Thu, Sep 30, 2021, 1:57 PM UTC

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.

There has been a lot of improvement in this release.

https://www.depesz.com/2020/08/25/waiting-for-postgresql-14-...

Re: PostgreSQL 14

#45

Post by briffle » Thu, Sep 30, 2021, 1:57 PM UTC

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?

We are currently using pg_upgrade to go from 9.6 to 13 in our systems. its supported and works well. We are using hte 'hardlinks' feature that makes it extremely fast as well.

Re: PostgreSQL 14

#46

Post by hyper_reality » Thu, Sep 30, 2021, 1:58 PM UTC

darksaints wrote: post #37

hyper_reality wrote: post #18

PostgreSQL is one of the most powerful and reliable pieces of software I've seen run at large scale, major kudos to all the maintainers for the improvements that keep being added. > PostgreSQL 14 extends its performance gains to the vacuuming system, including optimizations for reducing overhead from B-Trees. This release also adds a vacuum "emergency mode" that is designed to prevent transaction ID wraparound Dealin…

What is wrong with using a 64 bit, or even 128 bit transaction id?

It would increase disk usage by a significant amount, since transaction IDs appear twice in tuple headers (xmin/xmax). Essentially they are overhead on every database row. This submission has a discussion on it: https://news.ycombinator.com/item?id=19082944

Re: PostgreSQL 14

#47

Post by zozbot234 » Thu, Sep 30, 2021, 1:58 PM UTC

yarcob wrote: post #38

nicoburns wrote: post #33

Earlier quoted context omitted.

Looks like the best approach might be to use in-place upgrade tool that ships with Postgres to upgrade to v10 (use Postgres v10 to perform this upgrade). From there you'd be able to create a fresh v14 instance and use logical replication to sync over the v10 database. Before briefly stopping writes while you swap over to the v14 database. EDIT: Looks like pg_upgrade supports directly upgrading multiple major versions…

Why would you first upgrade to PG 10?

Native logical replication (making it possible to upgrade without downtime) was introduced in pgSQL 10. But if you're going to have downtime anyway, there's no reason not to do the upgrade in a single step. pg_upgrade should support that quite easily.

Re: PostgreSQL 14

#48

Post by nicoburns » Thu, Sep 30, 2021, 2:00 PM UTC

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 second Postico on Mac. I usually recommend TablePlus for other platforms.

Re: PostgreSQL 14

#49

Post by kiwicopple » Thu, Sep 30, 2021, 2:00 PM UTC

alberth wrote: post #41

kiwicopple wrote: post #28

Earlier quoted context omitted.

[supabase cofounder] While we position ourselves as a Firebase alternative, it might be simpler for experienced techies to think of us as an easy way to use Postgres. We give you a full PG database for every project, and auto-generated APIs using PostgREST [0]. We configure everything in your project so that it's easy to use Postgres Row Level Security. As OP mentions, we also provide a few additional services that y…

Thanks so much and really appreciate you taking the time to respond here. I think it's fantastic to make deploying existing software/tools easier, and people are definitely willing to pay for the ease, curious though - what prevents the Postgres team from taking supabase contributions (since it's Apache 2.0) and including it in core Postgres?

> what prevents the Postgres team from taking supabase contributions

Absolutely nothing - we are open source and we would encourage it. However we're not modifying Postgres itself at this stage, we're just providing tooling around it. We have hired developers to work specifically on Postgres but we will always attempt to upstream (if the PG community wants it) rather than maintain a fork

Re: PostgreSQL 14

#50

Post by PlugaruT » Thu, Sep 30, 2021, 2:01 PM UTC

I'm trying to understand if with v14 I will be able to connect Debezium to a "slave" node and not to the "master" in order to read the WAL but can't figure it out. Can someone help me with this?