I have a PostgreSQL 9.2 database using pgBouncer connection pool in a debian server. In that database, regular users performs queries over a few tables and I have a cron process fetching data and inserting in a table (using a pg/plsql function which makes some validations before insert). The problem I have is that when I have a huge load on the cron processes (many inserts), the table get locked and the queries to this table does not respond (or takes a lot of time to respond). Is there any way to set priorities by stored procedure, database user(cron and queries use different database users) or by type (select has higher priority than insert). If there is no way to user priority definition in postgreSQL, is there any workaround? Inserts can wait, but the user queries should not...
The cron process creates and drops a pgbouncer connection per insert. If I use the same connection, the problem is bigger (the queries takes even longer)
Thanks in advance, Claudio