maxtepkeev
commented
8 years ago Great question.
When a new record is inserted into a table, partitioned by architect, it is actually inserted into child and parent table at the same time, but immediately deleted by parentTableName_delete_master() trigger function, that is where this bloat on parent table comes from.
Now you are probably wondering why this happens, because in an ideal world a record should be really inserted only into the child table. This happens because parentTableName_insert_child() trigger function generated by architect finishes with RETURN NEW; instead of RETURN NULL; statement which makes PostgreSQL to insert a new record into parent table as well.
Now the final question is why architect is doing that. Well, because otherwise ORMs (at least the Django ORM) just don't work, because they expect a record to be returned after an insert statement has been issued to the database. This is a legacy code from django-db-parti package and there is a record in my TODO list to check if it's still an issue for other ORMs and the latest Django versions but I just currently don't have much time to do that ;(
I hope I answered your question, if not feel free to ask more. By the way there is a tool called pg_repack it is a successor to a pg_reorg tool and it's now in active development so it's worth trying.
ramusus
I have a high loaded postgres database - a lot of inserts and updates on partitioned table. I faced with bloat problem and as solution I'm using pg_reorg tool. it's working perfectly with any child separately and parent. But I found that parent table get bloated as well as children without any row inside. What the reason of it? For my opinion it should be empty, because all data is inserting into children tables.