Groovy with Guice

Despite the fact that this blog named "Software Development" and intended to give information about programming tips and tricks most of the posts are about PostgreSQL, mainly because there were just scarce articles for the latter and heaps of articles in the Internet related to what I usually do. Now it's time to fix it! Let me share with you some knowledge I got creating my own project glueing its pieces with Groovy and Guice and I hope you might find something useful here.

XML way of schemaless PostgreSQL

Since the moment I wrote my previous postgresql-praise-giving post I've been conceiving the idea of writing another one about how to use PostgreSQL in terms of managing data in a schemaless way. First it was just desire to met readers with internal "xml" data type and auxiliary XML-functions, but the latest PostgreSQL release (9.2) with brand new "JSON" feature literally made me mention it as well.

Any developer familiar with typical RDBMS might face the problem of having strict schema which could probably get solved faster using flexible one. Such problems might appear in different cases - a brand new software, like a social game, for instance, which need to have flexibility of storing data in rapidly changing environment; a legacy software, where it costs too much to rewrite software to support strict schema in relational database; even a simple program that's supposed to store data in plain machine-readable format.

[Перевод] Почему я отказался от MongoDB

Не так давно я закончил миграцию одного из наших проектов с MongoDB на PostgreSQL. Я бы хотел поделится почему я принял такое решение.

По правде говоря, мое решение использовать MongoDB нельзя считать хорошо продуманным. Урок усвоен - тщательно исследуйте любую новую технологию, которую вы внедряете в ваш рабочий стек, при этом отлично понимая все её сильные и слабые стороны и убедитесь, что вы кристально честно оцениваете - подходит ли она вашим нуждам или нет, независимо от того как её нахваливают. Ниже не будут описаны обычные жалобы на MongoDB, такие как порча данных, глобальная блокировка записи, конфигурация шардов и тому подобное. В наших сценариях использования MongoDB обманула наши ожидания на более простом уровне.

How to optimize search through 1 Terabyte in PostgreSQL

Today I wanted to share my experience of using a relatively big database. It's an active running database containing near 1 TB of data managed by RDBMS PostgreSQL 9.0. The most significant part of the data is located in only 2 tables, 650 GB and 350 GB respectively. There are lots of queries populating into these tables - nearly 15-20 inserts per second, however there are just few selects per minute. Each row in either table contains up to 50 KB of data. Additionally there is a single cleaning query starting once a day in order to remove oldest data and prevent excessive database growing. Well, let's take a look under the hood.

Magic of window functions in PostgreSQL

Once I had an opportunity to provide a solution generating "everyday" statistic of game service usage by users perfectly presented by the maximum amount of game points each user had every 24 hours. The most convenient way to show that data anyone who concerns is a graphical chart showing variations of gained points within predefined ranges.

Historically the only raw data we had at the moment was the information of current number of points every time user is logged in.
The interesting part was that user does not log in every day and there was no way simple to present points flow for specified period as we wanted as there was likely to be a lot of zero values. The most suitable behaviour for this chart was to show last available value from the preceding days to fill the gaps. The easiest solution involving less coding and giving potential of reusing it in other services was PostgreSQL window functions.

Awaiting of PostgreSQL 9.1 - short features review

Few weeks ago a PostgreSQL team announced the first release candidate of their powerful DBMS. It means that the release is going to be ready in several months. In the published release notes they have written following interesting information:

This release shows PostgreSQL moving beyond the traditional relational-database feature set with new, ground-breaking functionality that is unique to PostgreSQL.

From my point of view this is not just marketing words because every PostgreSQL major release always contained really  significant changes, which gives us high expectations for future releases as well. So, what are real advantages we might get from version 9.1 once it's released?