Thread: json datatype and table bloat?

On Tue, Oct 29, 2013 at 5:38 AM, Chris Travers <chris.travers@gmail.com> wrote:
>
>
>
> On Mon, Oct 28, 2013 at 4:17 PM, Gregory Haase <haaseg@onefreevoice.com>
> wrote:
>>
>> I have a table that is triggering my nagios database bloat alert
>> regularly. Usually, I have to give it the vacuum full more than once to get
>> it under the threshold. Today I tried repeatedly and cannot get the alert to
>> resolve.
>>
>> I had a discussion with one of the primary developers about how the table
>> is utilized, and it turns out they are basically only ever inserting into
>> it. This sort of flies in the face of conventional wisdom about bloat being
>> caused by frequent updates and deletes.
>
>
> As I understand it, vacuuming only removes the tuples removed by delete and
> update operations.

well, or by rolled back transactions.  we we have to wonder if OP has
a lot of queries trying to insert and failing.  maybe check the log?

merlin

db_name=# VACUUM FULL VERBOSE table_schema.table_name;

INFO:  vacuuming "table_schema.table_name"

INFO:  "table_name": found 2 removable, 29663 nonremovable row versions in 1754 pages

DETAIL:  0 dead row versions cannot be removed yet.

CPU 0.07s/0.10u sec elapsed 0.30 sec.

-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast

John R Pierce <pierce@hogranch.com> writes:
> On 10/29/2013 12:41 PM, Gregory Haase wrote:
>> db_name=# VACUUM FULL VERBOSE table_schema.table_name;
>> INFO:  vacuuming "table_schema.table_name"
>> INFO:  "table_name": found 2 removable, 29663 nonremovable row
>> versions in 1754 pages
>> DETAIL:  0 dead row versions cannot be removed yet.
>> CPU 0.07s/0.10u sec elapsed 0.30 sec.

> is there an old transaction pending?   that 'masks' vacuum from touching
> any tuples newer than the start of that transaction.

If old transactions were the problem, vacuum would be reporting that
some-large-number of dead row versions couldn't be removed yet.

There doesn't seem to be anything obviously wrong here.

            regards, tom lane

Gregory Haase <haaseg@onefreevoice.com> writes:
> I've isolated the problem to the json field not showing up in pg_stats,
> which affects the calculation of the avg row size in the bloat query.

> I'm not sure if this is a json issue or some other kind of issue.

Possibly your "bloat query" is failing to consider the toast table
associated with this table?  If the json values are large they'd
mostly be in the toast table not the main table.

(It's unfortunate that VACUUM FULL doesn't tell you about what's
in the toast table.  I'd try just VACUUM VERBOSE here, without the
FULL, to get more info.)

            regards, tom lane

Gregory Haase <haaseg@onefreevoice.com> writes:
> The json_data column is not accounted for in pg_stats:

Ah!  I hadn't twigged to the fact that your bloat measurement approach
assumed you had pg_stats entries for all the columns.

> So I'm not sure if I'd actually qualify this as a "bug", but it appears
> that there is no way to currently get stats on a json data type.

ANALYZE currently punts on columns that don't have an equality operator,
which json does not.  There isn't that much in the way of stats that we
could collect, though I suppose we could still compute average datum width
and null fraction.

I'm not sure whether there are plans to invent an equality operator for
json.

            regards, tom lane

On Fri, Nov 1, 2013 at 1:50 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Gregory Haase <haaseg@onefreevoice.com> writes:
>> The json_data column is not accounted for in pg_stats:
>
> Ah!  I hadn't twigged to the fact that your bloat measurement approach
> assumed you had pg_stats entries for all the columns.
>
>> So I'm not sure if I'd actually qualify this as a "bug", but it appears
>> that there is no way to currently get stats on a json data type.
>
> ANALYZE currently punts on columns that don't have an equality operator,
> which json does not.  There isn't that much in the way of stats that we
> could collect, though I suppose we could still compute average datum width
> and null fraction.
>
> I'm not sure whether there are plans to invent an equality operator for
> json.

IMNSO, this may not be a bug, but it's pretty close.  All base types
should have equality operator as well as other supporting
infrastructure that the database itself depends on (in/out and
send/receive for example).  This is a pretty good example of why.

merlin

ajelinek@gmail.com wrote
> Along the lines of the equality operator; I have ran into issues trying to
> pivot a table/result set with a json type due what seemed to be no
> equality
> operator.

For the curious, and also use-case considerations for development, would you
be able to share what it is you are doing (and how) that combines full json
documents with pivoting?

Compound types holding source data for a pivot seems problematic since
generally all the pivot components are single-valued and, for data, often
numerical.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/json-datatype-and-table-bloat-tp5776182p5776880.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Mon, Nov 4, 2013 at 12:14 PM, David Johnston <polobo@yahoo.com> wrote:
> ajelinek@gmail.com wrote
>> Along the lines of the equality operator; I have ran into issues trying to
>> pivot a table/result set with a json type due what seemed to be no
>> equality
>> operator.
>
> For the curious, and also use-case considerations for development, would you
> be able to share what it is you are doing (and how) that combines full json
> documents with pivoting?
>
> Compound types holding source data for a pivot seems problematic since
> generally all the pivot components are single-valued and, for data, often
> numerical.

