5 ms·

Question for anyone who knows a lot about Postgres connection pooling: We want to move to using a connection pooling infrastructure component, but so far, we h

by derefr 5y ago

Question for anyone who knows a lot about Postgres connection pooling: We want to move to using a connection pooling infrastructure component, but so far, we haven't been able to figure out how to do so given our setup. We've got a wacky data model where we have N Postgres schemas, each of which has the same tables (but with different data, obviously.) They're not tenants; more like separate "datasets." Picture something like: each schema is a digraph, with tables like "vertices", "edges", "edge_labels", etc. (Not what we're doing, but it's close.) In theory, we could solve our problem by just sticking all the "vertices" tables together as list-partitions of one mega "vertices" table keyed by dataset_id; but 1. it's kind of incoherent — the data makes no sense when combined together like that, it's only valid when considered in isolation; and 2. it's very operationally convenient for us to manage datasets as separate schemas, re: security, naming, onlining/offlining data, etc. Also, while each user request is only going to interact with a single dataset (schema), a given user might care about a lot of datasets, and make requests about many of them at arbitrary times—so, as far as I can tell, we don't have the sort of stable load per dataset that would allow us to hold separate connection pools per dataset. And, since there's no way to make a schema name into a bind variable, fully-qualifying our queries means generating dynamic SQL strings, which is both expensive on the business layer, and on the SQL query planner, which can't just recognize+unify its plans from the query's history from other schemas. Right now, the way we query the data, is that in our business layer, before each query, we have a middleware that injects a statement like this: SET search_path TO 'name_of_dataset', 'public'; We then run a non-fully-schema-qualified query, and it finds whichever tables that have been made visible to it. Our queries currently don't run in transactions (i.e. we're using JDBC auto-commit on the client side), because they're by-and-large just single (complex) SELECT statements. So the middleware-generated statement above (for now) runs as a separate statement, in a separate single-statement transaction, on the same connection, that runs the query. So per-statement connection-pooling would break everything, as our queries would be being routed to backend connections "primed" with the wrong search_path. And I get the feeling that transaction-level connection pooling won't work for us either (at least for now), because our whole operational problem currently is that our HTTP requests acquire DB connections and then sit on them while other things are processed, depleting the connection pool; and if we turned off JDBC auto-commit, our search_path-injecting middleware (which injects its statement on connection-pool checkout) would just end up starting a DB transaction at the beginning of each of those HTTP requests, where the business layer would then be sitting around with an idle in transaction DB connection, mapping directly to an open pgBouncer backend connection, completely destroying any wins connection-pooling would gain us. We'd be right back where we started. So, in other words, we want/need per-statement pooling; but we need it to allow us to also specify the search path per statement. I've seen that at least pgBouncer has an explicit WONTFIX for this requirement, since in their minds it conflicts with their "can't tell it apart from a regular Postgres session" session-state semantics. Should we bite the bullet and move to putting everything in one schema + list-partitioning + an explicit dataset_id column, so that we can parameterize out the dataset per-query using a bind variable? Or something else?

