A heroku plugin for awesome pg:* commands that are also great and fun and super.
$ heroku plugins:install git://github.com/heroku/heroku-pg-extras.gitPlease note that pg-extras frequently requires brand-new Heroku toolbelt
features. If you run into problems, the first thing to try is running heroku update
and try again.
$ heroku plugins:update git://github.com/heroku/heroku-pg-extras.git$ heroku pg:cache-hit -adashboard
name | ratio
----------------+------------------------
index hit rate | 0.99957765013541945832
table hit rate | 1.00
(2 rows)$ heroku pg:index-usage -adashboard
relname | percent_of_times_index_used | rows_in_table
---------------------+-----------------------------+---------------
events | 65 | 1217347
app_infos | 74 | 314057
app_infos_user_info | 0 | 198848
user_info | 5 | 94545
delayed_jobs | 27 | 0
(5 rows)
$ heroku pg:ps
procpid | source | running_for | waiting | query
---------+------------------------------------------+-----------------+---------+-----------------------
31776 | psql | 00:19:08.017088 | f | <IDLE> in transaction
31912 | psql | 00:18:56.12178 | t | select * from hello;
32670 | Heroku Postgres Data Clip daaiifuuraiyks | 00:00:25.625609 | f | BEGIN READ ONLY; select pg_sleep(60)
(2 rows)
$ heroku pg:locks
procpid | relname | transactionid | granted | query_snippet | age
---------+---------+---------------+---------+-----------------------+-----------------
31776 | | | t | <IDLE> in transaction | 00:19:29.837898
31776 | | 1294 | t | <IDLE> in transaction | 00:19:29.837898
31912 | | | t | select * from hello; | 00:19:17.94259
3443 | | | t | +| 00:00:00
| | | | select +|
| | | | pg_stat_activi |
(4 rows)
$ heroku pg:outliers
qry | exec_time | prop_exec_time | ncalls | sync_io_time
-----------------------------------------+------------------+----------------+-------------+--------------
SELECT * FROM archivable_usage_events.. | 154:39:26.431466 | 72.2% | 34,211,877 | 00:00:00
COPY public.archivable_usage_events (.. | 50:38:33.198418 | 23.6% | 13 | 13:34:21.00108
COPY public.usage_events (id, reporte.. | 02:32:16.335233 | 1.2% | 13 | 00:34:19.784318
INSERT INTO usage_events (id, retaine.. | 01:42:59.436532 | 0.8% | 12,328,187 | 00:00:00
SELECT * FROM usage_events WHERE (alp.. | 01:18:10.754354 | 0.6% | 102,114,301 | 00:00:00
UPDATE usage_events SET reporter_id =.. | 00:52:35.683254 | 0.4% | 23,786,348 | 00:00:00
INSERT INTO usage_events (id, retaine.. | 00:49:24.952561 | 0.4% | 21,988,201 | 00:00:00
COPY public.app_ownership_events (id,.. | 00:37:14.31082 | 0.3% | 13 | 00:12:32.584754
INSERT INTO app_ownership_events (id,.. | 00:26:59.808212 | 0.2% | 383,109 | 00:00:00
SELECT * FROM app_ownership_events .. | 00:19:06.021846 | 0.1% | 744,879 | 00:00:00
(10 rows)
$ heroku pg:calls
qry | exec_time | prop_exec_time | ncalls | sync_io_time
-----------------------------------------+------------------+----------------+-------------+--------------
SELECT * FROM usage_events WHERE (alp.. | 01:18:11.073333 | 0.6% | 102,120,780 | 00:00:00
BEGIN | 00:00:51.285988 | 0.0% | 47,288,662 | 00:00:00
COMMIT | 00:00:52.31724 | 0.0% | 47,288,615 | 00:00:00
SELECT * FROM archivable_usage_event.. | 154:39:26.431466 | 72.2% | 34,211,877 | 00:00:00
UPDATE usage_events SET reporter_id =.. | 00:52:35.986167 | 0.4% | 23,788,388 | 00:00:00
INSERT INTO usage_events (id, retaine.. | 00:49:25.260245 | 0.4% | 21,990,326 | 00:00:00
INSERT INTO usage_events (id, retaine.. | 01:42:59.436532 | 0.8% | 12,328,187 | 00:00:00
SELECT * FROM app_ownership_events .. | 00:19:06.289521 | 0.1% | 744,976 | 00:00:00
INSERT INTO app_ownership_events(id, .. | 00:26:59.885631 | 0.2% | 383,153 | 00:00:00
UPDATE app_ownership_events SET app_i.. | 00:01:22.282337 | 0.0% | 359,741 | 00:00:00
(10 rows)
$ heroku pg:blocking
blocked_pid | blocking_statement | blocking_duration | blocking_pid | blocked_statement | blocked_duration
-------------+--------------------------+-------------------+--------------+------------------------------------------------------------------------------------+------------------
461 | select count(*) from app | 00:00:03.838314 | 15682 | UPDATE "app" SET "updated_at" = '2013-03-04 15:07:04.746688' WHERE "id" = 12823149 | 00:00:03.821826
(1 row)
$ heroku pg:pull DATABASE localdbname --app myapp
$ heroku pg:push localdbname DATABASE --app myapp
$ heroku pg:total-index-size
size
-------
28194 MB
(1 row)
$ heroku pg:index-size
name | size
---------------------------------------------------------------+---------
idx_activity_attemptable_and_type_lesson_enrollment | 5196 MB
index_enrollment_attemptables_by_attempt_and_last_in_group | 4045 MB
index_attempts_on_student_id | 2611 MB
enrollment_activity_attemptables_pkey | 2513 MB
index_attempts_on_student_id_final_attemptable_type | 2466 MB
attempts_pkey | 2466 MB
index_attempts_on_response_id | 2404 MB
index_attempts_on_enrollment_id | 1957 MB
index_enrollment_attemptables_by_enrollment_activity_id | 1789 MB
enrollment_activities_pkey | 458 MB
index_enrollment_activities_by_lesson_enrollment_and_activity | 402 MB
index_placement_attempts_on_response_id | 109 MB
index_placement_attempts_on_placement_test_id | 108 MB
index_placement_attempts_on_grade_level_id | 97 MB
index_lesson_enrollments_on_lesson_id | 93 MB
(truncated results for brevity)
$ heroku pg:table-size
name | size
---------------------------------------------------------------+---------
learning_coaches | 196 MB
states | 145 MB
grade_levels | 111 MB
charities_customers | 73 MB
charities | 66 MB
(truncated results for brevity)
$ heroku pg:table-indexes-size
table | indexes_size
---------------------------------------------------------------+--------------
learning_coaches | 153 MB
states | 125 MB
charities_customers | 93 MB
charities | 16 MB
grade_levels | 11 MB
(truncated results for brevity)
$ heroku pg:total-table-size
name | size
---------------------------------------------------------------+---------
learning_coaches | 349 MB
states | 270 MB
charities_customers | 166 MB
grade_levels | 122 MB
charities | 82 MB
(truncated results for brevity)
$ heroku pg:unused-indexes
table | index | index_size | index_scans
---------------------+--------------------------------------------+------------+-------------
public.grade_levels | index_placement_attempts_on_grade_level_id | 97 MB | 0
public.observations | observations_attrs_grade_resources | 33 MB | 0
public.messages | user_resource_id_idx | 12 MB | 0
(3 rows)
$ heroku pg:seq-scans
name | count
-----------------------------------+----------
learning_coaches | 44820063
states | 36794975
grade_levels | 13972293
charities_customers | 8615277
charities | 4316276
messages | 3922247
contests_customers | 2915972
classroom_goals | 2142014
contests | 1370267
goals | 1112659
districts | 158995
rollup_reports | 115942
customers | 93847
schools | 92984
classrooms | 92982
customer_settings | 91226
(truncated results for brevity)
$ heroku pg:long-running-queries
pid | duration | query
-------+-----------------+---------------------------------------------------------------------------------------
19578 | 02:29:11.200129 | EXPLAIN SELECT "students".* FROM "students" WHERE "students"."id" = 1450645 LIMIT 1
19465 | 02:26:05.542653 | EXPLAIN SELECT "students".* FROM "students" WHERE "students"."id" = 1889881 LIMIT 1
19632 | 02:24:46.962818 | EXPLAIN SELECT "students".* FROM "students" WHERE "students"."id" = 1581884 LIMIT 1
(truncated results for brevity)
$ heroku pg:records_rank
name | estimated_count
-----------------------------------+-----------------
tastypie_apiaccess | 568891
notifications_event | 381227
core_todo | 178614
core_comment | 123969
notifications_notification | 102101
django_session | 68078
(truncated results for brevity)
$ heroku pg:bloat
type | schemaname | object_name | bloat | waste
-------+------------+-------------------------------+-------+----------
table | public | bloated_table | 1.1 | 98 MB
table | public | other_bloated_table | 1.1 | 58 MB
index | public | bloated_table::bloated_index | 3.7 | 34 MB
table | public | clean_table | 0.2 | 3808 kB
table | public | other_clean_table | 0.3 | 1576 kB
$ heroku pg:vacuum-stats
schema | table | last_vacuum | last_autovacuum | rowcount | dead_rowcount | autovacuum_threshold | expect_autovacuum
--------+-----------------------+-------------+------------------+----------------+----------------+----------------------+-------------------
public | log_table | | 2013-04-26 17:37 | 18,030 | 0 | 3,656 |
public | data_table | | 2013-04-26 13:09 | 79 | 28 | 66 |
public | other_table | | 2013-04-26 11:41 | 41 | 47 | 58 |
public | queue_table | | 2013-04-26 17:39 | 12 | 8,228 | 52 | yes
public | picnic_table | | | 13 | 0 | 53 |
$ heroku pg:mandelbrot
Thanks for trying it out. If you find any issues, please notify us at support@heroku.com