A Fine Slice Of SQL Server
Gabrielle Roth shares some advice:
For PGSQL Phriday #016, Ryan’s asked us to “…discuss your process for tuning difficult queries. Specifically, try to focus on that one problematic query that really challenged you and you always use it as an example when helping or teaching others your methods.”
Here are the generic steps I take, mostly in order.
Click through for those tips, as well as an example of using join_collapse_limit in practice to tame an unruly query.
This month’s PgSQLPhriday #015 topic is about UUIDs. Lætitia Avrot is this month’s host, see her post for the full challenge text. The topic is described as a debate between the Database People and Developers. I’m not sure there’s such a clean divide on people’s opinions on the topic, as I know plenty of Database People that have settled on using UUIDs as their default. Similarly, I know even more developer types that have followed the arguably more conventional choice of using an auto-incrementing ID.
Click through for the debate. Most of this is similar to the argument on the SQL Server side, though without the specter of clustered indexes added to the mix.
Comments closedRyan Booz goes beyond a short summary:
I think due to a number of people attending a PostgreSQL conference during the week blogs would have been written, and the ongoing runup to a pending release, participation this month was lower than normal. But the blog posts (and audio podcast) that we did receive were top-notch and I’m genuinely thrilled to see people make the effort. Keep an eye on these blogs for other content, because the quality of their work is excellent and you’ll surely learn new things with anything new they produce!
Read on for Ryan’s review of three blog posts and one podcast.
Comments closedRyan Lambert goes to infinity and beyond:
This month, Ryan Booz chose the topic: What Excites You About PostgreSQL 16? With the release of Postgres 16 expected in the near(ish) future, it’s starting to get real. It won’t be long until casual users are upgrading their Postgres instances. To decide what to write about I headed to the Postgres 16 release notes to scan through the documents. Through all of the items, I picked this item attributed to Vik Fearing.
- Accept the spelling “+infinity” in datetime input
The rest of this post looks at what this means, and why I think this matters.
Read on to see what’s new about this and what it all means.
Comments closedFor 20+ years of database and application development, time-series data has always been at the heart of the products I work with. Inevitably, as the quantity of data grew over time, management became more difficult and query performance suffered. Over the years, the primary method for managing this growth in data effectively would be to partition it. The problem is, until recently, partitioning wasn’t easy to setup in most OLTP databases like PostgreSQL or SQL Server.
Fortunately, PostgreSQL has significantly improved its ability to partition large data tables over the last 6 years, starting with PostgreSQL 10.
Read on for Ryan’s recommendations around partitioning and a few thoughts on sharding.
Comments closedMichael Christofides defines terms:
It has been possible to do partitioning in PostgreSQL for quite a while — splitting what is logically one large table into smaller physical tables. Since version 10, a huge leap was made with the introduction of declarative partitioning, and more improvements have come every year since.
Sharding is a different story — splitting what is logically one large database into smaller physical databases. The primary tool for this in the PostgreSQL ecosystem is the Citus extension. But you can also handle the sharding logic at the application level, as recent posts from the likes of Notion and Figma have described. Somewhat confusingly, some forms of sharding are sometimes referred to as vertical partitioning, including by the team at Figma.
Read on for a few thoughts on when to perform each action and what the costs and benefits are.
Comments closedLaetitia Avrot looks at the logs:
If you’re one of my customers, you might know how I insist on monitoring your Postgres logs and digging into them to find precious insights on what’s going on with your Postgres. For a long time now, there is pgBadger. For PGSQL Phriday #010, Alicja asks us to focus on pgBadger.
You might be surprised to find out I am not using pgBadger. I will explain why later, but keep assured that I do think pgBadger is a good tool. It will help DBAs get better performance and follow how their instance is doing before there is a very bad problem.
Click through for Laetitia’s reasons as well as an alternative way of analyzing log files.
Comments closedAnthony Nowocien takes us through a useful tool:
This week, #PGSQLPhriday is hosted by Alicja Kucharczyk. Every month, one community member proposes a new subject to this monthly blogging event and let the world (or your family/friends/neighbors if you prefer) know all about pgBadger. It’s a tool to analyze your PostgreSQL logs and present you a nice web report.
If you like some history, it has been developed by Gilles Darold since more than 11 years, as v1.0 came along on June the 10th in 2012. At this time, pgfouine was the main log analyzer and the complete Perl rewrite was greatly performance influenced. In V4, it started to have its current look, by embarking the Bootstrap library and fonts.
Click through to see what pgbadger does and an example of how it makes log analysis understandable.
Comments closedDian Fay has some links for us:
Another Phriday in the books, and it’s time to see what all happened:
Click through for the full set of participants in this month’s challenge, which was about database change management in Postgres.
Comments closedGrant Fritchey explains why stateful systems are difficult to roll back:
The invitation this month for #PGSqlPhriday comes from Dian Fay. The topic is pretty simple, database change management. Now, I may have, once or twice, spoken about database change management, database DevOps, automating deployments, and all that sort of thing. Maybe. Once or twice.
OK. This is my topic.
I’ve got some great examples on taking changes from the schema on your PostgreSQL databases and then deploying them. All the technical stuff you could want. However, I don’t want to talk about that today. Instead, I want to talk about something really important, the concept of rollbacks when it comes to database deployments.
I completely agree with Grant’s description of the pain and his recommendation. With stateful systems, roll forward, not backward.
Comments closed