Bookmarks (1408)

pgAdmin 4 v6.0 Released — The popular Web-based interface for Postgres gets a big upgrade and, notably, switches to using React to power its front end.

Psycopg 3.0 Released: A Postgres Adapter for Python — Back in issue 390 we featured an interview with Psycopg’s creator on his aims for Psycopg 3, the next generation of Python-Postgres adapters, which is now here in its final form. The docs will probably give you the best impression as to why to consider using it.

pspg (Postgres Pager) 5.4.0 Released — pspg is a tool we’ve linked to numerous times over the years. It’s a ‘pager’ tool for working with table data from Postgres but continues to add new features, this time including customization of its color theme.

SHOW TABLES in Postgres: What's Wrong with It? — Like many, I came to Postgres after being proficient with MySQL and getting my head around not using SHOW TABLES took a while. Of course, Postgres has its own even more efficient approach.

Multirange Types in Postgres 14 — One of the new features in Postgres 14 is multirange types, essentially sets of non-overlapping ranges (e.g. 1-3 and 6-8, as a singular concept). This post demonstrates their (very) basic use.

How to Tune Sorting Operations with work_mem — work_mem is a Postgres setting specifying how much memory to use for internal sort operations and hash tables before falling back to using temporary files. Given how common sorting is in database queries and how onerous temporary files can be, getting this setting right can yield some serious improvements.

📣 Tune in to Timescale Community Day on October 28 — Join live-streamed talks (full of demos!) with topics ranging from time-series data and PostgreSQL tips to general interest database topics. Connect with fellow community members, get expert tips, advice, and more.

Google Cloud Spanner Adds Postgres-Compatible Interface — Cloud Spanner is a Google cloud based relational database service boasting unlimited scalability, and now you can use it from even more places with a new Postgres interface. Notably, Kelsey Hightower posited a year ago that if Cloud Spanner were to have a Postgres compatible interface, it stood a chance of becoming “the Gmail of ACID-compliant databases.”...

