large table

From: Luke Coldiron <lukecoldiron(at)hotmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: large table
Date: 2014-09-22 18:17:05
Message-ID: BAY179-W6539997AAF631923CBB501C6B30@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am trying to figure out what would have caused a table in a PostgreSQL 8.4.16 to get into a state where there is only 1 live tuple and has only ever had one 1 tuple but the size of the table is huge.

CREATE TABLE public.myTable( myColumn timestamp with time zone NOT NULL);

Note: there is no primary key or index on this table.
CREATE OR REPLACE FUNCTION public.myFunc()RETURNS VOID AS $$BEGIN UPDATE public.myTable SET myColumn = CLOCK_TIMESTAMP();
IF NOT FOUND THEN INSERT INTO public.myTable(myColumn) VALUES (CLOCK_TIMESTAMP()); END IF;END;$$ LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER;
CREATE OR REPLACE FUNCTION public.wasUpdated(OUT was_updated boolean)RETURNS BOOLEAN AS $$BEGIN was_updated := COALESCE((SELECT myColumn FROM public.myTable) > (CLOCK_TIMESTAMP() - INTERVAL '5 SECOND'), FALSE);END;$$ LANGUAGE plpgsql VOLATILE STRICT SECURITY DEFINER;
SELECT *FROM pg_stat_all_tablesWHERE relname = 'myTable';



















relid
schemaname
relname
seq_scan
seq_tup_read
idx_scan
idx_tup_fetch
n_tup_ins
n_tup_upd
n_tup_del
n_tup_hot_upd
n_live_tup
n_dead_tup
last_vacuum
last_autovacuum
last_analyze
last_autoanalyze


16713
public
myTable
3991833
3992001


0
3775409
0
3771173
949135
183

2014-09-18
11:28:47.63545+00

2014-09-18
11:27:47.134432+00

The stats are very far off with n_live_tup at 949135 when there is only a single row in the table. Autovacuum appears to be running on a regular basis.
SELECT *
FROM pgstattuple('public.myTable');











table_len
tuple_count
tuple_len
tuple_percent
dead_tuple_count
dead_tuple_len
dead_tuple_percent
free_space
free_precent


34709504
1
32
0
105
3360
0.01
30757308
88.61

The actual size of the table is around 33 MB.
The myFunc function is called every 2.5 seconds and the wasUpdated function every 2 seconds by separate processes.
I realize that running a FULL VACUUM or CLUSTER command on the table will resolve the issue but I am more interested in a root cause that explains why this table would end up in this state. I have tried to reproduce this issue by running the exact setup and have not been able to get the table to grow like this example. Any plausible cause'es or explanations would be much appreciated.
Luke

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alan Hodgson 2014-09-22 18:34:45 Re: large table
Previous Message Tom Lane 2014-09-22 17:22:25 Re: ALTER TEXT field to VARCHAR(1024)