vscode-postgres
vscode-postgres copied to clipboard
Maintain active connection rather than connecting each time to enable a more "interactive" mode
Currently Database::runQuery
creates a new connection to the database every single time. This means you are starting a fresh session every time you run something which means that you can't change properties in the session and run some queries, maybe change some more session properties, run some more queries etc. This forces you to always run a set of commands to prepare your session each time rather than a more "interactive" mode.
A solution to this would be to actually maintain a set of open connections per document URI and simply handle graceful detection of closed connections and reopen them, if necessary, behind the scenes. It would also remove the overhead associated with having to establish a new connection each and every time a query is run which, when you're trying to iterate rapidly, adds up over time.
I can understand the benefit of a continuous session, and the overhead it takes to establish the new connections.
During the initial design I gave some thought to this but rejected it for a couple of reasons.
-
I often worked in a mixed environment, and as I worked if I would leave my tab open and it's connection, and then in another client tried to do something - I would often receive an error that another connection was preventing me from doing it. Then it was matter of trying to hunt down the window/tab that had the active connection and terminating it.
-
Memory usage. Keeping a connection open keeps memory allocated. Running postgres/docker/test app/vscode all on the same machine uses a lot of memory (I routinely hit 25-30 GB of used memory), and each connection causes both postgres and the vscode to hold more memory.