Some interesting PostgreSQL-related links which have come my way this week:
- Wikipedia has a overview of different relational database systems in table form. It's not particularly detailed but provides a useful "at-a-glance" comparision:
http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems - "Datamining Apache Logs with PostgreSQL": Article by Robert Bernier at OnLAMP.com:
http://www.onlamp.com/pub/a/onlamp/2005/03/03/pg_datamining.html - Benchw Results for PostgreSQL, Firebird and MySQL:
http://benchw.sourceforge.net/benchw_results_open3.html
The latest version of DBD::Pg, the PostgreSQL DBI driver, has been
released and provides some useful new functionality,
including support for server-side prepares,
SQLSTATE code retrieval, and a
last_insert_id() function. See below for
a brief overview.
PostgreSQL's command line utility psql provides a
settings toggle \timing which displays the time it takes
to run a statement, right down to thousandths of a millisecond.
This is very useful for debugging speed-critical statements,
especially for web-based applications where a few milliseconds either
way can make a noticeable cumulative difference.
Recently I was struggling with a particular statement which
normally executed in less than 10ms, but with the addition of a
single additional condition in the WHERE clause,
execution time went up by a factor of five to around 50ms.
(In human terms that might not sound a lot, "only" 1/20th of a second,
but the app should be able to deliver the entire dynamic web page within
250ms - in that case 40ms is suddenly a very long time).
The usual suspects in this kind of situation - differing query plans,
lack of recent VACUUM ANALYZE etc. - were evidently
not guilty, and no amount of SQL voodoo would make any difference.
Due to a potential security hole recently identified with the
LOAD command, security updates for currently supported
PostgreSQL versions (7.2 to 8.0) have been released.
- Announcement
- Mirrors: http://wwwmaster.postgresql.org/download/mirrors-ftp
- Bittorrent: http://bt.postgresql.org
Privilege-checking weaknesses with the LOAD command
may permit local privilege escalations in all current versions.
Updates are expected on Friday.
Reference: