Keeping database connection open - good practice?

Question

I'm writing a bit of Python code that watches a certain directory for new files, and inserts new files into a database using the cx_Oracle module. This program will be running as a service. At a given time there could be many files arriving at once, but there may also be periods of up to an hour where no files are received. Regarding good practice: is it bad to keep a database connection open indefinitely? On one hand something tells me that it's not a good idea, but on the other hand there is a lot of overhead in creating a new database object for every file received and closing it afterwards, especially when many files are received at once. Any suggestions on how to approach this would be greatly appreciated.

Answer 1

If you only need one or two connections, I see no harm in keeping them open indefinitely.

With Oracle, creating a new connection is an expensive operation, unlike in some other databases, such as MySQL where it is very cheap to create a new connection. Sometimes it can even take a few seconds to connect which can become a bit of a bottleneck for some applications if they close and open connections too frequently.

An idle connection on Oracle uses a small amount of memory, but aside from that, it doesn't consume any other resources while it sits there idle.

To keep your DBAs happy, you will want to make sure you don't have lots of idle connections left open, but I'd be happy with one or two.