Tag: connections
Connection Management in PostgreSQL – reserved_connections
In PostgreSQL 16, a new feature has been added: reserved_connections. What is the idea behind it? Back in the old days, DBA’s and software developers tended to work as a superuser (= postgres user). However, this is not ideal for security reasons. Therefore the PostgreSQL community has worked hard to reduce the necessity of connecting […]
Reserve connections in PostgreSQL 16
Reserve connections for the pg_use_reserved_connections group in PostgreSQL 16 Nathan Bossart implemented a brand-new patch that provides a way to reserve connection slots for non-superusers. The patch was reviewed by Tushar Ahuja and Robert Haas. Committed by Robert Haas. The commit message is: Let’s try reserve connections in our work! Let’s edit the postgresql.conf file […]
pgbouncer: Types of PostgreSQL connection pooling
pgbouncer is one of the most widely used tool for connection pooling. At CYBERTEC, we’ve successfully deployed it in many different situations. It has proven to be reliable as well as useful. Before we dive into different pooling modes and their implications, why do we need a connection pooler in the first place? The reason […]
Pipeline mode for better PostgreSQL performance on slow networks
© Laurenz Albe 2022 It is known that high network latency is bad for database performance. PostgreSQL v14 has introduced “pipeline mode” for the libpq C API, which is particularly useful to get decent performance over high-latency network connections. If you are using a hosted database in “the cloud”, then this article might be interesting […]
TCP keepalive for a better PostgreSQL experience
© Laurenz Albe 2021 (Updated 2023-06-22) If you’ve heard about TCP keepalive but aren’t sure what that is, read on. If you’ve ever been surprised by error messages like: server closed the connection unexpectedly SSL SYSCALL error: EOF detected unexpected EOF on client connection could not receive data from client: Connection reset by peer then […]
Terminating database connections in PostgreSQL
In PostgreSQL, every database connection is a server-side process. This makes PostgreSQL a robust multi-process rather than a multi-threaded solution. However, occasionally people want to terminate database connections. Maybe something has gone wrong, maybe some kind of query is taking too long, or maybe there is a maintenance window approaching. In this blog you will […]
PostgreSQL: Network latency does make a BIG difference
Database performance is truly important. However, when looking at performance in general people only consider the speed of SQL statements and forget the big picture. The questions now are: What is this big picture I am talking about? What is it that can make a real difference? What if not the SQL statements? More often […]
Tuning max_connections in PostgreSQL
© Laurenz Albe 2020 (Updated 2023-02-22) In my daily work, I see many databases with a bad setting for max_connections. There is little advice out there for setting this parameter correctly, even though it is vital for the health of a database. So I decided to write up something. What is max_connections? According to the […]
Discovering less-known PostgreSQL v12 features
By Kaarel Moppel Version 12 of PostgreSQL is not exactly fresh out of the oven, as the first minor release was already announced. However, I think it’s fair to say that this version can be still considered fresh for most users, and surely only a small percentage of users has upgraded. So I think it […]
pgbouncer authentication made easy
© Laurenz Albe 2023 UPDATED August 2023 pgbouncer is the most widely used connection pooler for PostgreSQL. This blog will provide a simple cookbook recipe for how to configure user authentication with pgbouncer. I wrote this cookbook using Fedora Linux and installed pgbouncer using the PGDG Linux RPM packages available from the download site. But […]
Connection pooling intro – PgBouncer and pgpool-II
When doing PostgreSQL consulting the other day, the talk went to the topic of connection pools – namely what approaches and products are commonly used and perform well? The topic is pretty wide in itself but mostly well-known for old-timers. Nevertheless it is worth a small write up on basic concepts and a small comparison […]
max_connections – Performance impacts
It’s commonly known that setting max_connections to an insanely high value is not too good for performance. Generations of system administrators have followed this rule. Just out of curiosity I thought I’d give it a try to see which impact setting max_connections in PostgreSQL really has. UPDATE 2023: See also this more recent blog post […]