Posts
-
Waiting for SQL:202y: Vectors
It’s been a while since SQL:2023 was published, and work on the SQL standard continues. Nowadays, everyone in the database field wants vectors, and SQL now has them, too.
-
How about trailing commas in SQL?
Anecdotally, this might be the most requested feature in SQL: Allow some trailing commas.
-
Implementing thread-safe scanners and parsers in PostgreSQL
I have been working recently on making various scanners and parsers in PostgreSQL thread-safe, and this article is a bit of a brain dump to record what I did, what the different steps were, because all of that was pretty difficult to piece together, and it might be worth recording somewhere what was found and learned.
-
Where are all the PostgreSQL users?
Let’s stipulate that PostgreSQL has grown significantly in popularity over the last 20 years. I don’t know by how much, but certainly at least one order of magnitude, probably two or more.
-
Why PostgreSQL major version upgrades are hard
Upgrades between PostgreSQL major versions are famously annoying. You can’t just install the server binaries and restart, because the format of the data directory is incompatible.
Why is that? Why can’t we just keep the data format compatible?
-
PostgreSQL LTO battle
I wrote recently about the performance of PostgreSQL when compiled with different compilers and optimization levels. Another dimension in that evaluation is link-time optimization (LTO).
-
The new PostgreSQL 17 make dist
When the PostgreSQL project makes a release, the primary artifact of that is the publication of a source code tarball. That represents the output of all the work that went into the creation of the PostgreSQL software up to the point of the release. The source tarball is then used downstream by packagers to make binary packages (or file system images or installation scripts or similar things), or by some to build the software from source by hand.
-
PostgreSQL performance with different compilers
I have tested several times which compiler builds PostgreSQL the fastest. Now let’s look at with which compiler PostgreSQL runs the fastest.
-
How engaging was PGConf.dev really?
PGConf.dev 2024 is over. What happened while no one was watching the source code?
-
New compilers, new timings
I had been eagerly awaiting the releases of Clang 18 and GCC 14 for the last few weeks. Now that they are both out, I figured I would check how fast they are for compiling PostgreSQL.
-
When to split patches for PostgreSQL
In my previous article on how to submit patches by email for PostgreSQL, I skipped over whether patches should be split up. Let’s discuss that now.
-
PostgreSQL supported platforms over time
The recent discussion about AIX support in PostgreSQL (as of now removed in PostgreSQL 17) led me to look through the project’s history, to learn what platforms we have supported when.
-
GQL:2024 is out
The news today is that GQL:2024, the first version of the GQL standard, has been published by ISO. GQL is a new language for graph databases, like SQL is for relational databases.
-
PostgreSQL 17 commitfest conclusion
Last October, I wrote:
-
Using clangd for PostgreSQL development
I have started using clangd (with a “d”) for PostgreSQL development. Maybe you want to try it too.
-
PostgreSQL and FIPS mode
PostgreSQL users sometimes ask whether PostgreSQL supports FIPS mode, whatever that might actually mean. “FIPS mode” is a thing provided by OpenSSL that, well, makes it more secure and prevents the use of old encryption methods, is I suppose a rough way to describe it? It has some interesting effects on PostgreSQL, which uses OpenSSL for various cryptographic purposes.
-
SQL identity columns
Autoincrementing columns, identity columns, serial columns, whatever you call it, this is clearly a popular feature in SQL databases, but with a bunch of different syntaxes. At least historically.
-
Commitfest throughput
I just finished a stint as commitfest manager for the September (2023-09) commitfest of the PostgreSQL project. After the conclusion, I of course looked at the statistics and saw that 68 patches had been committed, which I felt was low. But then I looked a bit into the past and noticed an interesting pattern: Since the beginning of the current five-commitfest system, the September commitfest almost always has the lowest number of commits.
-
Grouping digits in SQL
PostgreSQL 16 was released last week. This is the story of a feature.
-
PostgreSQL make install times revisited
We continue our exploration of PostgreSQL build system performance. A long time ago, I wrote an article about how to optimize the performance of
make install. This was quite helpful, as it reduced the time from 10.493 s by default to 1.654 s with some tweaks (6x faster). Now, with different hardware, a much newer PostgreSQL, and a new build system looming, let’s take another look. -
All kinds of licenses
After the recent news that HashiCorp has changed the licenses of its hitherto-open-source products, I thought it would be a good time to take a look at the licenses that have sprung up around PostgreSQL and adjacent and related communities, since quite a bit has changed there recently, and it’s hard to keep track.
-
Ccache and PostgreSQL build directories
We have talked before about how ccache affects build times of PostgreSQL. Now I was wondering how different build directory layouts affect ccache. I was never a user of separate build directories in the make build system (“vpath builds”), so this never concerned me much. But now with Meson this is required.
-
PostgreSQL compile times: Meson review
In a recent article, I compared the compilation times of PostgreSQL using different compilers. In the comments, I was asked for numbers for the new Meson build machinery. Let’s do that now.
-
Overview of ICU collation settings, part 2
In a recent article, I covered the parametric ICU collation customization settings. In today’s article, I will describe the other major collation customization option: the alternative collation types selected with the
cokey in the locale identifier. -
SQL:2023 is out
The news today is that SQL:2023, the new version of the SQL standard, has been published by ISO.
-
PostgreSQL compile times
What’s the fastest compiler for PostgreSQL? Let’s take a look.
-
Overview of ICU collation settings
ICU use is becoming more prominent in PostgreSQL. One of the benefits that ICU offers is a lot of customization options for collations. Some of these are given as examples in the PostgreSQL documentation, but I have always found it hard to get complete and easily-accessible information about this.
-
How to submit a patch by email, 2023 edition
In 2009, I wrote a blog post How to submit a patch by email, which became popular at the time and also ended up in the PostgreSQL wiki. That article was written pre-Git and pre-cfbot, so maybe it’s time for a refresher, as we head into the next PostgreSQL development cycle.
-
CREATE commands in PostgreSQL releases
Here is a fun little view on the progress of PostgreSQL. Consider the number of “CREATE SOMETHING” commands each release contains. As more features are added over time, more such CREATE commands are added.
-
PostgreSQL and SQL:2023
In a previous article, I introduced what is new in SQL:2023. Now I want to show the status of SQL:2023 support in PostgreSQL. See the previous article for details on these features.
-
How collation of punctuation and whitespace works
In a previous article, I described how collation works internally in PostgreSQL in general. In that article, we left open how collation of punctuation and whitespace works. This is where a lot of users get confused. Let’s look at that now.
-
SQL:2023 is finished: Here is what's new
SQL:2023 has been wrapped. The final text has been submitted by the working group to ISO Central Secretariat, and it’s now up to the ISO gods when it will be published. Based on past experience, it could be between a few weeks and a few months.
-
How collation works
In this blog post, and probably one or more following ones, I want to discuss how collations in PostgreSQL work internally. See also this previous post about the work we have done for collations in PostgreSQL 15. And there is even more coming together in PostgreSQL 16 right now, which we will talk about in the future.
-
Precompiled headers in PostgreSQL
As some readers might be aware, in PostgreSQL, we have been working on adding a new build system using Meson. The new Meson build system has support for precompiled headers. I wanted to find out how useful that is.
-
PostgreSQL supply chain
If you are an application developer and you build on PostgreSQL, then maybe you have looked into where PostgreSQL comes from, who develops it, and where you can get professional help if needed.
Now, if you are a PostgreSQL developer (hi!), do you know what you are building on, where those things come from, who develops them, and where you get get professional help if needed?
-
PostgreSQL largest commits
I like to poke around the PostgreSQL Git repository to find interesting statistics and trends that affect PostgreSQL development. The question I had lately is, what are the largest patches that have been committed? That might indicate some kind of upper bound on the size of features you can get committed in one piece. If something is larger, you might need to split it up.
-
PostgreSQL hidden gems
There is a PostgreSQL major release every year, and every release has about 200 changes listed in the release notes. A handful of those are typically listed at the top as “major items”, which are highlighted for the benefit of the public.
-
PostgreSQL commit times
PostgreSQL development has certainly professionalized over the years, in the sense that more people do it as a job now rather than (only) as a hobby. I was wondering whether that would be visible in the time of day when people do publicly visible PostgreSQL work.
-
git range-diff
Let’s say you are following a patch in the PostgreSQL commit fest. The author has posted “v5” of a patch, some feedback has been sent, the author comes back with “v6” and a note saying that they have incorporated all that feedback. How do you check what actually changed?
-
PostgreSQL 15 statistics
I have been gathering some statistics about each major PostgreSQL release. Here is the update for this year:
-
PostgreSQL system catalogs schema diagrams
Some time ago, someone asked on a PostgreSQL mailing list whether there was a diagram of the PostgreSQL system catalogs. There wasn’t at the time. Something like that used to be included in the PostgreSQL documentation, but it was never updated, and so it was eventually removed.
-
ICU features in PostgreSQL 15
One of the new features in PostgreSQL 15 is that you can use ICU collations on a database and instance level.
-
git diff and git log and dots
A little while ago, we had a few PostgreSQL hackers in a room and someone oversaw me typing something like
-
git rebase and ORIG_HEAD
I seem to be doing this a lot:
-
Using GNU GLOBAL with PostgreSQL
When you are coding in a source tree as big as PostgreSQL’s, you will at some point want to look into some kind of source code indexing. It’s often convenient not to bother, since
git grepis actually superfast. But when you want to find where a function is defined among all the call sites, some more intelligence is useful. -
Some git log tweaks
Here are some tweaks to
git logthat I have found useful. It might depend on the workflow of individual projects how applicable this is. -
Check your pg_dump compression levels
I was idly wondering what was taking
pg_dumpso long and noticed that it always seemed to be pegged at 100% CPU usage on the client. That was surprising because naively one might think that the bottleneck are the server’s or the client’s disk or the network. Profiling quickly revealed that the compression library zlib was taking most of the run time on the client. And indeed, turning compression off causedpg_dumpto fly without getting anywhere near 100% CPU. -
Have problems with PostgreSQL? Try using Hive!
So I had this PostgreSQL database that was getting a bit too big, and since it was really only for analytics, I figured it would be a good fit for putting in Hadoop+Hive instead.
-
Storing URIs in PostgreSQL
About two months ago, this happened:
-
Retrieving PgBouncer statistics via dblink
PgBouncer has a virtual database called
pgbouncer. If you connect to that you can run special SQL-like commands, for example -
The history of replication in PostgreSQL
2001: PostgreSQL 7.1: write-ahead log
-
Listing screen sessions on login
There is a lot of helpful information about
screenout there, but I haven’t found anything about this. I don’t want to “forget” any screen sessions, so I’d like to be notified when I log into a box and there are screens running for me. Obviously, there isscreen -ls, but it needs to be wrapped in a bit logic so that it doesn’t annoy when there is noscreenrunning or even installed. -
Directing output to multiple files with zsh
Normally, this doesn’t work as one might naively expect:
-
ccache and clang, part 3
In part 1 and part 2 I investigated how to use
ccachewithclang. That was more than three years ago. -
Checking whitespace with Git
-
First post
This is my new blog. My old blog was here, but it was time to move on.
subscribe via RSS