Cleaning Up PostgreSQL Bloat

gbloat
pp_bloat
objectname                          | objecttype | dead_tuples | free_space | free_percent
index_guests_on_state | index | 0 bytes | 9460 MB | 60.04
# SELECT pg_get_indexdef('index_guests_on_state'::regclass);
pg_get_indexdef
------------------------------------------------------------------
CREATE INDEX index_guests_on_state ON guests USING btree (state)
CREATE INDEX CONCURRENTLY index_guests_on_state_new ON guests USING btree (state);
ANALYZE guests;
DROP INDEX CONCURRENTLY index_guests_on_state;
ANALYZE guests;
ALTER INDEX index_guests_on_state_new RENAME TO index_guests_on_state;
postgres@production=# CREATE UNIQUE INDEX CONCURRENTLY email_records_pkey_new ON public.email_records USING btree (id);
CREATE INDEX
Time: 2794263.993 ms
postgres@production=# ANALYZE public.email_records;
ANALYZE
Time: 1658.590 ms
postgres@production=# ALTER TABLE public.email_records DROP CONSTRAINT email_records_pkey;
ALTER TABLE
Time: 43.774 ms
postgres@production=# ANALYZE public.email_records;
ANALYZE
Time: 1518.819 ms
postgres@production=# ALTER INDEX public.email_records_pkey_new RENAME TO email_records_pkey;
ALTER INDEX
Time: 0.586 ms
postgres@production=# ALTER TABLE public.email_records ADD PRIMARY KEY USING INDEX email_records_pkey;
ALTER TABLE
Time: 13.219 ms
postgres@production=# ANALYZE public.email_records;
ANALYZE
Time: 1611.376 ms

--

--

--

Database Admin w/ OmniTI & Player of Way Too Many Video Games

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Keith Fiske

Keith Fiske

Database Admin w/ OmniTI & Player of Way Too Many Video Games

More from Medium

Super-Linter, what is it? and how can I use it in my CI CD process in GitHub

GIT — The Distributed Version Control System

MiniKube

How to recover a MySQL database with .frm and .ibd files