Postgres-NIO with Long-Running PostgresClient?

Related Projects SwiftNIO
1

Context:

I'm building a Mac app that needs to interface with a PostgreSQL database. I'd like to use Postgres-NIO. I understand that the library is designed for server-side use, where a request comes in, a connection to the database is opened, work is done, and the connection is then closed.

Question:

My app will obviously have many, many database queries over time. Can I KEEP a PostgresClient object for long stretches of time (hours)? Or must I follow the server pattern where a client is opened, work performed, and then the client is closed until new work is ready?

The code below WORKS, but I'm not sure if I'll hit any downsides by keeping PostgresClient alive for long periods (resource/memory limits, networking limits, etc):

final class DatabaseEngine
{
    static let config = PostgresClient.Configuration(...)   // truncated for brevity
    
    private var client: PostgresClient? = nil 
    private var runTask: Task<Void, Never>? = nil

    // Fire up the client so we can handle queries
    func connect() async throws
    {
        client = PostgresClient(configuration: DatabaseEngine.config)
        let immutableClient = client!
        runTask = Task {
            await immutableClient.run()
        }
    }


    func fetchStuff() async throws
    {
        guard let client else {
            return
        }

        let rows = try await client.query("SELECT * FROM some_table")
        for try await (id, name, creationDate) in rows.decode((UUID, String, Date).self) { 
           ...
        }
    }


    // Stop the client. This likely wouldn't be called until the app is quit, potentially days later.
    func disconnect() async
    {
        runTask?.cancel()
    }
}

Additionally, is PostgresClient, when used this way, going to recover automatically if the user's WiFi momentarily disconnects for a minute? Will the connection re-establish, or must I close the client and reconnect a new one?

2

Without knowing the details of how postgres-nio behaves when run in a macOS app, I see no issues with using it the way you described - it is really not that different from a server-side use case.

It is actually very common and best practice to keep connections (to databases, or anything really) "warm" and simply reuse idle connections for requests. Just as your app, a server would essentially also just keep a PostgresClient around for as long as it runs. The individual connections are managed in a pool under the hood (keeping them open and ready).

The only thing I would look into is the "max pool size" (defaults to 20 afaik for postgres-nio) - that may be a bit too wide and wasteful for a client app. Something in the 1-3 range seems more reasonable.

1 Like
3

Yeah you'd want to store the client as a top level variable somewhere in your application.

What you should probably do is use PostgresConnection instead of the client directly which would then allow you to use the connection pool stuff currently being worked on inside PostgresNIO (and soon to be extracted) to store connections and cycle them as needed (like when the connection dies)

1 Like