Postgresql 9.1 failed insert occupies increasing disk space

disk-spacepostgresqlpostgresql-9.1

I have a postgresql 9.1 installation where the available disk space is very low (20Gb) however there is only one client that inserts data in various tables (typically one entry every few minutes) and a few web interface that show inserted data (refreshing every few minutes).
There is an error in the data reported of a specific table that generates a foreign key violation and overnight i find that all disk space is filled up; looking at the relation size i find that all the space is associated to a table and its primary key but no entries are present in that table (see FK error above).
If i reindex the pkey and vacuum table the space is correctly freed.
But i wonder, what is filling up space on error? is there a way to prevent that?

Best Answer

Error on inserting data due to FK constraint violation will leave dead tuple on the target table. That's the nature of Postgres. This dead tuple of course will occupy disk space. Things to prevent this filling up disk space :

  1. A well tuned autovacuum can handle this wasted space.
  2. Do a manual FK constraint check (on application side perhaps) prior to inserting data to the target tables.
Related Question