The infamous life of the Magician

What follows is the synthesis of several years of frustration. Before you start reading please note that the things written in the dark age section do not apply for the high end environments like Oracle. That's mostly because starting an Oracle project without a DBA on board is an interesting and creative way to get bankrupt in few months. Obviously things evolves and maybe in the next decade my Oracle fellows will join me in this miserable situation.

A long time ago... 

In the 1998, long before becoming a DBA, I spent few months working as tourist entertainer. For me it was a life changing job, my nature changed dramatically. I was very shy, afraid of human contact. After that job the real me came out, I discovered the pleasure of interacting with people and the shyness disappeared forever. I remember different aspects of this strange job. I entertained children and adults, I learned the art of balloon twisting, acting on the stage and, of course, how to do magic tricks.

From the common people point of view the magician seems a normal guy capable of doing impossible things. From the magician point of view... what a laugh. Everything is a cheat. When I first learned the tricks that fascinated me during my childhood I couldn't believe that somebody would trust me. Then, with great difficulties I discovered how a fluid movement combined with voice and face expression can lead people to loose their tracks and believe the unbelievable.

Often after the show children came to me asking how learn to do magic. I was vague, I talked about hard work, special attitude and magic powers secretly shared across the magicians. I didn't want to spoil their childhood dreams. What if they discovered that the disappearing foulard was possible because of a cheap fake thumb anybody can buy in a magic shop? The magician knows the secret behind the tricks. He dispense joy and wonder but he can't enjoy the magic anymore because he knows that, after all, there's no such thing like magic.

Here we go again

Fast forward to 2004. I started another life changing  job. I left the software development for good and I joined the elite society of the Oracle DBAs. For me was like discovering a whole new universe. A dimension where the data is almost a living entity and where the DBA can manipulate those data, exposing them to the common people transformed through a fake perception. Again, I started doing magic; This time the knowledge were shared across a restricted group of high skilled persons. The DBA team, the developers which had a solid database background and the designers which built and refined the data structures. In particular the design team acted like a glue between the DBA and the developers sometimes having different point of views. The magic were shared and everybody worked actively in the same direction, pushing the database and the data access to the maximum possibilities.

In this new world the children were the end users. They saw the magic and they believed things were simple and clear like presented. Ignoring the hard work and the carefully designed structures behind their magical interfaces. They didn't asked about the magic accepting it like as a fact of their experience. All worked quite well. 

 The dark age

Year 2015, the era of big data, the full stack developers and the data ignorance. In those days I often feel like Obi Wan Kenobi, longing for the old civilised times. The equation is still there. The magician the extended circle who knows the magic and the children. It's only completely unbalanced. An example will help you to understand better.
Let's consider a developer with a great idea. The developer knows that there is a technology which can build easily and without hassle this idea. Then the project starts from the wrong direction, from the application layer, going down to the logical data layer and finally to the physical layer. This crazy approach is like building a terraced house from the roof. But, ``hey it's just a proof of concept right? We need to have something working to show to the funders''. Nothing wrong with this except that when the money comes in, when the ball starts rolling nobody make this simple question: ``what happens if we are successful?''. If the project is successful, usually the same people which caused the problem of scale (because of scale that we are talking), start a brainstorm for finding new solutions in order to mitigate the issue with short sight. The loop is something like this.

With this approach all the fixes are lasting no more than few months, which incidentally is the horizon of the events given by the agile methodology. This approach have another bad effect. The skyscraper built on the terraced house foundation is patched and changed only on the upper side. The building becomes more unstable every time is ``fixed''. Sooner or later the brainstorm will consider the possibility to get on board somebody who understands the database layer. Compared to ten years ago doing this of job is pretty frustrating. Speaking of data integrity or normalisation is like talking in a foreign language. Even the simplest task of adopting views or functions like a database interface is a game of will, where any idea needs constant and exhausting explanations.
In this environment the magician still does the magic. The difference is that the magician only knows that the magic happens because of his hard work. The extended circle of knowledge that supports the magician is completely missing. All the things happening behind the scenes are hidden and unknown. And the children ignore completely that their perception is just a trick. They truly believe that things are working smooth because of their capabilities.

 

 It sucks being a magician

I'm a DBA and my job is horrible. Quite strange isn't it? I feel like I was an Alcoholics Anonymous trying to get rid of an addiction rather an IT professional. But it's true, my job is horrible. Being a DBA requires dedication and hard work in antisocial hours. I bet anybody wants to spend the entire Sunday night working on databases instead of sleeping or clubbing right? I wouldn't change my job for anything else. Sadly in the last few years the pleasure of my job has become a burden. Ten years ago the hard work was a known fact across sufficient people to build a critical mass, a support group against the children's push back. Today the miserable magician is left alone. Nobody understands what he's doing. Often the proposal for any improvement are hampered. The others see him like a disruption to the general work flow rather than a resource.

 

