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

131–140 of 293 posts

Re: PostgreSQL 14

#131

Post by tomc1985 »

tomhallett wrote: post #96
skrebbel wrote: post #63

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…

An orthogonal migration issue which I'm hitting right now: we need to migrate from heroku postgres to aws rds postgres, and I'm stressed about the risk and potential downtime in doing so. If there was a way to make a replica in rds based on heroku, promote the rds replica to be the primary, hard switch our apps over to rds, that'd be a lifesaver. I'm working through this blog post [1] now, but there is still a bit to…

When you subscribe to managed services instead of running the software yourself, these are the kinds of trade-offs that get made

Re: PostgreSQL 14

#132

Post by dragonwriter »

JohnBooty wrote: post #105
dragonwriter wrote: post #87

Earlier quoted context omitted.

> 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 I don't know if I’m a PG expert, but I just prefer “migration tool” to be a separate thing, and for the DB server engine to focus on being an excellent DB server engine, with the right hooks to support a robust tooling ecosystem, rather than trying to be the tooling ecosystem.

As a developer I fully support the notion of splitting the tools out from the server engine, like things are today. But, realistically, pg_upgrade's functionality would need to be integrated into the server itself if we're ever going to have zero-downtime upgrades, right? I don't know how other RDBMSs handle this, if at all

Yeah, the comment I was responding to addressed two different kinds of migration—schema migration and version upgrades—and my comment really applies more to schema migration than version upgrades; more support for smoothing the latter in the engine makes sense.

Re: PostgreSQL 14

#133

Post by turbocon »

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.

Going to second this, however I will warn, at least in my experience it is a little bit different from most DB IDEs. I didn't like it at all first time I used it, then a friend told me to give it another try. I've never looked back, fantastic tool.

Re: PostgreSQL 14

#134

Post by phamilton »

skrebbel wrote: post #63

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…

They are slowly getting there. For example, postgres 12 added REINDEX CONCURRENTLY. Under the hood it's just recreating the index and then name swapping and dropping the old one. Basically what pg_repack was doing.

There's a huge collection of tricks out there that just need to become formal features. The trick I'm working with today is adding a check constraint with NOT VALID and then immediately calling VALIDATE because otherwise it takes a very aggressive lock that blocks writes. That could easy become ALTER TABLE CONCURRENTLY or something.

Re: PostgreSQL 14

#135

Post by JohnBooty »

dragonwriter wrote: post #132
JohnBooty wrote: post #105

Earlier quoted context omitted.

As a developer I fully support the notion of splitting the tools out from the server engine, like things are today. But, realistically, pg_upgrade's functionality would need to be integrated into the server itself if we're ever going to have zero-downtime upgrades, right? I don't know how other RDBMSs handle this, if at all

Yeah, the comment I was responding to addressed two different kinds of migration—schema migration and version upgrades—and my comment really applies more to schema migration than version upgrades; more support for smoothing the latter in the engine makes sense.

Agreed: Postgres' transactional schema migration is freaking sublime.

I used and abused it pretty hard at my previous gig and now it's hard to imagine ever living without it.

At my gig before THAT, we had MySQL and schema migrations were so very very painful.

Re: PostgreSQL 14

#136

Post by gavinray »

TOMDM wrote: post #127
gavinray wrote: post #73

Earlier quoted context omitted.

ElasticSearch has "dense_vector" datatype and vector-specific functions. https://www.elastic.co/guide/en/elasticsearch/reference/current/dense-vector.html https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-script-score-query.html#vector-functions ZomboDB integrates ElasticSearch as a PG extension, written in Rust: https://github.com/zombodb/zombodb I dunno what exactly a "dense_vector" is, but…

ZomboDB has always seemed really interesting to me. Elastics search, with PostgreSQL's everything else sounds like a perfect match, but I've never seen it used anywhere which is kind of spooky to me. Do you know of any non-toy use cases I can look at?

You can ask Eric Ridge on the ZomboDB Discord, he's really nice:

https://discord.gg/hPb93Y9

Also probably one of the most knowledgeable people about both Postgres and Rust I've ever met.

He makes his living primarily from supporting enterprises using ZomboDB (I think), not sure how much he can say, but it's worth asking.

Re: PostgreSQL 14

#137

Post by mypastself »

ptrwis wrote: post #114
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.

When did you last use pgadmin4? It recently went through a big changes, in my opinion it's the best client for PostgreSQL.

A few months ago. I had semi-frequent troubles with starting up and freezing (on both Linux and Windows).

I would also easily make accidental GUI changes that I could only revert by reloading original settings.

A less galling example was the introduction of a highlight that marked the line in a query where an error occurred. It was a bright blue color, hard on the eyes, almost completely obscuring the text. It’s a comparatively minor issue, but illustrative of how the tool’s usability was steadily declining.

Re: PostgreSQL 14

#138

Post by mypastself »

JohnBooty wrote: post #107
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.

pgAdmin 4 has made rapid strides. If you haven't checked it out recently (~6 mos?) you may be pleasantly surprised. Many of the rough edges have been sanded off.

Interesting, it’s been roughly that amount of time since I’ve last used it. I might check it out again.

Re: PostgreSQL 14

#139

Post by gavinray »

anentropic wrote: post #109
gavinray wrote: post #73

Earlier quoted context omitted.

ElasticSearch has "dense_vector" datatype and vector-specific functions. https://www.elastic.co/guide/en/elasticsearch/reference/current/dense-vector.html https://www.elastic.co/guide/en/elasticsearch/reference/current/query-dsl-script-score-query.html#vector-functions ZomboDB integrates ElasticSearch as a PG extension, written in Rust: https://github.com/zombodb/zombodb I dunno what exactly a "dense_vector" is, but…

I think a dense vector is the opposite of a sparse vector i.e. in a dense vector every value in the vector is stored whereas sparse vectors exist to save space when you have large vectors where most of the values are usually zero - they reconstruct the full vector by storing only the non-zero values, plus their indices

  "a dense vector is the opposite of a sparse vector"
I think there's another thing besides vectors that are a bit dense in the room here, eh? Yeah that makes sense hahaha -- thank you.

Re: PostgreSQL 14

#140

Post by paulryanrogers »

JohnBooty wrote: post #120
paulryanrogers wrote: post #24

Earlier quoted context omitted.

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.

I stick to standard SQL syntax/features whenever possible as well, but... Honest question: how often do you switch databases? I've never really found myself wanting or needing to do this. Only time I could really see myself wanting to do this is if I was writing some kind of commercial software (eg, a database IDE like DataGrip) that needed to simultaneously support various multiple databases. > MySQL It feels partic…

I've done two moves, one from MySQL to Pg and the other from Pg to MySQL. I'm not opposed to leveraging their nonstandard parts where necessary.

Some companies end up with a mix of different DBs and it can help to consolidate to share expertise or resources.

Though at this point both have grown much closer together in capabilities and performance.

Post reply on HN