[HACKERS] System catalog bloat removing safety
Hi all, How safe is it to use the technique described by the link below with system catalog tables to remove bloat? (in a couple of words it is about moving tuples to the beginning of the table with a special way of updating) http://www.depesz.com/2010/10/17/reduce-bloat-of-table-without-longexclusive-locks/ Are there any caveats? Thank you. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] System catalog bloat removing safety
Hi, On 2013-09-17 23:12:24 -0700, Sergey Konoplev wrote: How safe is it to use the technique described by the link below with system catalog tables to remove bloat? (in a couple of words it is about moving tuples to the beginning of the table with a special way of updating) http://www.depesz.com/2010/10/17/reduce-bloat-of-table-without-longexclusive-locks/ Are there any caveats? At least for pg_index it's not safe, we use the xmin of rows when indcheckxmin = true. I am not aware of other cases, but I'd be hesitant to do so. You have bloat because of lots of temporary tables IIRC? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] System catalog bloat removing safety
On Wed, Sep 18, 2013 at 2:06 AM, Andres Freund and...@2ndquadrant.com wrote: On 2013-09-17 23:12:24 -0700, Sergey Konoplev wrote: How safe is it to use the technique described by the link below with system catalog tables to remove bloat? (in a couple of words it is about moving tuples to the beginning of the table with a special way of updating) http://www.depesz.com/2010/10/17/reduce-bloat-of-table-without-longexclusive-locks/ At least for pg_index it's not safe, we use the xmin of rows when indcheckxmin = true. I am not aware of other cases, but I'd be hesitant to do so. Thank you, Andres, I did not know this. Just wondering, why it was made of type bool and not xid? You have bloat because of lots of temporary tables IIRC? Actually I am developing a bloat removing tool based on the updating technique: https://code.google.com/p/pgtoolkit/source/browse/trunk/README Now the --system-catalog feature marked as experimental. About pg_index - I think the solution is to exclude it from processing strictly. Anyone has other ideas of what system tables it might be affected except pg_index? -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
