Introduction
PostgreSQL is an open-source, enterprise-class, powerful relational database that is developed and maintained by the PostgreSQL Global Development Group. It is a highly scalable database that supports both SQL and JSON querying. When utilised appropriately, the PostgreSQL database can act as a powerful and financially smart addition to the organisation management tools.
This blog provides some useful practices that a developer can follow while configuring and maintaining PostgreSQL databases.
1. Use Environment Variables to Expose Connection Strings
One of the practices that come in handy when building web applications for the cloud is not storing your database credentials in the codebase. Your code is considered inherently insecure and cannot be trusted with your database credentials. Hence, it is recommended to use an environment variable (DATABASE_URL) to expose your connection strings. This environment variable is supported by most of the popular web frameworks such as Django.
2. Change Credentials Periodically
Limiting the lifespan of passwords reduces the risks of password-based attacks. Hence, you should develop the habit of rotating passwords regularly. All it takes is one right attempt, and all your valuable and confidential information could get into the wrong hands compromising the entire organisation. Set hard deadlines to reset the database credentials periodically.
3. Do not Apply Changes Directly to Your Production Database
No matter how small or urgent a change is, always apply changes first to your non-production database to test out the functionality. If your code breaks, it will temper the live production database used by many other employees within the organisation. After testing, when you are satisfied with the results, then only you should merge your code with the production database. The process could be time-consuming, but it will not let you fall into problems.
4. Follow a Test-Driven Approach
Always follow the approach of writing test cases first rather than creating database objects such as schema, extensions, tables, functions, types, etc. It may seem difficult at first, but you will benefit from it later. It is easy to miss out a few things when you create objects. For PostgreSQL databases, you can use an extension pgTAP for writing test cases. Create an additional schema schema_name_tap to write functions for testing.
5. Customize Point-In-Time-Recovery
You should make sure that you set up a system to take a backup of your database periodically. PostgreSQL uses Barman to implement disaster recovery so that you can easily restore the database from any point in time whenever required.
6. Use PgBouncer Connection Pooler
You should never connect to the PostgreSQL databases directly. When a developer is connecting to a PostgreSQL database, it is recommended to use PgBouncer (connection pooler) to configure clients. This helps you in reducing the memory and CPU footprint of open connections to handle client connections efficiently.
7. Maintain Data Consistency
Inconsistency in your data can turn your entire database into the garbage. Therefore, you should always implement foreign keys, checks, and normalisation practices to store data in the database. Denormalisation should be applied only when you cannot store the data in normalised format with an acceptable level of complexity.
8. Avoid Public Schema
Every time you create a new database, PostgreSQL automatically creates a public schema and grants access to a role named as public. If you create a table without specifying the schema, PostgreSQL creates that table in the public schema by default. Therefore, you should create separate schemas for each entity to avoid using public schema.
9. Create Audit Triggers
To track changes and maintain the code, it is recommended to create triggers to perform audits on the actions. Audit triggers will help you in tracking changes made to a table like addition or deletion. Triggers will capture information of what has changed and who has made those changes along with the timestamp. This information can provide you with a great benefit when you want to track down activities for a specific change.
10. Version the Database Schema
The code that you write must be in the version control system, and you should create versions for the database schema along with the rest of the projects. You can use Pyrseas, a utility to maintain the database schema for PostgreSQL. Pyrseas converts all the objects in a schema into a YAML file so that you can work in branches efficiently.
11. Configure Monitor
You should configure the monitor to assess the matrices such as read or write activity, available memory, database size, connections, and transaction rates.
Conclusion
To achieve the best results, you should configure your PostgreSQL database as per the vendor’s requirements or follow the best practices mentioned in this blog. PostgreSQL databases serve as a good addition and a necessity in many setups, but this does not mean that you will never face any issues. Therefore, it is always helpful when you have some of the best practices handy.
For a free trial - https://bit.ly/freetrialcloud