title: High number of live/dead tuples in postgresql/ Vacuum not working
body: <p>There is a table , which has 200 rows . But number of live tuples showing there is more than that (around 60K) .</p>
  
  <pre><code>select count(*) from subscriber_offset_manager;
   count 
  -------
     200
  (1 row)
  
  
   SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables  where relname='subscriber_offset_manager' ORDER BY n_dead_tup
  ;
   schemaname |          relname          | n_live_tup | n_dead_tup 
  ------------+---------------------------+------------+------------
   public     | subscriber_offset_manager |      61453 |          5
  (1 row)
  </code></pre>
  
  <p>But as seen from pg_stat_activity and pg_locks , we are not able to track any open connection .</p>
  
  <pre><code>SELECT query, state,locktype,mode
  FROM pg_locks
  JOIN pg_stat_activity
    USING (pid)
  WHERE relation::regclass = 'subscriber_offset_manager'::regclass
    ;
   query | state | locktype | mode 
  -------+-------+----------+------
  (0 rows)
  </code></pre>
  
  <p>I also tried  full vacuum on this table  , Below are results : </p>
  
  <ul>
  <li>All the times no rows are removed </li>
  <li>some times all the live tuples become dead tuples . </li>
  </ul>
  
  <p>Here is output .</p>
  
  <pre><code>vacuum FULL VERBOSE ANALYZE subscriber_offset_manager;
  INFO:  vacuuming "public.subscriber_offset_manager"
  INFO:  "subscriber_offset_manager": found 0 removable, 67920 nonremovable row versions in 714 pages
  DETAIL:  67720 dead row versions cannot be removed yet.
  CPU 0.01s/0.06u sec elapsed 0.13 sec.
  INFO:  analyzing "public.subscriber_offset_manager"
  INFO:  "subscriber_offset_manager": scanned 710 of 710 pages, containing 200 live rows and 67720 dead rows; 200 rows in sample, 200 estimated total rows
  VACUUM
  
   SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables  where relname='subscriber_offset_manager' ORDER BY n_dead_tup
  ;
   schemaname |          relname          | n_live_tup | n_dead_tup 
  ------------+---------------------------+------------+------------
   public     | subscriber_offset_manager |        200 |      67749
  </code></pre>
  
  <p>and after 10 sec </p>
  
  <pre><code>SELECT schemaname,relname,n_live_tup,n_dead_tup FROM pg_stat_user_tables  where relname='subscriber_offset_manager' ORDER BY n_dead_tup
  ;
   schemaname |          relname          | n_live_tup | n_dead_tup 
  ------------+---------------------------+------------+------------
   public     | subscriber_offset_manager |      68325 |        132
  </code></pre>
  
  <p><strong>How Our App query to this table .</strong> </p>
  
  <ul>
  <li><p>Our application generally select some rows and based on some business calculation, update the row  . </p>
  
  <p><strong>select  query</strong> --  select based on some id </p>
  
  <p>select * from subscriber_offset_manager where shard_id=1 ;</p>
  
  <p><strong>update query</strong> --  update some other column  for this selected shard id</p></li>
  <li><p>around 20 threads do this in parallel  and One thread works on only one row .</p></li>
  <li>app is writen in java and we are using hibernate to do db operations . </li>
  <li>Postgresql version is 9.3.24</li>
  </ul>
  
  <p><strong>One more interesting observation :</strong> 
   -  when i stop my java app and then do full vacuum , it works fine (number of rows and live tuples become equal). So there is something wrong if we select and update continuously from java app . – </p>
  
  <p><strong>Problem/Issue</strong> </p>
  
  <p>These live tuples some times go to dead tuples and after some times again comes to live . </p>
  
  <p>Due to above behaviour select from the table taking time and increasing load on server as lots of live/deadtuples are there ..</p>