1 | # pg-extras
|
2 |
|
3 | A heroku plugin for awesome pg:* commands that are also great and fun and super.
|
4 |
|
5 | ### Installation
|
6 |
|
7 | ```bash
|
8 | $ heroku plugins:install heroku-pg-extras
|
9 | ```
|
10 |
|
11 | ### Usage
|
12 |
|
13 | ```bash
|
14 | $ heroku pg:cache-hit
|
15 | name | ratio
|
16 | ----------------+------------------------
|
17 | index hit rate | 0.99957765013541945832
|
18 | table hit rate | 1.00
|
19 | (2 rows)
|
20 | ```
|
21 |
|
22 | ```
|
23 | $ heroku pg:index-usage
|
24 | relname | percent_of_times_index_used | rows_in_table
|
25 | ---------------------+-----------------------------+---------------
|
26 | events | 65 | 1217347
|
27 | app_infos | 74 | 314057
|
28 | app_infos_user_info | 0 | 198848
|
29 | user_info | 5 | 94545
|
30 | delayed_jobs | 27 | 0
|
31 | (5 rows)
|
32 | ```
|
33 |
|
34 | ```
|
35 | $ heroku pg:ps
|
36 | procpid | source | running_for | waiting | query
|
37 | ---------+------------------------------------------+-----------------+---------+-----------------------
|
38 | 31776 | psql | 00:19:08.017088 | f | <IDLE> in transaction
|
39 | 31912 | psql | 00:18:56.12178 | t | select * from hello;
|
40 | 32670 | Heroku Postgres Data Clip daaiifuuraiyks | 00:00:25.625609 | f | BEGIN READ ONLY; select pg_sleep(60)
|
41 | (2 rows)
|
42 | ```
|
43 |
|
44 | ```
|
45 | $ heroku pg:locks
|
46 | procpid | relname | transactionid | granted | query_snippet | age
|
47 | ---------+---------+---------------+---------+-----------------------+-----------------
|
48 | 31776 | | | t | <IDLE> in transaction | 00:19:29.837898
|
49 | 31776 | | 1294 | t | <IDLE> in transaction | 00:19:29.837898
|
50 | 31912 | | | t | select * from hello; | 00:19:17.94259
|
51 | 3443 | | | t | +| 00:00:00
|
52 | | | | | select +|
|
53 | | | | | pg_stat_activi |
|
54 | (4 rows)
|
55 | ```
|
56 |
|
57 | ```
|
58 | $ heroku pg:outliers
|
59 | qry | exec_time | prop_exec_time | ncalls | sync_io_time
|
60 | -----------------------------------------+------------------+----------------+-------------+--------------
|
61 | SELECT * FROM archivable_usage_events.. | 154:39:26.431466 | 72.2% | 34,211,877 | 00:00:00
|
62 | COPY public.archivable_usage_events (.. | 50:38:33.198418 | 23.6% | 13 | 13:34:21.00108
|
63 | COPY public.usage_events (id, reporte.. | 02:32:16.335233 | 1.2% | 13 | 00:34:19.784318
|
64 | INSERT INTO usage_events (id, retaine.. | 01:42:59.436532 | 0.8% | 12,328,187 | 00:00:00
|
65 | SELECT * FROM usage_events WHERE (alp.. | 01:18:10.754354 | 0.6% | 102,114,301 | 00:00:00
|
66 | UPDATE usage_events SET reporter_id =.. | 00:52:35.683254 | 0.4% | 23,786,348 | 00:00:00
|
67 | INSERT INTO usage_events (id, retaine.. | 00:49:24.952561 | 0.4% | 21,988,201 | 00:00:00
|
68 | COPY public.app_ownership_events (id,.. | 00:37:14.31082 | 0.3% | 13 | 00:12:32.584754
|
69 | INSERT INTO app_ownership_events (id,.. | 00:26:59.808212 | 0.2% | 383,109 | 00:00:00
|
70 | SELECT * FROM app_ownership_events .. | 00:19:06.021846 | 0.1% | 744,879 | 00:00:00
|
71 | (10 rows)
|
72 | ```
|
73 |
|
74 | ```
|
75 | $ heroku pg:calls
|
76 | qry | exec_time | prop_exec_time | ncalls | sync_io_time
|
77 | -----------------------------------------+------------------+----------------+-------------+--------------
|
78 | SELECT * FROM usage_events WHERE (alp.. | 01:18:11.073333 | 0.6% | 102,120,780 | 00:00:00
|
79 | BEGIN | 00:00:51.285988 | 0.0% | 47,288,662 | 00:00:00
|
80 | COMMIT | 00:00:52.31724 | 0.0% | 47,288,615 | 00:00:00
|
81 | SELECT * FROM archivable_usage_event.. | 154:39:26.431466 | 72.2% | 34,211,877 | 00:00:00
|
82 | UPDATE usage_events SET reporter_id =.. | 00:52:35.986167 | 0.4% | 23,788,388 | 00:00:00
|
83 | INSERT INTO usage_events (id, retaine.. | 00:49:25.260245 | 0.4% | 21,990,326 | 00:00:00
|
84 | INSERT INTO usage_events (id, retaine.. | 01:42:59.436532 | 0.8% | 12,328,187 | 00:00:00
|
85 | SELECT * FROM app_ownership_events .. | 00:19:06.289521 | 0.1% | 744,976 | 00:00:00
|
86 | INSERT INTO app_ownership_events(id, .. | 00:26:59.885631 | 0.2% | 383,153 | 00:00:00
|
87 | UPDATE app_ownership_events SET app_i.. | 00:01:22.282337 | 0.0% | 359,741 | 00:00:00
|
88 | (10 rows)
|
89 | ```
|
90 |
|
91 | ```
|
92 | $ heroku pg:blocking
|
93 | blocked_pid | blocking_statement | blocking_duration | blocking_pid | blocked_statement | blocked_duration
|
94 | -------------+--------------------------+-------------------+--------------+------------------------------------------------------------------------------------+------------------
|
95 | 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
|
96 | (1 row)
|
97 | ```
|
98 |
|
99 |
|
100 | ```
|
101 | $ heroku pg:pull DATABASE localdbname --app myapp
|
102 | ```
|
103 | ```
|
104 | $ heroku pg:push localdbname DATABASE --app myapp
|
105 | ```
|
106 |
|
107 | ```
|
108 | $ heroku pg:total-index-size
|
109 | size
|
110 | -------
|
111 | 28194 MB
|
112 | (1 row)
|
113 | ```
|
114 |
|
115 | ```
|
116 | $ heroku pg:index-size
|
117 | name | size
|
118 | ---------------------------------------------------------------+---------
|
119 | idx_activity_attemptable_and_type_lesson_enrollment | 5196 MB
|
120 | index_enrollment_attemptables_by_attempt_and_last_in_group | 4045 MB
|
121 | index_attempts_on_student_id | 2611 MB
|
122 | enrollment_activity_attemptables_pkey | 2513 MB
|
123 | index_attempts_on_student_id_final_attemptable_type | 2466 MB
|
124 | attempts_pkey | 2466 MB
|
125 | index_attempts_on_response_id | 2404 MB
|
126 | index_attempts_on_enrollment_id | 1957 MB
|
127 | index_enrollment_attemptables_by_enrollment_activity_id | 1789 MB
|
128 | enrollment_activities_pkey | 458 MB
|
129 | index_enrollment_activities_by_lesson_enrollment_and_activity | 402 MB
|
130 | index_placement_attempts_on_response_id | 109 MB
|
131 | index_placement_attempts_on_placement_test_id | 108 MB
|
132 | index_placement_attempts_on_grade_level_id | 97 MB
|
133 | index_lesson_enrollments_on_lesson_id | 93 MB
|
134 | (truncated results for brevity)
|
135 | ```
|
136 |
|
137 | ```
|
138 | $ heroku pg:table-size
|
139 | name | size
|
140 | ---------------------------------------------------------------+---------
|
141 | learning_coaches | 196 MB
|
142 | states | 145 MB
|
143 | grade_levels | 111 MB
|
144 | charities_customers | 73 MB
|
145 | charities | 66 MB
|
146 | (truncated results for brevity)
|
147 | ```
|
148 |
|
149 | ```
|
150 | $ heroku pg:table-indexes-size
|
151 | table | indexes_size
|
152 | ---------------------------------------------------------------+--------------
|
153 | learning_coaches | 153 MB
|
154 | states | 125 MB
|
155 | charities_customers | 93 MB
|
156 | charities | 16 MB
|
157 | grade_levels | 11 MB
|
158 | (truncated results for brevity)
|
159 | ```
|
160 |
|
161 | ```
|
162 | $ heroku pg:total-table-size
|
163 | name | size
|
164 | ---------------------------------------------------------------+---------
|
165 | learning_coaches | 349 MB
|
166 | states | 270 MB
|
167 | charities_customers | 166 MB
|
168 | grade_levels | 122 MB
|
169 | charities | 82 MB
|
170 | (truncated results for brevity)
|
171 | ```
|
172 |
|
173 | ```
|
174 | $ heroku pg:unused-indexes
|
175 | table | index | index_size | index_scans
|
176 | ---------------------+--------------------------------------------+------------+-------------
|
177 | public.grade_levels | index_placement_attempts_on_grade_level_id | 97 MB | 0
|
178 | public.observations | observations_attrs_grade_resources | 33 MB | 0
|
179 | public.messages | user_resource_id_idx | 12 MB | 0
|
180 | (3 rows)
|
181 | ```
|
182 |
|
183 | ```
|
184 | $ heroku pg:seq-scans
|
185 |
|
186 | name | count
|
187 | -----------------------------------+----------
|
188 | learning_coaches | 44820063
|
189 | states | 36794975
|
190 | grade_levels | 13972293
|
191 | charities_customers | 8615277
|
192 | charities | 4316276
|
193 | messages | 3922247
|
194 | contests_customers | 2915972
|
195 | classroom_goals | 2142014
|
196 | contests | 1370267
|
197 | goals | 1112659
|
198 | districts | 158995
|
199 | rollup_reports | 115942
|
200 | customers | 93847
|
201 | schools | 92984
|
202 | classrooms | 92982
|
203 | customer_settings | 91226
|
204 | (truncated results for brevity)
|
205 | ```
|
206 |
|
207 | ```
|
208 | $ heroku pg:long-running-queries
|
209 |
|
210 | pid | duration | query
|
211 | -------+-----------------+---------------------------------------------------------------------------------------
|
212 | 19578 | 02:29:11.200129 | EXPLAIN SELECT "students".* FROM "students" WHERE "students"."id" = 1450645 LIMIT 1
|
213 | 19465 | 02:26:05.542653 | EXPLAIN SELECT "students".* FROM "students" WHERE "students"."id" = 1889881 LIMIT 1
|
214 | 19632 | 02:24:46.962818 | EXPLAIN SELECT "students".* FROM "students" WHERE "students"."id" = 1581884 LIMIT 1
|
215 | (truncated results for brevity)
|
216 | ```
|
217 |
|
218 | ```
|
219 | $ heroku pg:records_rank
|
220 | name | estimated_count
|
221 | -----------------------------------+-----------------
|
222 | tastypie_apiaccess | 568891
|
223 | notifications_event | 381227
|
224 | core_todo | 178614
|
225 | core_comment | 123969
|
226 | notifications_notification | 102101
|
227 | django_session | 68078
|
228 | (truncated results for brevity)
|
229 | ```
|
230 |
|
231 | ```
|
232 | $ heroku pg:bloat
|
233 |
|
234 | type | schemaname | object_name | bloat | waste
|
235 | -------+------------+-------------------------------+-------+----------
|
236 | table | public | bloated_table | 1.1 | 98 MB
|
237 | table | public | other_bloated_table | 1.1 | 58 MB
|
238 | index | public | bloated_table::bloated_index | 3.7 | 34 MB
|
239 | table | public | clean_table | 0.2 | 3808 kB
|
240 | table | public | other_clean_table | 0.3 | 1576 kB
|
241 | ```
|
242 |
|
243 | ```
|
244 | $ heroku pg:vacuum-stats
|
245 | schema | table | last_vacuum | last_autovacuum | rowcount | dead_rowcount | autovacuum_threshold | expect_autovacuum
|
246 | --------+-----------------------+-------------+------------------+----------------+----------------+----------------------+-------------------
|
247 | public | log_table | | 2013-04-26 17:37 | 18,030 | 0 | 3,656 |
|
248 | public | data_table | | 2013-04-26 13:09 | 79 | 28 | 66 |
|
249 | public | other_table | | 2013-04-26 11:41 | 41 | 47 | 58 |
|
250 | public | queue_table | | 2013-04-26 17:39 | 12 | 8,228 | 52 | yes
|
251 | public | picnic_table | | | 13 | 0 | 53 |
|
252 |
|
253 | $ heroku pg:mandelbrot
|
254 | ```
|
255 |
|
256 | ## THIS IS BETA SOFTWARE
|
257 |
|
258 | Thanks for trying it out. If you find any issues, please notify us at
|
259 | support@heroku.com
|
260 |
|