On Thursday 13 February 2025 I spent the evening at a PostgreSQL user meetup in Amsterdam. There were three talks, and between them they covered most of the PostgreSQL stack. One was about a social network that grew faster than its database could cope with. One was about a government platform team running PostgreSQL on Kubernetes. The last was about a single query that ran hundreds of millions of times a year.
There were also JetBrains cards going around, offering a free three-month JetBrains AI Pro plan.
Mastodon and PostgreSQL, or: ââŚand then November happened!â
The first talk was Ruud Schildersâ âMastodon and PostgreSQLâ, with the subtitle âor: .. and then November happened!â. His âWho am Iâ slide said he has been a database admin since 1998 (Oracle, PostgreSQL and others), plays pool, collects music and loves self-hosting.
The opening slide: the Mastodon mascot next to the PostgreSQL elephant.
His history with Mastodon started in 2017, when he registered mastodon.nu and installed it on YunoHost, then abandoned it after a few months. In August 2021 he registered mastodon.world and installed it with Docker on a dedicated server at Hetzner. Then November came. By the end of one working day the server had 3,500 users. In the evening it grew even faster, with 1,000 new users in 30 minutes, until things started breaking and he closed registrations.
âIn the evening it even went faster.. 1000 new users in 30 minutes.â
The rest of the talk was a list of bottlenecks and how he got past each one:
- Sidekiq. It handles all of Mastodonâs background tasks, and the default is one process with five threads. The queue went past 350,000. Raising the thread count to 200 brought it down quickly.
- 6 November, another spike. He added extra Sidekiq processes as containers, reaching 800 threads in total. That moved the problem to
max_connectionsin Postgres, and the emergency fix was settingmax_connectionsto 800. It worked, and then e-mail started failing, so mail moved to Mailgun. - 18 November, the next spike. The slide called it âElon-causedâ (the Twitter layoffs). The server went from 30k to 62k users in 12 hours, with up to 4,000 new users an hour. He closed registrations again, added Sidekiq threads and hit new database connection issues. The fix was PgBouncer in Docker with
max_client_conn=2000anddefault_pool_size=500.
Todayâs setup: 44 Sidekiq processes, 1,100 threads, and max_client_conn=10000 to absorb peaks.
The â2023 and onwardsâ slide covered what happened next. He set up backups with borgbackup and Barman. Growth slowed: from 150k to 181k users in 2023, then 181k to 188k in 2024. The causes on the slide were mastodon.social, loss of interest, limiting sign-ups because of spam, and later Bluesky. The result was fewer active users, fewer donations and fewer volunteers. The first big spam wave was solved with hCaptcha. For the second, he limited sign-ups and installed a CSAM scanner.
Then it all happened again. Lemmy, which the slide described as âthe Fediverse version of Reddit (Yes, also uses Postgres)â, became his next project. He installed lemmy.world on 1 June 2023, just as a post about Redditâs API pricing went viral. Lots of users went looking for a general-purpose Lemmy server, there werenât many, and most were having performance problems. The next slide was titled âHistory repeats again..â. He also covered the Fedihosting Foundation, registered as a foundation (stichting) âfor continuity, accountability, share knowledge / resourcesâ, and the other instances he runs, including photofed.world, sharkey.world and bookwyrm.world.
My take: almost every fix here was about connections and concurrency, not query speed. Once you scale the workers, the database connection limit is the next thing to break. PgBouncer is the fix Iâd reach for first, and itâs good to see that order of events confirmed in production.
SDS Store: PostgreSQL as a service at the Dutch police
The second talk had POLITIE branding on every slide. It showed an internal platform called SDS Store. The âTechnology stackâ slide showed GitLab and ArgoCD deploying onto Kubernetes (with Cilium). The platform offers five products: Elasticsearch, Kafka (Strimzi), OpenSearch, PostgreSQL (âCloudnative-pg, Kubernetes hosted Postgresql databaseâ) and S3 buckets.
The SDS Store stack, with CloudNativePG as the PostgreSQL offering.
The talk followed the platformâs story slide by slide:
- Microsegmentation. Network segmentation is configured per application through the platform.
- âOur problemsâ. A monthly stacked bar chart from November 2023 to November 2024, split by product (Kafka, Elasticsearch and others). The total climbs from below 100 to around 450.
- Patching. A central application config listing each productâs supported versions with
upgradeTotargets, so teams get upgrades declaratively. It also had feature toggles such as enabling pgAdmin. - SDS reStore. A restore flow. The Java code validates the PostgreSQL create request (âS3 backup application cannot be emptyâ), and a Herstel applicatie (restore application) form lets users pick the source application, a name for the new cluster, and either the latest backup or a specific backup file.
- The future. A growth projection chart.
- CNPG Manifest. Argo CD showing a live CloudNativePG
Clustermanifest (postgresql.cnpg.io/v1) with pod anti-affinity, node affinity and abarmanObjectStorebackup to S3 with gzip compression and AES256 encryption.
âExpanding CloudnativePGâ: PostGIS, TimescaleDB, system_stats and character sets around the operator.
My take: this is what a good internal database platform looks like. GitOps does the deployment, the operator handles day-2 work, and restore is a form instead of a ticket. Making restore self-service is the part most teams skip, and itâs the part that matters when something goes wrong. I wrote more about the operator itself in CloudNativePG: Production PostgreSQL on Kubernetes.
Database Academy: Working with PostgreSQL for coders
The last talk was Ellert van Koperenâs âDatabase Academy: Working with PostgreSQL for codersâ. It opened with an âAnonymous Quoteâ: âQuery optimization is not rocket science. When you flunk out of query optimization, we make you go build rockets.â
Database Academy: one query, three indexing strategies.
The case study was a single statement against a PARAMETERINFO table:
- According to
pg_stat_statements, it averaged about 8.656 ms per execution. - The table was small (about 13,000 rows), but the query ran 359,513,795 times in one year and caused a considerable share of the systemâs load.
- The query could not be changed.
The original plan was a sequential scan that removed 13,029 rows to return none, with an execution time of 7.337 ms. He first rewrote the OR as an equivalent UNION to make it readable, then worked through three indexing strategies:
- Functional or expression indexes: one B-tree index on
NAMEand one onLOWER(NAME), thenANALYZE. - Partial indexes: the same two indexes, but with
WHERE (FLAGS & x'20'::int4) > 0(and thex'80'flag for the lower-case variant), so they only cover the rows the query can match. - Hash indexes:
USING hash(NAME)andUSING hash(LOWER(NAME)), with the same partial predicates.
From expression indexes to the final plan: a BitmapOr over two index scans.
The âPerformanceâ slide showed a bitmap heap scan with a BitmapOr over parameterinfo_name_idx and parameterinfo_lower_idx1. Planning took 0.101 ms and execution took 0.035 ms, against the original 7.337 ms.
My take: this was the most useful talk of the evening for application developers. When you canât touch the SQL, because itâs generated by an ORM or a vendor product, the index is your only lever. A partial index that matches the queryâs fixed predicates is often the cheapest win available. Multiply a few milliseconds by 360 million calls a year and itâs no longer a small number.
