Log Buffer #539: A carnival of the vanities for DBAs

This Log Buffer covers Cloud, Oracle, and PostgreSQL.

Cloud:

Oracle:

This item is, by a roundabout route, a follow-up to yesterday’s note on a critical difference in cardinality estimates that appeared if you used the coalesce() function in its simplest form as a substitute for the nvl() function.

PostgreSQL:

Thanks to Jeremy Schneider.

Another busy two weeks in the database world. There’s certainly never a dull moment for me. Our work related to new versions & features and our work directly with specific customers could easily take all of my time just by themselves! And it’s also that time of year for us to have
conversations about team goals for the upcoming year, individual strengths and potential growth areas. Reminds me that I’m thankful to
work with such a great group of people!

So what has been noticeable recently in the world of blogging about PostgreSQL? We will start with the high level and work down. And don’t
miss my final and favorite article.

First, a few articles about PostgreSQL and databases in general. On Jan 19, Karl Brodowsky wrote an interesting perspective on the current
database landscape. Compared with NoSQL, Propriatary and other OSS technologies, Karl explains why he recommends PostgreSQL so often.

On Feb 13, René Dudfield wrote a fascinating perspective on many aspects of the current development landscape and shows how PostgreSQL can be good enough for all sorts of different use cases where developers typically reach into another toolbox.

Finally, one of my favorite articles I saw over the last two weeks – Craig Kerstiens’ short compilation of “Postgres Hidden Gems” that he
published on Jan 31!

And while this isn’t an article, I noticed a few weeks ago that Toad World now has a PostgreSQL Community. I can’t easily tell from their
website, but I wonder if their Development Software and Benchmark Factory support PostgreSQL as well yet?

Next up, I saw a nice collection of feature-specific articles over the past few weeks. First off is arguably one of the defining and most
distinctive features of relational databases: constraints. The ability to define invariant rules about your data. In our modern “big data” days
of Twitter sentiment analysis and customer recommendation engines, many of us work on systems where fuzzy answers and margins of error are normal. But let’s not forget that correctness still matters for many business problems, and it’s more than just the cliche bank account application. On Jan 8, David Signer wrote a great article about tree inspectors in Switzerland. But actually, the article was about
PostgreSQL and data quality/integrity. And our age-old constraints really shine!

A few people this week were retweeting CEO and Java Champion Vlad Mihalcea’s old post on PostgreSQL Advisory Locks. This is a useful
feature that allows applications to directly use PostgreSQL’s locking infrastructure even when there’s no data directly involved.

On Feb 12, Jonathan Katz (from Crunchy) published a nice piece on PostgreSQL Row-Level Security and large policies. Perhaps it’s not
widely known that this has been available in PostgreSQL since version 9.5 and you don’t need an extra license to use it!

Are you familiar with pg_stat_statements? If not, then you should be; it’s probably one of the most important performance tools available on
PostgreSQL. Hans-Jürgen Schönig from CyberTec wrote a nice introduction on Feb 12 which is worth glancing at.

Finally, on Jan 23 Class Method Co. in Japan published an article on the little-known SQL “filter” clause. The article is written in Japanese but can be read in English with Google Translate.

Am I including too many articles this week? Are you wondering if you’ll ever have time to read all of this? But I still have more! In fact, my
favorite part: three good internals articles!

First up is Nawaz Ahmed on Jan 30 with an articles about PostgreSQL memory components. I actually haven’t seen many articles about memory in PostgreSQL—it would be great to see more of these.

Franck Pachot took a quick look at PostgreSQL process names and compared then with Oracle process names on Feb 10.

It’s always worthwhile to include an article about vacuum—and this week we have a real gem. Lei Zeng from Amazon recently migrated a set of OLTP (online transaction processing) databases from Oracle to Amazon RDS for PostgreSQL version 9.6.3 and on Feb 12 he published a case study of tuning autovacuum. This is a quality article worth reading!

Before I wrap up, I want to mention two good follow-up articles to the Spectre and Meltdown sagas. I’ve gotten so many kernel update emailsvfrom my Linux distro mailing lists that I completely lost track. There’s a lot happening to patch Linux kernels. And lots of good discussion about performance.

If you’re tracking with this conversation, then you might be interested in Michael Larabel’s article on Feb 10 where he was able to compare Amazon EC2 compute performance between December and February.

Secondly, the inimitable Brendan Gregg came out with his own blog post on Feb 9 about the same subject.

Well I have one final article to highlight. PostgreSQL is an open source and community driven database. Its survival depends on volunteer effort and cooperation, as well as support from the many businesses who benefit from its continued development. Have you ever wondered what it’s like to be the person that keeps the community functioning? I really like Magnus Hagander and his article from Feb 7 is one of my favorite that he’s ever written: What does it mean to be on the board of PostgreSQL Europe? Now you can find out what all these people do, and maybe you can join them to help support the movement!

And on that note, have a great week and keep learning!

Fahd Mirza

Snowflake

SAP

Snowflake

SAP

Fixed Utility Menu