postgreSQL successes

[Erno Kuusela]

>>>>> "Eric" == Eric Lee Green <eric at estinc.com> writes:

    Eric> Insert speed is horrible -- inserting data takes
    Eric> forever and a half, though PostGreSQL 7.0 now has a bulk
    Eric> import facility that may make it faster (I haven't had a
    Eric> chance to benchmark it yet).

hmm. there are some things that can make inserts slow:

1) there is a switch telling postgresql whether it should flush to
disk after each transaction. this calls the flush() system call. its
implementation is very inefficient on linux.  if you are running on
linux and can live with the reduced robustness, turn that feature off.

2) if you are using sql insert statements without surrounding
begin/commit (ie in autocommit mode), a transaction for each insert
makes things slow.

3) using sql insert statements is not itself the fastest thing int the
world. the pygresql connection objects have a inserttable() method for
doing something similar (i think) to what COPY does with the "psql"
interactive interactive terminal.

try these, i bet you will have much better luck.

    Eric> And, finally, the documentation is cryptic, incomplete, and
    Eric> scattered all over the place... I had to reference two
    Eric> different manuals in order to figure out how to create (and
    Eric> destroy) a PL/SQL stored procedure, for example.

the book on the postgresql web site covers all of thiss stuff
pretty nicely.  <URL:http://www.postgresql.org/docs/awbook.html>.

    Eric> The biggest minus for the back-end of a b2b web site,
    Eric> though, would be the fact that it is bog-slow at opening a
    Eric> connection. PostGreSQL forks a new process for each
    Eric> connection, and the new process must do all sorts of things
    Eric> to establish authentication etc...

well, usually if you do not want to wait for seasons to change
when using the web site you do not use cgi anyway. even without
connecting to a sql rdbms it is dog slow.

    Eric> PHP3's PostGres support gets around this by creating
    Eric> "persistent" connections that hang around with the Apache
    Eric> process, but I have no idea if Python's Apache module
    Eric> support is robust enough to handle this situation.

yeah. i've been using httpdapy for many projects and it works fine.
mod_python (http://www.modpython.org/) is a rewrite of it by
the same author...

zope also works very well.

    Eric> Not to mention that Python's Apache module support is almost
    Eric> entirely undocumented... it is quite unclear, for example,
    Eric> how to run your former Python CGI scripts via the PyApache
    Eric> module.

i don't know about the documentation of PyApache (it's about as slow
as CGI anyway), but the mod_python and zope docs are pretty nice
(httpdapy docs are not totally nonexistant either).

  -- erno