Bruce Momjian

Postgres Blog

This blog is about my work on the Postgres open source database, and is published on Planet PostgreSQL. PgLife allows monitoring of all Postgres community activity.

Online status:
Unread Postgres emails:
Email graphs: incoming, outgoing, unread, commits (details)

Performance Oracle Real Application Clusters (RAC)

Monday, July 9, 2018

I get asked about Oracle RAC often. My usual answer is that Oracle RAC gives you 50% of high reliability (storage is shared, mirroring helps) and 50% of scaling (CPU and memory is scaled, storage is not). The requirement to partition applications to specific nodes to avoid cache consistency overhead is another downside. (My scaling presentation shows Oracle RAC.)

I said the community is unlikely to go the Oracle RAC direction because it doesn't fully solve a single problem, and is overly complex. The community prefers to fully-solve problems and simple solutions.

For me, streaming replication fully solves the high availability problem and sharding fully solves the scaling problem. Of course, if you need both, you have to deploy both, which gives you 100% of two solutions, rather than Oracle RAC which gives you 50% of each.

However, I do think database upgrades are easier with Oracle RAC, and I think it is much easier to add/remove nodes than with sharding. For me, this chart summarizes it:

                         HA   Scaling  Upgrade Add/Remove
        Oracle RAC       50%     50%    easy    easy
        Streaming Rep.  100%     25%*   hard    easy
        Sharding          0%    100%    hard    hard
        * Allows read scaling

(I posted this to pgsql-general in 2016.)


Performance Query Planner Interview

Saturday, June 23, 2018

Software Engineering Radio has just posted a one-hour audio recording of an interview I did about the Postgres query optimizer. It is generic enough to be useful to anyone wanting to understand how relational databases optimize queries.

On an unrelated note, I am leaving soon for a 25-day European Postgres speaking tour. I am speaking at one-day conferences in Zürich, London, and Amsterdam, a user group in Frankfurt, and will be presenting at ten EnterpriseDB events across Europe.

Post a Comment

Presentation Will Postgres Live Forever?

Thursday, June 7, 2018

I had the opportunity to present an unusual topic at this year's Postgres Vision conference: Will Postgres Live Forever? It is not something I talk about often but it brings out some interesting contrasts in how open source is different from proprietary software, and why innovation is fundamental to software usage longevity. For the answer to the question, you will have to read the slides.

Post a Comment

News Draft of Postgres 11 Release Notes

Thursday, May 17, 2018

I have completed the draft version of the Postgres 11 release notes. Consisting of 167 items, this release makes big advances in partitioning, parallelism, and server-side transaction control via procedures. One of the more unexpected yet useful features is "Allow 'quit' and 'exit' to exit psql when used in an empty buffer".

The release notes will be continually updated until the final release, which is expected in September or October of this year.

Post a Comment

Documentation Intermediate Certificates

Monday, January 22, 2018

I previously mentioned the importance of high quality documentation, so we are always looking for improvements. This email thread from 2013 attempted to codify the rules for how to properly use intermediate ssl/tls certificates with Postgres. At this time, our documentation was updated to recommend storing intermediate certificates with root certificates because it was unclear under what circumstances intermediate certificates are transferred to the remote server to be chained to a trusted root certificate.

During research for my four security talks, I studied certificate handling. I found certificate chain resolution rules in the verify manual page. In testing various certificate locations, I also found that Postgres follows the same rules.

Based on this testing, I realized the conclusions reached in 2013 were inaccurate, or at least incomplete. While the documented procedure worked, the more practical and recommended approach is to store intermediate certificates (created with v3_ca extensions) with leaf certificates to be sent to the remote end. (I think the requirement of using the v3_ca extension when creating intermediate certificates is what caused much of the testing confusion in the past.)

This new procedure allows short-lived leaf and intermediate certificates to be replaced at expire time while long-lived root certificate stores remains unchanged. For example, for clients to verify the server's certificate, the server would contain the intermediate and server's leaf certificates, and clients only need root certificates, which rarely change.

The documentation of all supported Postgres versions has been updated to recommend this new procedure. I have also added sample scripts showing how to create root-leaf and root-intermediate-leaf certificate chains.

These changes will be distributed in the next minor Postgres releases, scheduled for next month. Until this new documentation is released, you can read the updates in the Postgres 11 docs in the server and libpq ssl sections. I am hopeful this clarified documentation will encourage people to use ssl and ssl certificate verification.

Post a Comment

Presentation Four New Security Talks

Monday, January 15, 2018

In the past few months I have completed four new security talks, totaling 294 slides. The first and third talks explain the fundamentals of cryptography and cryptographic hardware, respectively. The second and fourth talks cover application of these fundametals. The second talk covers tls, including the use of ssl certificates by Postgres. The fourth covers the use of cryptographic hardware by applications, including Postgres.

Post a Comment

Conference Video of Russian Interview

Wednesday, January 10, 2018

I just did a two-hour interview in English with the Russian Postgres user group. A video recording of the interview is online and covers questions asked by the Russian attendees.

Post a Comment

Community Web Forums?

Friday, January 5, 2018

This email thread explores the idea of the community supporting web forums instead of or in addition to the email lists, where the majority of community development and discussion happen. Reason stated for not pursuing web forums included:

  • It has been tried before and failed
  • Larger web forum communities already exist, e.g. Stack Overflow
  • Established Postgres community members prefer email

Ultimately I think the Postgres community needs to do a better job of publicizing the existence of external communities that help Postgres users, e.g. Slack. As an example, the Postgres irc channel is well publicized and currently has 1,100 connected users. Also, EnterpriseDB created the Postgres Rocks web forum six months ago.

View or Post Comments

Documentation Wal and Xlog

Wednesday, January 3, 2018

Postgres isn't the best at naming things. Of course, there is the old computer saying, "There are only two hard things in Computer Science: cache invalidation and naming things." With Postgres being 31 years old and developed by several different project teams, naming can be even more inconsistent.

One naming inconsistency, which we have lived with for years, is the name of the write-ahead log. Postgres references this using the acronym wal in server variables like wal_level, but the PGDATA directory containing the write-ahead log files was called pg_xlog. In "pg_xlog," the "x" stands for "trans" which is short for "transaction", and of course "log" means "log", so "xlog" was short for "transaction log." This was also confusing because there is a clog directory which records "transaction status" information (commits, aborts). So, "xlog" or "transaction log" was already a bad name, and having it also referenced as wal just made it worse.

Postgres 10 has made the difficult change of removing references to "xlog" and "clog," and instead name them "wal" and "pg_xact" consistently. This email thread covers many of the gory details of what we changed and why. It isn't ideal to be changing the name of internal database objects, and it will cause some pain to those moving to Postgres 10, but future users of Postgres will have a more consistent experience of Postgres and how it works.

View or Post Comments