4

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?

13
  • 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 '17 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 '17 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 '17 at 8:57
  • @Vérace OP has addressed the defaults already, otherwise that'd by my first point. – Craig Ringer Nov 15 '17 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 '17 at 9:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.