🎂  PostgreSQL 14 Released — Just over a year from the final release of Postgres 13, the next major release is here. Like with 13, 14 is what you could call an ‘evolutionary’ release with no huge new features (it's all about the little things), but a laundry list of enhancements and a sense of maturity. These things include:

Timescale Cloud: A New Postgres-Based Cloud Database Service for Time-Series Workloads — Postgres continues its path to dominating the cloud database scene with a new offering from Timescale and their open source, time-series database extended TimescaleDB Postgres variant. This release post goes into a lot of depth as to why they think it’s a compelling offer, and they encourage you to try it out...

Why Are Relational DBs The Standard Instead of Graph-Based DBs? — Not Postgres-specific, but this question led to some interesting comments on Hacker News this week with the argument spanning all the way back to the 70s.

Postgres is Still a 'Teenager'? — The author tried to find a focal point for what Postgres 14 is all about before realizing.. there isn’t one, and it’s more about a roster of smaller but solid enhancements.

How ÖBB – Austria's Railway Company – Moved to Postgres on Azure — A case study of how Austria’s national rail system migrated from an on-premises Oracle-based system to Azure Database for PostgreSQL.

Upgrading from Postgres 9.6 to Postgres 13 — Before you worry about upgrading to Postgres 14, maybe getting off of an older version like 9.6 is a bigger concern. Postgres 9.6, which came out five years ago now, is officially at its ‘end of life’ from November 2021.

PostgreSQL 14 on Kubernetes (With Examples) — Crunchy Data has rolled out their own flavored Postgres 14 experience to PGO, the open source Postgres Operator for Kubernetes.

Three Ways to Auto-Increment with Postgres — The author looks at sequences, the SERIAL data type, and the identity column, which overlap in various ways.

How Azure Shipped Postgres 14 Within One Day of its Release — If you think upgrading your Postgres install is hard, it might boggle the mind thinking about how cloud providers upgrade entire platforms. Microsoft are proud to have begun this process within 24 hours of release, and while this post doesn’t go into a lot of detail about it, it’s a good look...

Zero-Downtime Postgres Schema Migrations Need lock_timeout and Retries — Deploying schema changes in heavily loaded systems is challenging because of the locking involved which can rapidly become more complicated than you’d assume. Instead of waiting around, failing fast and retrying can avoid some of the problems.

Postgres 14 Release Candidate 1 Released — Progress towards the next major version of Postgres takes a big leap with the first release candidate, meaning the team are ready for you to try it out and report those last bugs. A final release is due in the next week if nothing big rears its head. Draft release notes.

JSON in Postgres: How to Use It Right — Postgres has had solid JSON support for several major versions now and I use it heavily myself without any negative incidents, but there are ‘bad uses’ for it, claims the author.

A Better Way to Index Your Postgres Database: Pganalyze Index Advisor — After many months of effort we are launching our index advisor. We looked into ML-based Postgres indexing but found that relying on the Postgres planner and using a deterministic model was better. See how we built the advisor and try it for free.

Using Recursive Queries to Get Distinct Elements from a Table — Working through a query problem and coming up with a creative solution that’s far quicker than the first attempt.

SQLite FDW 2.1.0 Released — The SQLite Foreign Data Wrapper for Postgres now supports Postgres 14 and makes it possible to use SQLite database files as if they were Postgres tables (with some limitations).

Building an Oracle to Postgres Migrator: Lessons Learned — “A lot has been written about Oracle to PostgreSQL migration; today, I want to share some of the lessons learned when it comes to actually building a migration tool”.

PGLoader: Data Loading and Continuous Migration from Other Databases to Postgres — Can load data in from files (e.g. CSV) or migrate an entire database into Postgres. Written in Lisp and open source.

How Percentile Approximation Works (And Why It's More Useful Than Averages) — Breaks down the basics of averages vs. percentiles, complete with a real scenario (API monitoring) to demonstrate the “dangers” of relying on averages for large time-series datasets. Wraps up with a look at how percentiles work in Postgres.

ErrorPush: Minimalist Error Collection Service Compatible with Rollbar Clients — An open source error collection service that stores logs in a single Postgres table. Written in Python.

pglog: A Flexible and Simple Log Analyzer — If you work with numerous databases and want to quickly get a grip on any problems by analyzing the logs (locally, not in production), pglog is the extension for you.

Migrating a JPA Application from Oracle to Postgres — The Java Persistence API (JPA) is essentially a set of standards for working with relational databases to store Java objects.

How to Upgrade Postgres with pglogical and Database Migration Service (DMS) — Even if you’re not using Google’s Cloud SQL service yet, you can still use their Database Migration Service to help with an upgrade to it.

Dealing with Broken Indexes After a Linux Upgrade — “upgrading the OS running a Postgres cluster and involving a glibc library version update, or migrating a database to another machine running another OS (and glibc) version poses a little known, but very significant risk…”

How to Automate Benchmark Tests for Amazon Aurora PostgreSQL — An explanation of how to set up an automated but realistic benchmarking test system for Postgres databases on AWS Aurora.

Amazon RDS Now Supports X2g Instances for Postgres Databases — X2g instances are memory heavy Gravton2-based instances starting at 32GB and working up to 1TB of RAM if you have the money.

What’s New in the Citus 10.2 Extension to Postgres — The latest news from the popular, and open source, horizontal scaling system for Postgres from Microsoft. It’s now ‘Postgres 14 ready’, has simplified partition maintenance, and improved support for indexes on columnar tables.

A Comparison Table of Postgres Schedulers — One of the developers of pg_timetable, a scheduling extension for Postgres, and a self proclaimed ‘scheduler addict’, gives a quick comparison of pg_timetable, pg_cron, pgAgent, jpgAgent, and pgbucket.

pganalyze Index Advisor: Ideal Postgres Index Recommendations — Our free index advisor gives index recommendations based on your database schema and statistics and your query workload and additional insights for each table scan. If you want to use the recommended ideal index, simply copy the CREATE INDEX command.

Postgres 14: It's The Little Things — Postgres 14 is on the way but while many releases have what Craig calls a ‘big pillar’ or major feature, version 14 is more of a collection of small, though not unimportant, improvements and Craig picks a few of his favorites here.

Creating Indexes Before or After Bulk Loading? — Does it matter whether you create an index before or after a large ingestion of data? Hans-Jürgen performs one of his characteristic benchmarks to find out. (In short: after is faster, but it may not suit your workflow.)

How Postgres Aggregation Inspired Our Hyperfunctions’ Design — Our engineers break down how PostgreSQL aggregation works, how it inspired the design of TimescaleDB’s hyperfunctions, and how it interacts with continuous aggregates & other advanced features – complete with examples and pictures. 🚀

Boundless TEXT and Back Again — With Postgres, there’s no performance difference between having a VARCHAR(n) column or a boundless TEXT one, but what about if you want to limit the length? Brandur shares a slightly messy story from Stripe of what can happen if there are no limits in place, along with his current rationale for using VARCHAR after all.

Release Dates of Different Postgres Versions of AWS RDS and Aurora — I’m not sure how useful this is but I like how it’s presented :-)

How We Went All In On sqlc/pgx for Postgres in Go — sqlc generates Go from files of SQL queries which provide many of the same benefits as an ORM without some of the downfalls. This is not a one size fits all scenario though and is hugely a matter of preference.

A Roundup of Postgres GUIs in 2021 — A post from 2020 that’s been updated a little for 2021 covering some common database clients like pgAdmin, Navicat, DBeaver, HeidiSQL, Datagrip, and several others.

Using jq to Get Information Out of 'pgbackrest' — pgBackRest is a popular backup and restore tool and it can return its output in JSON format, making it ideal to parse with the even more popular jq JSON command line processor.

Trademark Actions Against the PostgreSQL Community — If cross-border trademark disputes aren’t your bag, move on – long story short, the Postgres core team and the community association with manages Postgres’s trademark (and from who we got kind permission for this newsletter’s name) are in a conflict with Fundación PostgreSQL over the use of the name. If you want to see the other side...