Re: Speed of postgres compared to ms sql, is this

On Sat, 2006-11-11 at 19:15, novnov wrote:
> I agree with what you say. And I'd still be interesting in hearing of first
> hand experience with the speed of the two databases from someone who is
> 'good' at both. The article commentor was obviously not a pgsql expert. I've
> heard recently that pgsql is as fast as mysql, so it seems odd that ms sql
> would be faster than pgsql. The actual test, what was use to benchmark,
> would make a difference of course.
>
> I'm hoping someone with a good handle on both databases has direct
> experience can chime in here.

My experience with comparing the two was several years (and versions)
ago.

We had a quad Xeon- 550MHz with 4 gig ram machine running Win-2K and SQL
Server and a Dual P-III 750MHz with 1.5 gig ram running PostgreSQL 7.2.x
on RH 7.2. The windows machine had a large RAID array with many drives,
the pgsql machine had two Ultra-320 SCSI drives in a linux sw mirror.

Like I said, it was several years ago.

The SQL Server was maintained by a Microsoft certified professional of
some type. I maintained the pgsql box. Both machines were tested
during off hours.

We had an internal client who was taking data from a lotus notes
"database" (I use the term loosely) and creating insert statements to
put them into SQL Server. He was having to truncate at 4k (or is it 8k)
because of the size limit of a single row back then in sql server. The
time to extract and then insert the 40,000 or so records from Notes to
SQL Server was almost 40 minutes exactly. Ran the same test on
postgresql, with only a few changes. Some of these articles were 64k to
256k of data, and were being truncated to 4k/8k for MSSQL. So, we were
inserting noticeable more data into postgresql. Total time to extract /
insert? about 12 minutes.

My pgsql machine still showed itself as being pretty much idle, both in
CPU and I/O usage, so I had him just created a batch sql file, and ran
it on the bare pgsql server directly, no network overhead, no lotus
overhead. Time to insert: 4.0 minutes. Changed it to copy from stdin
format, time to insert: 2.5 minutes.

We also tested several queries against this database, including many
that joined >5 tables, and in every test, the pgsql machine handily beat
the mssql machine, usually by a factor of 2 or more every time.

I'm not saying MSSQL is always slower than pgsql. but I am saying that
given competitive hardware and tuning, pgsql can hold its own.