This Log Buffer Edition covers Cloud, Oracle, and PostgreSQL.
Cloud:
Central Logging in Multi-Account Environments
New AWS Certified Solutions Architect – Associate Exam: Now in General Availability
Announcing new milestones for Microsoft Cognitive Services vision and search services in Azure
Confidently plan your cloud migration: Azure Migrate is now generally available!
Get Reserved Instance purchase recommendations based on usage pattern
Oracle:
Does an automated cloud data warehouse need partitioning and indexes?
ORA-19527: physical standby redo log must be renamed
Changing Security Role Password in Oracle GoldenGate 12.3 Microservices
Set the minimum password length on your default authenticator in Weblogic
There’s a question on the ODC database forum about column groups that throws up an interesting side point.
PostgreSQL:
Thanks to Jeremy Schneider.
Backups and replication! Three cheers for durability! That’s what seemed (to me) to get the most @mentions over the past two weeks. And in our business – the data business – it’s not a bad topic to keep coming back to. But first, a few announcements worth highlighting.
First, do you enjoy the networking and learning opportunities of conferences? There’s no shortage of upcoming opportunities all over the globe this year. This week on twitter I saw news about conferences in New Jersey and San Francisco; and on the postgresql.org events page I see 15 upcoming conferences in 10 countries! If you don’t already have plans to attend one, now is a great time to nail it down!
Second, on Mar 1 the community decided that a recent CVE was important enough to merit an early release of 10.3 and other minors.
In addition to planning for upgrades, you should take a few minutes to review this wiki page with information about general risks inherent in postgresql’s architecture and specifically the “search_path” feature.
The final announcement that’s getting a lot of retweets this week was the Amazon RDS official release of PostgreSQL 10. This represents a lot of hard work by our team! A few interesting points I’d note would be ICU version 60.2, an important plv8 update which I think is still making its way into many other postgresql distributions, and the announcement that this will be the last major RDS version to make tsearch2 available. (You’ve probably been using the equivalent functionality from core PG for years, but just in case…)
As a footnote, I think it’s worth pointing out that the community made a change to defaults in PG10 such that your planner will start generating parallel query plans when you upgrade (even if they end up executing serially due to lack of available parallel workers). That’s something to keep an eye on. Parallel queries were not enabled by default in version 9.6.
With announcements out of the way, lets see what everybody’s been talking about over the past two weeks. We’ll start at the top… and I really mean the top: This week I’m starting off with a heady, high-level article about how we use databases. But very important and worth a read from anyone who might ever create a table. By now, probably most people who write code have heard the term “code smell”. Wikipedia claims it was coined by Kent Beck in the late 90s, popularized by Martin Fowler in a ’99 book, and amplified by the agile movement. Well, thanks to an ICSE17 paper we now have Database Relation Smell. One of the authors, Tushar Sharma, has kindly summarized the paper in an article on his blog and I’m very interested to follow this discussion!
While we’re at the level of “using databases” lets give a quick shout out to Tanin Na Nakorn from google who published a short tip on Feb 20 about what NULL means in databases. This often catches people off guard and is always worth repeating.
Next, I didn’t include this in the last digest but I do want to mention Micha? Konarski‘s article from Jan 26 about Common Table Expressions. CTE’s are a very useful SQL syntax (the “with” clause) that many people don’t know about. My personal experience is that they can make complicated logic much more readable and maintainable, and generally I’ve seen query planners able to optimize them efficiently.
If you’ve used relational databases for any length of time, then I’m sure you’ve bumped into locks… and Marco Slot of Citus swoops in this week with two articles to help us understand them. First, on Feb 15, a great overview of lock types (both table and row) as well as the pg_locks view in PostgreSQL.
And on Feb 22, seven important techniques for reducing lock contention in your usage of PostgreSQL.
I noticed a cluster of articles related to testing and benchmarking in February. Lets take a look.
On Feb 15, Baji Shaik from SeveralNines published an article with a general overview about benchmarking PostgreSQL.
On Feb 21, Michael Malis from Heap published an article called Testing Database Changes the Right Way.
ArangoDB constructed a benchmark to run key-value, document and graph-oriented queries against Stanford University’s Pokec dataset. The included PostgreSQL results and later published an update after tweaking some PostgreSQL parameters.
Now lets drop down a layer and talk more about administrative and operational tasks.
I mentioned at the top about Amazon RDS release PostgreSQL 10. You might ask, what do I need to know about upgrading to version 10? You’re in luck! On Feb 22, Vladimir Svedov from SeveralNines published this article: “Upgrading Your Database to PostgreSQL Version 10 – What You Should Know!” OK… so actually it’s largely about upgrading your self-managed PostgreSQL databases to version 10. Doesn’t actually relate much to RDS – I just want to mention the RDS release again because I’m excited. ;)
Is your postgresql database secure? Sebastian Insausti published an article on Feb 26 with a great checklist for using PostgreSQL securely. I think my favorite tip is to stay on top of patching; everyone knows this already and yet with my background in operations I know how challenging it is to actually stay on top of it!
And last but certainly not least, a whole slew of articles related to backups and replication in PostgreSQL.
First, on Feb 12 Michal Zimmermann introduced us to PostgreSQL backups with the core utility pg_pasebackup and WAL archiving.
In a follow-up article on Feb 16, Michal walks us through both general recovery and point-in-time recovery of the same database.
On Feb 25, Michael Malis from Heap walks through backup concepts and introduces Citus’ backup management tools WAL-E and WAL-G.
And I didn’t mention it last month but it’s certainly appropriate now: on Jan 19, Shaun Thomas from 2nd Quadrant wrote an in-depth article about using another PostgreSQL backup management tool, BARMAN.
Bridging the small gap between backup and physical replication, Ozgun Erdogan from Citus published an article on Feb 21 covering three strategies for leveraging the backup infrastructure to initially construct a physical replica.
And on the topic of replication, I couldn’t figure out the exact dates but over the last month or two (I think) opsdash published two informative articles about replication. First, a nice overview of the different replication topologies you can create with PostgreSQL.
Second, a description of physical replication slots and how they are used to manage WAL in the replication process. I’d like to point out that even if you’re using a cloud provider like Amazon RDS (where physical replication is managed for you), it’s still crucial to understand how it works under the covers.
And finally, for a topic slightly related to backups – Franck Pachot published an excellent article on Feb 14 comparing WAL/REDO generation rates in PostgreSQL and Oracle. In particular he looks at when each system writes full blocks to the redo stream (hint… it’s sometimes related to backups). Franck is a refreshingly systematic and careful researcher; I really appreciate the care taken in writing this article!
Well that’s a wrap for this week. Let me conclude by repeating my opening appeal: take a look at the postgresql.org events page today and plan a trip to a conference in your part of the world. Maybe we’ll bump into each other!
Have a great week and keep learning!
No comments