Version control systems

It's recommended you maintain your code using a revision-control system, such as Git or SVN. When writing SQL code, it's better to create an installation script and execute it in one transaction. This approach makes it easy to clean up if an error occurs. Also, a good practice is to create a rollback script to quickly return schema to the previous state if something goes wrong at the application level.

Database objects have different properties: some are a part of the physical schema, and some control database access. The following is a proposal for organizing the database code in order to increase the separation of concerns (SoC).

For each database in a PostgreSQL cluster, you should maintain the DDL script, for objects that are part of the physical schema, and the DML script, which populates the tables with static data, together. The state of an object, such as a table or index in the physical schema, is defined by the object structure and the data that is contained by this object; thus, the object can't be recreated without being dropped first. Also, the structure of the physical schema object doesn't change often. In addition, the refactoring of some of the physical schema objects, such as tables, might require data migration. In other words, changing the definition of a physical schema object requires some planning.

Store the DDL scripts for objects that aren't part of the physical schema, such as views and functions, separately. Keeping the definitions of views and functions together allows the developer to refactor them easily. Also, the developer will be able to extract the dependency trees between these objects.

Maintain the DCL script separately. This allows the developer to separate the security aspect from the functional requirements of the database. This means that the database developers and administrators can work closely without interfering in each other's work.