Good day. It seems that we have some strange case of VACUUM malfunction and table bloating.
PostgreSQL 9.5.3
OS #uname -a
FreeBSD db-host 10.2-RELEASE-p18 FreeBSD 10.2-RELEASE-p18 #0: Sat May 28 08:53:43 UTC 2016 root@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx:/usr/obj/usr/src/sys/GENERIC amd64
General info about our database:
https://gist.github.com/aCLr/dec78ab031749e517550ac11f8233f70
Information about problematic tables:
https://gist.github.com/tarabanton/edf7f8dc26eb7ec37a9cfa3424493871
As you can see, there 2 to 10 times dead tuples compared to actual row count.
We've tried VACUUM FULL and CLUSTER without any success.
There is no long term locks, idle in transaction requests or prepared transactions.
We are temporarily fixing this like that:
PostgreSQL 9.5.3
OS #uname -a
FreeBSD db-host 10.2-RELEASE-p18 FreeBSD 10.2-RELEASE-p18 #0: Sat May 28 08:53:43 UTC 2016 root@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx:/usr/obj/usr/src/sys/GENERIC amd64
General info about our database:
https://gist.github.com/aCLr/dec78ab031749e517550ac11f8233f70
Information about problematic tables:
https://gist.github.com/tarabanton/edf7f8dc26eb7ec37a9cfa3424493871
As you can see, there 2 to 10 times dead tuples compared to actual row count.
We've tried VACUUM FULL and CLUSTER without any success.
There is no long term locks, idle in transaction requests or prepared transactions.
We are temporarily fixing this like that:
BEGIN;
CREATE TABLE _orders_temp (LIKE orders_y2017_m3 INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING COMMENTS);
INSERT INTO _orders_temp select * from orders_y2017_m3;
ALTER TABLE orders_y2017_m3 RENAME TO __orders_y2017_m3_to_drop;
ALTER TABLE __orders_y2017_m3_to_drop NO INHERIT orders;
ALTER TABLE _orders_temp RENAME TO orders_y2017_m3;
ALTER TABLE orders_y2017_m3 INHERIT orders;
COMMIT;
but bloat returns again and again
CREATE TABLE _orders_temp (LIKE orders_y2017_m3 INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING COMMENTS);
INSERT INTO _orders_temp select * from orders_y2017_m3;
ALTER TABLE orders_y2017_m3 RENAME TO __orders_y2017_m3_to_drop;
ALTER TABLE __orders_y2017_m3_to_drop NO INHERIT orders;
ALTER TABLE _orders_temp RENAME TO orders_y2017_m3;
ALTER TABLE orders_y2017_m3 INHERIT orders;
COMMIT;
but bloat returns again and again
--
___________________________
С наилучшими пожеланиями,
Антон Тарабрин
With best regards,
Anton Tарабрин
