Directory format and default compression

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.

Initially I believed that the latest release introduced a change in the default compression level. However I noticed that using the custom format the resulting file was smaller.

For testing purposes I've created a database with two tables big 700MB each one. Using the custom format with the default compression level they are saved in 442 MB. The directory format stores the same data in 461MB, +4%.

After some research I spotted a recent change introduced in the version 9.4.2.
In pg_dump, fix failure to honor -Z compression level option together with -Fd

With another search, this time on the committers mailing list, I found this commit .

Basically the change  in the 9.4.2 "ignored the fact that gzopen() will treat "-1" in the mode argument as an invalid character", causing the compression level to be set to 1, the minimum.

The fix is already committed and should appear in the next minor release. Until then, if using the directory format, the  workaround is to pass the flag -Z 6 to pg_dump.

The bug has been backpatched to the version 9.1. However I've never noticed this issue until I switched from the 9.3.9 to the 9.4.4.

Nuts and bolts part 4 - naming conventions and editors

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.

Both naming schemas prevent the risk of having reserved keywords used for the identifier's name.

The drawback is if the object type changes. For example if a table becomes a view the name should reflect the change. In an ideal world this shouldn't happen. In the real life building a solid schema design reduces greatly this kind of issues. However, changing the data type, in particular if dealing with big tables, is more complicated than renaming a field and, after all is a DBA problem.

The editor

Unlikely many commercial RDBMS PostgreSQL, ships only with the command line client psql. There is a good quantity of third party clients with good support for the database features and a good connectivity layer. An exhaustive list of those clients can be found on the PostgreSQL wiki
https://wiki.postgresql.org/wiki/Community_Guide_to_PostgreSQL_GUI_Tools. Is difficult to say which editor is the best. When I started learning PostgreSQL the only tool available was PgAdmin 2 and phpPgAdmin. I decided for the former and then I switched to the newer PgAdmin 3 which added a powerful editor and multi platform support. In the years I tested some of the other clients like TOra, SQL workbench and SQL Maestro and I never found the same confidence and ease of usage like PgAdmin 3. Whether is the tool of your choice this should have the following features.

libpq connector

One of the reasons I do not like SQL workbench is the JDBC connector. Writing and testing the SQL code is a quick process. Write the statement, test it, then change it, the test it again and so on. The client response in this method is absolutely important. The libpq connector have virtually no lag, except the disk/network bandwidth.

Removal of the Byte Order Mark (BOM)

The BOM is a marker set in the UTF8 files to determine the byte order. The client psql manages the presence but for some reasons it can fail with an error when running sql scripts with this marker. In PgAdmin 3 is possible to disable the BOM when saving the files.