would also like to see this. json type has completely displaced
crosstab in my usage. I don't typically pivot json though: I pivot the
raw data then transform to json.  With limited exceptions I consider
storing json in actual table rows to be an anti-pattern (but it should
still work if you do it).

merlin

>>> Along the lines of the equality operator; I have ran into issues trying
to
>>> pivot a table/result set with a json type due what seemed to be no
>>> equality
>>> operator.
>>
>> For the curious, and also use-case considerations for development, would
>> you
>> be able to share what it is you are doing (and how) that combines full
>> json
>> documents with pivoting?
>>
>> Compound types holding source data for a pivot seems problematic since
>> generally all the pivot components are single-valued and, for data, often
>> numerical.

>would also like to see this. json type has completely displaced
>crosstab in my usage. I don't typically pivot json though: I pivot the
>raw data then transform to json.  With limited exceptions I consider
>storing json in actual table rows to be an anti-pattern (but it should
>still work if you do it).

I could not figure out what I was doing last month to reproduce this.  So  I
did a small pivot poc, and it is erroring on the max function. So it is
probably not the same issue. My guess is I tried the using the GREATEST
function as a hail marry (which would not have worked) and got the following
message; ERROR:  could not identify a comparison function for type json and
then thought/hopped it was the same thing when reading the emails.

CREATE TABLE bad_table_json(id int, detail_type text, details json);
INSERT INTO bad_table_json values(1, 'a', '{"a":1}'::json);
INSERT INTO bad_table_json values(1, 'b', '{"a":1}'::json);
INSERT INTO bad_table_json values(1, 'c', '{"a":1}'::json);

SELECT id
      ,MAX(CASE WHEN detail_type = 'a' THEN details END) AS a
      ,MAX(CASE WHEN detail_type = 'b' THEN details END) AS b
      ,MAX(CASE WHEN detail_type = 'c' THEN details END) AS c
  FROM bad_table_json
 GROUP BY id



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/json-datatype-and-table-bloat-tp5776182p5776947.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

While I agree that an equality operator doesn't really make sense for json operationally, there are certain maintenance reasons why it may come in handy. Removing duplicate records comes to mind.

Other than adding basic stats to necessary columns, I would say that the equality operator is really one of the most basic tenets of a relational database and should probably exist for any data type - regardless of it's given usefullness.

Greg Haase

On Mon, Nov 4, 2013 at 8:31 PM, ajelinek@gmail.com <ajelinek@gmail.com> wrote:
>>>> Along the lines of the equality operator; I have ran into issues trying
> to
>>>> pivot a table/result set with a json type due what seemed to be no
>>>> equality
>>>> operator.
>>>
>>> For the curious, and also use-case considerations for development, would
>>> you
>>> be able to share what it is you are doing (and how) that combines full
>>> json
>>> documents with pivoting?
>>>
>>> Compound types holding source data for a pivot seems problematic since
>>> generally all the pivot components are single-valued and, for data, often
>>> numerical.
>
>>would also like to see this. json type has completely displaced
>>crosstab in my usage. I don't typically pivot json though: I pivot the
>>raw data then transform to json.  With limited exceptions I consider
>>storing json in actual table rows to be an anti-pattern (but it should
>>still work if you do it).
>
> I could not figure out what I was doing last month to reproduce this.  So  I
> did a small pivot poc, and it is erroring on the max function. So it is
> probably not the same issue. My guess is I tried the using the GREATEST
> function as a hail marry (which would not have worked) and got the following
> message; ERROR:  could not identify a comparison function for type json and
> then thought/hopped it was the same thing when reading the emails.
>
> CREATE TABLE bad_table_json(id int, detail_type text, details json);
> INSERT INTO bad_table_json values(1, 'a', '{"a":1}'::json);
> INSERT INTO bad_table_json values(1, 'b', '{"a":1}'::json);
> INSERT INTO bad_table_json values(1, 'c', '{"a":1}'::json);
>
> SELECT id
>       ,MAX(CASE WHEN detail_type = 'a' THEN details END) AS a
>       ,MAX(CASE WHEN detail_type = 'b' THEN details END) AS b
>       ,MAX(CASE WHEN detail_type = 'c' THEN details END) AS c
>   FROM bad_table_json
>  GROUP BY id

Aside: here's a way to do those type of things.  It's not faster
necessarily but seems cleaner to me.  This will bypass need for json
comparison.  IMMUTABLE plpgsql is generally the fastest way to
implement highly iterated trivial functions.

CREATE OR REPLACE FUNCTION PickInternal(State anyelement, WantValue
TEXT, PickValue TEXT, Value anyelement)
  RETURNS anyelement AS
$$
BEGIN
  RETURN CASE WHEN WantValue = PickValue THEN Value ELSE State END;
END;
$$ LANGUAGE PLPGSQL IMMUTABLE;

CREATE AGGREGATE Pick(TEXT, TEXT, anyelement) (
  SFUNC=PickInternal,
  SType=anyelement);

SELECT id
      ,Pick('a', detail_type, details) AS a
      ,Pick('b', detail_type, details) AS b
      ,Pick('c', detail_type, details) AS c
  FROM bad_table_json
  GROUP BY id;

merlin