PostgreSQL, referred to simply as Postgres, is a relational database engine focused on high performance, scalability, and enterprise readiness. It shares characteristics with MySQL and MariaDB. Although as an object-relational database, the features go well beyond what MySQL and other purely relational database engines offer. Postgres can handle complex queries, massive databases, function overloading, table inheritance, and much more. In short, it supports a number of enterprise application and database tasks.
In this blog, we look at PostgreSQL System Catalogs, with an overview and glossary of commonly used catalogs, pg_stat_user_tables, pg_statio_user_indexes, pg_statio_user_tables, pg_stat_activity, pg_database, and pg_locks.
Inside of Postgres, there’s an extensive system of data catalogs available. When you’re troubleshooting, this is where you’re going to spend the bulk of your time. In short, the data catalogs are where Postgres stores schema metadata. This includes information about tables and columns, internal bookkeeping information, and database performance variables as a very short list of examples. When hooking Postgres up to a monitoring solution, this is where your monitoring data ultimately comes from. Below, we will explore some key system catalogs in greater depth.
This catalog returns valuable information pertaining to the database engine activity behind particular tables. Seq_scan (sequential scans) and seq_tup (rows added and deleted) count the number of sequential scans of tables and the number of tuples (rows) read during those scans.
This offers granular detail on how often indexes are being used inside of the database. Administrators can check this catalog and in general you will hope to see a high number. Relative to operations you are running against a database, your indexes should be utilized as often as possible. If this view returns a low index number, you likely may have an issue with your table relations design. Or you may not have have appropriately set up your index field.
Going a layer deeper beyond logical indexes, this shows where within our memory we are being hit. It’s complementary to pg_stat_user_tables, and shows IO information from several mediums. This can answer questions like, how much memory utilization are we seeing?
When clients enter the database and request a new connection, the Postgres main process generates a backend process, and that backend process is basically a user session. Ultimately, we see those sessions and information on individual sessions within the pg_stat_activity catalog. This information is organized by name of database, application hitting the database, utility for looking at that database, as well as the bid and process ID. This information is very useful if you have a client who appears to be stuck against the database.
When you run this catalog against your own system catalog internally, it will return information on a number of different databases. Why? The pg_database view displays information about each database, with one database represented per row.
datcollate and datctype
Postgres is a locking database that supports several different types of locks. If we want to view how each lock is functioning, this is where the pg_locks catalog comes into play. You can also see the lock mode, Exclusive vs. Share, etc.. If the lock is currently held, then the “granted” Boolean will be true. For “Fast path” locking, locking is indicated by fastpath 0.
Do you need PostgreSQL support? At OpenLogic, a highly experienced enterprise architect responds to every support ticket. Going well beyond bug fixes, we help you solve complex technology problems. This often includes security, performance issues, interoperability, or getting started with an emerging open source platform.
Need help with a tough integration? Have questions about how to optimize your open source database performance? All you need to do is call or submit a ticket and support is on the way.
TALK TO AN EXPERT