7 ms·

Postgres' inability to handle multiple concurrent queries on a single connection makes it a pain when using it for multi-tenant services. With all the SAAS comp

• andrewstuart 8y ago
  Why not just open another connection?
  • chatmasta 8y ago
    Each open postgres connection requires about 10mb of memory overhead IIRC
    • grumpydba 8y ago
      That's why you need a connection pool. That's true for all database systems.
      • cbsmith 8y ago
        Not all databases systems require a connection pool, though it is often a good idea.
        • grumpydba 8y ago
          I disagree. I've faced incidents on oracle/sql server/postgres/sybase instances when several hundreds of connections where spawned. You definitely need to think about pooling fro the beginning of the design of your application.
          • cbsmith 8y ago
            Yes, all of those databases allocate query memory for queries at connection time, and consequently don't scale efficiently for large number of idle clients. That's not all databases. Some don't do that. Some don't even do connections period. Connection pools are a necessary work around for a specific design decision with the system software. They can be helpful other problems as well, but they aren't necessarily a requirement.
• doh 8y ago
  I'm not sure it's that critical. You can use connection pooling on client and/or you can outsource it to a proxy/bouncer that will do this for you. We have tens of thousands of connections and no problems. We also forked pgbouncer to use multicore [0] which allows us properly utilize servers. [0] https://github.com/Pexeso/pgbouncer-smp https://github.com/Pexeso/pgbouncer-smp
  • ahachete 8y ago
    That's really cool! But why don't you contribute back this code to pgbouncer? :)
    • doh 8y ago
      Oh, we tried. Believe me, we tried many times. Original pgb creator didn't bother to respond to any of my emails.
  • thdxr 8y ago
    PGBouncer doesn't do anything to magically prevent connections to postgres from being locked and idle for a pending query. It's just an external connection pooler when your native language driver doesn't have a good one
    • doh 8y ago
      pgb can timeout connections and close them if they are idle for too long, which is usually the way to deal with stale connections. Not sure what solution are you imagining would be the right one.
      • thdxr 8y ago
        Examine any other distributed system that implements pipelining. Allows for multiple pending requests on a connection (simply by having IDs on every request) which makes for efficient use of connections.
        • 8y ago
• zbentley 8y ago
  That's because connections (sessions, really, but in practice most things map those 1:1) are the primitives on which Postgres and most other RDBMSes divide their units of transactional consistency--one of their key guarantees. Connections are expensive to create and maintain largely (but not entirely) because of those properties.
  • deleted 8y ago
    [deleted]
  • thdxr 8y ago
    This isn't a required design to achieve transactional consistency. See for example how this would be done in Erlang - single connection dispatching to isolated Erlang processes. Fundamentally just needs isolation to be done at a different layer.