When you do things right...

...people won't be sure you've done anything at all. Said God to Futurama's Bender. That's true. Unfortunately this is a two edge blade. In the years I've learnt how to do super critical tasks with confidence generating a minimal disruption to the production. In fact most of the time nobody will notice anything. And that's quite bad because in the general perception it will seem that you are a lazy bad tempered ugly jerk. And that's all true for me, except for the laziness. With this particular skill a DBA can fix things under the hood with people barely noticing something happened, except for the things running better. There's nothing wrong in this but maybe a better recognition that things aren't happening by real magic it would help. Maybe it's just time to stop doing magic in the background, put the people in front of their responsibility and let the children grow.

The cluster in action - part 2

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. When the amount of buffers cleaned reaches the value set in bgwriter_lru_maxpages the process sleeps for the time set in bgwriter_delay.

2.5 The autovacuum

The routine vacuuming is an important task to prevent the table bloat and the dreaded XID wraparound failure. If enabled the autovacuum launcher starts one daemon for each relation with enough dead tuples to trigger the conditions set in autovacuum_vacuum_threshold and autovacuum_vacuum_scale_factor. An autovacuum daemon is a normal backend and appears in the view pg_stat_activity. Because the XID wraparound failure is a really serious problem, the autovacuum to prevent wraparound starts even if the autovacuum is turned off.

2.6 The backends

The PostgreSQL backend architecture is the brilliant solution to a nasty problem. How to guarantee the buffers are read only by one session at time and avoid the bottleneck of a long waiting queue. When a backend needs to access a particular tuple, either for read or write, the relation's pages are accessed to find the tuple matching the search criteria. When a buffer is accessed then the backend sets a pin on the buffer which prevents the other backends requiring the same page to wait. As soon as the tuple is found and processed the pin is removed. If the tuple is modified the MVCC enforces the tuple's visibility to the other backends. The process is fine grained and very efficient. Even with an high concurrency rate on the same buffers is very difficult to have the backends entangled.
A backend process is a fork of the main postgres process. It's very important to understand that the backend is not the connection but a server process which interacts with the connection. Usually the backend terminates when the connection disconnects. However, if a client disconnects ungracefully meanwhile a query is running without signalling the backend, the query will continue only to find there's nothing listening on the other side. This is bad for many reasons. First because is consuming a connection slot for nothing. Also the cluster is doing something useless consuming CPU cycles and memory.

Like everything in PostgreSQL the backend architecture is oriented to protect the data and in particular the volatile shared buffer. If for some reasons one of the backend process crashes then the postgres process terminates all the backends in order to prevent the potential shared buffer corruption. The clients should be able to manage this exception resetting the connection.

2.7 Wrap up

The cluster's background activity remains most of the time unnoticed. The users and developers can mostly ignore this aspect of the PostgreSQL architecture leaving the difficult business of understanding the database heartbeat to the DBA, which should have the final word on any potential mistake in the design specs. The next chapters will explore the PostgreSQL's architecture in details, starting with the memory.

Failure is not an option

The failure is not an option. Despite this statement is quite pretentious is also the rule number zero of any decent DBA. The task failure, should this be a simple alter table or an emergency restore, is not acceptable. The database is the core of any application and therefore is the most important element of the infrastructure.


In order to achieve this impossible level, any task should be considered single shot. Everything must run perfectly at the first run like if without rollback plan. However the rollback procedure must be prepared and tested alongside with the main task on the test environment in order to get a smooth transition if the rollback should happen. It's also very important to remember the checklist. This allow the DBA to catch the exact point of any possible failure ensuring a rapid fix when this happens.


Having a plan B gives a multiple approach for the task if something goes wrong. For example, when dealing with normal size databases^* is possible to implement many strategies for the disaster recovery having a similar time needed for the recovery. When the amount of data becomes important this is no longer true. For example a logical restore takes more time than a point in time recovery or a standby failover. In this case the plan A is the physical restore.If this does not work then the logical recovery should be used instead.

 * I consider normal sized any database under the 500 GB

Happy 2015

Finally I finished the first volume of the PostgreSQL book administration.
The book is free to download on slideshare or, if  you want the hard copy there is the button to order on lulu.com.

I'll build an ebook version in the next days and I'll make it available on amazon's kindle and the kobo.

I hope this book will spread the knowledge on PostgreSQL.

Anyway, I've not finished. I've already started a second volume and a manual for the developers willing to learn the noble art of the SQL writing.

Download the book : http://goo.gl/y5CLcg
Buy the paperback: http://goo.gl/EqWtPU 


Happy new PostgreSQL year everybody.

Brand new elephant

So finally PostgreSQL 9.4 has been released. This version introduces a new schema less type, the jsonb which joins the other fantastic features abused and misused by the developers all around the globe.

The DBA improvements in this release is much more interesting and can push seriously the adoption in the enterprises of this DBMS.

Alter system

Finally is possible to change the postgresql.conf settings using the ALTER SYSTEM SET command. The cool part is the values are validated when the command is issued. No more hosed clusters by typos. Kudos!

autovacuum_work_mem

Tuning the maintenance_work_mem, in particular on large instances, is a pain in the ass. If the value is too high multiple autovacuum processes can exaust the server's memory. If too low the vacuum of the indices can take ages. This parameter sets the maintenance work mem just for the autovacuum daemons. Kudos!

Replication slots

The streaming replication is a fantastic feature with a big problem. The master doesn't know which location standby is replaying. The WAL files are recycled on a regular basis, and if the lag becomes too much the SR will loose the connection with the WAL sequence. This requires the double implementation SR/Wal shipping to have a fallback strategy if the slave accumulates lag. The replications slots set an active communication between the slave and the master which does not recycle the wal files unless are no longer required by the slave. Kudos!

Planning time

Now EXPLAIN ANALYZE shows the time spent by the planner to build the execution plan, giving to the performance tuners a better understanding of the query efficiency.

pg_prewarm

This additional module loads the relation's data into the shared buffer after a shutdown. This allows the cluster reaching the efficiency quickly.

huge_pages

This parameter enables or disables the use of huge memory pages on Linux. Turning on this parameter can result in a reduced CPU usage for managing large amount of memory on Linux.

Book done!

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.com free for download and in hardcopy. The latter will have a production/shipping cost of course. I'll also put the mobi version on amazon for kindle distribution. Unfortunately amazon doesn't allow free books. I'll put the minimum price possible 0.99$. Anyway if anybody wants the mobi for free just give me a shout.


I've already started the second volume. This one will cover the PostgreSQL's advanced topics like the memory manager and the HA.


And now there are the final two sections. The restore performance and the dba advice.

Restore performances

When restoring a database, in particular in a disaster recovery scenario, the main goal is to have the data back on line as fast as possible. Usually the data section's restore is fast. If the dump has been taken using the copy statements, which are enabled by default, the reload requires a fraction of the entire restore's time. Taking the advantage of the parallel jobs, available for the custom and directory format, it's still possible to improve the data section's reload.

The other face of the coin is the post-data section. Because the objects in this section are mostly random access operations, the completion can require more time than the data section itself; even if the size of the resulting objects is smaller than the table's data. This happens because the unavoidable sort operations are CPU and memory bound. The parallel restore gives some advantage, but as seen in 10.2 each loop's process is single threaded.

Setting up an emergency postgresql.conf file can speed up the restore, reducing the time up to 40% than the production's configuration. What it follows assumes the production's database is lost and the data is restored from a custom format's backup.

shared_buffers

When reloading the data from the dump, the database performs a so called bulk load operation. The PostgreSQL's memory manager have a subroutine which protects the shared segment from the block eviction caused by IO intensive operations. It's then very likely the ring buffer strategy will be triggered by the restore, sticking the IO in a small 4 MB buffer protecting the rest of the memory. A big shared buffer it can cache the data pages when in production but becomes useless when restoring. A smaller shared buffer, enough to hold the IO from the restore processes will result in more memory available for the backends when processing the post-data section. There's no fixed rule for the sizing. A gross approximation could be 10 MB for each parallel job with a minimum cap of 512 MB.

wal_level

The wal_level parameter sets the level of redo informations stored in the WAL segments. By default is set to minimal, enabling the xlog skip. Having the database in with a standby, or simply using the point in time recovery as alternate backup strategy requires the parameter to be set to archive or hot_standby. If this is the case and you have a PITR or standby to failover, stop reading this book and act immediately. Restoring from a physical backup is several time faster than a logical restore. If you have lost the standby or PITR snapshot then before starting the reload the wal_level must be set to minimal.

fsync

Turning off fsync can improve massively the restore's speed. Having this parameter turned off is not safe, unless the cache is have the backup battery to prevent data loss in case of power failure. However, even without the battery at restore time having the fsync off is not critical. After all the database is lost, what else can happen?

checkpoint_segments, checkpoint_timeout

The checkpoint is a vital event in the database activity. When occurs all the pages not yet written to the data files are synced to disk. This in the restore context is a disturbance. Increasing the checkpoint segments and the timeout to the maximum allowed values will avoid any extra IO. In any case the dirtied blocks will be written on disk when the buffer manager will need to free some space.

autovacuum

There's no point in having vacuumed the tables after a complete reload. Unfortunately autovacuum does not know if a table is being restored. When the limit for the updated tuples is recognised the daemon starts a new process wasting precious CPU cycles. Turning off temporarily the setting will let the backends to stay focused on the main goal. The data restore.

max_connections

Limiting the max connections to number of restore jobs is a good idea. It's ok also giving a slight headroom for one or two connections, just in case there's need to log in and check the database status. This way the available memory can be shared efficiently between the backends.

maintenance_work_memory

This parameter affects the index builds which are stored in the restore's post-data section. Low values will results in the backends sorting on disk and slowing down the entire process. Higher values will keep the index build in memory with great speed gain. The value should be carefully sized keeping in mind the memory available on the system. This value should be reduced by a 20% if the total ram is up to 10 GB and by 10% if bigger. This reduction is needed to consider the memory consumed by the operating system and the other processes. From the remaining ram must be subtracted the shared_buffer's memory. The remaining value must be divided by the expected backends to perform the restore. For example if we have a system with 26GB a shared_buffer of 2 GB and 10 parallel jobs to execute the restore, the maintenance_work_mem is 2.14 GB.

26 - 10% =  23.4
23.4 - 2 = 21.4
21.4 / 10 = 2.14

Ignoring this recommendation can trigger the swap usage resulting in a slower restore process.

Get DBA advice

The database administration is weird. It's very difficult to explain what a DBA does. It's a job where the statement ``failure is not an option" is the rule number zero. A DBA usually works in antisocial hours, with a very limited time window. A DBA holds a lot on the shoulders and that's the reason why those people can seem hostile or living in their own world.
Despite the strange reputation, a database expert is an incredible resource for building up efficient and scalable designs. Nowadays is very simple to set up a PostgreSQL instance. Even with the default configuration parameter the system is so efficient that basically doesn't show any problem when running at low or medium load. If this, at first sight, seems a fantastic feature actually is a really bad thing. Any mistake at design level is hidden and when the problem sooner or later appears is too late.

This final advice is probably the most important of the entire chapter. If you have a DBA don't be shy. Ask for any suggestion, even if the solution seems obvious or if the task is small. The database layer is a completely different universe. A small mistake can result in a very big problem.

Of course if there's no DBA, that's bad. Never sail without a compass. Never start a database project without an expert. A professional to take care of the most important part, the foundations.

If this is the case, ask your employer to hire a PostgreSQL DBA as soon as possible.

Chapter 11 - A couple of things to know before start coding...

 This is almost the entire chapter 11. I'm still writing the final section, I'd like to put into a separate post though. I've also almost finished the restore's performance. After this the book is complete. I will start a review to make it a decent writing before publishing onto lulu.com and amazon kindle.

I'm not sure amazon permits to sell books for free I'll find a solution anyway.

A couple of things to know before start coding...

This chapter is completely different from the rest of the book. It's dedicated to the developers. PostgreSQL is a fantastic infrastructure for building powerful and scalable applications. In order to use al its potential there are some things to consider. In particular if coming from other DBMS there are subtle caveats that can make the difference between a magnificent success or a miserable failure.

SQL is your friend

Recently the rise of the NOSQL approach,has shown more than ever how SQL is a fundamental requirement for managing the data efficiently. All the shortcuts, like the ORMs or the SQL engines implemented over the NOSQL systems, sooner or later will show their limits. Despite the bad reputation around it, the SQL language is very simple to understand. Purposely built with few simple English words is a powerful tool set for accessing, managing or defining the data model. Unfortunately this simplicity have a cost. The language must be parsed and converted into the database structure and sometimes there is a misunderstanding between what the developer wants and what the database understands.
Mastering the SQL is a slow and difficult process and requires some sort of empathy with the DBMS. Asking for advice to the database administrator is a good idea to get introduced in the the database mind. Having a few words with the DBA is a good idea in any case though.

Design comes first

One of the worst mistakes when building an application is to forget about the foundation, the database. With the rise of the ORM this is happening more frequently than it could be expected. Sometimes the database itself is considered as storage, one of the biggest mistake possible.
Forgetting about the database design is like building a skyscraper from the top. If unfortunately the project is successful there are good chances to see it crumbling down instead of making money.
In particular one of the PostgreSQL's features, the MVCC, is completely ignored at design time. In 7.6 it's explained how is implemented and what are the risks when designing a data model. It doesn't matter if the database is simple or the project is small. Nobody knows how successful could be a new idea. Having a robust design will make the project to scale properly.

Clean coding

One of the the first things a developer learns is how to write formatted code. The purpose of having clean code is double. It simplifies the code's reading to the other developers and improves the code management when, for example, is changed months after the writing. In this good practice the SQL seems to be an exception. Is quite common to find long queries written all lowercase, on one single line with keywords used as identifier. Trying just to understand what such query does is a nightmare. What follow is a list of good practice for writing decent SQL and avoid a massive headache to your DBA. If you don't have a DBA see 11.4.

