Some time ago I've presented an article about various ways to store file in a PostgreSQL database, and at the end of the article I've promised a benchmark comparing the ways described. It took me some time, but the benchmark is here ...
Yesterday, I've finally commited a first version of the CryptoPIM project into sf.net repository. This proejct's goal is to develop a simple web-based PIM interface (contact management, tasts, etc.) with all the user data encrypted (using a key unique for each user).
I had some free time finally, so I was able to "stitch" the large photo from this year's PG Dev Day 2009, held in February. So here it is (it's a result of hugin scaled to 25%):
Probably every developer who dug in to web applications at least a little bit, probably met cookies. Regrettably, although the cookies are used to store important values, they often not treated accordingly.
Are you using relational databases but the indexes are closed book for you? You absolutely don't know what the indexes are and how they work, or you are confused by various types of indexes (B-Tree, Hash, Bitmap) and you're not sure about their advantages or disadvantages? Or you just can't find out why a particular index is not used when evaluating a query? Maybe this article will help you to answer these questions ...
In one of the previous articles the logging library was described - in this article we'll look into the library used to analyze the collected data, which basically means aggregation of the data according to various criteria. This library is not called directly from your application, but from the cron scripts provided. Nevertheless basic undertandning to it's "guts" is useful when configuring the analysis process. We'll look at the cron script and it's configuration.
When developing and deploying applications, performance testing is quite important - it allows you to allocate database tables to tablespaces, determine performance limits of the system, etc. For the OpenStats project I wrote a small Java multi-threaded benchmarking tool, reading SQL queries from a file and executing them in a database.
Just as in case of PGMon project, when collecting this kind of data there may be huge amounts of data, but these data go out quite quickly - in most cases it's not necessary to work with data older that 1 month. There are two consequences of the quick growth - decrease of performance and problems with maintenance (backup and removal of old data).
I have nothing against Google Analytics - it's a great project and I'm using it on several sites to monitor overall statistics. But in many cases it's not sufficient and as it is - there are many deficiencies of using it, and it's not possible to fix them as it is a closed solution.
The amount of data collected by PGMon may be quite significant, and it may cause serious problems regarding their processing (the more data the slower queries) and maintenance (collected data become stale quite quickly, and deleting large amounts of data may cause problems in PostgreSQL). Partitioning represents relatively effective solution of these problems.
