19 ms·

PostgreSQL 14

941 points by jkatz05 5y ago

mattashii 5y ago
Once again, thanks to all the contributors that provided these awesome new features, translations and documentation. It's amazing what improvements we can get through public collaboration.
johnthuss 5y ago
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
83457 5y ago
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!
- mattashii 5y ago
  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').
  - my123 5y ago
    It’s currently in a limited preview, not accessible outright to any AWS customer even.
    - mattashii 5y ago
      Yep. I'm quite interested when the 'somewhere in 2021' will be, as the last mention of babelfish from an Amazon-employee on the mailing lists was at the end of March and I haven't heard news about the project since.
maxpert 5y ago
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.
- paulryanrogers 5y ago
  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.
  - assface 5y ago
    > maybe zheap will help? According to Robert Haas, the zheap project is dead.
    - eurg 5y ago
      Where did Robert Haas say this? Quick search didn't surface much, except for a blog post from July by Hans-Jürgen Schönig: https://www.cybertec-postgresql.com/en/postgresql-zheap-current-status/ https://www.cybertec-postgresql.com/en/postgresql-zheap-curr... Doesn't sound like they discontinued the project.
    - Tostino 5y ago
      I'd love to see that info, been interested in why it was taken up by Cybertec and EDB seemingly dropped it.
- xwdv 5y ago
  Lighter connections would finally allow for using lambda functions that access a Postgres database without needing a dedicated pgbouncer server in the middle.
postgresapp 5y ago
If you want to test the new features on a Mac, we've just uploaded a new release of Postgres.app: https://postgresapp.com/downloads.html https://postgresapp.com/downloads.html
- abdusco 5y ago
  I love this app on Mac, but I wonder if there is a similar app for Windows (i.e. portable Postgres)?
- TedShiller 5y ago
  This app is amazing. Highly recommend it.
rolobio 5y ago
Love the JSONB subscripts! It will be so much easier to remember! I may not even have to reference the docs!
- yarcob 5y ago
  Now if I only could remember how to convert a JSONB string to a normal string (without quotes)...
- ape4 5y ago
  What is JSONB... https://stackoverflow.com/questions/22654170/explanation-of-jsonb-introduced-by-postgresql https://stackoverflow.com/questions/22654170/explanation-of-... (Don't upvote me, I just googled it)
candiddevmike 5y ago
How does everyone do postgresql upgrades with the least amount of downtime?
- aeyes 5y ago
  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.
- paulryanrogers 5y ago
  For Pg and MySQL I usually have to resort to replicating to a newer instance then cutting over. Tools like pg_upgrade offer promise but I rarely have the time or access to test with a full production dataset. Hosting provider constraints sometimes limit my options too, such as no SSH to underlying instances.
  - loopdoend 5y ago
    Bucardo is great for replication.
