0

I'm connecting to a small PostgreSQL database on the local LAN using Java and running some queries and I'm not sure what is best practice.

Should I close the connection after each query or keep the connection open for the next query?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
plisken
  • 67
  • 1
  • 8
  • use Hibernate, JPA, Spring data in this situations – Youcef LAIDANI Nov 18 '19 at 09:30
  • 6
    Use a connection pool –  Nov 18 '19 at 09:32
  • It depends on the context. I've written lots of c#/pg apps, using only the `npgsql` library and no fancy ORMs or pooling etc. In short, creating/closing a new conn every time is resource intensive and takes time (a new pg process has to be created for the new connection, plus the latency of the back and forth between your app and pg). But you don't want to keep conns open unnecessarily else you may run out. So if you're running a bunch of queries one after the other, or doing a high-frequency repetitive task, keep the conn open in between, otherwise dispose and create new one when required. – 404 Nov 18 '19 at 10:12
  • Related: https://stackoverflow.com/questions/1039419/when-to-close-connection-statement-preparedstatement-and-resultset-in-jdbc – Mark Rotteveel Nov 18 '19 at 12:25

1 Answers1

0

you probably want to keep the connection open for as long a possible, but in practice it probably won't matter much unless you're making hundreds of little queries and reconnecting for every one

here's an overview of what's going on, assuming your network has ping times of around 1ms:

  1. making the socket connection, a TCP handshake should take <5ms
  2. your client with present authentication credentials, and the server will check them. this might take a while depending on how you've got things configured but by default it'll take <1ms
  3. your client sends a query, the server will need to populate various internal data-structures and caches (this will take a few milliseconds), the server will then be able to plan and execute the query which will vary according to the query

more details here and here

if you reuse this connection then you save the cost of authentication and catalog loading. you're therefore recommended to reuse connections where possible so this work isn't performed every time. depending on your usage this may or may not actually make much difference. for example:

  • if you're running lots of queries that take a few milliseconds each then it'll certainly be helpful to reuse connections
  • if you're mostly running queries that take many seconds to execute then this cost will be negligible
Sam Mason
  • 15,216
  • 1
  • 41
  • 60
  • 1
    The Java specific solution to use a connection pool instead of trying to keep the connection open yourself. Then you can close a connection as soon as your done with it, which will return it to the pool for reuse. The pooling is transparent to the user. – Mark Rotteveel Nov 18 '19 at 12:19