Over the past few weeks, I’ve been highlighting how you can drive better PostgreSQL performance with a focus on a few aspects of your environment. First, I provided some insight into replication and how you can leverage high availability to work for you. Last week, I followed up with a post about indexes — and how maintaining these can transform how you manage and access your data.
This week, I’m covering schemas — much like indexes, how you manage and use schemas can have a huge impact on the performance of your PostgreSQL environment.
What are schemas?
Within your PostgreSQL database, you likely have one or more schemas. Schemas contain tables and can also host other named objects, like functions, operators and data types.
To make things a little bit more confusing, you can use the same object name in different schemas and it will not pose a conflict. In addition, schemas don’t have rigid rules — meaning that, with the right permissions, you can access objects in any schema that you are connected to.
So, what’s the advantage to having schemas? It ensures that you can have multiple people accessing your database and they don’t cause interference with each other’s activities. For larger teams, this is a big bonus. In addition, schemas are extremely helpful for organization and managing third-party applications.
Just like how you can organize schemas so multiple people can access information without interference, third-party applications can be organized into different schemas so if they have the same object names, they don’t collide or cause problems.
How to keep your schemas healthy
Schemas are great — as long as they are operating efficiently and helping reduce the amount of work, instead of creating it. One of the issues I’ve heard from customers is that they’ll have one person work in the PostgreSQL environment and create schemas, never to be touched again. When someone else finally uncovers these schemas, they haven’t been maintained at all and create more problems than they are worth.
Monitoring schemas performance starts at the basic level of the table — knowing how your table performs can give you an indication of the health of your schema. A database performance monitoring solution like SelectStar enables you to monitor specific tables.
Figure 1: Example of how you can monitor your tablespace by name, per database, within PostgreSQL
By specifying your table, you can see the associated health of the data associated with it. Within SelectStar, for example, you can set up alerts and recommendations by table — so if you start to see a delay in query performance times you are instantly alerted so you can maintain the data, keeping the table and associated schema in good health.
As a result, it simplifies your database management — making it easy for your user to access key data without delays due to performance problems. It provides a single console for you to see all of the tables and thus, schemas, that you may have across your PostgreSQL environment — giving you essential insight into how your users are accessing data and where you could potentially focus on performance improvements.
Interested in tracking your schema performance? Try a 14-day trial with SelectStar.