Log Buffer #538: A Carnival of the Vanities for DBAs

Posted in: Log Buffer, Technical Track

This Log Buffer covers blog posts from Cloud, Oracle, SQL Server, MySQL and PostgreSQL.

Cloud:

Finer-grained security using custom roles for Cloud IAM

Google Cloud Platform opens region in the Netherlands

12 best practices for user account, authorization and password management

Invoking AWS Lambda from Amazon MQ

Task Networking in AWS Fargate

Oracle:

Webserver Logfile Analysis with Oracle APEX

Oracle Solaris 11.4 Public Beta Released

Quick Debugger Tip, where was that class loaded from.

JET 4.1 : How to use toastr to show non-blocking notifications

Index Skip Scan: Potential Use Case or Maybe Not ? (Shine On You Crazy Diamond)

All of Your SQL Developer Google Questions

SQL Server:

A ship, a hairbrush, and the importance of having a Plan B

Differentiation by Denny: LCPtracker’s Reporting Warehouse

How to backup large databases. Recommendation.

How to Upgrade SQL Server

Scripting Tip: Start With Crediting Your Source

MySQL:

Aurora Hash Join Optimization (with a Gentle Reminder on Lab Features)

Choosing the best indexes for MySQL query optimization

MySQL Connector/Java 8.0.9-rc has been released

Partial LCP in MySQL Cluster 7.6.4

How To Achieve PCI Compliance for MySQL & MariaDB with ClusterControl – The Replay

PostgreSQL:

Thanks to Jeremy Schneider from Ardent Performance for PostgreSQL section.

Before digging into the chatter of the week, lets get announcements out of the way. I think it’s worth mentioning the recent JDBC driver release because it brings support for the new postgresql 10 authentication method called SCRAM. Java-based applications can finally start testing this new protocol now.

If I had to pick a dominant theme for the last two weeks, I’d probably choose partitioning and time-series data. First off, on Jan 20 JoãoFerreira published a short and widely-shared article pointing out a short sentence from the PostgreSQL 10 docs saying it supports not only partitions but also sub-partitions!

But what I really noticed this week wasn’t just partitioning, it was one specific application of partitioning: time-series or log data. I
personally helped stand up elasticsearch clusters at past jobs in order to analyze large volumes of logs from many machines. Within the past few months I’ve taken trace data and imported it into postgresql to have access to window functions and other useful features of SQL. And it’s not just me – a lot of people are talking about returning to relational systems with their time-series data.

Want to see what this looks like in PostgreSQL? Arpit Agarwal wrote a great article on Jan 23 describing how they cut their Amazon RDS costs by 90% with PostgreSQL traditional (inheritance-based) partitioning and he even included code snippits to show exactly how they did it.

On Jan 25, Timothy Prickett Morgan published an article on The Next Platform called It’s About Time For Time Series Databases. Seemed to me that there were a fair number of retweets on this piece about the new postgres-based TimescaleDB.

But is TimescaleDB your only choice of specialized PostgreSQL extensions? Of course not! In fact, on Jan 24 Craig Kerstiens from Citus wrote a great article about using their stalwart pg_partman extension for time-series data – which is even able to leverage PostgreSQL 10’s new native partitioning!

So between all of this you can see why it felt like time-series was getting a little more discussion than usual. But the usual discussion topics continued at full steam too, such as the ever-present subject of indexes. There were quite a few really good articles worth highlighting.

On Jan 25, Russell Cohen dug into B-Tree implementation with his article Postgres Indexes Under the Hood.

On Jan 18, Amit Bansal from askdba.org wrote what he learned while Migrating Function based indexes from Oracle to PostgreSQL.

And Alibaba Cloud FTW with two articles: first, an excellent walk-through of 14 different indexing types and strategies in PostgreSQL! This is worth reading and bookmarking.

Second, Alibaba Cloud published an article talking about using indexes to tune queries with four specific examples.

Ok, I’m just going to have to stop myself now. There are a lot more good articles I could pass along… but it would be too much for one week! So I’ll leave you with two final articles.

The big talker on Jan 30 was Robert Haas disclosure that EnterpriseDB has evidently been working on adding a whole new MVCC mechanism to PostgreSQL – namely, using undo. His article “DO or UNDO – there is no VACUUM” is sure to generate lots of discussion.

And finally, we’ve been following Jan Karremans last two articles about his personal journey from Oracle to PostgreSQL. On Jan 27 he published his third and final chapter, where he talks more on what PostgreSQL actually is and why it attracted him. If you’ve been following along then you can now hear the rest of the story!

There is a lot more, but I’ll stop here. Have a great week and keep learning!!

email

Interested in working with Fahd? Schedule a tech call.

About the Author

I have been in love with Oracle blogging since 2007. This blogging, coupled with extensive participation in Oracle forums, plus Oracle related speaking engagements, various Oracle certifications, teaching, and working in the trenches with Oracle technologies has enabled me to receive the Oracle ACE award. I was the first ever Pakistani to get that award. From Oracle Open World SF to Foresight 20:20 Perth. I have been expressing my love for Exadata. For the last few years, I am loving the data at Pythian, and proudly writing their log buffer carnivals.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *