Postgresql versus MySQL insert performance with same indexes I'm trying to compare the performance speed differences for an insert of 100,000 records (7 columns) between Postgresql and MySQL:

I get the following insert query speeds for MySQL:

Test 1 (primary key): 58 seconds 
Test 2 (primary key, index 1) : 52 seconds
Test 3 (primary key, index 1, index 2): 414 seconds


I get the following insert query speeds for Postgresql:

Test 1 (primary key): 56 seconds 
Test 2 (primary key, index 1) : 52 seconds
Test 3 (primary key, index 1, index 2): 60 seconds


Is there a reason why Postgresql doesn't seem to suffer from insert performance when I add more indexes for 100,000 records? Does this have something to do with the type of indexes Postgresql is using compared to MySQL or is it something to do with memory differences.
For MySQL, I would guess that got bogged down in I/O.  Possible scenario:


you are using InnoDB, and
the total data + index size exceeded innodb_buffer_pool_size, and
the extra index in the slow test was very random (such as a UUID)


As long as everything fits in the buffer_pool, InnoDB can run quite rapidly.  When you run out of cache space, there might be a lot of I/O, thereby slowing things down.

Also..., InnoDB caches and delays non-unique index writes.  But eventually the writes must be done.  It is hard to say whether you got to that point.

Other things that may speed up inserts:


Batching, either through LOAD DATA or multi-row INSERTs.  The latter runs about 10 times as fast when inserting 100 rows at a time.
Even single-row INSERTs run a lot faster if you do not COMMIT after each row (such as with autocommit=ON).
Sorting the data according to some index, especially some UNIQUE index.  (This helps with caching.)


Those are just some of the ways your benchmark may have been biased in favor of or against MySQL.  (I cannot speak for Postgres, other than  to say that the PRIMARY KEY is stored quite differently.)

An argument against the benchmark:  The main use for indexes is during SELECT, so why measure INSERTs?  Also, if you need to insert a zillion rows per millisecond, when will the database find time to run SELECTs?

Here are more tips on MySQL high-speed ingestion.  (I do not know which parts would also help Postgres.)

A common flaw in benchmarking is to accidentally set the number of iterations (or rows or whatever) just above or below some critical point.  Perhaps 10K rows would report nice rows/second for both engines; perhaps 1M rows works terrible for both, but 100K is near the "cliff" --  and MySQL fell off the cliff just before Postgres would.