jonplackett 5y ago
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 for you?
- alberth 5y ago
  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.
  - kiwicopple 5y ago
    [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/ https://postgrest.org/
    - alberth 5y ago
      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?
      - kiwicopple 5y ago
streamofdigits 5y ago
the sense of pride is palpable (and well deserved)
- deleted 5y ago
  [deleted]
netcraft 5y ago
And now the wait for RDS to support it. Thanks PG team!
- rsanheim 5y ago
  Any ideas on the typical delay before its supported in RDS?
  - netcraft 5y ago
    dont quote me on this, but I think it used to be quite a while, but since 12 things have gotten a lot better. I think they generally wait for the .1 patch and then get it in pretty quick.
    - netcraft 5y ago
      Postgres 13 was released 2020-09-24 13.1 was released 2020-11-12 13.2 was released 2021-02-11 https://www.postgresql.org/docs/13/release-13-2.html https://www.postgresql.org/docs/13/release-13-2.html AWS supported 13 as of 2021-02-24 https://aws.amazon.com/about-aws/whats-new/2021/02/amazon-rds-now-supports-postgresql-13/ https://aws.amazon.com/about-aws/whats-new/2021/02/amazon-rd...
      - darksaints 5y ago
        Well that's a lot worse than I was expecting.
  - bmdavi3 5y ago
    https://www.brianlikespostgres.com/rds-aurora-release-dates.html https://www.brianlikespostgres.com/rds-aurora-release-dates.... I gathered release dates a few weeks ago because I was curious. There aren't that many data points, but 150 days might be a good guess
hyper_reality 5y ago
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 Dealing with transaction ID wraparounds in Postgres was one of the most daunting but fun experiences for me as a young SRE. Each time a transaction modifies rows in a PG database, it increments the transaction ID counter. This counter is stored as a 32-bit integer and it's critical to the MVCC transaction semantics - a transaction with a higher ID should not be visible to a transaction with a lower ID. If the value hits 2 billion and wraps around, disaster strikes as past transactions now appear to be in the future. If PG detects it is reaching that point, it complains loudly and eventually stops further writes to the database to prevent data loss. Postgres avoids getting anywhere close to this situation in almost all deployments by performing routine "auto-vacuums" which mark old row versions as "frozen" so they are no longer using up transaction ID slots. However, there are a couple situations where vacuum will not be able to clean up enough row versions. In our case, this was due to long-running transactions that consumed IDs but never finished. Also it is possible but highly inadvisable to disable auto-vacuums. Here is a postmortem from Sentry who had to deal with this leading to downtime: https://blog.sentry.io/2015/07/23/transaction-id-wraparound-in-postgres https://blog.sentry.io/2015/07/23/transaction-id-wraparound-... It looks like the new vacuum "emergency mode" functionality starts vacuuming more aggressively when getting closer to the wraparound event, and as with every PG feature highly granular settings are exposed to tweak this behaviour (https://www.postgresql.org/about/featurematrix/detail/360/ https://www.postgresql.org/about/featurematrix/detail/360/)
- mattashii 5y ago
  > 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.
TOMDM 5y ago
PostgreSQL is one of those tools I know I can always rely on for a new use-case. There are very few cases where it can't do exactly what I need (large scale vector search/retrieval). Congrats on the 14.0 release. The pace of open source has me wondering what we'll be seeing 50 years from now.
- gk1 5y ago
  I recall seeing some library that adds vector search to Postgres. Maybe https://github.com/ankane/pgvector https://github.com/ankane/pgvector? Also there's Pinecone (https://www.pinecone.io https://www.pinecone.io) which can sit alongside Postgres or any other data warehouse and ingest vector embeddings + metadata for vector search/retrieval.
  - gavinray 5y ago
    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 if you can't use the native "tsvector" maybe you could use this?
    - anentropic 5y ago
      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
      - gavinray 5y ago
        "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.
hackandtrip 5y ago
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!
- paulryanrogers 5y ago
  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.
  - JohnBooty 5y ago
    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 particularly limiting to stick to "standard" SQL for MySQL's sake, since they frequently lag behind on huge chunks of "standard" SQL functionality anyway. For example, window functions (SQL2003 standard) took them about a decade and a half to implement.
    - paulryanrogers 5y ago
      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.
    - magicalhippo 5y ago
      We got an application which has been developed for over 20 years with the mentality of "we're never switching db's". Yet now we are, because some core customers demand MSSQL support... Of course the db we're using supports all kind of non-standard goodness that has been exploited all over. Gonna be fun times ahead...
buro9 5y ago
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?
- sudhirj 5y ago
  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.
  - yarcob 5y ago
    I think I remember a talk where someone manually set up logical replication with triggers and postgres_fdw to upgrade from an old server with zero downtime.
  - Diggsey 5y ago
    pg_upgrade has a `link` option that when used reduces the time take to ~15 seconds. IMO, that counts as "near-zero downtime".
    - sudhirj 5y ago
      Sort of, yeah. I usually consider zero-downtime to be ‘imperceptible to users’. This option is interesting, though, first I’ve heard of it. Thanks!
- nicoburns 5y ago
  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. So maybe just use that if you can afford the downtime.
TOMDM 5y ago
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.
mypastself 5y ago
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.
- thornygreb 5y ago
  I pay for jetbrains datagrip, worth every penny.
  - apocalyptic0n3 5y ago
    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.
  - dpcx 5y ago
    One of my coworkers uses datagrip. Needing to install mysql specific tooling so that they can take a full database dump is kind of frustrating. Many other tools can do it out of the box, why not datagrip?
  - turbocon 5y ago
    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.
- Keyframe 5y ago
  DataGrip is cool.
- paozac 5y ago
  I like Postico, but it is mac-only and not free.
I_am_tiberius 5y ago
Anyone know when it will be available on Azure Flexible Server? Also, does anyone know when Flexible Server will leave Preview status?
PlugaruT 5y ago
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?
- zozbot234 5y ago
  Whoa, I thought the proper terminology was more like primary/replica. Are we talking Postgres databases or decades-old clunky IDE hard drives?
  - PlugaruT 5y ago
    Yeah, I should have used "primary"/"replica", I agree. Somehow this words did not came to my mind. Thanks
  - slumpr 5y ago
    Both are valid. Master/slave is an older way of describing how the two are related, but most DBMSes used this terminology up until recently.
- anarazel 5y ago
  Unfortunately that didn't make it into 14, there were too many rough edges to file off.
- gunnarmorling 5y ago
  I was just yesterday talking to someone about this; they mentioned that Patroni leverages some way for setting up replication slots on replicas [1]. Haven't tried my self yet, but seems worth exploring. Something I'd like to dive into within Debezium is usage of the pg_tm_aux extension, which supposedly allows to set up replication slots "in the past", so you could use this to have seamless failover to replicas without missing any events. In any case, this entire area is of high importance for us and we're keeping an eye on any improvements closely, so I hope it will be sorted out sooner or later. [1] https://twitter.com/cyberdemn/status/1443130986116624388 https://twitter.com/cyberdemn/status/1443130986116624388 [2] https://github.com/x4m/pg_tm_aux https://github.com/x4m/pg_tm_aux
skrebbel 5y ago
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.
- harikb 5y ago
  Doesn’t PG already support inplace version upgrade? Also PG is one of the few that support schema/DDL statements inside a transaction.
  - skrebbel 5y ago
    "one of the few" is a pretty low bar though, I don't know a DB that doesn't suck at this.
    - jeltz 5y ago
      Or maybe it is you who are underestimating the technical complexity of the task? A lot of effort has been spent on making PostgreSQL as good as it is on migrations. Yes, it is not as highly prioritized as things like performance or partitioning but it is not forgotten either.
- bmcahren 5y ago
  We currently use MongoDB and while Postgres is attractive for so many reasons, even with Amazon Aurora's Postgres we still need legacy "database maintenance windows" in order to achieve major version upgrades. With MongoDB, you're guaranteed single-prior-version replication compatibility within a cluster. This means you spin up an instance with the updated version of MongoDB, it catches up to the cluster. Zero downtime, seamless transition. There may be less than a handful of cancelled queries that are retryable but no loss of writes with their retryable writes and write concern preferences. e.g. MongoDB 3.6 can be upgraded to MongoDB 4.0 without downtime. Edit: Possibly misinformed but the last deep dive we did indicated there was not a way to use logical replication for seamless upgrades. Will have to research.
nicoburns 5y ago
Does anyone know the status of the zheap project? I always hope to say news in postgres release notes, but nothing so far.
- murkt 5y ago
  https://www.cybertec-postgresql.com/en/postgresql-zheap-current-status/ https://www.cybertec-postgresql.com/en/postgresql-zheap-curr... The last status update is from July, so seems like things are progressing.
garyclarke27 5y ago
Fantastic piece of software. The only major missing feature that I can think of is Automatic Incremental Materialized View Updates. I'm hoping that this good work in progress makes it to v15 - https://yugonagata-pgsql.blogspot.com/2021/06/implementing-incremental-view.html https://yugonagata-pgsql.blogspot.com/2021/06/implementing-i...
- Tostino 5y ago
  This has been a major one i've wanted for a long time too, but sadly it's initial implementation will be too simple to allow me to migrate any of my use cases to use it.
darksaints 5y ago
I know this isn't even a big enough deal to mention in the news release, but I am massively excited about the new multirange data types. I work with spectrum licensing and range data types are a godsend (for representing spectrum ranges that spectrum licenses grant). However, there are so many scenarios where you want to treat multiple ranges like a single entity (say, for example, an uplink channel and a downlink channel in an FDD band). And there are certain operations like range differences (e.g. '[10,100)' - '[50,60)'), that aren't possible without multirange support. For this, I am incredibly grateful. Also great is the parallel query support for materialized views, connection scalability, query pipelining, and jsonb accessor syntax.
- jkatz05 5y ago
  Multiranges are one of the lead items in the news release :) I do agree that they are incredibly helpful and will help to reduce the complexity of working with ranges.
