PostgreSQL DB performance issues with thousands of connections and distributed transactions

Question

We are trying to evaluate PostgreSQL DB as an alternative to Oracle database in our application. We use PostgreSQL 9.5 which is installed on a Linux machine with 128 GBs of memory, 32 CPU cores and SSD storage. Connection pools and distributed transactions are managed by JBoss 7 application server, SQL queries are generated/executed by Hibernate 4. Most tables have tens millions rows, one of them has hundreds millions rows. In total around 3,000 database connections (they are pooled by the application server) are active and used concurrently. We modified some queries, created indexes for slow ones, tuned DB and OS settings based on documentation, etc. However, throughput is few times slower and eventually DB response time increases 10-20 times.

I've done some googling and I couldn't find information about anyone else (ab)using PostgreSQL DB the same way:

• using thousands of active database connections
• using this high number of distributed transactions (PREPARED TRANSACTIONS)
• storing billions of rows in one table

Oracle doesn't have any problem handling even higher load. I would appreciate sharing your experience, suggestions, links, etc.

Thanks

Answer 1

The solution was upgrading the Linux kernel and decreasing the number of DB connections in our Java connection pools from 3000 to 300. After this change we could handle the same traffic as we could with Oracle DB.

By accident I have discovered a precious piece of information which lead to problem resolution in comments section for the post Did I Say 32 Cores? How about 64? written by Robert Haas (VP, Chief Architect, Database Server @ EnterpriseDB, PostgreSQL Major Contributor and Committer):

No, I'm saying that to get good performance on a 64-core server, you're going to need PostgreSQL >= 9.2 and Linux >= 3.2. Most of the changes are actually on the PostgreSQL side, but the lseek scaling stuff in the Linux kernel was important, too.