![\"database](\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2026\/10\/database-connections.webp\")
<\/a><\/p>\nThis blog is for the PG developer<\/h3>\n
Good news! The SQL standard is shared across both engines; that should help you get up to speed quickly. However, there are differences, and there aren\u2019t always equivalents. To help fast-track your learning, this series will highlight some differences worth knowing. Although some may surprise you, I believe most will be a delight.<\/p>\n
PostgreSQL connections<\/h2>\n
Let’s start with the prerequisite of prerequisites:<\/strong> connecting to the database<\/em>. A user or system attempting to interact with any database must first connect.<\/p>\n This C# snippet makes it look easy to create a connection to a PostgreSQL database. It is! But there’s a lot you’ve had to pay attention to, too. Let’s talk through the high-level concerns you’ve had to manage.<\/p>\n In PostgreSQL, developers always have connection management on their minds. This is because PostgreSQL uses one operating-system process per connection, making connections relatively expensive.<\/p>\n PostgreSQL connections are dedicated backend processes with private memory and execution state, plus OS scheduling and context-switching overhead. It’s a lot, and there’s a practical ceiling on the number of connections a server can handle before performance starts degrading.<\/p>\n Processor + Memory + Overhead = Connection Cost<\/strong><\/p><\/blockquote>\n This isn’t just theory. PostgreSQL’s own documentation says the default A long-lived PostgreSQL connection isn’t necessarily a problem, but a lot of them can become a problem quickly. Idle connections still consume resources, while idle transactions trigger bigger concerns.<\/p>\n You’ve probably used You might also deploy PgBouncer, the popular PostgreSQL connection pooler, to reduce the number of physical connections reaching the server.<\/p>\n You will certainly already know commands like these for ongoing management of problematic connections:<\/p>\n <\/i>All of this is really a matter of constant vigilance<\/strong><\/p>All of this is really a matter of constant vigilance to ensure connections are properly managed and idle transactions do not accumulate. You can fire-and-forget your databases, but as reliability and scale start to dominate the conversation, you will pay far more attention to connection and transaction management.<\/div><\/p>\n We also know that PostgreSQL connection limits are cluster-wide, not database-level. Each connection corresponds to a separate backend process, and the server’s finite capacity matters<\/strong>. When you move a database, the connection calculus must account for all the other databases on that server.<\/p>\n <\/i>Think about that for a moment<\/strong><\/p>The typical PostgreSQL default is 100 concurrent connections, and those connections are shared across its databases. PostgreSQL lets you raise that limit, of course, but raising it increases resource requirements.<\/p>\n Spoiler alert: <\/strong>SQL’s connection limit is more than 32 thousand<\/em>!<\/div><\/p>\n Unfortunately, app developers designing apps and agents must account for the cost and limitations of PostgreSQL connections in their architecture and code. Generally, this translates to limiting concurrent connections and keeping transactions short.<\/p>\n Consequently, PostgreSQL’s connection model dictates quite a bit of how developers must design and manage their applications, particularly in terms of connection handling and transaction management. When your app needs to break out of the box, you often must introduce additional strategies that can increase maintenance costs.<\/p>\nusing Npgsql;\r\n\r\nusing var connection = new NpgsqlConnection(connectionString);\r\nconnection.Open();\r\n\r\n\/\/ Use the connection\r\n<\/code><\/pre>\nUnfortunately, you already know the caveat<\/h2>\n
1) PostgreSQL connections are expensive<\/h3>\n
max_connections<\/code> is typically only 100. Increasing it also causes PostgreSQL to allocate more resources, including shared memory. PostgreSQL is designed around the idea that connections are a resource you manage carefully.<\/p>\n2) PostgreSQL connections must be managed<\/h3>\n
pg_stat_activity<\/code> for this. It’s a built-in PostgreSQL system view used to monitor sessions, their state, current queries, and transaction duration to identify connections that are active<\/code>, idle<\/code>, or, most importantly, idle in transaction<\/code>.<\/p>\nSELECT *\r\nFROM pg_stat_activity;\r\n<\/code><\/pre>\n\n
pg_cancel_backend()<\/code><\/li>\npg_terminate_backend()<\/code><\/li>\n<\/ul>\nPreventing connection exhaustion<\/h3>\n
\nPostgreSQL behavior forces developer behavior<\/h3>\n
Great news! You\u2019re using SQL now.<\/h2>\n