the sense of pride is palpable (and well deserved)
PostgreSQL 14
11–20 of 293 posts
Re: PostgreSQL 14
#12How does everyone do postgresql upgrades with the least amount of downtime?
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.
Re: PostgreSQL 14
#13How does everyone do postgresql upgrades with the least amount of downtime?
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.
Bucardo is great for replication.
Re: PostgreSQL 14
#14Love the JSONB subscripts! It will be so much easier to remember! I may not even have to reference the docs!
Now if I only could remember how to convert a JSONB string to a normal string (without quotes)...
Re: PostgreSQL 14
#15I 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.
Lighter connections would finally allow for using lambda functions that access a Postgres database without needing a dedicated pgbouncer server in the middle.
Re: PostgreSQL 14
#16If 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.
Re: PostgreSQL 14
#17And now the wait for RDS to support it. Thanks PG team!
Re: PostgreSQL 14
#18PostgreSQL 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-...
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/)
Re: PostgreSQL 14
#19PostgreSQL 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.
Re: PostgreSQL 14
#20Any 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!