23

I've a C# client application that need to checks a table on a Postgres db every 15 minutes. The problem is that I need to install this client into more or less 200 client so, for that I need to close the DB connection after the query.

I use .Close() method but, if I check on pg_stat_activity table on Postgres DB, I can see the connection still open in IDLE status. How can I fix that issue? Is it possible to close definitely the connection?

thanks, Andrea

Jon Hanna
  • 110,372
  • 10
  • 146
  • 251
Andrea Girardi
  • 4,337
  • 13
  • 69
  • 98
  • I would like to understand if let it idle status is a problem. I'm just curious! – Ismael Jan 27 '12 at 13:28
  • with a max connection number to 100 and 200 clients, yes, it is :) – Andrea Girardi Jan 27 '12 at 14:15
  • An alternative approach to the same problem is to have the 100-200 clients connect to a single server application you write (perhaps a web application on IIS), and then that becomes the single client of the database. Among other things, it allows you to lock down the postgresql a lot more. With that approach you'd want to undo what I suggest in my answer, because you want pooling to mean that a handful of connections is shared between all of those 100-200 users. – Jon Hanna Jan 27 '12 at 14:34
  • 1
    @Andrea Girardi Yes! I didn't think that! So obvious! Thanks. – Ismael Jan 27 '12 at 18:48
  • @Jon Hanna thanks for the suggestion, really helpful! I'll keep in mind for that project and for the next! – Andrea Girardi Jan 28 '12 at 10:25
  • I think it is worth pointing out that Npgsql also has a MinPoolSize which controls how many connections Npgsql should keep idle at minimum. After some time, Npgsql will start to close idle connections above minpoolsize value. This way, even if you have a burst of connection usage and open 100 or 200 connections, Npgsql won't keep them idle all the time and will eventually start to close them. Unless you change the minpoolsize, of course. I hope it helps. – Francisco Junior Feb 04 '12 at 16:58

1 Answers1

33

Like most ADO.NET providers, Npgsql uses connection pooling by default. When you Close() the NpgsqlConnection object, an internal object representing the actual underlying connection that Npgsql uses goes into a pool to be re-used, saving the overhead of creating another unnecessarily. (See What does "opening a connection" actually mean? for more).

This suits most applications well, as it's common to want to use a connection several times in the space of a second.

It doesn't suit you at all, but if you include the option Pooling=false in your connection string, it will override this default, and Close() will indeed close the actual connection.

Community
  • 1
  • 1
Jon Hanna
  • 110,372
  • 10
  • 146
  • 251