The identifier's name

Any DBMS have its way of managing the identifiers. PostgreSQL transforms the identifier's name in lowercase. This doesn't work very well with the camel case, however it's still possible to mix upper and lower case letters enclosing the identifier name between double quotes. This makes the code difficult to read and to maintain. Using the underscores in the old fashion way it makes things simpler.

Self explaining schema

When a database structure becomes complex is very difficult to say what is what and how it relates with the other objects. A design diagram or a data dictionary can help. But they can be outdated or maybe are not generally accessible. Adopting a simple prefix to add to the relation's name will give an immediate outlook of the object's kind.

Object	Prefix
Table	t_
View	v_
Btree Index	idx_bt_
GiST Index	idx_gst_
GIN Index	idx_gin_
Unique index	u_idx_
Primary key	pk_
Foreign key	fk_
Check	chk_
Unique key	uk_
Type	ty_
Sql function	fn_sql_
PlPgsql function	fn_plpg_
PlPython function	fn_plpy_
PlPerl function	fn_plpr_
Trigger	trg_
rule	rul_

A similar approach can be used for the column names, making the data type immediately recognisable.

Type	Prefix
Character	c_
Character varying	v_
Integer	i_
Text	t_
Bytea	by_
Numeric	n_
Timestamp	ts_
Date	d_
Double precision	dp_
Hstore	hs_
Custom data type	ty_

Query formatting

Having a properly formatted query helps to understand which objects are involved in the data retrieval and the relations between them. Even with a very simple query a careless writing can make it very difficult to understand.

A query like this have many issues .

• using lowercase keywords it makes difficult to spot them
• the wildcard * hides which columns are effectively needed; it retrieves all the columns consuming more bandwidth than required; it prevents the index only scans
• the meaningless aliases like a and b it make difficult to understand which relations are involved.
• writing a statement with no indenting it makes difficult to understand the query's logic.

Despite existence of tools capable to prettify such queries, their usage doesn't solve the root problem. It's better to write immediately decent SQL following those simple rules.

• All SQL keywords should be in upper case
• All the identifiers and kewrords should be grouped by line break and indented at the same level
• In the SELECT list specify all and only the columns required by the query
• Avoid the auto join in order to make clear the relation's logic
• Adopt meaningful aliases

And that's the fixed SQL.

The missing chapter 6 part 1 and two, data integrity

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. My former colleague and friend Craig Barnes already started reviewing the tex files, many thanks for the priceless help.

Here's the github repository url : https://github.com/the4thdoctor/pgdba_books

Data integrity

There's just one thing worse than losing the database. Having the data set full of rubbish. The data integrity has been part of PostgreSQL since the beginning. It offers various levels of strength ensuring the data is clean and consistent. In this chapter we'll have a brief look to the various constraints available. The PostgreSQL's constraints can be grouped in two kind. The table constraints and the column constraints. The table constraints are defined on the table's definition after the field's list. The column constraints appear in the field's definition after the data type. Usually for the primary keys and the unique keys the definition is written as table constraint.
The constraint applies the enforcement to any table's row without exclusion. When creating a table constraint on a fully populated table the data is validated first. Any validation error aborts the constraint creation. However, the foreign keys and check constraints accept the clause NOT VALID. With this clause the database assumes the data is valid and skips the validation. The cration is almost immediate. The new constraint is then enforced only for the new data. When using this option the data must be consistent.

Primary keys

A primary key is the unique row identifier. Having this constraint enforced ensures the row can be addressed directly using the key value. A primary key can be enforced on a single or multi column. The data aspect must be unique with the strictest level. That means the NULL values are not permitted in columns participating to the primary key. When creating a primary key this implicitly adds a new unique index on the affected fields. In order to avoid the exclusive lock on the affected table the unique index can be built before the primary using the CONCURRENTLY clause and then used in the primary key definition as shown in 8.3. Using the primary key is the fastest way to access the table's contents.
There is the primary key definition as table and column constraint.

With the table's constraint definition is possible to specify the constraint name and to have a multi column constraint. When writing a multi column constraint the participating columns should be listed separate by commas.

The most common primary key implementation, and probably the best, is to have a serial column as primary key. A serial field is short for integer NOT NULL which default value is associated to the nextval for an auto generated sequence. Because the sequence have its upper limit to the bigint upper limit, this ensures the data does not wraps in the table's lifetime. In the case the primary key is expected to reach the value of 2,147,483,647 the type of choice should be bigserial rather serial. This will create the primary key's field as bigint which upper limit is 9,223,372,036,854,775,807.

