On Thursday 02 October 2003 09:10, My Internet wrote:
> Hello,
>
> I am in the process to define if our product can use PostgreSQL.
>
> Do you know what type of application use PostgreSQL, and also what is the
> size of the database for these projects?
Everything from small-business apps (which I use it for), web-based apps,
large-scale scientific data processing, GIS support, substantial parts of the
DNS system...
> Our application has a table with more than 300000 rows. There are complexe
> query with many joins. And we must respect some time constraints. Also the
> application is running 24 hours per day, so the product must be stable,
> with good recovery in case of problems.
People have been saying it's inconvenient to upgrade between versions when
dealing with 100GB+ databases, so if 3million rows is your largest table,
that might put things in perspective.
The query planner generally makes a good job optimising. Three things you
probably want to be aware of though:
- PG is very strict on types - if you have an int2 field compared to an int4
value, you will want to cast the int4 to make sure an index is used.
- Use of the JOIN keyword forces joins in the order you specify them - I
believe this is configurable in 7.4
- Use of IN (...) with many values can be slow. Recommended procedure is to
rewrite using EXISTS, although again I believe the situation is much improved
in 7.4
- count(*) isn't optimised - it has to scan the table.
- you will need to tune the values in the configuration file, they are set
quite small to start with.
Stability is good. If you're feeling cautious, you could use 7.3.4 rather than
the forthcoming 7.4. It's had hot-backup (i.e. no need to stop the db to get
a valid snapshot) for ages. It has a write-ahead-log to replay active
transactions in case of a crash, and a couple of replication options.
Some of its high points I would say are:
- good proedural language support
- many client interfaces (C,C++,Perl,Python,PHP,JDBC,ODBC...)
- good compliance with SQL standards
- very helpful mailing lists
In short, I'd recommend spending a couple of days installing and running some
tests - the beauty of open-source is that it costs you nothing to try it out.
--
Richard Huxton
Archonet Ltd