5

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):

Comparison

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:

PostgreSQL

What I have tried so far:

  • Increasing effective_cache_size to 16 GB
  • Setting autovacuum to off
  • Setting max_connections to 3
  • Setting shared_buffers to 3GB
  • Setting work_mem to 512MB
  • Setting maintenance_work_mem to 64MB
  • Setting synchronous_commit to off
  • Even setting fsync to off

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?

Improve this question
13
  • 1
    Windows is definitely not an ideal host for PostgreSQL, but it should be OK for newer PostgreSQL and Windows versions. Windows 7, however, is a pretty bad choice for a benchmark platform for anything. Why benchmark on an obsolete consumer version of the OS?
    – Craig Ringer
    Nov 15, 2017 at 8:54
  • 2
    If there was a monthly prize for the best written question, this would undoubtedly be a contender (+1)! Not an expert, I understand that PostgreSQL defaults are set for servers with low specs - below yours! Have you looked here? Two small points. 1) please don't use images - they work well in this case, but people can't always read them (e.g. me on bus!) - give formatted text also please! 2) I don't think you're allowed publish Oracle benchmarks (just say well known vendor not from Redmond! ;-) ).
    – Vérace
    Nov 15, 2017 at 8:55
  • @CraigRinger got in before me - PostgreSQL is not Windows native (but then, neither is Oracle, so...). Also, if you fail to receive a satisfactory answer here, try the PostgreSQL-general mailing list - guys who actually write the server code post there, regularly! MfG.
    – Vérace
    Nov 15, 2017 at 8:57
  • @Vérace OP has addressed the defaults already, otherwise that'd by my first point.
    – Craig Ringer
    Nov 15, 2017 at 8:59
  • BTW, do not set autovacuum to off. It might get you a short term performance boost, but at a growing I/O cost over time as your tables and indexes bloat with dead tuples. If I had to make a guess I'd say the FK constraint is likely the issue, PostgreSQL is less than wonderful at bulk FK cascades.
    – Craig Ringer
    Nov 15, 2017 at 9:00

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.