---

Transaction Processing Chapter

Wednesday, September 27, 2023

Postgres 16 has a new chapter about transaction processing internals, which includes details about transaction identifiers, locking, subtransactions, and two-phase transactions. While this is new in Postgres 16, it also applies to all previous supported releases.

Post a Comment

---

Setting Per-User Server Variables

Monday, September 25, 2023

Postgres server variables offer powerful control over how the database behaves. Privileged users often want to set superuser-restricted server variables for non-superusers. The simplest approach is for the superuser to issue alter role ... set on the non-superuser role. If the superuser wants the non-superuser role to have more control over a server variable, a security definer function can be used, e.g.:

-- Use a transaction block so the function is only visible with the proper permissions.
BEGIN WORK;
 
-- This function allows log_statement to be set to only 'ddl' or 'mod'.
CREATE OR REPLACE FUNCTION set_log_statement (value TEXT)
RETURNS VOID AS
$$
BEGIN
        IF value = 'ddl' OR value = 'mod'
        THEN    EXECUTE format('SET log_statement TO %1$s', value);
        ELSE    RAISE EXCEPTION 'log_statement cannot be set to "%"', value;
        END IF;
END
$$
LANGUAGE plpgsql
SECURITY DEFINER;
 
-- Grant non-superusers permission to execute this function.
REVOKE EXECUTE ON FUNCTION set_log_statement FROM PUBLIC;
GRANT EXECUTE ON FUNCTION set_log_statement TO non_superuser;
 
COMMIT WORK;

Postgres 15 added the ability for grant to give non-superusers permission to change superuser-restricted server variables, e.g.:

GRANT SET ON PARAMETER log_statement TO non_superuser;

As you can see, superusers can adjust superuser-only server variables for non-superusers in several ways:

• To constants on non-superuser login via alter role ... set
• Allow non-superusers to set such variables to a limited number of values via security definer functions
• Allow non-superusers to fully control them via grant

Post a Comment

---

Postgres 16 Features Presentation

Wednesday, September 20, 2023

Now that I have given a presentation about Postgres 16 features in Jakarta and Chicago, I have made my slides available online.

Post a Comment

---

Selecting Conferences

Monday, September 18, 2023

Since the covid crisis ended, the number of Postgres conferences has returned to normal. As a frequent speaker I have to use criteria to decide which conferences to attend. Having to make attendance decisions regularly, I came up with the following criteria to help guide my decisions:

• Employer sales or strategic business purpose for my attendance
• Density of community members that requires my attendance
• Small enough that my attendance helps the event grow
• Non-Postgres event that needs a highly-visible Postgres speaker
• Geographically close to my home or another event

I hope publishing these criteria helps other speakers who have to make similar decisions.

Post a Comment

---

Leadership Abuse

Friday, September 15, 2023

U.S. President Harry Truman had a defining quote displayed on his desk, "It is amazing what you can accomplish if you do not care who gets the credit." Leaders who embrace this concept attract engaged team members who invest in their goals.

Unfortunately, not everyone follows this concept. Some people are driven by desires for power and recognition, and are willing to exploit organizational weaknesses to accomplish their goals. As I mentioned earlier, the Postgres community structure is very open, and this gives people who want power and recognition the opportunity to quickly get involved, with few guard rails to limit their efforts. They abuse this freedom and try to get out-sized influence. The community usually tolerates their behavior for a long time but eventually sees the harm being done and reacts, often completely eliminating the offending person's influence. These kinds of reactions happen rarely, so they often surprise those who are punished because they can't remember seeing or hearing about any previous punishments, and thought the community was open and that there were no limits.

In fact, the initial punishment is often invisible. It might be as simple as people ignoring them, not accepting them as speakers, or not attending their events. For people who desire power and recognition, this is a serious blow and they start to look for other ways of exploiting the system, leading to further problems which can cause more public punishments. The community also adds structures to avoid similar problems in the future.

In the past I have warned several people who eventually got into such problems. Unfortunately, they thought my warnings were simply negative and that I didn't understand the huge value they were bringing to the community with their "leadership". Once they end up in a big hole, they contact me to ask how to get out, and I am often unable to help since so much damage has been done.

Post a Comment

---

Who's in Charge?

Monday, September 11, 2023

When you get involved with a new organization, you assume it is like previous organizations you have been involved with that have a hierarchical structure. It has a person or group of people in charge, who are either given their positions by the organization owners, shareholders, or elected by the organization's members. Those in charge control limited resources, like money, budgets, or property. Those not in charge work within the constraints set by those in charge, and their decision-making is limited to implementing the goals assigned to them.

Therefore, when people want to get involved with the Postgres project, they assume we have a structure similar to other organizations. They look for those in charge: "Who controls limited resources?", "How to get in a position of leadership?", and they get confused. Sometimes they focus on the core team — they must be in charge, but they aren't elected, and the list of things they manage doesn't appear to be foundational to the project.

There is a list of committers who control changes to the source code. Are they in charge, at least of the source code? While they initiate the changes, they are subject to acceptance or rejection from anyone on the hackers email list, and they must address any feedback. Sometimes new developers who represent companies ask how they can become committers because they don't understand that committing is a mechanical process and that the hackers list is the proper place to start development discussions.

