Skip to content

PGSqlPhriday #006: One Thing You Wish You Knew While Learning PostgreSQL: psql commands

For this month's #PGSQLPhridayGrant Fritchey asks: What is the one thing you wish you knew while you learn PostgreSQL.

My preferred client for PostgreSQL is psql, and while it is very powerful I like a few features a most:

  • \timing
  • \watch

Both are internal commands in psql, \timing is there for a very long time, but got improved at some point. \watch came later, but is also there for a couple years now.


Continue reading "PGSqlPhriday #006: One Thing You Wish You Knew While Learning PostgreSQL: psql commands"

Unterlagen für Tutorial beim Perl-Workshop in Frankfurt/Main

Die Unterlagen für das Tutorial beim Perl-Workshop 2009 in Frankfurt/Main am 25.02.2009 sind unter:

zu finden. Folgende Änderungen wurden gegenüber dem Vortrag vorgenommen:

- ein falscher Variablenbezeichner im "Perl" Teil wurde korrigiert

- Beispiele für Nutzung von SPI in Perl Stored Procedures wurden aufgenommen

Viel Spass mit den Unterlagen

Update: Die Unterlagen aller Vorträge gibt es im Wiki:

PostgreSQL and the Perl Workshop 2009 in Frankfurt (Main)

There will be a workshop "optimize PostgreSQL, combine Perl and PostgreSQL" at the Perl Workshop 2009 on february 25 in Frankfurt/Main in Germany.

More details (in german) are available at:

The full timetable (also mostly in german) for the workshop is available at:

Speed up your Serendipity blog running on PostgreSQL

In the last days i monitored the PostgreSQL logfile on my webserver to find slow queries in the Serendipity blog software. I was hunting another bug in the "events" module so i decided it's woth the additional work and maybe i can tweak some settings to speed up the response time a bit.

Continue reading "Speed up your Serendipity blog running on PostgreSQL"

Performance issues

Last year, did some performance tests with MySQL and PostgreSQL and they found out that MySQL did not scale very well with many concurrent connections.

Now Kris Kennaway did some tests on his own and posted the results to the FreeBSD performance list. The resulting graphs look very similar.

Avoid autovacuum for entire databases

We have a database with production data for a platform and another database with a production database dump for testing queries. We also have a test database but this one has slightly different DDL because it's for developing new features. Now autovacuum is running on this server and keeps vacuuming this test database.
A simple way for stopping autovacuum from even checking this db at all is adding an entry for every table in pg_autovacuum:

INSERT INTO pg_autovacuum
(vacrelid, enabled, vac_base_thresh, vac_scale_factor, anl_base_thresh, anl_scale_factor, vac_cost_delay, vac_cost_limit, freeze_min_age, freeze_max_age)
SELECT oid,false,-1,-1,-1,-1,-1,-1,-1,-1 FROM pg_class WHERE relkind='r';

This adds a stop entry for every table in your database, even for system tables. If you want the autovacuum daemon to just ignore tables from a specific schema, add a "relnamespace=scheme_oid" in the WHERE clause.

some tests from has tested again the performance of PostgreSQL 8.2-dev and Mysql 4.1.20 and Mysql 5.0.20a. Results are nice and shown here.

Best thing: the collapses for requests per seconds if Mysql has a concurrency greater than 7. Oh, and by the way, they don't state the table type for Mysql so we don't know, if it is the fast and forgetfully MyIsam or the better but slower InnoDB type.