Utility scripts to make PostgreSQL performance tuning easier.
Brought to you by the folks at SmartPostgres.com.
Report Bug · Contribute Code for a Feature

Click Releases at the top of this page to download the installation file, which contains each of the sql scripts.

Our target audience is folks who already know how to create and query Postgres functions. If you don't fall into that audience, we're not quite ready for you yet, but at some point in the future we'll have more detailed instructions for folks who are completely new to Postgres.

The Box of Tricks works with all currently supported versions of Postgres (as of this writing, going back to v12), plus Amazon RDS Aurora PostgreSQL. Other proprietary cloud brands of Postgres may also work, we just haven't tested 'em. If you run into problems on other versions, we only take bug reports that also include a pull request to make the necessary changes. Otherwise, we just can't test the Box of Tricks on every possible cloud platform.

(back to top)

This function analyzes the health and design of your indexes.

Parameters include:

• v_schema_name - default null (all schemas)
• v_table_name - default null (all tables)
• v_warning_format - default 'rows', which means each warning gets its own row. That's the only supported output format for now, but in the future we'll add a way to support multiple warnings in a single row.
• v_debug_level - default 0 (no debug output), can also be 1 (minimal debug output) or 2 (detailed debug output with dynamic SQL).

To check the health and design of all of the tables & indexes in your database, run:

select * from check_indexes(null, null);

Those first two parameters are schema name and table name. If you want to check all of the objects in a particular schema, run:

select * from check_indexes('my_schema_name', null)

Or to check a single table:

select * from check_indexes('my_schema_name', 'my_table_name')

Output columns include (skipping the ob)

• schema_name
• table_name
• index_name
• index_type - btree, ordinary table, sequence, toast, etc.
• index_definition - the create statement to reproduce it, useful if you need to put objects into source control or recreate them in other environments
• size_kb
• estimated_tuples
• estimated_tuples_as_of - date
• dead_tuples
• last_autovacuum - date
• last_manual_nonfull_vacuum_ - date
• fill_factor
• is_unique
• is_primary
• table_oid
• index_oid
• priority - if we return a warning about a problem with this object, like autovacuum not keeping up, then the most urgent priorities (like 1) go first, and lower priorities are sorted lower (like 200)
• warning_summary
• warning_details
• url - if we return a warning, you can copy-paste the URL into your browser to learn more about it
• reloptions
• drop_object_command - useful if you want to drop specific objects

(back to top)

This function does what it says on the label. It's useful for training class attendees who want to reset a table to its base state before doing index tuning.

Parameters include:

• v_schema_name - default null (all schemas), or you can pass in a single schema to focus on just one.
• v_table_name - default null (all tables), or you can pass in a single schema to focus on just one.
• v_drop_primary_keys, v_force_drop_with_constraints, v_drop_concurrently - all boolean options that default to false.
• v_list_indexes_being_dropped - useful if you want confirmation that work was actually done.
• v_print_drops_but_dont_execute - default false, but set it to true if you want to do a dry run to see what it will delete.

Contributions are what make the open source community such an amazing place to learn, inspire, and create. Any contributions you make are greatly appreciated.

If you have a suggestion that would make this better, please fork the repo and create a pull request as described in our Contributing Guide.

(back to top)

Distributed under the MIT License. See the LICENSE file for more information.

(back to top)

Brent Ozar - [email protected]

Project Link: https://github.com/SmartPostgres/Box-of-Tricks

(back to top)