nezirus 5y ago
Maybe this can help https://github.com/awslabs/pgbouncer-rr-patch https://github.com/awslabs/pgbouncer-rr-patch It is a pgbouncer fork/patch with query rewriting support.
chatmasta 5y ago
We are building a solution for this problem at Splitgraph [0] – it sounds like we could probably help with your use case, assuming this is for analytical (OLAP) data. You can get it to work yourself with our open source code [1], but our (private beta, upcoming public) SaaS will put all your schemas on a more scalable “data delivery network,” which incidentally, happens to be implemented with PgBouncer + rewriting + ephemeral instances. We also have private repositories / fine-grained ACL shipping shortly. In a local engine (just a Postgres DB managed by Splitgraph client to add extra stuff), there is no PgBouncer or batteries-included authN/Z, but we use Foreign Data Wrappers to accomplish the same query resolving. Our goal is for you to have the ability to do everything locally as an individual, with the SaaS becoming useful for “multiplayer” (teams and orgs). On Splitgraph, every dataset – and every version of every dataset – has an address. Think of it like tagged Docker images. The address either points to an immutable “data image” (in which case we can optionally download objects required to resolve a query on-the-fly, although loading up-front is possible too) or to a live data source (in which case we proxy directly to it via FDW translation). This simple idea of _addressable data products_ goes a long way – for example, it means that computing a diff is now as simple as joining across two tables (one with the previous version, one with the new). Please excuse the Frankenstein marketing site – we’re in the midst of redesign / rework of info architecture while we build out our SaaS product. Feel free to reach out if you’ve got questions. And if you have a business case, we have spots available in our private pilot. My email is in my profile – mention HN :) [0] https://www.splitgraph.com/connect https://www.splitgraph.com/connect [1] examples: https://github.com/splitgraph/splitgraph/tree/master/examples https://github.com/splitgraph/splitgraph/tree/master/example...
- derefr 5y ago
  We're already sharding our datasets onto separate PG nodes using FDWs (mostly due to us running PG on GCP, and GCE VMs having inherent vertical scaling limitations on node-local storage, which we rely 100% on for our workloads.) Also, our "datasets" are all live data. They aren't modified by the users querying our API, but they are constantly appended to (and I mean constantly, i.e. every few milliseconds.) For us, PG is in our stack at this point because of its hybrid-OLAP nature: it can do realtime, row-at-a-time ingestion of data without degradation, like an OLTP store / time-series DB; but it can then perform intensive OLAP workloads against that up-to-the-moment data, involving joins, CTEs, partial computed-expression indices, etc. (The use-case for this sort of mixed workload? Think "realtime monitoring/alerting on custom-per-user financial Business-Intelligence queries from a common financial transaction stream." We can't pre-denormalize the data, because each client wants something different. Instead, we need a normalized representation with tons of indices that serves all potential queries equally well, in roughly real time.) For non-realtime analysis of "data at rest" (i.e. data that can be ingested in at-most-hourly batches), we can just use Snowflake. We already do, for logs and other things. To be honest, our fondest dream would be to have a two-tiered DB setup: 1. a cold layer, where we get the SQL semantics of Postgres's querying engine, but where the storage engine is similar to those of scale-out DW services like Snowflake, with elastic per-workload compute warehouse nodes fetching compressed-columnar data from object storage into local per-worker caches (it sounds like this is similar to what you're building?) 2. a hot layer, using traditional Postgres storage, which serves as a sort of writeback cache for the cold layer: • where all INSERTs hit the hot layer and then get async-batched to the cold layer; • where the hot layer keeps its own indices to enable fancy OLAP querying of the hot data; • where those queries see the cold data as "part of" the hot data, in a UNION ALL sense (probably through a FDW); • where those queries will constraint-exclude the cold data (and thus trigger no workload on the cold layer) if the cold data isn't relevant to resolving the query — like PG11 partition constraint-exclusion. AFAIK, you can't currently use a foreign table as a partition of a local parent table, so that'd be the first thing needing to be solved there. The next problem after that would be resolving the need for an AccessExclusiveLock to modify the range constraint on each of the parititons, since 1. the split-point between "historical" and "timely" data would be sliding forward every hour-or-so, but 2. the table would be just saturated with long-running SELECTs, so much so that it would never get a moment to lock itself to make a modification like that. (Really, the magic wand there would be to allow partitions to have multiple parents and for "a partition" — the metadata that contains the list/range/hash constraint — to be a separate DB object from the DB table it refers to, where multiple "partitions" can reference one table-with-the-partition's-data-in-it, as long as the data meets the union of all their constraints. With those abstractions, you could build a new parent table that also references the old child tables through new partition metadata, and then just do an atomic swap of the parent tables' names when you're ready, where old queries would go on using the tables they had already dereferenced to their OIDs, and new queries would start using the new tables. Then queue up an effectively-async DROP TABLE on the old parent table, that would resolve when the DB runs out of old queries locking it open.)

by derefr 5y ago

nezirus 5y ago

Maybe this can help https://github.com/awslabs/pgbouncer-rr-patch https://github.com/awslabs/pgbouncer-rr-patch It is a pgbouncer fork/patch with query rewriting support.

chatmasta 5y ago

We are building a solution for this problem at Splitgraph [0] – it sounds like we could probably help with your use case, assuming this is for analytical (OLAP) data. You can get it to work yourself with our open source code [1], but our (private beta, upcoming public) SaaS will put all your schemas on a more scalable “data delivery network,” which incidentally, happens to be implemented with PgBouncer + rewriting + ephemeral instances. We also have private repositories / fine-grained ACL shipping shortly. In a local engine (just a Postgres DB managed by Splitgraph client to add extra stuff), there is no PgBouncer or batteries-included authN/Z, but we use Foreign Data Wrappers to accomplish the same query resolving. Our goal is for you to have the ability to do everything locally as an individual, with the SaaS becoming useful for “multiplayer” (teams and orgs). On Splitgraph, every dataset – and every version of every dataset – has an address. Think of it like tagged Docker images. The address either points to an immutable “data image” (in which case we can optionally download objects required to resolve a query on-the-fly, although loading up-front is possible too) or to a live data source (in which case we proxy directly to it via FDW translation). This simple idea of _addressable data products_ goes a long way – for example, it means that computing a diff is now as simple as joining across two tables (one with the previous version, one with the new). Please excuse the Frankenstein marketing site – we’re in the midst of redesign / rework of info architecture while we build out our SaaS product. Feel free to reach out if you’ve got questions. And if you have a business case, we have spots available in our private pilot. My email is in my profile – mention HN :) [0] https://www.splitgraph.com/connect https://www.splitgraph.com/connect [1] examples: https://github.com/splitgraph/splitgraph/tree/master/examples https://github.com/splitgraph/splitgraph/tree/master/example...

derefr 5y ago
We're already sharding our datasets onto separate PG nodes using FDWs (mostly due to us running PG on GCP, and GCE VMs having inherent vertical scaling limitations on node-local storage, which we rely 100% on for our workloads.) Also, our "datasets" are all live data. They aren't modified by the users querying our API, but they are constantly appended to (and I mean constantly, i.e. every few milliseconds.) For us, PG is in our stack at this point because of its hybrid-OLAP nature: it can do realtime, row-at-a-time ingestion of data without degradation, like an OLTP store / time-series DB; but it can then perform intensive OLAP workloads against that up-to-the-moment data, involving joins, CTEs, partial computed-expression indices, etc. (The use-case for this sort of mixed workload? Think "realtime monitoring/alerting on custom-per-user financial Business-Intelligence queries from a common financial transaction stream." We can't pre-denormalize the data, because each client wants something different. Instead, we need a normalized representation with tons of indices that serves all potential queries equally well, in roughly real time.) For non-realtime analysis of "data at rest" (i.e. data that can be ingested in at-most-hourly batches), we can just use Snowflake. We already do, for logs and other things. To be honest, our fondest dream would be to have a two-tiered DB setup: 1. a cold layer, where we get the SQL semantics of Postgres's querying engine, but where the storage engine is similar to those of scale-out DW services like Snowflake, with elastic per-workload compute warehouse nodes fetching compressed-columnar data from object storage into local per-worker caches (it sounds like this is similar to what you're building?) 2. a hot layer, using traditional Postgres storage, which serves as a sort of writeback cache for the cold layer: • where all INSERTs hit the hot layer and then get async-batched to the cold layer; • where the hot layer keeps its own indices to enable fancy OLAP querying of the hot data; • where those queries see the cold data as "part of" the hot data, in a UNION ALL sense (probably through a FDW); • where those queries will constraint-exclude the cold data (and thus trigger no workload on the cold layer) if the cold data isn't relevant to resolving the query — like PG11 partition constraint-exclusion. AFAIK, you can't currently use a foreign table as a partition of a local parent table, so that'd be the first thing needing to be solved there. The next problem after that would be resolving the need for an AccessExclusiveLock to modify the range constraint on each of the parititons, since 1. the split-point between "historical" and "timely" data would be sliding forward every hour-or-so, but 2. the table would be just saturated with long-running SELECTs, so much so that it would never get a moment to lock itself to make a modification like that. (Really, the magic wand there would be to allow partitions to have multiple parents and for "a partition" — the metadata that contains the list/range/hash constraint — to be a separate DB object from the DB table it refers to, where multiple "partitions" can reference one table-with-the-partition's-data-in-it, as long as the data meets the union of all their constraints. With those abstractions, you could build a new parent table that also references the old child tables through new partition metadata, and then just do an atomic swap of the parent tables' names when you're ready, where old queries would go on using the tables they had already dereferenced to their OIDs, and new queries would start using the new tables. Then queue up an effectively-async DROP TABLE on the old parent table, that would resolve when the DB runs out of old queries locking it open.)