PostgreSQL 14 • Hacker News Board

Re: PostgreSQL 14

#21

Post by my123 » Thu, Sep 30, 2021, 1:38 PM UTC

mattashii wrote: post #6

83457 wrote: post #3

Can someone who uses Babelfish for PostgreSQL compatibility with SQL Server commands please describe their experience, success, hurdles, etc. We would move to PostgreSQL if made easier by such a tool. Thanks!

I haven't been able to play with it yet. This was partly due to my lack of SQL Server projects, but mostly due to the lack of availability of the supposedly Apache-licenced sources and/or binaries (and not wanting to configure AWS for 'just testing').

It’s currently in a limited preview, not accessible outright to any AWS customer even.

Re: PostgreSQL 14

#22

Post by kiwicopple » Thu, Sep 30, 2021, 1:40 PM UTC

jonplackett wrote: post #10

If you’d like to try out PostgreSQL in a nice friendly hosted fashion then I highly recommend supabase.io I came from MySQL and so I’m still just excited about the basic stuff like authentication and policies, but I really like how they’ve also integrated storage with the same permissions and auth too. It’s also open source so if you can to just host it yourself you stil can. And did I mention they’ll do your auth fo…

[Supabase cofounder] thanks for the kind words.

We're about to wrap up Row Level Security on our real-time APIs too[0] - soon you'll be able to use Policies universally on all APIs.

[0] https://github.com/supabase/walrus

Re: PostgreSQL 14

#23

Post by buro9 » Thu, Sep 30, 2021, 1:41 PM UTC

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?

Re: PostgreSQL 14

#24

Post by paulryanrogers » Thu, Sep 30, 2021, 1:41 PM UTC

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!

Pg docs are so good I reference them whenever I want to check the SQL standards, even if I'm working on another DB. (I prefer standard syntax to minimize effort moving DBs.)

Otherwise maybe try it with a toy project.

Re: PostgreSQL 14

#25

Post by jkatz05 » Thu, Sep 30, 2021, 1:44 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.

I'd be curious to see if the concurrency improvements in PostgreSQL 14 help with increasing the threshold for when you need to introduce a connection pooler.

Re: PostgreSQL 14

#26

Post by mattashii » Thu, Sep 30, 2021, 1:45 PM UTC

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…

> Each time a transaction modifies rows in a PG database, it increments the transaction ID counter.

It's a bit more subtle than that: each transaction that modifies, deletes or locks rows will update the txID counter. Row updates don't get their own txID assigned.

> It looks like the new vacuum "emergency mode" functionality starts vacuuming more aggressively when getting closer to the wraparound

When close to wraparound, the autovacuum daemon stops cleaning up the vacuumed tables' indexes, yes. That saves time and IO, at the cost of index and some table bloat, but both are generally preferred over a system-blocking wraparound vacuum.

Re: PostgreSQL 14

#27

Post by sudhirj » Thu, Sep 30, 2021, 1:46 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?

With downtime, I guess the pg_upgrade tool works fine.

Without downtime / near-zero downtime is more interesting though. Since this is an old version, something like Bucardo maybe? It can keep another pg14 instance in sync with your old instance by copying the data over and keeping it in sync. Then you switch your app over to the new DB and kill the old one.

Newer versions make this even easier with logical replication - just add the new DB as a logical replica of the old one, and kill the old one and switch in under 5 seconds when you're ready.

Re: PostgreSQL 14

#28

Post by kiwicopple » Thu, Sep 30, 2021, 1:46 PM UTC

alberth wrote: post #16

jonplackett wrote: post #10

If you’d like to try out PostgreSQL in a nice friendly hosted fashion then I highly recommend supabase.io I came from MySQL and so I’m still just excited about the basic stuff like authentication and policies, but I really like how they’ve also integrated storage with the same permissions and auth too. It’s also open source so if you can to just host it yourself you stil can. And did I mention they’ll do your auth fo…

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 you typically need when building a product - connection pooling (pgbouncer), object storage, authentication + user management, dashboards, reports, etc. You don't need to use all of these - you can just use us as a "DBaaS" too.

[0] https://postgrest.org/

Re: PostgreSQL 14

#29

Post by TOMDM » Thu, Sep 30, 2021, 1:46 PM UTC

The query parallelism for foreign data wrappers bring PostgreSQL one step closer to being the one system that can tie all your different data sources together into one source.

Really exciting stuff.

Re: PostgreSQL 14

#30

Post by mypastself » Thu, Sep 30, 2021, 1:46 PM UTC

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.