I am evaluating PostgreSQL as an alternative to Oracle. I have a database with 533 Tables which contain up to 250,000 entries.
For a performance comparison, I built the datbase both on Oracle and PostgreSQL.
However, PostgreSQL is much slower, and it doesn't store much in RAM and instead has huge Disk I/O.
My performance tests:
- Insert 50,000 entries into a table with roughly 250 columns
- Select all of them including a join on a different table
- Update a string field appending an 'A'
- Dropping an entry in a referenced table which causes all entries to be deleted (on delete cascade)
My System configuration:
- Windows 7, 2 Xeons à 8 cores, 32GB Ram, 256GB SSD
- PostgresSQL 9.6
- Oracle XE 11
- MariaDB 10.2
Below is the performance I measured (average of 5 runs):
The NoTriggers version ran with an
ALTER TABLE x DISABLE ALL TRIGGERS.
As seen in the Chart, PostgreSQL does not really use the available ram. Looking into the resource monitor, it is indeed using high disk io:
What I have tried so far:
effective_cache_sizeto 16 GB
- Even setting
None of these had any impact on either Disk IO or Memory Usage, nor the Performance.
What am I missing? Is there a flag I missed? Is windows just so bad as a host for PostgreSQL?