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.
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.
The PostgreSQL memory at first sight looks simple. If compared with the complex structures implemented in the other DBMS to a careless reader could seem rudimentary. However, the memory and in particular the shared buffers implementation is complex and sophisticated. This chapter will dig down deep into the PostgreSQL’s memory.
3.1 The shared buffer The shared buffer is a segment allocated at cluster’s startup. Its size is determined by the GUC parameter shared_buffers and the size can be changed only restarting the cluster.
2.4 The background writer Before the spread checkpoints the only solution to ease down the IO spike caused by the checkpoint was to tweak the background writer. This process were introduced with the revolutionary PostgreSQL 8.0. The writer, as the name suggests, works in the background searching for dirty buffers to write on the data files. The writer works in rounds. When the process awakes scans the shared buffer for dirty buffers.
The cluster in action PostgreSQL delivers his services ensuring the ACID rules are enforced at any time. This chapter will give an outlook of a ``day in the life’' of a PostgreSQL’s cluster. The chapter approach is purposely generic. At this stage is very important to understand the global picture rather the technical details.
After the startup When the cluster completes the startup procedure it starts accepting the connections. When a connection is successful then the postgres main process forks into a new backend process which is assigned to the connection for the connection’s lifetime.
The book is now complete. There’s still a lot to do for reviewing the writing and fixing the bad grammar. Anyway is about 107 pages and I’m pretty satisfied. I’ve worked on this document for 4 months in my spare time and I became more confident with my English during the writing.
So, what’s next? I’ll spend the next couple of weeks reviewing and fixing the book. After that I’ll put the pdf on lulu.
Foreign keys A foreign key is a constraint enforced using the values another table’s field. The classical example is the tables storing the addresses and cities. We can store the addresses with the city field, inline.
Being the city a duplicated value over many addresses, this will cause the table bloat by storing long strings, duplicated many and many times, alongside with the the address. Defining a table with the cities and referencing the city id in the addresses table will result in a smaller row size.
I’ve started the sixth chapter, the one on the data integrity I’ve forgotten. There are the first two parts alongside with the introduction. I’ve also updated the book on slideshare with the new cover and the last incomplete chapter for the developers. The beautiful cover is made by Chiaretta & Bon. Kudos and many thanks. I’ve also uploaded the latex sources on github for anybody to fork and review my crappy english.