However it's still possible to alter the field later in order to match the new requirements. Because changing the data type requires a complete table's rewrite, any view referencing the affected column will abort the change.

Here's the t_data's type change output with the client message level set to debug3.

Dealing with a big amount of data presents also the problem to have enough space for fitting twice of the original table plus the downtime caused by the exclusive lock on the affected relation. A far better approach is to add a new bigint NULLable column without default value. Setting up a trigger for the inserts will keep in sync the new values with the original primary key. Then an update procedure will set the value for the rows. This should run in small batches to avoid to overfill the pg_xlog directory with long running transactions. When everything is in place the new column could then become NOT NULL and a unique index will finally enforce the uniqueness for the new field.

The primary key can then be dropped and recreated using the new unique index. This is permitted only if there's no foreign key referencing the field. In this case a multi drop and create statement is required. The final cleanup should include the trigger's drop and the old primary key removal. Any view using the old primary key should be rebuilt before the drop.

Unique keys

The unique keys are similar to the primary keys. They enforce the uniqueness using an implicit index but they allow the presence of NULL values. Their usage is for enforcing uniqueness on columns not used as primary key. Similar to the primary key the unique constraints are based on a unique index. In fact there's little difference between the unique index and the unique key except the presence of the latter in the system table pg_constraint.

This week update

For this week I'll not update the blog with the new chapter.
I'm scattered around time and space and I've little time to concentrate on writing.

Anyway I'll take the occasion to stress a little on the source of informations about PostgreSQL.
As someone will surely notice on my blog there's no indication about the postgresql releases nor informations about the critical releases.

The reason is because it's better to refer to the official website.

There're few websites which display the PostgreSQL releases in a foreign language.
Yes, bunch of idiots, I'm talking about you.

If you follow their outdated informations you will miss critical bugs which can harm your standbys or result in a catastrophic data loss exposing your data directory to dogs and pigs.

So this week's lesson is, follow www.postgresql.org for any release and don't trust anything except the official releases.

Pythons elephants and zombie

Sorry for the delay.
I want to thank the brighton and hove python users group for the great evening.

As I promised here's the view to get the dead files on the PGDATA.
Dead files are files not mapped in the pg_class system table.
They can be present if any new file generation is interrupted by a crash recovery where the wal replay rollback the system catalogue operation but not delete the new files.
Not sure if this is a bug.
Anyway this is the code on pastebin.

Please be aware the view is specific for PostgreSQL 9.2. I'll try to build a self adapting version sooner or later.

http://pastebin.com/THzti7gW

As I had some interest in PostgreSQL I've started a simple google group, the sea elephants, for aggregating PostgreSQL users in the Brighton and Hove area.

I know there's a national usergroup (or some sort of it) but in my humble experience, local is better.

Because it's better to start from the little things, like a good beer in a lovely pub.

And yes this is an hint for the first meeting ever.

Upcoming Talk

A quick post to update my upcoming tlalk.

I still having some problems to organise and concentrate, so I decided to cancel my talk in Milton Keynes.

I'll be this Tuesday 9th of July at the skiff in Brighton with a talk on PostgreSQL covering the physical storage, the memory management and the query execution, plus a couple of things.

Details here.
http://brightonpy.org/


I'll publish the slides on slideshare ASAP.

still alive....

I'm not dead.
I've just passed the worst period in my life. Four months, it seems ages.

Anyway, I'm back.
For now I'll do just a quick update then I hope I will start to post with more frequency.

I've submitted my first bug report on PostgreSQL, but id #8192, the default block size. Funny indeed.
The bug, if a bug it is, is quite strange. I noticed on a strange lag on my hot standby machines,  9.1 and 9.2. During a conventional vacuum on the master the slave started lagging in a strange way, the recovery process stuck on a single wal file accumulating lag and lag.
The bug description with the steps to reproduce can be found here. With small databases probably the problem will run silent unnoticed, but I'm not dealing with small databases...

And that's drive us to the next news. I'll do a talk to the upcoming PGDay UK next July the 12th. I will talk about very large database maintenance, how to keep in shape indices and tables with minimal impact on the application layer using a very small maintenance window.

Last but not least July the 9th at the Skiff in the sunny Brighton UK I will do a talk for the Python Brighton and Hove Users Group.
I'm still deciding the title, probably will be some sort of cookbook for developers from a DBA point of view.

A couple of things to know about Postgresql....
(before start coding) 
Sounds good.

The Christmas Shitload Post

Happy Christmas! Believe it or not we are almost done. We’re almost out
of winter!