And are other groups:

• Contributors
• Planet Postgres team
• Irc channel moderators
• Events organizers
• User groups organizers
• Trademark board
• Funds group
• Infrastructure team
• Security team
• Code of conduct committee

Which groups report to which other groups, and who is in charge? The answer is that the groups are distributed and operate mostly independently because they lack resource limits which require hierarchical structures. However, they must take suggestions and criticism from almost anyone in the community, and must respond to it. If a group does a consistently poor job, new members will join the team to help. Almost anyone can join any of the groups, though the last three listed groups limit membership and are more hierarchical because they manage limited resources or handle sensitive information.

Who is ultimately in charge? There is a great temptation to say it is the core team, and technically this is correct because their final listed role is, "Making difficult decisions when consensus is lacking." However, this authority is used so rarely to be almost an afterthought. Instead of thinking of the core team as the center of a vast web of control, it might be better to think of it as the core of the Earth — yes, it needs to be there for other layers be on top of, but most of the Earth's activity happens near the Earth's crust, and the Earth's core can usually be safely ignored.

So, who is in charge? You are, and everyone else in the community. You have the ability to help in almost any area, with few hindrances except for the suggestions and corrections of other community members. How do you get anything done? You give your opinion, you listen for the opinions of others, hopefully adjust your goals with these new opinions, and once no one else complains, you are good to go! Do that for 27 years, like Postgres has, and it is amazing how good the result can be.

Post a Comment

---

Email Etiquette

Friday, September 8, 2023

While a recent Postgres survey indicates that email lists are an impediment to new people getting involved with the project, email continues to be the primary means of communication in the community. Some of the reasons are historical, some are based on email's distributed nature, and some on email's flexibility of expression.

Unfortunately, fewer people are familiar with how to use email efficiently, especially on email lists with thousands of people. While the Postgres community tried to educate users on the intricacies of email, there are a few issues that continue to challenge users:

• Top posting: Just adding text to the top of a message is discouraged since is supplies no context about what part of the previous email this new comment refers to.
• Bottom posting with no trimming: This adds text only to the bottom of the email, which has the same problems as top posting. In fact, it is worse since most users have to view the entire quoted message just to see the addition.
• Informality: Using abbreviations, short-cuts, and unclear language is appropriate for two-person communication, but when communicating with thousands of people, additional time is needed to craft emails.

One additional complexity is that email composition agents are different, meaning that it is hard to trim and inline-quote text in some email agents, and display of inline text is sometimes handled differently. While long-time community members usually use email agents where these formatting options are easy, new community members are less likely to have such email agents and less likely to understand the complexities of emailing thousand of people. Effectively, this makes a double-barrier to people entering the community via the email lists. Fortunately, people are patient and email posters eventually understand the intricacies of email, but it is something that isn't easy at the beginning.

View or Post Comments

---

Supermarket Chicken

Wednesday, September 6, 202

You would think that open source and the inexpensive rotisserie chickens available in many supermarkets would have little in common. Open source exists in the virtual enterprise-dominated world, while supermarket chickens are, well, very domestic and practical. However, economic forces tend to operate similarly in all spheres of life.

First, let's look at the precooked supermarket chicken. A Costco executive explained why those chickens are so cheap:

Some of the things you go to Costco for, you don't need once a week. In order to attract customers more often, they have chicken or hot dogs or the low prices on gasoline. They don't put the chickens at the front of the store. You have to walk past the blenders and the big-screen TVs. The guy who stops in to buy a rotisserie chicken for a quick, hot meal might walk past the laptops and say, "Maybe I'll go to the Costco to buy a laptop for my son for Christmas."

So, cheap chickens get people into the store, and they buy more later. With open source, its low cost gets people to try it out and adopt it for small projects. As the software proves its worthiness, its use expands and ultimately organizations standardize on it. While open source wasn't designed to use this method to expand its footprint, its zero-cost adoption has rapidly increased its growth. Once people use Postgres, and understand the value of its unique features, they would probably choose Postgres even if it were not cheaper.

The normal perception flow during Postgres adoption is:

• Postgres is cheaper but inferior to proprietary databases in features, performance, and reliability
• Postgres is similar to proprietary databases in features, performance, and reliability
• Postgres has superior features compared to proprietary databases

It's a funny thing — people need low cost to try out Postgres, but once they experience it, its cost benefit is immaterial.

View or Post Comments

---

PostgreSQL Benefits and Challenges: A Snapshot

Tuesday, September 5, 2023

Ivan Panchenko wrote a great article about why and how businesses should start using Postgres. It covers Postgres's structural benefits like vendor independence, code quality, and technology like NoSQL. It also covers the challenges that could be faced, like cost, time, and in-house expertise. When I saw "Not controlled by a single vendor" in both the benefits and challenges sections, I knew he was on the right track!

View or Post Comments

---

Community Edition

Friday, September 1, 2023

