Question 1

How do I set up pgx_scripts to kill idle transactions automatically?

Accepted Answer

Use the kill_idle stored procedures in a cron job. For PostgreSQL 9.2+, run kill_idle_93.sql with parameters for idle time and exempt users; it outputs JSON for logging. Older versions use kill_idle_91.sql with pipe-delimited output.

Question 2

Does pgx_scripts work with PostgreSQL 13 or later?

Accepted Answer

Yes, scripts like kill_idle_93.sql for PostgreSQL 9.2+ are likely compatible with newer versions, but always test in your environment as some queries might need adjustments for changes in system catalogs or features.

Question 3

How accurate is the bloat estimation in pgx_scripts?

Accepted Answer

It's accurate for BTree indexes and tables with statistics, based on expert-overhauled queries. However, it has limitations: it can't estimate for GIN indexes or tables with no-stats columns like original JSON, so results may be incomplete.

Question 4

pgx_scripts vs pg_stat_statements: which is better for performance tuning?

Accepted Answer

pgx_scripts focuses on administrative tasks like bloat and idle transactions, while pg_stat_statements tracks query execution statistics. They complement each other; use pgx_scripts for maintenance and pg_stat_statements for deep query optimization.

Question 5

Can I use pgx_scripts to find missing foreign key indexes?

Accepted Answer

Yes, the fk_no_index.sql query lists foreign keys without indexes on the referencing side, helping identify potential performance bottlenecks in joins. Note that indexes aren't always necessary, so review each case manually.

Question 6

What are the common false positives in pgx_scripts' duplicate index check?

Accepted Answer

The duplicate_indexes_fuzzy.sql reports false duplicates for partial indexes (with WHERE clauses) and functional indexes (with expressions) because it only checks column names, not the full index definition. Always verify results before removing indexes.

pgx_scripts

What is pgx_scripts?

Overview

Use Cases

Best For

Found a gem we're missing?

Not Ideal For

Pros & Cons

Pros

Cons

Frequently Asked Questions