This post is my present for a brilliant new PostgreSQL year.
You know, Christmas usually means goodness, happiness, joy and
kindness.
And if you’re waiting for this, well, you will be hardly disappointed.
I decided to close the year writing my feelings, about VACUUM FULL, one
of the  PostgreSQL’s key functionality and an interesting thing
I discovered on the utility script shipped with the debian package for controlling
the clusters, pg ctlcluster.

And believe me if I tell you I feel like the Angry Birds, when the bad piggies
stole their eggs.

VACUUM two face

VACUUM is one of the most important functions shipped with PostgreSQL. It’s
main goal is to free space cleaning the dead rows generated during the normal
database activity.

As the conventional VACUUM simply marks as reusable the dead rows,
and freeze the tuples avoiding the s***t to hit the fan, preventing the XID
wraparound failure, this does not shrink the data file.
If you have a large table with many dead rows after the VACUUM you will
see the table stop growing.
If you need to reclaim space on disk the VACUUM FULL is what do you
need as it shrinks down the datafile locking in accessExclusive mode the affected
relation and preventing the read and the write.

During a VACUUM FULL on a massively bloated table I noticed a strange
behaviour.

The free space on the table’s tablespace lessened and then suddendly stopped
meanwhile the free space on the pg default began to lower until the filesystem
runt out of space with the subsequent failure of the task.

I started investigating the problem increasing the client verbosity with the
SET client min messages=’debug’;

I’ll explain what I’ve found with a simple test using a
copy of the pg attribute table.

To watch what’s happening I’ve switched on the debug message level for the
client and I’ve turned on the trace sort parameter during a single column index
creation on the relname field.
This is the entire procedure output.


db_test =# CREATE TABLE t_vacuum AS
SELECT
*
FROM
pg_attribute ;
SELECT 2155
-- repeat this many times in order to
-- fill the table with a good amount of data
db_test =# INSERT INTO t_vacuum
db_test -# SELECT * FROM t_vacuum
db_test -# ;
INSERT 0 2155
SET trace_sort = ’ on ’;
SET client_min_messages = ’ debug ’;
db_test =# CREATE INDEX
idx_attname ON t_vacuum USING btree ( attname );
DEBUG : building index " idx_attname " on table " t_vacuum "
LOG : begin index sort : unique = f , workMem = 16384 , randomAccess = f
LOG : switching to external sort with 59 tapes : CPU 0.04 s /0.48 u sec elapsed 0.53 sec
LOG : performsort starting : CPU 0.04 s /0.72 u sec elapsed 0.77 sec
LOG : finished writing run 1 to tape 0: CPU 0.08 s /2.56 u sec elapsed 2.65 sec
LOG : finished writing final run 2 to tape 1: CPU 0.08 s /2.56 u sec elapsed 2.65 sec
LOG : performsort done ( except 2 - way final merge ): CPU 0.09 s /2.61 u sec elapsed 2.72
LOG : external sort ended , 836 disk blocks used : CPU 0.14 s /2.84 u sec elapsed 3.16 se
CREATE INDEX

As my work mem is small the index sort switch immediately on an external

sort in order to build the index, then generate the btree from the external sort

file.

Now let’s run a VACUUM FULL the same table.

db_test =# VACUUM FULL t_vacuum ;
DEBUG : building index " pg_toast_16411_index " on table " pg_toast_16411 "
LOG : begin index sort : unique = t , workMem = 16384 , randomAccess = f
LOG : begin index sort : unique = f , workMem = 1024 , randomAccess = f
LOG : internal sort ended , 17 KB used : CPU 0.00 s /0.00 u sec elapsed 0.00 sec
LOG : performsort starting : CPU 0.00 s /0.00 u sec elapsed 0.00 sec
LOG : performsort done : CPU 0.00 s /0.00 u sec elapsed 0.00 sec
LOG : internal sort ended , 17 KB used : CPU 0.00 s /0.00 u sec elapsed 0.08 sec
DEBUG : vacuuming " public . t_vacuum "
DEBUG : " t_vacuum " : found 0 removable , 275840 nonremovable row versions in 4756 page
DETAIL : 0 dead row versions cannot be removed yet .
CPU 0.17 s /0.99 u sec elapsed 1.87 sec .
DEBUG : building index " idx_attname " on table " t_vacuum "
LOG : begin index sort : unique = f , workMem = 16384 , randomAccess = f
LOG : switching to external sort with 59 tapes : CPU 0.07 s /0.51 u sec elapsed 0.58 seLOG : finished writing final run 2 to tape 1: CPU 0.14 s /2.58 u sec elapsed 2.73 sec
LOG : performsort done ( except 2 - way final merge ): CPU 0.14 s /2.64 u sec elapsed 2.80
LOG : external sort ended , 836 disk blocks used : CPU 0.21 s /2.85 u sec elapsed 3.27 se
DEBUG : drop auto - cascades to type pg_temp_16402
DEBUG : drop auto - cascades to type pg_temp_16402 []
DEBUG : drop auto - cascades to toast table pg_toast . pg_toast_16411
DEBUG : drop auto - cascades to index pg_toast . pg_toast_16411_index
DEBUG : drop auto - cascades to type pg_toast . pg_toast_16411
VACUUM