Related to my previous blog entry about bait and switch, I have heard people struggle to distinguish between binaries based on source code released by the Postgres community and binaries based on modifications of the community source code. The term "community edition" has often been used to represent binaries in the first category.

I think "community edition" has several problems. First, it suggests that there is a non-community edition, i.e., an enterprise edition. While some companies do produce modified enterprise-focused versions of Postgres, the community only produces one version of the source code which is designed for hobbyists, developers, small businesses, and large enterprises.

A second problem is that companies that control the development of open source software often create a "community edition" of their software which is free, and an "enterprise edition" which requires payment and might have more features, fewer license limitations, or fewer usage restrictions. Again, the Postgres community only produces one version.

I wonder if the terms "community Postgres" or "community release" would be superior to "community edition".

View or Post Comments

---

Open Source Bait and Switch: Licensing and Beyond

Wednesday, August 30, 2023

Having been involved in open source for thirty years, I have seen many open source distribution methods, packaging systems, licensing options, and business models. In the early days there were only two classes of licenses — public domain-style licenses like BSD and MIT, and the GNU licenses. Though the GNU licenses had confusion around applications linking to static GNU libraries, the general goals of the two licenses were well understood. The Postgres license is similar to public domain-style licenses, and is accepted as an OSI-certified license.

As open source took over the enterprise IT infrastructure, companies formed around open source, and diverse licenses started to proliferate. Rather than align with the previous two popular open styles, they created new licenses that were closer to proprietary licenses to gain economic advantage. While the new open source licenses make the source visible, how binaries created from the source could be used and how modifications could be produced were often limited, and licenses changed to be more restrictive. Users often overlooked the negative effects of company control of the software development process until it was too late. Companies even used GPL licenses for revenue-generating purposes in ways there were not obvious to people who initially adopted the GPL software.

In summary, users often equated open source as "not proprietary," and hence always good. It has taken years for organizations to realize that all open source licenses are not the same, and that companies can distort the open source process to their own advantage, to the detriment of the user community. This article (comments) by Peter Zaitsev goes into the gory details of how open source is used to gain economic advantage. Its theme is "bait and switch" — that users choose open source, no matter what open source license is used or who controls development, and often don't realize the downsides until they are hit with the negative consequences.

Having worked with Postgres for 27 years, I have seen this bait-and-switch many times, as users overlooked Postgres for more polished open source software, backed by companies designed to exploit the attraction of open source. As open source-based companies grew, and then, after exploitation was widely known, declined, Postgres continued its steady progress. Twenty-seven years later, that steady progress has paid off with an exceptionally-capable feature set and sterling reputation.

View or Post Comments

---

The Postgres Trajectory

Friday, June 30, 2023

I am not known for light, fluffy talks, but rather "down in the weeds" talks where I can go through 80 slides in 50 minutes. Maybe not something to be proud of, but it is what excites me.

So, when the PostgreSQL Singapore Community Meetup asked me to give a 20-minute talk about Postgres trends, I was challenged, and was reminded of something Robert Haas said recently about short talks often being harder than longer ones. It required me to come up with the foundational reasons Postgres is so popular, and I choose two, open source and extendibility — both have clear historic and current data on why they are important to Postgres's success.

All I then needed to do was to create a slide deck, and unsurprisingly, I already had a lot of material on these topics, though not distilled to their essence as required. I took the highlights of my material, mostly diagrams, and created a short 14-slide presentation called The Postgres Trajectory. I presented the talk virtually for Singapore and hope to use it for other events where a short, big-picture talk is required.

View or Post Comments

---

Postgres 16 Release Notes

Friday, May 19, 2023

I have just completed the first draft of the Postgres 16 release notes. It includes developer community feedback but still needs more xml markup and links.

The release note feature count is 199, which is 7% higher than the average count of the previous five releases. Postgres 16 Beta 1 should be released soon. The final Postgres 16 release is planned for September/October of this year.

View or Post Comments

---

Dissecting Partitioning

Thursday, January 12, 2023

Having delivered my new talk Dissecting Partitioning at PGDay/MED, I have now added this 109-slide presentation to my website. The talk first covers the purpose and limitations of Postgres's declarative partitioning implementation. The bulk of the talk uses SQL queries to show the many optimizations possible with partitioning. It concludes by showing some complicated data architectures made possible by partitioning.

View or Post Comments

---

Beyond Joins and Indexes

Thursday, January 12, 2023

My presentation Explaining the Postgres Query Optimizer covers the details of query optimization, optimizer statistics, joins, and indexes. I have delivered it 20 times since 2011.

While that talk covers common optimizer issues, in recent years I began to feel it would be helpful to produce a comprehensive talk about the other plans the optimizer can choose. Researching all the details proved to be a challenge, and the result is Beyond Joins and Indexes, which covers the 42 other plans that were not covered in my previous optimizer talk. I delivered this talk at the DC PostgreSQL Users Group on Monday so I have now added the slides to my website.

Update: I incorrectly stated in my two previous live presentations (Los Angeles, Russia) that memoize did not cache negative matches. I have corrected the slides on this point. 2023-04-07

View or Post Comments

---