PostgreSQL 14

Post by gunnarmorling » Thu, Sep 30, 2021, 4:13 PM UTC

tomhallett wrote: post #110

sidmitra wrote: post #104

Earlier quoted context omitted.

One possible solution for the ETL stuff might be to use Heroku Kafka for the Change Data Capture and then from that Kafka you can move it someplace else. See https://blog.heroku.com/streaming-data-connectors-beta Heroku's own Kafka seems to have slightly more native support than if you use a 3rd party like Confluence. We've not yet tried any of this, but it's been bookmarked as a possible solution to explore.

Interesting that you bring this up. I looked into heroku's streaming connectors to facilitate an integration with materialize.com, but Heroku's support team wasn't confident we could sync all 187 postgres tables under 1 connection. I thought about using Debezium and Kafka to roll my own micro-batch ETL solution, but listening to this podcast made me walk away slowly: https://www.dataengineeringpodcast.com/datacoral-c…

Post by sharadov » Thu, Sep 30, 2021, 4:14 PM UTC

dylanz wrote: post #101

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?

If you're on a single node, probably something like this. This is what my "friend" is doing later this week. He's migrating from 9.5 to 13: sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - sudo apt-get update sudo apt install postgresql-13 sudo servi…

Post by ralfhn » Thu, Sep 30, 2021, 4:15 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.

Post by TOMDM » Thu, Sep 30, 2021, 4:19 PM UTC

gavinray wrote: post #73

gk1 wrote: post #52

Earlier quoted context omitted.

I recall seeing some library that adds vector search to Postgres. Maybe https://github.com/ankane/pgvector ? Also there's Pinecone ( https://www.pinecone.io ) which can sit alongside Postgres or any other data warehouse and ingest vector embeddings + metadata for vector search/retrieval.

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…

Post by oauea » Thu, Sep 30, 2021, 4:20 PM UTC

jeltz wrote: post #98

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…

Migrations are quite highly prioritized in PostgreSQL. PostgreSQL has the by far best migration support of any database I have worked with. There is of course a lot of work left to do but that is true for many other areas in PostgreSQL. Also I can't agree at all with "nobody coded it", patches in this area generally welcome. My first real patch for PostgreSQL was about improving migrations. And there has been talk ab…