Home Original page

postgreSQL successes

Eric Lee Green eric at estinc.com
Wed Jun 28 15:20:26 EDT 2000 
robin.escalation at ACM.org wrote:
> Does anyone have any success stories (or gotchas) related to
> implementing a PostgreSQL database with Python that they'd like to
> share? I am hoping to use it as a back-end to a b2b web site.

Yes. Can't tell you exactly at the moment though (NDA). 

Basic plusses of PostGreSQL: transactions, cursors, stored procedures, joins
with subselects, all mostly work as you'd expect of any SQL database. The same
cannot be said to be true of most other embedded databases out there (e.g.
RAIMA, C-Tree, MySQL, ...). Read performance is good, and the DBMS engine
supports all the usual kinds of locking that you'd expect from an RDBMS. 

Minuses: The page length restriction on size of a record can be the pits, if
you're not careful about your design. If you need BLOBs, you're out of luck
(the Python module supposedly supports the PostGreSQL BLOB type, but it's not
reliable). Insert speed is horrible -- inserting data takes forever and a
half, though PostGreSQL 7.0 now has a bulk import facility that may make it
faster (I haven't had a chance to benchmark it yet). And, finally, the
documentation is cryptic, incomplete, and scattered all over the place... I
had to reference two different manuals in order to figure out how to create
(and destroy) a PL/SQL stored procedure, for example. Lack of explicit support
for replication is a pain too, though it's no worse than any other embedded
database (i.e., a 'modified' field and an explicit procedure can be used to do
it). 

The biggest minus for the back-end of a b2b web site, though, would be the
fact that it is bog-slow at opening a connection. PostGreSQL forks a new
process for each connection, and the new process must do all sorts of things
to establish authentication etc... PHP3's PostGres support gets around this by
creating "persistent" connections that hang around with the Apache process,
but I have no idea if Python's Apache module support is robust enough to
handle this situation. Not to mention that Python's Apache module support is
almost entirely undocumented... it is quite unclear, for example, how to run
your former Python CGI scripts via the PyApache module. 

My basic conclusion is that PostGreSQL makes a fine alternative to current
embedded databases. It has better functionality than the xBase types, and
unless you need massive bulk inserts, the performance is excellent.  On the
other hand, I personally would not use Python with PostGreSQL and Apache for
implementing a b2b web site. Python with ZOPE/Zserver and PostGreSQL, however,
might be reasonable (I have not investigated that yet). 

-- 
Eric Lee Green                         eric at estinc.com
Software Engineer                      Visit our Web page:
Enhanced Software Technologies, Inc.   http://www.estinc.com/
(602) 470-1115 voice                   (602) 470-1116 fax
More information about the Python-list mailing list