Category “Postgresql” — 78 articles

Denormalizing Tags

In our Tour of Extensions today’s article is about advanced tag indexing. We have a great data collection to play with and our goal today is to be able to quickly find data matching a complex set of tags. So, let’s find out those lastfm tracks that are tagged as blues and rhythm and blues, for instance. In this article, we’re going to play with music related tags

At the Open World Forum two weeks ago I had the pleasure to meet with Colin Charles. We had a nice talk about the current state of both MariaDB and PostgreSQL, and even were both interviewed by the Open World Forum Team. The interview is now available online. Dear French readers, it’s in English. Here’s the video: Executive Summary: MariaDB is a drop-in fully Open Source replacement for MySQL and sees quite some progress and innovation being made, and PostgreSQL is YeSQL!

PostgreSQL is an all round impressive Relational DataBase Management System which implements the SQL standard (see the very useful reference page Comparison of different SQL implementations for details). PostgreSQL also provides with unique solutions in the database market and has been leading innovation for some years now. Still, there’s no support for Autonomous Transactions within the server itself. Let’s have a look at how to easily implement them with PL/Proxy.

The following story is only interesting to read if you like it when bad things happen, or if you don’t have a trustworthy backup policy in place. By trustworthy I mean that each backup you take must be tested with a test recovery job. Only tested backups will prove useful when you need them. So go read our Backup and Restore documentation chapter then learn how to setup Barman for handling physical backups and Point In Time Recovery.

In a previous article about Trigger Parameters we have been using the extension hstore in order to compute some extra field in our records, where the fields used both for the computation and for storing the results were passed in as dynamic parameters. Today we’re going to see another trigger use case for hstore: we are going to record changes made to our tuples.

Sometimes you want to compute values automatically at INSERT time, like for example a duration column out of a start and an end column, both timestamptz. It’s easy enough to do with a BEFORE TRIGGER on your table. What’s more complex is to come up with a parametrized spelling of the trigger, where you can attach the same stored procedure to any table even when the column names are different from one another.

There was SQL before window functions and SQL after window functions: that’s how powerful this tool is. Being that of a deal breaker unfortunately means that it can be quite hard to grasp the feature. This article aims at making it crystal clear so that you can begin using it today and are able to reason about it and recognize cases where you want to be using window functions.

We see a part of the data as if through a little window

Dimitri Fontaine

PostgreSQL Major Contributor

Open Source Software Engineer