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?

0

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.
  • thanks for you answer but that is a "normal" tuple or what? i.e. does it occupy more disk space than a successful insert? there's no way that if INSERTs were successful the occupied disk space would fill 20Gb overnight, we're talking of some thousands entry at most (with no blobs or free text fields) – Alienpenguin Sep 17 '14 at 8:54
  • it's a normal tuple but not visible, it's for supporting postgres mvcc postgresql.org/docs/current/static/mvcc-intro.html it should occupy the same disk space as the normal successful insert, please note that it also increase index size. Is the 20 GB is the table and indexes size or PGDATA size? If it's the table size, i can't figure out why it occupy more than it should be. – Soni Harriz Sep 17 '14 at 9:19
  • I use the following query to stat the size of the relations: SELECT nspname || '.' || relname AS "relation", pg_size_pretty(pg_relation_size(C.oid)) AS "size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN ('pg_catalog', 'information_schema') ORDER BY pg_relation_size(C.oid) DESC LIMIT 10; – Alienpenguin Sep 17 '14 at 10:42
  • Besides, i'll also add that i am monitoring the platform and since i last reclaimed disk space there have been a constant report of fk errors on that table but the table size is not increasing in a noticeable rate (let alone fill 20Gb in 8-12hrs or so). So, might it be something else? – Alienpenguin Sep 17 '14 at 10:45
  • Yes, that's the table size reported by the query. Then, there's definitely something else happened. It could be Your autovacuum busy on vacuuming another big table and let Your table-in-question unvacuumed and occupy more disk space. It could also be your autovacuum busy on freezing tuples postgresql.org/docs/current/static/routine-vacuuming.html – Soni Harriz Sep 17 '14 at 11:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.