Thoughts about autonomous transactions

When reading Gianni Ciolli’s wiki page about autonomous transactions this week I spent some time to think about things in more detail.

The idea of an autonomous transaction is actually quite simple:

START TRANSACTION;

some_sql_action;


START AUTONONOUS TRANSACTION;

INSERT INTO log_table VALUES (some_data);

COMMIT AUTONOMOUS TRANSACTION;


some_more_sql_action;

ROLLBACK;

The main advantage here is actually that you can commit the log entry without having to commit the parent transaction. This makes the log entry visible if the parent transaction fails badly. Reporting errors to a table is ways more robust in this case. Technically the idea floating around is to push transactions to a stack doing some push / pop operations as autonomous transactions come and go.

BUT: What should happen in this case?

START TRANSACTION;
SELECT some_data FROM some_table WHERE id = 1 FOR UPDATE;

START AUTONONOUS TRANSACTION;
SELECT some_data FROM some_table WHERE id = 1 FOR UPDATE; -- > problem
COMMIT AUTONOMOUS TRANSACTION;

some_more_sql_action;

ROLLBACK;

If the child transaction is really supposed to be fully independent of its parent this should actually never be possible. The parent transaction locks up a row which is then used by the autonomous transaction. This leaves us with two choices: Either the child transaction is NOT fully independent or you allow users to “kill” themselves by locking up the connection.

No tags Hide

No tags Hide

When being out on the road doing consulting it is sometimes impressing to figure out how little people actually know about how joins work. Especially outer joins seem to be a mystery for most people.

Here is an example which might bring some enlightenment:

test=# CREATE TABLE a (id int4);
CREATE TABLE
test=# CREATE TABLE b (id int4);
CREATE TABLE
test=# INSERT INTO a VALUES (1), (2), (3);
INSERT 0 3
test=# INSERT INTO b VALUES (2), (3), (4);
INSERT 0 3

We just insert a couple of rows and see how things work out.

Let us start with a simple LEFT JOIN:

test=# SELECT * FROM a LEFT JOIN b ON (a.id = b.id);
 id | id 
----+----
  1 |   
  2 |  2
  3 |  3
(3 rows)

The goal here is to get all rows from the left hand side and see which rows match on the right side. The “missing” value is filled with a NULL token.

One of the key misunderstandings is this magical ON-clause:

test=# SELECT * FROM a LEFT JOIN b ON (a.id = b.id AND b.id = 2);
 id | id 
----+----
  1 |   
  2 |  2
  3 |   
(3 rows)

To most people this result is surprising. It is important to notice that the filter is only applied to the right side. So, we get one more NULL value. This is pretty easy to see and comprehend if the usecase is that simple – but, in case of an outer-join orgy it is not that trivial anymore.

The example I have just shown is not what people usually expect. Here is an alternative:

test=# SELECT * FROM a LEFT JOIN b ON (a.id = b.id) WHERE b.id = 2;
 id | id 
----+----
  2 |  2
(1 row)

The WHERE-clause will filter on both columns at the same time. This will actually turn the LEFT JOIN into a useless operation because the WHERE-clause will filter stuff away anyway. NOTE: This is a mistake which can commonly observed; most people don’t get ON and WHERE right when it comes to outer joins.

FULL OUTER joins

FULL OUTER joins are the next bug miracle:

test=# SELECT * FROM a FULL JOIN b ON (a.id = b.id);
 id | id 
----+----
  1 |   
  2 |  2
  3 |  3
    |  4
(4 rows)

We take all rows from the left hand side, take the matches on the right hand side and add NULL values in case no matches are found. So far this is no surprise …

The problem usually starts when we try to make the ON clause a little bit more fancy:

test=# SELECT * FROM a FULL JOIN b ON (a.id = b.id AND b.id = 2);
 id | id 
----+----
  1 |   
  2 |  2
  3 |   
    |  4
    |  3
(5 rows)

Most people would not expect “3” to exist at all on the right hand side. As you can see it is moved to the NULL-part of the join. This is not too intuitive for most people. However, this is perfectly expected behavior.

If we filter in the WHERE clause we will again turn the outer join into a useless operation:

test=# SELECT * FROM a FULL JOIN b ON (a.id = b.id) WHERE b.id = 2;
 id | id 
----+----
  2 |  2
(1 row)

Maybe this will bring some enlightenment to some users out there.

No tags Hide

PostgreSQL is famous for its scalability and especially for its exceptionally good support for transactions. But, not only this makes PostgreSQL fairly unique.

We are currently already experimenting with some customer applications to see if they will work nicely with PostgreSQL 9.1. One of the features which will make PostgreSQL really cool is the fact that we now have writable CTEs.

I think most of us will know this scenario: It is 3 o’clock in the morning. You are dead tired and something just does not want to work. You want to delete data from a table … a lot of data. Are you brave enough to hit commit? You want to, let’s say, 20 mio rows … wonna risk it?

Well, with writable CTEs things will be a little easier.
Here is a demo:

test=# CREATE TABLE t_test AS SELECT * FROM generate_series(1, 10000) AS id;
SELECT 10000

