{"id":4430,"date":"2023-09-13T08:58:15","date_gmt":"2023-09-13T08:58:15","guid":{"rendered":"https:\/\/test.opensource-db.in\/wp1\/?p=4430"},"modified":"2023-09-13T08:58:16","modified_gmt":"2023-09-13T08:58:16","slug":"postgres-beyond-the-basics-exploring-extensibility-part-iii","status":"publish","type":"post","link":"https:\/\/test.opensource-db.in\/wp1\/postgres-beyond-the-basics-exploring-extensibility-part-iii\/","title":{"rendered":"Postgres Beyond the Basics: Exploring Extensibility – Part III"},"content":{"rendered":"\n
\"\"<\/figure>\n\n\n\n

In the earlier posts, we\u2019ve had an Overview of Postgres Extensions<\/a>, and we\u2019ve also looked at the pg_cron<\/a> extension in some detail. In today\u2019s post, we shall examine the pg_stat_statements, pgstattuple, and pg_buffercache extensions.<\/p>\n\n\n\n

Using pg_stat_statements extension<\/h2>\n\n\n\n

The pg_stat_statements<\/code> view is a valuable PostgreSQL extension that offers insights into the execution statistics of SQL statements processed by the PostgreSQL server. This information can be instrumental in identifying and addressing performance issues within your database system.<\/p>\n\n\n\n

You can leverage the pg_stat_statements<\/code> View in several ways to trace and tackle performance problems:<\/p>\n\n\n\n

Identifying Slow Queries:<\/strong> To pinpoint queries that consume significant time during execution, sort the view by the total_time <\/code>column in descending order:<\/p>\n\n\n\n

SELECT * FROM pg_stat_statements\nORDER BY total_time DESC;<\/code><\/pre>\n\n\n\n
This reveals which queries are likely culprits for performance bottlenecks.<\/p>\n\n\n\n
Identifying Frequently Executed Queries:<\/strong> Use the calls<\/code> column to discover queries that are executed frequently. These queries may not individually be time-consuming, but their high frequency can impact performance:<\/p>\n\n\n\n
SELECT * FROM pg_stat_statements\nORDER BY calls DESC;<\/code><\/pre>\n\n\n\n
Addressing frequently executed queries is crucial for overall system optimization.<\/p>\n\n\n\n
Analyzing Resource Usage:<\/strong> By examining the rows <\/code>and memory <\/code>columns, you can determine which queries consume significant resources. Queries with high row<\/code> values process substantial data, while those with high memory<\/code> values utilize extensive memory:<\/p>\n\n\n\n
SELECT * FROM pg_stat_statements\nORDER BY rows DESC;<\/code><\/pre>\n\n\n\n
and<\/p>\n\n\n\n
SELECT * FROM pg_stat_statements\nORDER BY memory DESC;<\/code><\/pre>\n\n\n\n
Identifying resource-intensive queries aids in resource management.<\/p>\n\n\n\n
Detecting Query Plan Changes:<\/strong> The plan_changes <\/code>column highlights queries that frequently alter their execution plans. Queries exhibiting many plan changes can pose optimization challenges for PostgreSQL:<\/p>\n\n\n\n
SELECT * FROM pg_stat_statements\nORDER BY plan_changes DESC;<\/code><\/pre>\n\n\n\n
Understanding such queries is essential for query plan stability.<\/p>\n\n\n\n
Once problematic queries are identified, you can harness the data from the pg_stat_statements<\/code> view to scrutinize their execution plans. This analysis offers insights into the reasons behind slow performance and provides guidance on optimizing query execution for improved database efficiency.<\/p>\n\n\n\n
Using pgstattuple extension<\/h2>\n\n\n\n
The pgstattuple module provides various functions to obtain tuple-level statistics.<\/p>\n\n\n\n
Because these functions return detailed page-level information, access is restricted by default. By default, only the role pg_stat_scan_tables <\/code>has EXECUTE<\/code> privilege. Superusers of course bypass this restriction. After the extension has been installed, users may issue GRANT <\/code>commands to change the privileges of the functions to allow others to execute them. However, it might be preferable to add those users to the pg_stat_scan_tables <\/code>role instead.<\/p>\n\n\n\n
Create the extension using the CREATE EXTENSION command as follows:<\/p>\n\n\n\n
osdb=# CREATE EXTENSION pgstattuple;\nCREATE EXTENSION<\/code><\/pre>\n\n\n\n
\"C:\\Users\\lenovo\\Desktop\\create_pgstattupple.png\"<\/p>\n\n\n\n
We can check the installed pgstattuple extension from the PSQL <\/code>prompt as follows:<\/p>\n\n\n\n
\"C:\\Users\\lenovo\\Desktop\\pgstattupple_dx.png\"<\/p>\n\n\n\n
pgstattuple returns a relation’s physical length, percentage of \u201cdead\u201d tuples, and other info. This may help users determine whether a vacuum is necessary or not. The argument is the target relation’s name (optionally schema-qualified) or OID.<\/p>\n\n\n\n
For example:<\/strong><\/p>\n\n\n\n
Create a table named ‘t1’ and insert 10,000 rows of random data into the ‘t1’ table using the following SQL commands.<\/p>\n\n\n\n
osdb=# CRATE TABLE t1 (id int, name varchar);\nCREATE TABLE\nosdb=#INSERT INTO t1 SELECT generate_series (1,10000),md5(generate_series(1,10000)::text);\nINSERT 0 10000<\/code><\/pre>\n\n\n\n
\"C:\\Users\\lenovo\\Desktop\\create_table_t1.png\"<\/p>\n\n\n\n
\"C:\\Users\\lenovo\\Desktop\\insert_into_t1.png\"<\/p>\n\n\n\n
\"C:\\Users\\lenovo\\Desktop\\insert_into_t1.png\"\/<\/figure>\n\n\n\n
We can check the table t1 tuple data as following<\/p>\n\n\n\n
\"C:\\Users\\lenovo\\Desktop\\t1_tupples_data.png\"<\/p>\n\n\n\n
Delete a portion of the data from the ‘t1’ table to intentionally generate some dead tuples.<\/p>\n\n\n\n
\"C:\\Users\\lenovo\\Desktop\\del_some_rows.png\"<\/p>\n\n\n\n
We can assess table-level bloat by executing the following command:<\/p>\n\n\n\n
\"C:\\Users\\lenovo\\Desktop\\after_delete_rows.png\"<\/p>\n\n\n\n
To reclaim space and remove dead tuples, you can perform an VACUUM <\/code>operation on the ‘t1’ table. This will free up space previously occupied by the dead tuples.<\/p>\n\n\n\n
\"C:\\Users\\lenovo\\Desktop\\vacuum.png\"<\/p>\n\n\n\n
Now, we can check the dead tuple count, length, and percentage after completion of the vacuum.<\/p>\n\n\n\n
This free space can now be used to store new rows inside your table.<\/p>\n\n\n\n
\"C:\\Users\\lenovo\\Desktop\\after_vacuum.png\"<\/p>\n\n\n\n
Conclusion:<\/strong><\/p>\n\n\n\n
In this example, we have demonstrated the process for checking and addressing bloat in a single table. You can now apply sorting and filtering techniques as needed to identify and address bloat in other tables within your PostgreSQL database.<\/p>\n\n\n\n
Using pg_buffercache extension<\/h2>\n\n\n\n
The pg_buffercache module provides a means for examining what’s happening in the shared buffer cache in real-time.<\/p>\n\n\n\n
Create the extension using the CREATE EXTENSION <\/code>command as follows:<\/p>\n\n\n\n
osdb=# create extension pg_buffercache;\nCREATE EXTENSION<\/code><\/pre>\n\n\n\n
\"C:\\Users\\lenovo\\Desktop\\create_buffer_ext.png\"<\/p>\n\n\n\n
We can check the installed extension from the PSQL<\/code> prompt as follows:<\/p>\n\n\n\n
osdb=# \\dx pg_buffercache <\/code><\/pre>\n\n\n\n
                    List of installed extensions\n      Name      | Version | Schema |           Description           \n----------------+---------+--------+---------------------------------\n pg_buffercache | 1.3     | public | examine the shared buffer cache\n\n<\/code><\/pre>\n\n\n\n
\"C:\\Users\\lenovo\\Desktop\\buffer_dx.png\"<\/p>\n\n\n\n
After the creation of the pg_buffercache extension, it will create a view called pg_buffercache <\/code>with the following structure:<\/p>\n\n\n\n
\"C:\\Users\\lenovo\\Desktop\\buffer_view.png\"<\/p>\n\n\n\n
The view created after the installation of the extension called pg_buffercache <\/code>has several columns.<\/p>\n\n\n\n