2

OGR (GDAL) provides a vector driver for "PostgreSQL" and data in the DB can be accessed with something like the following:

driver = ogr.GetDriverByName("PostgreSQL")
datasource = driver.Open("PG:host=localhost port=5432 user=postgres password=postgres")
layer = datasource.GetLayerByName("geo_table_name")
print(f"{layer.GetName()} has {layer.GetFeatureCount()} feature(s)")

This approach abstracts and obscures the database connection, and as a result we do not appear to have control over connection pooling.

I intend to use OGR PostgreSQL in an API and there could potentially be significant variation in the number of postgres datasource "connections", varying by load. I would like to know if there is an established pattern for managing database connections in OGR, if I am required to implement something myself, or if the idea itself is fundamentally flawed in some way.

Can OGR PostgreSQL connections come from a connection pool, or is there an established approach for ensuring the number of connection attempts does not grow unsustainably under load?

Improve this question
4
  • 1
    MapServer seems to use its own connection pool with OGR connections mapserver.gis.umn.edu/tr/input/vector/…. – user30184 Mar 31 at 6:08
  • 1
    Have you considered pooling on the DB side (pg_bouncer)? – JGH Mar 31 at 12:13
  • @JGH it's a good suggestion but I cannot guarantee I will have control over DB configuration. The API is being designed to accommodate a collection of different data sources and that could include any number of external Postgres databases – tomfumb Mar 31 at 16:20
  • @user30184 thanks, though this appears to be a slightly different model to typical database connection pooling. I'll have to review the source to be sure, but based on the documentation I expect this to retain exactly one ogr.DataSource per data source and there is no mention of how concurrent data requests would be handled. DB connection pooling would typically grow the pool of connections as required by concurrent demands and, once at a given threshold, force data requests to wait until an existing connection became available – tomfumb Mar 31 at 16:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.