Elixir-dbvisor/sql Needs a SOTA pool and I want to hear your ideas

Questions / Help
,
1

So the day has come, and due to unforeseen technical issues, then SQL - Brings an extensible SQL parser and sigil to Elixir, confidently write SQL with automatic parameterized queries. - Libraries - Elixir Programming Language Forum will require it’s own pool, we want to aim high, so we’re talking SOTA. I want to hear your ideas, but lets talk requirements so we know what we’re working with.

  • Minimal

  • Ideally no message passing, one or two is not a deal breaker

  • Affinity for prepared queries and scheduler id, all queries have a global unique integer set at compile time

  • Linear

  • Dynamic sizing

1 Like
2

Some thoughts.

  • In order to “warm up” the db connections it is best to quickly re-use connections, instead of round-robin them.
  • Make it possible to cancel long-running queries
  • Check that the workers/connections are not shared between processes
  • It would be nice to have a “pauze” function where you pause workers when you are doing schema maintenance.
  • Ability to have different pools (for multi-tenant)
  • Stats.. #worker-checkout/min, pool usage, etc
1 Like
3

I hope the pool library would be written in Erlang so it is easily usable from both Erlang and Elixir!

We’ve also had a real production need for a pool that has a max lifetime on connections (the underlying reason being having to go through firewalls that has a max connection lifetime of e.g. 1 hour, so we would frequently get errors when checking out a stake connection that was silently dead on the TCP level). I don’t know of any Erlang pool library that supports this.

1 Like
4
  • When you talking warm-up are you referring to conns that has already prepared a query?
  • I don’t believe it’s the pools job to cancel anything, the caller should handle that.
  • This is essential, but not the pools responsibility, a data structure should be used to flag rather a connection is in use or not.
  • This is interesting, why would you want the pool to be responsible for this, I imagine that you would want to leverage the database to lock the tables as there could be clients that connect outside of your application.
  • I don’t believe the pool in it self should be concerned with multi tenancy, as user should be able to create as many independent pools as they see fit.
  • Yes, stats are important, especially for dynamic sizing.

I hope the pool library would be written in Erlang so it is easily usable from both Erlang and Elixir!

Unfortunately, this pool will not be it’s own library to begin with, also it will be in Elixir. I do plan to port most if the code to Erlang but currently there are some issues with some macros we use for unicode set identification which is preventing us from porting must of the code to Erlang.

5

No, database connections internally cache information from previous queries. Reusing that cache is what I meant. BTW, that may also be a reason to drop the connection after a period, because it could lead to excessive memory use.

6

For when you do a live upgrade of your schema. This way you can temporarily let your callers wait until the update is done. We generally don’t take external clients into account.

7

You probably don’t want to return a connection to the pool with a running query in it.

8

Correct, a connection shouldn’t be claimable before a caller has released it and no queries should be running when it’s checkin.

For when you do a live upgrade of your schema. This way you can temporarily let your callers wait until the update is done. We generally don’t take external clients into account.

The most common situation I’ve seen, then there has always been other technical issues that warranted a multiple release cycle. Although sounds like a neat feature but I have a hard time imagining a real world use case.

No, database connections internally cache information from previous queries. Reusing that cache is what I meant. BTW, that may also be a reason to drop the connection after a period, because it could lead to excessive memory use.

Okay, this makes sense. Connections cannot be long lived and need to be refreshed.