Re: Weird disk/table space consumption problem

Dirk Riehle <dirk(at)riehle(dot)org> writes:
> For one analysis, I created a table of about 15 columns, and then ran an
> insert with a subselect on the table, where the select was drawing from
> three other tables, merging over ids. One of the other tables has about
> 60M rows.

> The insert ran for about 18h before running out of disk space. Before
> the query, there had been about 1TB free on the disk.

> After the insert query failed, the disk space was not made available
> again; PostgreSQL did not free it up (or would not free it up quickly).

What that part sounds like is you mistyped the insert such that it
was inserting a huge number of rows. It's not too hard to do if
you get the join condition wrong --- what you meant to be a sane
join can easily turn into a Cartesian product. In theory vacuum
could reclaim the space eventually, but it'd take awhile.

> I rebooted soon thereafter.

> During boot, fsck (must have been fsck) gave me tons of freeing up inode
> messages.

And this part is a filesystem bug; it cannot possibly be Postgres'
fault that the filesystem got corrupted.

regards, tom lane