Press "Enter" to skip to content

Category: PGSQL Phriday

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

Source Control and Change Management for Postgres

Published 2023-06-05 by Kevin Feasel

Ryan Booz relives an older story:

For those of you that don’t know, those ER tools were really expensive (probably still are for the ones that exist) and only a few developers had access to the tool. They didn’t have a great DX either.

Aside from the lack of automation and ability of our developers to be more integrated into the process, there was always the one looming issue that we just couldn’t reconcile.

If Joe left and joined the circus (see, I got you there), we were stuck.

We knew this was a bottleneck for some time and we had tried multiple times to change the process. Our ability to iterate on new feature development went through one person and a set of 15-year-old scripts. It didn’t match our otherwise Agile process of front-end code and data analysis projects.

Read on for Ryan’s thoughts on database change management. Some of the tools mentioned work with multiple database platforms, whereas others are specific to Postgres.

Comments closed

pg_stat_statements and Public Sentiment

Published 2023-05-08 by Kevin Feasel

Andreas Scherbaum polls the audience:

For anyone who doesn’t know, I’m running a weekly interview series with people from the PostgreSQL community. It’s called “PostgreSQL Person of the Week“. One of the questions in the default set I give everyone is:

What is your favorite PostgreSQL extension?

And guess what the answer is: by far everyone’s favorite is pg_stat_statements!

Read on to learn a bit more about what the extension does, why people like it, and what other extensions interviewees prefer.

Comments closed

Tracking Configuration-Based Performance Differences in Postgres

Published 2023-05-08 by Kevin Feasel

Ryan Lambert shows off a Postgres extension:

This is my entry for PgSQL Phriday #008. It’s Saturday, so I guess this is a day late! This month’s topic, chosen by Michael from pgMustard, is on the excellent pg_stat_statements extension. When I saw Michael was the host this month I knew he’d pick a topic I would want to contribute on! Michael’s post for his own topic provides helpful queries and good reminders about changes to columns between Postgres version 12 and 13.

In this post I show one way I like using pg_stat_statements: tracking the impact of configuration changes to a specific workload. I used a contrived change to configuration to quickly make an obvious impact.

Read on for the example.

Comments closed

Against Triggers in PostgreSQL

Published 2023-04-10 by Kevin Feasel

Laetitia Avrot is not a fan of triggers:

My opinion comes from years of practicing as a production DBA, then as a database consultant. As such a professional, my opinion is biased because I am never called when it works! I’ve always been called when there are problems (big problems, usually) so that I see the worst developers can do and never the best. I try to be aware of that bias, but it’s not that easy.

I am sympathetic to Laetitia’s argument but ultimately don’t agree, at least in the general case. Some of these thoughts and alternatives are Postgres-specific, so I don’t have an opinion on those.

Comments closed

A Love-Hate Relationship with Triggers

Published 2023-04-10 by Kevin Feasel

Ryan Booz shares some thoughts on triggers:

By design, plain ANSI SQL is declarative (“hey database, this is the data I want, you figure out how to do it”), not procedural (“Hey database, I want this data and I want you to retrieve it like this”). Early on, there wasn’t a standard design for how to add on additional procedural-like features, although that later came with the definition of SQL/PSM sometime in the mid-90s.

However, through the late 80s and most of the 90s, database vendors were trying to keep pace with very quickly changing requirements and needs in the database space. Even though triggers weren’t officially added until the SQL:99 standard, databases like Oracle had already released their own procedural languages and features. Triggers may have been deferred in the SQL-92 standard, but the Standards team couldn’t ignore them (or the complexity that triggers add to transactional consistency).

Click through for a bit more background, some of the pros and cons of triggers, and a few cases where triggers can make sense.

Comments closed

Audit Operations with Triggers in PostgreSQL

Published 2023-04-07 by Kevin Feasel

Ryan Lambert creates a trigger:

I rarely use triggers. I don’t hate triggers, I just think they should be used sparingly. Like any specialized tool, you should not expect to use triggers for every occasion where they could be used. However… there is one notable use where case I really like triggers: audit tables. Part of the magic of using triggers for auditing data changes in Postgres is the JSON/JSONB support available.

I think this attitude toward triggers is correct. Triggers are really useful, but they come with a few drawbacks, such as tools not making it readily obvious which tables have triggers associated with them, the effects of triggers being “hidden” until execution, and execution problems (e.g., performance issues, potentially modifying data incorrectly, assuming triggers will only work with one row at a time, etc.).

Comments closed