pgstattuple query performance

Question

pgstattuple query performance

Naveen Kumar Kakarla (Quadrant Resource) 201

I am trying to build the query that checks dead tuples, live tuples percent, free space etc., in all my users tables using pgstattuple extension. Can you please help me with the query? Just FYI., there are couple of large tables with more than 100GB of size.

Alicja Kucharczyk 195 Reputation points Microsoft Employee

2024-01-03T06:09:40.0033333+00:00

for really big tables you might want to query pg_class instead of the extension. For instance take a look at this query: https://wiki.postgresql.org/wiki/Show_database_bloat

Accepted answer

0 additional answers

Your answer

Alicja Kucharczyk 195 Reputation points Microsoft Employee

2024-01-03T06:09:40.0033333+00:00

for really big tables you might want to query pg_class instead of the extension. For instance take a look at this query: https://wiki.postgresql.org/wiki/Show_database_bloat

Answer 1

GeethaThatipatri-MSFT 29,542 Microsoft Employee Moderator

Hi, @Naveen Kumar Kakarla (Quadrant Resource) Thanks for posting your question in the Microsoft Q&A forum.

Here is the blog post provides a detailed explanation of how to use pgstattuple to get tuple-level statistics for PostgreSQL tables, including dead tuples, live tuples, free space, and more.

https://www.percona.com/blog/postgresql-tuple-level-statistics-with-pgstattuple/

The blog post also includes examples of queries that you can use to get statistics for individual tables or for all tables in a database. These queries can be useful for identifying table bloat and optimizing database performance.

I hope this information helps! Let me know if you have any further questions.

Regards

Geetha

GeethaThatipatri-MSFT 29,542 Reputation points Microsoft Employee Moderator

2024-01-05T16:03:26.53+00:00

Hi, @Naveen Kumar Kakarla (Quadrant Resource) I am checking to see if you got a chance to look into the above response. Please let me know if you have any further questions.

If this answers your question, please consider accepting the answer by hitting the Accept answeras it helps the community look for answers to similar questions.

Regards

Geetha

Share via

pgstattuple query performance

0 additional answers

Your answer