Could someone explain this behavior to me? I ran the following query on Postgres 9.3 running natively on OS X. I was trying to simulate some behavior where the index size could grow much larger than the table size, and instead found something even more bizarre.
CREATE TABLE test(id int); CREATE INDEX test_idx ON test(id); CREATE FUNCTION test_index(batch_size integer, total_batches integer) RETURNS void AS $$ DECLARE current_id integer := 1; BEGIN FOR i IN 1..total_batches LOOP INSERT INTO test VALUES (current_id); FOR j IN 1..batch_size LOOP UPDATE test SET id = current_id + 1 WHERE id = current_id; current_id := current_id + 1; END LOOP; END LOOP; END; $$ LANGUAGE plpgsql; SELECT test_index(500, 10000);
I let this run for about an hour on my local machine, before I started getting disk issue warnings from OS X. I noticed that Postgres was sucking up about 10MB/s from my local disk, and that the Postgres database was consuming a grand total of 30GB from my machine. I ended up cancelling the query. Regardless, Postgres did not return the disk space to me and I queried the database for usage statistics with the following result:
test=# 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 20; relation | size -------------------------------+------------ public.test | 17 GB public.test_idx | 14 GB
However, selecting from the table yielded no results.
test=# select * from test limit 1; id ---- (0 rows)
Running 10000 batches of 500 is 5,000,000 rows, which should yield a pretty small table/index size (on the scale of MB). I suspect that Postgres is creating a new version of the table/index for each INSERT/UPDATE that's happening with function, but this seems strange. The entire function is run transactionally, and the table was empty to start.
Any thoughts on why I'm seeing this behavior?
Specifically, the two questions I have are: why has this space not yet been reclaimed by the database and the second is why did the database require this much space in the first place? 30GB seems like a lot even when accounting for MVCC