- deleted 5y ago
  [deleted]
ggktk 5y ago
PostgreSQL is my favorite database, but I wish it was possible to use it as a library like sqlite. That would let me use it in a lot more places.
rubyist5eva 5y ago
Postgres is my bread and butter for pretty much every project. Congratulations to the team, you work on and continue to improve one of the most amazing pieces of software ever created.
stronglikedan 5y ago
Anyone know where to download it? Looks like they haven't updated the download pages, even though they link to them at the end of this post.
- veidelis 5y ago
  Here, for example, https://www.enterprisedb.com/downloads/postgresql https://www.enterprisedb.com/downloads/postgresql
unixhero 5y ago
Congrats to the team. I cannot wait to work on v14.
simonebrunozzi 5y ago
Here we are, at a fantastic version 14, and still no sign of an MySQL AB-like company able to provide support and extensions to a great piece of open source software. There's a few small ones, yes, but nothing at the billion dollar size. I am still unable to understand why.
- arwineap 5y ago
  Which extensions do you think would be enough of a value add to support a billion dollar company?
- mixmastamyk 5y ago
  Microsoft and Citus are "a little" over a billion?
  - threeseed 5y ago
    I hope you don’t think Microsoft/Citus is going to continue to support PostgreSQL installations on anything other than Azure. This is ask about the battle of the clouds for them.
    - mixmastamyk 5y ago
      Doesn't matter what I think, it's an option though.