First we create a small demo with some simple data.
Now we do our fancy cleanup. For the sake of security we do it inside a transaction:

test=# BEGIN;
BEGIN

How about analyzing data while it is being deleted? Here is how it works …

test=# WITH x AS ( DELETE FROM t_test RETURNING * ) 
    SELECT min(id), max(id), avg(id), count(*) FROM x;
 min |  max  |          avg          | count 
-----+-------+-----------------------+-------
 1 | 10000 | 5000.5000000000000000 | 10000
(1 row)

We put the DELETE inside a so called WITH-clause. WITH will give us a “virtual table” called “x” which contains the data returned by DELETE. Note, we used DELETE in combination with a  “RETURNING *” … so we essentially tell DELETE to give us the data we are about to kick. As we might want to do it for 20 mio rows or so it is definitely too much for a night-time reading exercise. So, we turn this data coming back from the DELETE into something we can then nicely analyze inside our SELECT part. Given this analysis it is a lot easier to judge if our DELETE has really touched the data we wanted it to touch. It greatly reduces the odds of breaking something late at night.

If we feel safe, we can nicely commit …

test=# COMMIT;
COMMIT

Sometimes people have to delete a lot of data. Common table expressions (CTEs) make this process a lot more reliable and robust.

No tags Hide

There are points in your life when you look into the mirror asking yourself if your model of the world you used to have is really still sustainable …

Again, this is not sane design and it was never intended — nobody knew beforehand for how long this can be sustained. It just ended up a little scary .

Some 5 years ago we fired up some 8.1 database servers which have successfully served XX bio transactions since then. Running a large PostgreSQL database successfully on UNIX over a number of years is actually nothing special. So, why care? Well, this thing is special — and trust me, it was never intended to be …

Imagine you start designing a simple database system for a small company (maybe 10 people). Things should be scalable and simply work for the given purpose. You might want to use some partitioning and maybe do a little trickery with indexing. So far so good … nothing special.

But, sometimes things just don’t work out as expected. In this special case it means: End users started to behave somehow totally differently then expected — and: there is A LOT more growth then ever anticipated. So, given the totally unexpected way people use the database, given the seriously higher growth in both users and amount of data the number of tables / partitions in the PostgreSQL systems went up and up and up and up ….

This kind of growth is especially scary when you take into account that in case of high growth it is usually not too easy to make a serious change in architecture within a small period of time (there is never enough time). The business side is usually occupied with sales, important extensions required by the customers and so on. Given a (meanwhile) complex application it is not too easy to simply introduce some PL/Proxy layer to scale out more easily without investing a lot of time and (hardly available manpower). So, you are actually forced to “tweak” things a little to make sure that you can actually buy some time for a change which can reflect the new needs …

And months go by and you keep tweaking and months go by and at some point you end up with something like this:

db=# \x
Expanded display is on.

db=# SELECT count(*) FROM pg_tables;
-[ RECORD 1 ]--
count | 2574107

I want to point out again that this was never intentional … nobody EVER planned to do put over 2 mio tables in the very same PostgreSQL database on the very same box. This happened definitely WAYS beyond my control — again; it was never intentional and NEVER planned this way. But still, it is impressing to see how well PostgreSQL behaves under really disgusting workloads.

Files, files, files …

Let us think for a moment how PostgreSQL stores tables inside a database instance — they simply go into the same directory unless you use tablespaces:

postgres@db1:/var/lib/postgresql/8.1/main/base/16385$ ls | wc –l
8104310

How can the operating system handle that? Well, first of all you have to adjust some kernel parameters to make this work. Secondly you got to make sure that you filesystem uses a tree down to the file level — otherwise a “seq scan” on the file list will kill you during lookup. Fortunately this was properly set when the system was set up.

Again, this is not sane design and it was never intended — nobody knew beforehand for how long this can be sustained. It just ended up a little scary .

What really impresses me here is that this thing works like a charm. We did not have a single database crash in 5 years. Interestingly the fileystem is playing along well for years as well — no filesystem corruption for 5 years; it simply works like a charm while serving around 20 mio transactions a week. The load on the system is actually fairly mixed — there is a large number of small transactions but also a fair list of real killers (bulk load, creating / removing hundreds or tables at a time and so on).

Why not consolidate into larger tables?

Well, given the large number of tables people would actually think that using larger tables would make more sense. Given the load this is actually not quite true … recall, using a seriously partitioned structure you basically get a “free index pointer” to a chunk of data without actually having to index the data itself. If you want to read a million rows at once it is a lot cheaper to look up the right table containing and read it sequentially than to actually fetch 1 mio rows from an insane amount of data by traversing some insanely large index. A seq scan on the right 1 mio rows is a lot faster than to actually go through a (largely) uncached, giant index.

Tribute to PostgreSQL 8.1

It is seriously impressing how far you can squeeze even an old release such as 8.1 when things get really hard to manage. Given the vast number of improvements which went into PostgreSQL since 8.1 it is fun too imagine how much better and easier to handle a new release might be. Still, this kind of application is definitely a killer … and PostgreSQL can really show its potential.

No tags Hide