Dominic: On Fri, Feb 15, 2019 at 3:35 PM Dominic Gua�a <organicchemistry_01@xxxxxxxxxxxx> wrote: > I am new to postgresql and I am creating a c program that can receive request from different users. I want to maximize the performance of postgresql so I intend to just create 1 connection that would service all queries of different users. How do I do this? > Do I create a new connection each time there is a new request like how it goes in MySQL or can I just have one connection that is established in my `main` function? Much more data is needed about your problem. IF you are determined to use a single connection, you can stablish it in main and keep it forever ( although it may acumulate some crud, but there are methods to reset it and you can always reconnect every x minutes ). No point in using a pooler here, if the DB is only for your application or your connection is going to be really busy. OTOH, if your connection is sporadic and you have more programs like yours running against the same db it could help. OTOH, a single connection should give you the fastest request response time IN THE DB, but you need to queue requests on the connection. If the requests for your program can come in paralell, using several connections will give you better troughput. I mean, if you get 10 request 1 ms apart, it may happen that you need only 100ms db time for each with a single connection, and 200ms using 10 connections, the 1st case will have a average response time of nearly (100+199+298..+991)/10 = 545.5 ms if my math is right, the second one 200. Basically, to analyse this you need to state if the "requests" to your C program are paralell. Postgres can do a lot of things in paralell, specially if your server is multicore. If they are, using multiple connections in your program will normally give you better throughput, but you should limit the number of paralell connections to avoid problems on spikes, there are many ways to do this, the simple way is use a connection pooler and build a connection for each requests, which is quite fast with things like a collocated pgbouncer instance, or you can pool in your program, which is just the classic bounded buffer with some syncinc stuff, if you want to squeeze the last microsecond, but I would not recomend that ( anything DB related can normally tolerate the minuscule delay of going to a properly configured pooler, and the poolers give you a lot of things which are difficult to get right, plus some bonus like resetting and recycling connections, and you can easily test your program without it ). That's is why I said more data is needed. If I narrow the problem to "a c program which cannot process request in paralell, going against a dedicated server", I'll say "single connection in main, potentially recycling". If it is something like "C web server with DB server in another machine", a pool will probably be better. Francisco Olarte.
