Checking for PostgreSQL Bloat

Old Table Bloat:
2. public.group_members.........................................................(9.6%) 4158 MB wasted

Old Index Bloat:
1. public.group_members_id_pk..................................................(19.5%) 4753 MB wasted
3. public.group_members_user_id_idx.............................................(9.6%) 2085 MB wasted
5. public.group_members_deleted_at_idx..........................................(6.2%) 1305 MB wasted
$ pg_bloat_check.py -c "dbname=prod" -t public.group_members

kfiske@prod=# select objectname, pg_size_pretty(size_bytes) as object_size, pg_size_pretty(free_space_bytes) as reusable_space, pg_size_pretty(dead_tuple_size_bytes) dead_tuple_space, free_percent from bloat_stats ;
objectname | object_size | reusable_space | dead_tuple_space | free_percent
-----------------------------------+-------------+----------------+------------------+--------------
group_members | 42 GB | 16 GB | 4209 kB | 37.84
group_members_user_id_idx | 21 GB | 14 GB | 1130 kB | 64.79
group_members_id_pk | 24 GB | 16 GB | 4317 kB | 68.96
group_members_deleted_at_idx | 20 GB | 13 GB | 3025 kB | 63.77
group_members_group_id_user_id_un | 11 GB | 4356 MB | 6576 bytes | 38.06
group_members_group_id_idx | 17 GB | 9951 MB | 0 bytes | 56.8
group_members_updated_at_idx | 15 GB | 7424 MB | 0 bytes | 49.57
kfiske@prod=# CREATE INDEX concurrently ON group_members USING btree (user_id);
CREATE INDEX
Time: 5308849.412 ms

$ pg_bloat_check.py -c "dbname=prod" -t public.group_members

kfiske@prod=# select objectname, pg_size_pretty(size_bytes) as object_size, pg_size_pretty(free_space_bytes) as reusable_space, pg_size_pretty(dead_tuple_size_bytes) dead_tuple_space, free_percent from bloat_stats ;
objectname | object_size | reusable_space | dead_tuple_space | free_percent
-----------------------------------+-------------+----------------+------------------+--------------
group_members | 42 GB | 16 GB | 2954 kB | 37.84
group_members_user_id_idx | 21 GB | 14 GB | 1168 kB | 64.79
group_members_id_pk | 24 GB | 16 GB | 4317 kB | 68.96
group_members_deleted_at_idx | 20 GB | 13 GB | 3025 kB | 63.77
group_members_group_id_user_id_un | 11 GB | 4356 MB | 6784 bytes | 38.06
group_members_group_id_idx | 17 GB | 9951 MB | 0 bytes | 56.8
group_members_updated_at_idx | 15 GB | 7424 MB | 0 bytes | 49.57
group_members_user_id_idx1 | 8319 MB | 817 MB | 336 bytes | 9.83
kfiske@prod=# select objectname, pg_size_pretty(size_bytes) as object_size, pg_size_pretty(free_space_bytes) as reusable_space, pg_size_pretty(dead_tuple_size_bytes) dead_tuple_space, free_percent from bloat_stats ;
objectname | object_size | reusable_space | dead_tuple_space | free_percent
-----------------------------------+-------------+----------------+------------------+--------------
group_members | 25 GB | 27 MB | 79 kB | 0.1
group_members_id_pk | 8319 MB | 818 MB | 0 bytes | 9.83
group_members_user_id_idx | 8319 MB | 818 MB | 0 bytes | 9.83
group_members_deleted_at_idx | 8319 MB | 818 MB | 0 bytes | 9.83
group_members_group_id_user_id_un | 7818 MB | 768 MB | 0 bytes | 9.83
group_members_group_id_idx | 8319 MB | 818 MB | 0 bytes | 9.83
group_members_updated_at_idx | 8318 MB | 818 MB | 0 bytes | 9.83
(7 rows)

Why Bloat Happens

--

--

--

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

From On-Chain to Event Streaming: Getting Started with Aiven’s Apache Kafka

Google Cloud Monitoring: Monitoring and Alerting on number of kubernetes pod replicas in GKE

Scale to 0 with EKS — KNative

Expert recommendations for container adoption