Citus Blog

PgBouncer is probably the most popular connection pooler for Postgres. It is essentially a transparant middleware between clients and the server. However, it is not %100 transparent in practice. There are a few intricacies that should be taken into account when using PgBouncer. One such consideration is that PgBouncer does not support the use of all session variables in transaction pooling mode. This lack of support is one of the reasons that the most commonly used transaction pooling mode is not fully compatible with Postgres. PgBouncer 1.20.0 started supporting two of the most requested session variables and laid ground work to be able to support all session variables in the future. Let’s break this down further.

The Impact of Pooling Mode on Postgres Compatibility

A connection pooler between the client and the server should ideally be completely transparent such that your application doesn’t have to be aware of the presence of a connection pooler. This is not the case with PgBouncer for all the pooling modes.

There are three different connection pooling modes:

• Session
• Transaction
• Statement

The pooling mode determines how the connections in the pool are assigned to the clients. The way this is done may impose some limitations impacting Postgres compatibility.

In session pooling mode, there is a one-to-one mapping between the client and the pooled server connection throughout the lifetime of the client connection.

Hence session pooling mode is the most compatible mode with Postgres. The clients can use session variables which are Postgres parameters whose values can be set per session.

Keep reading

How do you feel when your day doesn’t go as planned? In this episode of the Path To Citus Con, the podcast for developers who love Postgres, guest Derk van Veen joins co-hosts Claire Giordano and Pino de Candia to talk about his journey from Java developer to Postgres specialist.

What makes you feel alive at work? Is it the routine tasks, the predictable outcomes, the stable environment? Or is it the unexpected challenges, the unknown variables, the chaotic situations? If you are like Derk (and I), you thrive on the latter. Maybe you love to jump on tough problems and find beautiful solutions—or maybe you enjoy the thrill of finding the root cause of a slow system or some faulty code. You don't just follow a recipe. You ask questions, explore options, and experiment with different strategies. How do you partition a table? Why do you partition a table? What are the trade-offs of each approach?

Keep reading

Episode 11 of Path To Citus Con—the monthly podcast for developers who love Postgres—is now out. This episode featured guests Jelte Fennema-Nio and Marco Slot who joined us (along with co-hosts Claire Giordano and Pino de Candia) to talk about performance benchmarking, specifically benchmarking databases and Postgres.

The official title of Episode 11 with Jelte and Marco is “My Journey into Performance Benchmarking”.

I love hearing how people find themselves doing what they do, so it was fascinating to hear how they got started. One of the most interesting insights was the impact something like benchmarking can have on your career—even if you don’t particularly enjoy it!

Keep reading

The PGConf.dev CFP closes on Monday, January 15 at 11:59pm PST, so if you want to speak at the inaugural PGConf.dev, submit a proposal!

PGConf.dev is the new PostgreSQL Development Conference, the successor to PGCon, a Postgres contribution-focused conference that took place every year in Ottawa. Pronounced "Pee-gee-conf-dot-dev", the inaugural year of PGConf.dev will take place in beautiful Vancouver, Canada, on May 28-31, 2024—with many of the same conference features that made PGCon so great:

• sessions covering Postgres hacking and contribution
• full-day Unconference geared toward collaboration and impromptu creativity
• opportunities to brainstorm with others interested in Postgres development in the “hallway track”

What type of talks is PGConf.dev looking for? The CFP page for PGConf.dev has more details, but, in short: session proposals on all topics related to contributing to Postgres and how to ensure Postgres continues to be the best open source relational database on the planet.

Keep reading

The latest episode of Path To Citus Con—the monthly podcast for developers who love Postgres—is now out. This episode featured guests Lukas Fittl (founder of pganalyze) and Rob Treat (an early Circonus developer) on the topic “My Journey into Postgres Monitoring” along with co-hosts Claire Giordano and Pino de Candia.

Have you ever asked yourself: “Why is my query so slow?” Or had to figure out which query is slowing things down? Or why your database server is at 90% CPU? According to Lukas, you might find these and many more answers by reviewing your error logs.

If you’re running Postgres on a managed service, what kinds of things do you need to monitor & optimize for? Versus what will your cloud service provider do? There is a discussion on this as well as a segue onto monitoring vs. observability: what’s the difference?

Keep reading

Did you know you can solve most of the puzzles in Advent of Code with SQL? In this episode of the Path To Citus Con podcast for developers who love Postgres, guests Dimitri Fontaine and Vik Fearing join co-hosts Claire Giordano and Pino de Candia to explore whether you can (or should) solve every data problem in SQL.

Learning SQL requires practice, just like any other skill. Dimitri will tell you why being lazy might just be a good reason to learn SQL. And how it can make your life easier and more efficient.

If you’re a fan of relational databases, you might have wondered how to pronounce the acronym SQL. Or maybe you’ve been mispronouncing it all along. So pronunciation is discussed too: Is SQL pronounced “ess-que-ell” or “sequel”? Vik, who is a member of the SQL Standard committee, has the answer to this question.

Keep reading