Friday 14th August we kicked off the Brighton PostgreSQL Meetup. We had a nice evening with cool people all togheter discussing about PostgreSQL and how we can run effectively the meetup. We decided to have a regular monthly meetup hosted around Brighton, possibly, by companies or any suitable venue. The next meetup will be the 25th of September and this time there will be some PostgreSQL talks. The general interest favours the standby servers and the streaming replication.

There is just one day left and we’ll start the Brighton PostgreSQL Meetup. I invested some resources in this project like and I truly believe it can be a success. I still can’t believe that in just one month 25 people already have shown the interest on being part of the Brighton PostgreSQL Group. And today another nice suprise. I received the new shiny mascot for our group. He’s Marvin, the sea elephant.

After upgrading some clusters to PostgreSQL 9.4.4 I noticed an increase of the database backup. Because the databases are quite large I’m taking the advantage of the parallel export introduced with PostgreSQL 9.3. The parallel dump uses the PostgreSQL’s snapshot export with multiple backends. The functionality requires the dump to be in directory format where a toc file is saved alongside with the compressed exports, one per each table saved by pg_dump.

Named identifiers Writing and debugging the SQL is not simple and a little bit of clarity becomes a massive help. Adding a prefix to the identifiers gives to the SQL developer a great knowledge about the database schema. This naming convention makes clear the difference between tables which are physical entities, and the views which are names for saved SQL statement. Adopting a similar approach for the column names makes the data type immediately recognisable.

After a while I finally started a PostgreSQL Meetup in Brighton. I reckon there are several people interested in PostgreSQL in the UK and I’ve met many of them in the Sussex area. I’m quite new to the Meetup platform so I still need to learn how to use it correctly. However, anybody is absolutely welcome to help to organise a PostgreSQL community in the area. The link is here. http://www.

INSERT The INSERT statement is composed by two elements, the attribute list and the values list. The lists are positional. The formatting should mark clearly the various elements of the query in order to make it simpler to spot the correspondence between the attribute and the value. After the words INSERT INTO there is one space followed by the table’s name After the table’s name there is one carriage return The attributes if present are enclosed between round brackets indented one tab from the word INSERT The attribute’s list indents one tab from the round brackets The attributes indent one tab fro the round brackets and are separated by a carriage return The word VALUES indents at the same level of INSERT and is followed by a carriage return The value’s list is surrounded by round brackets indented one tab from the word VALUE The values indent one tab fro the round brackets and are separated by a carriage return UPDATE The WHERE condition on the update is the same of the SELECT.

WITH statements Because the WITH statement works like having a temporary table, the formatting will follow rules similar to the CREATE TABLE statement. • Between the WITH and the alias there is a tab followed by the word AS and a carriage return • The round brackets surrounding the inner query indent one tab from the alias • The inner query indents one tab from the round brackets Conclusions This coding style is at first sight complex and with some drawback as it can slow down the coding process because of more keystrokes required for the uppercase and the carriage return.

Before starting with the book’s topic I want to explain how to set up an efficient environment and some good practice which can improve the code’s readability and quality. As somebody will notice these methods are completely opposite to the general code style trends. I’ll try to give the motivation for each rule. Anyway, in general because the SQL universe is a strange place this requires strange approach. In order to write and read effectively the SQL the coder should gain the capability to get a mental map between the query’s sections and the underlying logic.

With the revolutionary PostgreSQL 8.0 were introduced a different memory manager, the two queues. This algorithm uses three list of buffer page pointers called cache directory blocks (CDB). The lists T1,T2 are actually pointing buffered pages. B1 lists pages been recently in the shared buffer. The list T1 is used as LRU for the pages loaded from disk. The list T2 is used as LRU list for pages already cached and evicted from the list T1.

I’m not dead yeat. Just hard days. However, I’ll talk at the Skiff in the sunny Brighton about PostgreSQL, the big the fast and the (NOSQL on) ACID. If you are around we should kick in the talk at 19.00 BST. This talk is part of the SANE meetings happening on a monthly basis. More informations about the Skiff there. http://www.theskiff.org/ Here the slides PostgreSQL, the big the fast and the (NOSQL on) Acid from Federico Campoli