Technical Track

Log buffer #545: a carnival of the vanities for DBAs

5 min read

May 3, 2018 12:00:00 AM

From Oracle to SQL Server, and from Cloud to PostgreSQL; things are changing lightning fast. Innovation in these areas is being captured promptly by the bloggers and this log buffer edition covers a few of the most pertinent ones. Cloud: Every application has to store and access operational data, usually in a database. Managed database services can help you ship apps faster and reduce operational toil so you can focus on what makes your business successful and unique. Introducing Kubernetes Service Catalog and Google Cloud Platform Service Broker: find and connect services to your cloud-native apps Exploring container security: Running a tight ship with Kubernetes Engine 1.10 Introducing Partner Interconnect, a fast, economical onramp to GCP Kubernetes best practices: How and why to build small container images Good news for cloud security experts: the AWS Certified Security — Specialty exam is here. This new exam allows experienced cloud security professionals to demonstrate and validate their knowledge of how to secure the AWS platform. Oracle: There was an incident where statistics were being gathered during prime operating hours causing performance issues. One DBA already verified GATHER_STATS_JOB has already been configured to not run during critical hours. Speculation is stats are being gathered manually and how to prove this? An Interesting Problem with ODI: Unable to retrieve user GUID Modifications to hidden parameters between 12.1.0.2 and 12.2.0.1 logic apps inserting to an ‘ on premises’ database Maven Duplicated Versions consolidated with flatten SQL Server: Detecting Problems on Databases that use Snapshot-based Transaction Isolation Packaged-Application Database Nightmares - A Horror Story Concurrency Week: An Odd Case of Blocking Introducing SQL Information Protection for Azure SQL Database and on-premises SQL Server! Accelerate real-time big data analytics with Spark connector for Microsoft SQL Databases PostgreSQL: Thanks to Jeremy Schneider for PostgreSQL section. The Seattle Times is forecasting a balmy high of almost 80 degrees Fahrenheit today. That's unseasonably warm for this part of the year but nobody is complaining! And the PostgreSQL world continues to heat up too. As usual, lots of exciting new content online to keep up with. :) Starting off with headlines - I think the top prize goes to the new PostgreSQL website! At PostgresConf last week, Scott Yara from Pivotal had a keynote where he talked about how the PostgreSQL website hadn't changed in about 15 years. Within a matter of hours, the new website rolled out. No kidding! In true PostgreSQL style, I couldn't find any official announcement from the web team beyond this one little tweet. (Though there's plenty of commentary online!) Three other headlines deserve mention. First, Pivotal Software is now officially a public company. They forked PostgreSQL years ago to create their product "greenplum" and now they have open sourced the entire code base, hired key PostgreSQL developers and are aggressively merging the community code to catch up to the latest PostgreSQL release. Their rapid pivot is a strong endorsement of the broader PostgreSQL community and codebase, and the IPO is a great indicator of it's growing commercial importance. Second - on the tails of Microsoft's announcement last month - Google Cloud SQL for PostgreSQL is now officially GA. Third, Percona - perhaps one of the best-known open source database companies - announced this week that they are officially adding PostgreSQL support to their services offerings. I also noticed a number of PostgreSQL sessions at Percona Live this week! Moving beyond headlines, what articles were people talking about the most this week? A few stand out to me. First, on March 28 Craig Ringer sent an email to the postgresql hackers list reporting on a user who's PostgreSQL database became corrupted after a storage error. After a lot of digging, Craig realized that the root problem was incorrect assumptions about how the linux kernel implements fsync(). In fact it seems that every OS platform behaves a little differently in how it handles errors when flushing filesystem cache to disk - which means that it's even harder than we thought to write cross-platform code that reliably gets data to persistent storage in a default OS config with filesystem caching. I have to wonder how many other databases are also affected by this; it can't just be PostgreSQL. The best write-up I've seen so far is on LWN and it just went public today! Outside of the fsync tale, I'll mention two other articles that I thought had a fair bit of attention. On April 16, Adrien Nayrat wrote a really nice high-level overview listing out many specific technical enhancements that PostgreSQL has shipped in the past few years. It's a testament to the surprising velocity of development happening in this already-quite-advanced database. And after spending a day at PostgresConf last week, Tony Baer published a nice article in ZDNet along the familiar theme that something is happening with PostgreSQL these days. Is something happening with PostgreSQL? Of course, companies using the PostgreSQL code to build products is nothing new. IBM's Netezza? Pivotal's Greenplum? Amazon's Redshift? Check out the related documents on the PostgreSQL wiki for a list of many products which you didn't know were based on this database: But I think there might be something happening. Maybe the PostgreSQL community itself is getting some new currency and importance. I also notice that many recent startups building on PostgreSQL are moving toward building as extensions instead of forks. At PostgresConf last week, the closing panel included a VC-backed startup doing timeseries in PostgreSQL (timescale). I had dinner with the co-founder of a startup doing AI in PostgreSQL (ziff). I saw articles on twitter over the past two weeks about startups doing stream processing in PostgreSQL (pipelinedb) and building a graph database on PostgreSQL (edgedb). Even Cockroach chose PostgreSQL for its wire protocol and client libraries. Three of these companies are aiming to ship as extensions. I'm leaving these links off the newsletter because I want to focus on core PostgreSQL here, but feel free to google them. My point here is simply that PostgreSQL might be more important than you thought, if you didn't realize just how big the ecosystem is! Anyway, moving on... let's dive in to a few technical articles. First up, a quick article on every DBAs job #1: recovery (and backups). Making sure data is safe and durable. On April 16, Viorel Tabara published a nice list of "Top Backup Tools for PostgreSQL" on the severalnines blog. Quick example of how powerful regular expressions are in PostgreSQL? Take a look at this short blog on cybersandwich.com that was published April 24. Two articles about bind variables showed up recently on the jOOQ blog - a reliable source of great articles digging into database optimizers. While the examples were focused on Oracle, both articles touch on PostgreSQL too. First, Why SQL Bind Variables are Important for Performance ... and second, When Using Bind Variables is not Enough: Dynamic IN Lists. Finally, my past newsletters have been following Dimitri Fontaine's series on PostgreSQL data types. I'm getting behind. He's got five new articles out already!!! Data and time processing, network addresses, ranges, arrays and XML! Finally, would you be interested in a digest of PostgreSQL articles in video format instead of email? Last week I stumbled across Creston Jamison's blog where he does exactly that! His most recent digest covers 10 articles (many of which I also have covered in writing). Check it out! That's a wrap for today. Have a great week and keep learning!

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

Log buffer #545: a carnival of the vanities for DBAs

Share this

Share this

More resources

Linux file system for SQL Server DBAs

Exadata's Best Kept Secret: Storage Indexes

Observations and insights from Google Cloud Next '18

Ready to unlock value from your data?