I've always used PgBouncer in session mode, but its performance isn't so good, as PHP scripts that run for a long time end up with a long session connected to the database (even for minutes), without releasing the connection for the other requests. Increased the number of connections in IDLE until reaching the limit of connections.
I tried switching to Transaction mode, but for lack of knowledge, it turned out to be a bad idea.
As I found out the hard way, some PostgreSQL functions require to be in the same session as the previous command, such as lastval(), currval() after an insert.
PHP Code:
$this->db->insert(self::TABLE);
$this->db->insert_id('table', 'pk');
Does CodeIgniter have any method that executes all commands in the same transaction?
