Press "Enter" to skip to content

Category: PGSQL Phriday

UUIDs in Postgres

Published 2024-02-01 by Kevin Feasel

Ryan Lambert is not a fan:

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 closed

PGSQL Phriday 012 Roundup

Published 2023-09-11 by Kevin Feasel

Ryan 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 closed

PostgreSQL 16 and Infinity

Published 2023-09-01 by Kevin Feasel

Ryan 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 closed

Thoughts on Partitioning in Postgres

Published 2023-08-07 by Kevin Feasel

Ryan Booz splits things out:

For 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 closed

An Overview of Partitioning and Sharding in Postgres

Published 2023-08-07 by Kevin Feasel

Michael 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 closed

Log Analysis by Hand in Postgres

Published 2023-07-10 by Kevin Feasel

Laetitia 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 closed

Analyzing Postgres Logs with pgbadger

Published 2023-07-10 by Kevin Feasel

Anthony 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 closed

Postgres Change Management Rollbacks

Published 2023-06-05 by Kevin Feasel

Grant 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

Thoughts on Postgres File Layout and Migration

Published 2023-06-05 by Kevin Feasel

Dian Fay shares some advice:

I’ve used several migration frameworks in my time. Most have been variations on a common theme dating back lo these past fifteen-twenty years: an ordered directory of SQL scripts with an in-database registry table recording those which have been executed. The good ones checksum each script and validate them every run to make sure nobody’s trying to change the old files out from under you. But I’ve run into three so far, and used two in production, that do something different. Each revolves around a central idea that sets it apart and makes developing and deploying changes easier, faster, or better-organized than its competition — provided you’re able to work within the assumptions and constraints that idea implies.

Read on for thoughts about three tools: sqitch, graphile-migrate, and migra.

Comments closed