Fuzzy.cz

Not every developer has enough experience with building relational queries, not every performance problem is obvious when writing the SQL query, not every SQL query is executed the expected way, and not every problem manifests itself when performing sketchy testing. One of my job responsibilities at the previous position was supervising the performance of the production PostgreSQL database, and identification of problematic SQL queries. I've seen many "broken" SQL statements so let me present my personal "hit parade" in a series of articles.

If you're a Java developer, maybe you know a concept of a "Java puzzler", maybe even the book Java Puzzlers: Traps, Pitfalls, and Corner Cases from Joshua Bloch. The term "puzzler" denotes a piece of code that does not work in the expected way, thanks to lesser-known features and specialities. But why the puzzlers might not exist in other languages besides Java - e.g. in SQL?

Goal of this project is to develop a simple web-based PIM tool (contact management, tasks, calendar, etc.) with emphasis on data security - this is achieved using pgcrypto contrib package and an encryption scheme with unique key for each user.

If you nedd a simple PHP library to build basic charts, check this project. It was created as part of the pgmon project, namely to visualize collected data, but it may be used separately.

This project is hosted on sourceforge.net, on http://timechart.sf.net (or more precisely at http://apps.sourceforge.net/trac/timechart/).

Monitoring a database usage is a very important part of application lifecycle and tuning - PostgreSQL already provides pg_stats view (and other pg_stat_* views), but this does not provide any information about history and without such context it's not possible to detect suspicious patterns (for example leap increase of number of dead tuples), etc. This project aims to provide a tool to gather, display and analyze such historical data.

This project is hosted on sourceforge.net, on http://pgmonitor.sf.net (or more precisely at http://apps.sourceforge.net/trac/pgmonitor/).

When running a web application on PostreSQL, it's nice to monitor the SQL queries continuously, so that it's possible to detect slow / not well optimized queries as soon as possible. This tool should give you interesting information about each of the suspicious queries.

So you're working on a web application, but you need a flexible way to log and compute statistics. Well, maybe this project will provide the solutiou you're looking for - it's based on PostgreSQL (but it shouldn't be very difficult to port it to a different database engine.

This project is hosted on sourceforge.net, on http://openstats.sf.net (or more precisely at http://apps.sourceforge.net/trac/openstats/).