Re: Need help to decide Mysql vs Postgres

> My tests included using aqua studios connection to both databases and
> .asp
> page using odbc connections.

    Performance also depends a lot on the driver.
    For instance, the PHP driver for MySQL is very very fast. It is also very
dumb, as it returns everything as a string and doesn't know about quoting.
    For Python it's the reverse : the MySQL driver is slow and dumb, and the
postgres driver (psycopg 2) is super fast, handles all quoting, and knows
about type conversions, it will automatically convert a Python List into a
postgres Array and do the right thing with quoting, and it works both ways
(ie you select a TEXT[] you get a list of strings all parsed for you). It
knows about all the postgres types (yes even numeric <=> python Decimal)
and you can even add your own types. That's really cool, plus the
developer is a friendly guy.

------------------ in psql :
test=> CREATE TABLE typetests ( id SERIAL PRIMARY KEY, iarray INTEGER[]
NULL, narray NUMERIC[] NULL, tarray TEXT[] NULL,vnum NUMERIC NULL, vint
INTEGER NULL, vtext TEXT NULL) WITHOUT OIDS;
NOTICE:  CREATE TABLE will create implicit sequence "typetests_id_seq" for
serial column "typetests.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"typetests_pkey" for table "typetests"
CREATE TABLE

------------------- in Python :
data = {
    'myiarray' : [1,5,8,6],
    'mytarray' : ['hello','world'],
    'mynarray' : [Decimal("1.23"),Decimal("6.58")],
    'mynum'    : Decimal("66.66"),
    'myint'    : 555,
    'mytext'   :u "This is an Unicode String Портал по изучению иностранных"
}
cursor.execute( """INSERT INTO typetests
(iarray,narray,tarray,vnum,vint,vtext)
    VALUES
(%(myiarray)s,%(mynarray)s,%(mytarray)s,%(mynum)s,%(myint)s,%(mytext)s)""",
data );

------------------ in psql :
test=> SELECT * FROM typetests;
  id |  iarray   |   narray    |    tarray     | vnum  | vint |   vtext
----+-----------+-------------+---------------+-------+------+-----------
  4 | {1,5,8,6} | {1.23,6.58} | {hello,world} | 66.66 |  555 | This is an
Unicode String Портал по изучению иностранных
(1 ligne)

------------------- in Python :

cursor.execute( "SELECT * FROM typetests" )
for row in cursor.fetchall():
    for elem in row:
        print type(elem), elem

------------------- output :

<type 'int'> 4
<type 'list'> [1, 5, 8, 6]
<type 'list'> [Decimal("1.23"), Decimal("6.58")]
<type 'list'> ['hello', 'world']
<class 'decimal.Decimal'> 66.66
<type 'int'> 555
<type 'str'> This is an Unicode String Портал по изучению иностранных

------------------- in Python :

cursor = db.cursor(cursor_factory = psycopg.extras.DictCursor)
cursor.execute( "SELECT * FROM typetests" )
for row in cursor.fetchall():
    for key, value in row.items():
        print key, ":",  type(value), value

------------------- output :

iarray : <type 'list'> [1, 5, 8, 6]
tarray : <type 'list'> ['hello', 'world']
vtext : <type 'str'> This is an Unicode String Портал по изучению
иностранных
id : <type 'int'> 4
vnum : <class 'decimal.Decimal'> 66.66
vint : <type 'int'> 555
narray : <type 'list'> [Decimal("1.23"), Decimal("6.58")]

------------------- Timings :

Time to execute SELECT * FROM typetests and fetch the results, including
type conversions :

Plain query : 0.279 ms / request
Prepared query : 0.252 ms / request

(not that bad ! Pentium-M 1600 MHz laptop with local postgres).

Just doing SELECT id FROM typetests gives 0.1 ms for executing query and
fetching the result.


Who said Postgres was slow on small queries ?