The interesting part begins at row 9. The database actually performs the
VACUUM, shrinking down the data file and cleaning the dead rows then at row
13 we found the same index creation output.
This explains well why the VACUUM FULL failed.

As VACUUM FULL acts exactly as CLUSTER,  during the process the
table is read sequentially and the data output sent to a new node file and this
happens on the same tablespace where the original table belongs.

The index build is performed on the same index tablespace except for the
sort on disk which is governed by the temp tablespaces parameter. And if this
is not set then the sort is performed on the pg default.

In conclusion, if you want to run a VACUUM FULL you will need space for the new
file node on the same table’s tablespace. The indexes do require the space on the
same tablespace plus the sort on disk, if you don't have enough memory.

So, if you have space shortage on the pg_default, the command SET temp_tablespaces to a
more spacious tablespace will prevent the pg default to run out of space.

Aggressive shutdown

Working with the hot standby on debian I had a bad experience when I’d set up
a new slave server. After the data copy with the archive command started I ac-
tually started the slave forgetting to set the hot standby parameter to on. After
a while the slave reached the consistent state and I issued the pg ctlcluster com-
mand in order to stop the cluster itself with the –force to have a fast shutdown
instead of a smart one.
The script doesn’t have an option to switch between PostgreSQL’s shutdown
modes but, if without options performs a smart shutdown mode.
Adding the –force option the script first try a fast shutdown mode, if the
database doesn’t shutdown in a reasonable time the script switch to the imme-
diate shutdown, if this is not enough to stop the instance finally a kill -9 on
the postgres process is performed.

WARNING INCOMING AIRSTRIKE!!!!!

If you are too much delicate I suggest you to stop reading, now begins the real
shitload.

I understand the hard efforts done by the global team to create a first class
product, I left Oracle for PostgreSQL and I don’t regret my choice because
PostgreSQL is a damn good database, probably too much good.

But can anyone explain me why, in the name of one of any of the thousands
fake gods invented by the human race, no one put the VACUUM FULL be-
haviour in the on line manual leaving this HUGE lack of documentation on
PostgreSQL’s KEY FEATURE?

Regarding the debian's script, I understand the –force option of pg ctlcluster is not
 to be run in normal condition, but does the author understand what
does it means to perform in normal conditions a smart shutdown?

A user that forget to logout can let the script to run forever.
On the other side if the –force is passed to the script and the database takes too much to
shutdown an immediate shutdown is performed without warnings.

And this will wipe out any bloody unlogged table from the cluster on
the next startup.

Then, as cherry on the top, if the immediate does not work a kill -9 is
performed on the postmaster leaving the shared memory as garbage.

And this works the same way if you use the debian’s init script.


HEAVY MACHINE GUN! (Metal slug’s quote)
So, my last advice, is do not use the wrapper pg ctlcluster for stopping the
clusters created with pg createcluster.
It’s better to shutdown the cluster using the command.

export PGDATA=<PATH TO DATA>
/usr/lib/postgresql/<MAJOR VERSION>/bin/pg ctl stop -m <THEWAYYOUWANT>



HAPPY CHRISTMAS!

Welcome

What is blue, bigger in the inside and with time travel capabilities?
 If your answer is the TARDIS, then, yes you're close but the right one is PostgreSQL.

 Welcome to my little quiet space in the noisy and confusing PostgreSQL universe.

 I've started working seriously with databases in the 2004, before I toyed with small web applications MySQL driven, then my life changed when I was employed by a big consulting company in Florence. I became member of what I call the Oracle dba friar.

I discovered how big and challenging can be working with serious amount of data and how important is to have clear, defined and well written informations, possibly in one single place and not scattered around the time and space.

I left the Oracle world in the 2007 and I created one of the biggest PostgreSQL conference in Europe, if I well think, before me the name PGDay was unknown.

Now I moved from Italy to the UK, actually I'm Italian so please accept my apologies for my english, and I had confirmed my feelings.
The pure  PostgreSQL dba is a rare animal, personally I know only two of them, myself and a clever guy from the east midlands I had the pleasure to work with.

I tried to understand why, despite the wide use by great companies PostgreSQL have so few real dba and so many  people pretend to be.

Imho I think the reason can be the lack of good training on this complex argument.

Yes, you can get training from great companies, directly from the developers, but let me ask you a question.

Should you want to learn how to drive a Formula 1, who do you ask?
The engineer or the driver?

I am the driver.