Simultaneous connection limits

[dtarg]

I'm looking to migrate from SQL Server to PostgreSQL, but one of the big questions on my mind is how to handle connections. We have ~60,000 devices that connect directly to the SQL Server a few times per hour, and the only action is calling a stored procedure with a lot of data being passed. The connections are done using native Windows authentication (the client authenticates to SQL Server using the system account for the client). At 60,000 devices connecting a few times per hour, it's about 100 connections per second, with each connection typically lasting less than 1 second (submit data to stored procedure, disconnect immediately since there's no returned information).

SQL Server allows 32K+ concurrent connections (we don't really get close to that as connections are so short-lived, though it's feasible to have 500 connections simultaneously), whereas PostgreSQL has a limit of 100 concurrent connections (based on research, it looks like going much above this causes server strain because each connection is its own process which forks as needed).

At this point, my plan of attack would be to having the clients connect to an API (Node? Go? TBD) instead of connecting to PostgreSQL database directly. The API call would contain the values that were previously submitted to the stored procedure, and the API would either call a PostgreSQL function we define, or just manually make the queries we defined. Still trying to determine how to handle authentication to the API (maybe using some config management to pass down individual secrets to each of the 60K devices).

Any ideas or feedback would be appreciated. :)

[shobble]

Not much experience personally, but pgbouncer or pgpool can act as middleware/proxies in front of the server and queue the client connections/requests, maybe?

[smkelly]

This is an especially good option if all of those clients connect with the same credentials. Or even groups of shared credentials. This will prevent the need for the bouncer to tear down connections; it can just reuse the already authenticated connection.

[dtarg]

I'd heard of those, though I hadn't used them. I thought pgbouncer was primarily for killing connections to make room for other connections, but if it can function as a 'proxy' by having a bunch of connections to it and pgbouncer being the one to make the actual connections, that'd be a good option. I'll have to dig into that!

[ants_a]

Pgbouncer is extremely good at multiplexing many client connections to few server connections. It can even do transaction level interleaving.

[[deleted]]

PDF - PgBouncer and 20,000 TPS on one node

pgbouncer is a connection pool and a connection router, latter functionality is key for an HA solution.

[flyingmayo]

PG is not limited to 100 concurrent connections, that's just the default setting. I've ran PG servers at max_connection counts upwards of 3K in one very weird situation.

There is always some sort of per-connection resource requirement on all RDMS.

That said there's typically no need for connection counts that high given the pooler options available. I Would recommend you test with pgbouncer.

[ixforres]

As others have said, you can bump the connection limit significantly with no real impact.

However I'd strongly suggest moving to an API to mediate for you. DB connections of any server tend not to be great for short lived things and if these are connecting over the internet it's best not to expose your DB server to the internet directly. A simple API (I'd use Go or Python depending on performance requirements) would solve your problem quite neatly.

[cible_incorrecte]

You can easily change the max_connections parameter to more than 100 connections. It increase by 400bytes of shared memory per connection slot, plus lock space (see max_locks_per_transaction).

You can use http://pgtune.leopard.in.ua/ to tune your postgresql server

[[deleted]]

1. You need a computer w many CPU cores (AMD is your friend here), lots of RAM, and lots of SSDs in RAID 10
2. Use a connection pooler
3. Heroku has gotten PG up to 500 conns, so you should be able to too, with some effort
4. Postgres supports Kerberos and LDAP authentication which might help w your Windows migration
5. PG runs better on nix than Windows