- MrWiffles 5y ago
  I thought this is what Enterprise DB was? Or am I misinformed? I concede that is indeed a possibility.
throwawaybchr 5y ago
Does anyone know when this will be available on RDS?
ablekh 5y ago
Congratulations and thanks to all involved! Do I understand correctly that, at this time, while PG has data sharding and partitioning capabilities, it does not offer some related features found in Citus Open Source (shard rebalancer, distributed SQL engine and transactions) and in Citus on Azure aka Hyperscale (HA and streaming replication, tenant isolation - I'm especially interested in the latter one)? Are there any plans for PG to move toward this direction?
- zozbot234 5y ago
  Streaming replication is supported as per https://www.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLICATION https://www.postgresql.org/docs/current/warm-standby.html#ST... . You can likely build shard rebalancing and tenant isolation on top of the existing logical replication featureset. There are some groundwork features for distributed transactions (PREPARE TRANSACTION, COMMIT PREPARED, ROLLBACK PREPARED), but they're not supported as such.
  - ablekh 5y ago
    I see. Thank you for clarifications.
ewgwegg 5y ago
Disappointed by the release. No big changes. Still using processes instead of threads for connections. No build-in sharding/high availability (like Sql Server Always On Availability Group). No good way to pass session variables to triggers (like username). No scheduled tasks like in MySql. Temporal tables are still not supported 10 years after the spec. is ready.

941 points by jkatz05 5y ago

Once again, thanks to all the contributors that provided these awesome new features, translations and documentation. It's amazing what improvements we can get through public collaboration.

johnthuss 5y ago

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

83457 5y ago

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!

mattashii 5y ago
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').
- my123 5y ago
  It’s currently in a limited preview, not accessible outright to any AWS customer even.
  - mattashii 5y ago
    Yep. I'm quite interested when the 'somewhere in 2021' will be, as the last mention of babelfish from an Amazon-employee on the mailing lists was at the end of March and I haven't heard news about the project since.

maxpert 5y ago

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.

paulryanrogers 5y ago
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.
- assface 5y ago
  > maybe zheap will help? According to Robert Haas, the zheap project is dead.
  - eurg 5y ago
    Where did Robert Haas say this? Quick search didn't surface much, except for a blog post from July by Hans-Jürgen Schönig: https://www.cybertec-postgresql.com/en/postgresql-zheap-current-status/ https://www.cybertec-postgresql.com/en/postgresql-zheap-curr... Doesn't sound like they discontinued the project.
  - Tostino 5y ago
    I'd love to see that info, been interested in why it was taken up by Cybertec and EDB seemingly dropped it.
xwdv 5y ago
Lighter connections would finally allow for using lambda functions that access a Postgres database without needing a dedicated pgbouncer server in the middle.

postgresapp 5y ago

If you want to test the new features on a Mac, we've just uploaded a new release of Postgres.app: https://postgresapp.com/downloads.html https://postgresapp.com/downloads.html

abdusco 5y ago
I love this app on Mac, but I wonder if there is a similar app for Windows (i.e. portable Postgres)?
TedShiller 5y ago
This app is amazing. Highly recommend it.

rolobio 5y ago

Love the JSONB subscripts! It will be so much easier to remember! I may not even have to reference the docs!

yarcob 5y ago
Now if I only could remember how to convert a JSONB string to a normal string (without quotes)...
ape4 5y ago
What is JSONB... https://stackoverflow.com/questions/22654170/explanation-of-jsonb-introduced-by-postgresql https://stackoverflow.com/questions/22654170/explanation-of-... (Don't upvote me, I just googled it)

candiddevmike 5y ago

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

aeyes 5y ago
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.
paulryanrogers 5y ago
For Pg and MySQL I usually have to resort to replicating to a newer instance then cutting over. Tools like pg_upgrade offer promise but I rarely have the time or access to test with a full production dataset. Hosting provider constraints sometimes limit my options too, such as no SSH to underlying instances.
- loopdoend 5y ago
  Bucardo is great for replication.

jonplackett 5y ago

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 for you?

alberth 5y ago
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.
- kiwicopple 5y ago
  [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/ https://postgrest.org/
  - alberth 5y ago
    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?
    - kiwicopple 5y ago

streamofdigits 5y ago

the sense of pride is palpable (and well deserved)

deleted 5y ago
[deleted]

netcraft 5y ago

And now the wait for RDS to support it. Thanks PG team!

rsanheim 5y ago
Any ideas on the typical delay before its supported in RDS?
- netcraft 5y ago
  dont quote me on this, but I think it used to be quite a while, but since 12 things have gotten a lot better. I think they generally wait for the .1 patch and then get it in pretty quick.
  - netcraft 5y ago
    Postgres 13 was released 2020-09-24 13.1 was released 2020-11-12 13.2 was released 2021-02-11 https://www.postgresql.org/docs/13/release-13-2.html https://www.postgresql.org/docs/13/release-13-2.html AWS supported 13 as of 2021-02-24 https://aws.amazon.com/about-aws/whats-new/2021/02/amazon-rds-now-supports-postgresql-13/ https://aws.amazon.com/about-aws/whats-new/2021/02/amazon-rd...
    - darksaints 5y ago
      Well that's a lot worse than I was expecting.
- bmdavi3 5y ago
  https://www.brianlikespostgres.com/rds-aurora-release-dates.html https://www.brianlikespostgres.com/rds-aurora-release-dates.... I gathered release dates a few weeks ago because I was curious. There aren't that many data points, but 150 days might be a good guess

hyper_reality 5y ago

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 Dealing with transaction ID wraparounds in Postgres was one of the most daunting but fun experiences for me as a young SRE. Each time a transaction modifies rows in a PG database, it increments the transaction ID counter. This counter is stored as a 32-bit integer and it's critical to the MVCC transaction semantics - a transaction with a higher ID should not be visible to a transaction with a lower ID. If the value hits 2 billion and wraps around, disaster strikes as past transactions now appear to be in the future. If PG detects it is reaching that point, it complains loudly and eventually stops further writes to the database to prevent data loss. Postgres avoids getting anywhere close to this situation in almost all deployments by performing routine "auto-vacuums" which mark old row versions as "frozen" so they are no longer using up transaction ID slots. However, there are a couple situations where vacuum will not be able to clean up enough row versions. In our case, this was due to long-running transactions that consumed IDs but never finished. Also it is possible but highly inadvisable to disable auto-vacuums. Here is a postmortem from Sentry who had to deal with this leading to downtime: https://blog.sentry.io/2015/07/23/transaction-id-wraparound-in-postgres https://blog.sentry.io/2015/07/23/transaction-id-wraparound-... It looks like the new vacuum "emergency mode" functionality starts vacuuming more aggressively when getting closer to the wraparound event, and as with every PG feature highly granular settings are exposed to tweak this behaviour (https://www.postgresql.org/about/featurematrix/detail/360/ https://www.postgresql.org/about/featurematrix/detail/360/)

mattashii 5y ago
> 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.

TOMDM 5y ago

PostgreSQL is one of those tools I know I can always rely on for a new use-case. There are very few cases where it can't do exactly what I need (large scale vector search/retrieval). Congrats on the 14.0 release. The pace of open source has me wondering what we'll be seeing 50 years from now.

gk1 5y ago
I recall seeing some library that adds vector search to Postgres. Maybe https://github.com/ankane/pgvector https://github.com/ankane/pgvector? Also there's Pinecone (https://www.pinecone.io https://www.pinecone.io) which can sit alongside Postgres or any other data warehouse and ingest vector embeddings + metadata for vector search/retrieval.
- gavinray 5y ago
  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 if you can't use the native "tsvector" maybe you could use this?
  - anentropic 5y ago
    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
    - gavinray 5y ago
      "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.

hackandtrip 5y ago

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!

paulryanrogers 5y ago
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.
- JohnBooty 5y ago
  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 particularly limiting to stick to "standard" SQL for MySQL's sake, since they frequently lag behind on huge chunks of "standard" SQL functionality anyway. For example, window functions (SQL2003 standard) took them about a decade and a half to implement.
  - paulryanrogers 5y ago
    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.
  - magicalhippo 5y ago
    We got an application which has been developed for over 20 years with the mentality of "we're never switching db's". Yet now we are, because some core customers demand MSSQL support... Of course the db we're using supports all kind of non-standard goodness that has been exploited all over. Gonna be fun times ahead...

buro9 5y ago

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?

sudhirj 5y ago
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.
- yarcob 5y ago
  I think I remember a talk where someone manually set up logical replication with triggers and postgres_fdw to upgrade from an old server with zero downtime.
- Diggsey 5y ago
  pg_upgrade has a `link` option that when used reduces the time take to ~15 seconds. IMO, that counts as "near-zero downtime".
  - sudhirj 5y ago
    Sort of, yeah. I usually consider zero-downtime to be ‘imperceptible to users’. This option is interesting, though, first I’ve heard of it. Thanks!
nicoburns 5y ago
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. So maybe just use that if you can afford the downtime.

TOMDM 5y ago

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.

mypastself 5y ago

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.

thornygreb 5y ago
I pay for jetbrains datagrip, worth every penny.
- apocalyptic0n3 5y ago
  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.
- dpcx 5y ago
  One of my coworkers uses datagrip. Needing to install mysql specific tooling so that they can take a full database dump is kind of frustrating. Many other tools can do it out of the box, why not datagrip?
- turbocon 5y ago
  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.
Keyframe 5y ago
DataGrip is cool.
paozac 5y ago
I like Postico, but it is mac-only and not free.

I_am_tiberius 5y ago

Anyone know when it will be available on Azure Flexible Server? Also, does anyone know when Flexible Server will leave Preview status?

PlugaruT 5y ago

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?

zozbot234 5y ago
Whoa, I thought the proper terminology was more like primary/replica. Are we talking Postgres databases or decades-old clunky IDE hard drives?
- PlugaruT 5y ago
  Yeah, I should have used "primary"/"replica", I agree. Somehow this words did not came to my mind. Thanks
- slumpr 5y ago
  Both are valid. Master/slave is an older way of describing how the two are related, but most DBMSes used this terminology up until recently.
anarazel 5y ago
Unfortunately that didn't make it into 14, there were too many rough edges to file off.
gunnarmorling 5y ago
I was just yesterday talking to someone about this; they mentioned that Patroni leverages some way for setting up replication slots on replicas [1]. Haven't tried my self yet, but seems worth exploring. Something I'd like to dive into within Debezium is usage of the pg_tm_aux extension, which supposedly allows to set up replication slots "in the past", so you could use this to have seamless failover to replicas without missing any events. In any case, this entire area is of high importance for us and we're keeping an eye on any improvements closely, so I hope it will be sorted out sooner or later. [1] https://twitter.com/cyberdemn/status/1443130986116624388 https://twitter.com/cyberdemn/status/1443130986116624388 [2] https://github.com/x4m/pg_tm_aux https://github.com/x4m/pg_tm_aux

skrebbel 5y ago

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.

harikb 5y ago
Doesn’t PG already support inplace version upgrade? Also PG is one of the few that support schema/DDL statements inside a transaction.
- skrebbel 5y ago
  "one of the few" is a pretty low bar though, I don't know a DB that doesn't suck at this.
  - jeltz 5y ago
    Or maybe it is you who are underestimating the technical complexity of the task? A lot of effort has been spent on making PostgreSQL as good as it is on migrations. Yes, it is not as highly prioritized as things like performance or partitioning but it is not forgotten either.
bmcahren 5y ago
We currently use MongoDB and while Postgres is attractive for so many reasons, even with Amazon Aurora's Postgres we still need legacy "database maintenance windows" in order to achieve major version upgrades. With MongoDB, you're guaranteed single-prior-version replication compatibility within a cluster. This means you spin up an instance with the updated version of MongoDB, it catches up to the cluster. Zero downtime, seamless transition. There may be less than a handful of cancelled queries that are retryable but no loss of writes with their retryable writes and write concern preferences. e.g. MongoDB 3.6 can be upgraded to MongoDB 4.0 without downtime. Edit: Possibly misinformed but the last deep dive we did indicated there was not a way to use logical replication for seamless upgrades. Will have to research.

nicoburns 5y ago

Does anyone know the status of the zheap project? I always hope to say news in postgres release notes, but nothing so far.

murkt 5y ago
https://www.cybertec-postgresql.com/en/postgresql-zheap-current-status/ https://www.cybertec-postgresql.com/en/postgresql-zheap-curr... The last status update is from July, so seems like things are progressing.

garyclarke27 5y ago

Fantastic piece of software. The only major missing feature that I can think of is Automatic Incremental Materialized View Updates. I'm hoping that this good work in progress makes it to v15 - https://yugonagata-pgsql.blogspot.com/2021/06/implementing-incremental-view.html https://yugonagata-pgsql.blogspot.com/2021/06/implementing-i...

Tostino 5y ago
This has been a major one i've wanted for a long time too, but sadly it's initial implementation will be too simple to allow me to migrate any of my use cases to use it.

darksaints 5y ago

I know this isn't even a big enough deal to mention in the news release, but I am massively excited about the new multirange data types. I work with spectrum licensing and range data types are a godsend (for representing spectrum ranges that spectrum licenses grant). However, there are so many scenarios where you want to treat multiple ranges like a single entity (say, for example, an uplink channel and a downlink channel in an FDD band). And there are certain operations like range differences (e.g. '[10,100)' - '[50,60)'), that aren't possible without multirange support. For this, I am incredibly grateful. Also great is the parallel query support for materialized views, connection scalability, query pipelining, and jsonb accessor syntax.

jkatz05 5y ago
Multiranges are one of the lead items in the news release :) I do agree that they are incredibly helpful and will help to reduce the complexity of working with ranges.
deleted 5y ago
[deleted]

ggktk 5y ago

PostgreSQL is my favorite database, but I wish it was possible to use it as a library like sqlite. That would let me use it in a lot more places.

rubyist5eva 5y ago

Postgres is my bread and butter for pretty much every project. Congratulations to the team, you work on and continue to improve one of the most amazing pieces of software ever created.

stronglikedan 5y ago

Anyone know where to download it? Looks like they haven't updated the download pages, even though they link to them at the end of this post.

veidelis 5y ago
Here, for example, https://www.enterprisedb.com/downloads/postgresql https://www.enterprisedb.com/downloads/postgresql

unixhero 5y ago

Congrats to the team. I cannot wait to work on v14.

simonebrunozzi 5y ago

Here we are, at a fantastic version 14, and still no sign of an MySQL AB-like company able to provide support and extensions to a great piece of open source software. There's a few small ones, yes, but nothing at the billion dollar size. I am still unable to understand why.

arwineap 5y ago
Which extensions do you think would be enough of a value add to support a billion dollar company?
mixmastamyk 5y ago
Microsoft and Citus are "a little" over a billion?
- threeseed 5y ago
  I hope you don’t think Microsoft/Citus is going to continue to support PostgreSQL installations on anything other than Azure. This is ask about the battle of the clouds for them.
  - mixmastamyk 5y ago
    Doesn't matter what I think, it's an option though.
MrWiffles 5y ago
I thought this is what Enterprise DB was? Or am I misinformed? I concede that is indeed a possibility.

throwawaybchr 5y ago

Does anyone know when this will be available on RDS?

ablekh 5y ago

Congratulations and thanks to all involved! Do I understand correctly that, at this time, while PG has data sharding and partitioning capabilities, it does not offer some related features found in Citus Open Source (shard rebalancer, distributed SQL engine and transactions) and in Citus on Azure aka Hyperscale (HA and streaming replication, tenant isolation - I'm especially interested in the latter one)? Are there any plans for PG to move toward this direction?

zozbot234 5y ago
Streaming replication is supported as per https://www.postgresql.org/docs/current/warm-standby.html#STREAMING-REPLICATION https://www.postgresql.org/docs/current/warm-standby.html#ST... . You can likely build shard rebalancing and tenant isolation on top of the existing logical replication featureset. There are some groundwork features for distributed transactions (PREPARE TRANSACTION, COMMIT PREPARED, ROLLBACK PREPARED), but they're not supported as such.
- ablekh 5y ago
  I see. Thank you for clarifications.

ewgwegg 5y ago

Disappointed by the release. No big changes. Still using processes instead of threads for connections. No build-in sharding/high availability (like Sql Server Always On Availability Group). No good way to pass session variables to triggers (like username). No scheduled tasks like in MySql. Temporal tables are still not supported 10 years after the spec. is ready.