I've been to PGConf.dev recently, and one of the talks was about collations.

The whole talk was interesting (to put it mildly), but the thing that stuck with me is that we really shouldn't be using default collation provider (libc with locale based collation), unless it's really needed, because we're wasting performance. But how much of a hit is it?

Figured that I can simply test it.

With PostgreSQL 17 we have three separate collation providers:

• libc – default usually
• icu – handles pretty complex collations related to unicode, with various cool tricks, like proper number sorting
• new, builtin provider. Very limited in functionality (only C and C.UTF-8 collations), but should be fast

Based on some talks decided to test following collation provider/collations:

• libc / C
• libc / C.UTF-8
• libc / en_US.UTF-8
• icu / und (which is basically like libc/C.UTF-8)
• icu / en-US
• builtin / C
• builtin / C.UTF-8

For all of them I will test following cases:

• select specific row, without index, based on equality check of text column – without index
• select subset of 1000 rows, based on ≥/≤ check – without index
• create index on table with the same 1 million rows (unique values, but index definition will not use unique)
• add 2nd million of rows to table that has already 1 million rows inserted, and index created
• select specific row, without index, based on equality check of text column – with index
• select subset of 1000 rows, based on ≥/≤ check – with index

First things first, table creation is:

=$ CREATE TABLE test_locale_speed_libc_en (
    id INT8 generated always AS IDENTITY PRIMARY KEY,
    the_text TEXT COLLATE "en_US"
);
=$ CREATE TABLE test_locale_speed_libc_c (
    id INT8 generated always AS IDENTITY PRIMARY KEY,
    the_text TEXT COLLATE "C"
);
=$ CREATE TABLE test_locale_speed_libc_cutf8 (
    id INT8 generated always AS IDENTITY PRIMARY KEY,
    the_text TEXT COLLATE "C.utf8"
);
=$ CREATE TABLE test_locale_speed_icu_und (
    id INT8 generated always AS IDENTITY PRIMARY KEY,
    the_text TEXT COLLATE "und-x-icu"
);
=$ CREATE TABLE test_locale_speed_icu_en (
    id INT8 generated always AS IDENTITY PRIMARY KEY,
    the_text TEXT COLLATE "en-US-x-icu"
);
=$ CREATE TABLE test_locale_speed_builtin_c (
    id INT8 generated always AS IDENTITY PRIMARY KEY,
    the_text TEXT COLLATE "ucs_basic"
);
=$ CREATE TABLE test_locale_speed_builtin_cutf8 (
    id INT8 generated always AS IDENTITY PRIMARY KEY,
    the_text TEXT COLLATE "pg_c_utf8"
);

Then, I will load en-first-1m.txt file into each of the tables, using:

=$ \copy test_locale_speed_X (the_text) FROM en-first-1m.txt

And then we're off to races. Selecting single row will be made by running test-01.sql.

This test gets 100 rows from the table using equality on text column. There are 100 queries, each selecting single row. To compare I took summarized time across 100 queries, and I repeated whole test 5 times picking the best time. First results:

Second test: test-02.sql runs 10 queries each selecting around 1000 rows using ≥ and ≤ comparisons.

Again, I sum execution time of all 10 queries, and then repeat the whole thing 5 times picking the best time. Results:

Next test: test-03.sql: index creation. Very simple, just drop index if it already exists, and then time how long it takes to create new index on each of the tables. Results:

Next test, adding one more million of rows is a bit tricker, as I still want to repeat it 5 times and get best time, but for each test I will have to clean the db and recreate everything from scratch. This needs slightly more complex test file: test-04.sql. If you'd like to repeat it for yourself, you will also need en-second-2m.txt.bz2 file, with second million of rows.

Results:

And then back to selecting rows. First test-05.sql, which is like test-01.sql but it will work with 2 million rows, and an index. Results:

And then back to selecting rows. First test-06.sql, which is like test-02.sql but it will work with 2 million rows, and an index. Results:

Since all this data can be hard to read a small chart:

It looks that in almost every case libc/C won. In one case it didn't, it was ~ 3.5% slower.

Now, does that say that we should switch all our dbs to use libc/C? Well, not really. For a whole lot of cases C is not sensible. We need ordering that makes sense for us – humans. But for some cases, like identifiers, slugs, anything that doesn't have to strictly conform to order as humans would expect – switching to C collation gives some non trivial performance improvements.

What struck me the most is that builtin C/C.UTF-8 didn't really fare all that well. I don't know why, but especially in seq scan test (which involved checking every single row in table) performance was over 20% worse than libc/C. Otoh, virtually every db I use uses libc/en_US, which proved to be the worst in majority of cases.

Well, whatever the results, I hope you'll find it interesting 🙂