save primary key in a column

[3nids]

Hi, I am wondering if you would be open to saving the primary key (if it exists) of row-level changes. I think it is quite common to look in the logged_actions for a certain row in particular. That would make the search quite efficient, though not 100% future proof (in case of pkey changes).

What do you think?

[nineinchnick]

You can index the row data column and add a condition for your primary key. You don't need to extract it into a separate column. Also, consider cases when primary key is composite, that is it's more than one column. You'd end up with something similar to row data column.

I don't know exactly how it performs in hstore, but it works fine in my version where I use jsonb instead. See #12.

[3nids]

The index on such a column would be quite large I suppose. I would see this as an option when calling audit_table.

[nineinchnick]

I have an example, I'm using my jsonb version:

• table size is 900MB with 1002888 rows
• row_data index is 245MB
• other indexes are about 50MB
• indexing only the id field in row_data takes only 7,5MB

Full index:

CREATE INDEX logged_actions_row_data_idx
  ON audits.logged_actions
  USING gin
  (row_data jsonb_path_ops)

PK index:

CREATE INDEX logged_actions_row_data_pk_idx
  ON audits.logged_actions
  USING gin
  ((row_data -> 'id'));

The PK index makes sense when you have consistent PK naming.

Now to compare the usage of those two indexes:

select * from audits.logged_actions where row_data @> '{"id": 78}'

'Bitmap Heap Scan on logged_actions  (cost=307.77..4037.14 rows=1003 width=956)'
'  Recheck Cond: (row_data @> '{"id": 78}'::jsonb)'
'  ->  Bitmap Index Scan on logged_actions_row_data_idx  (cost=0.00..307.52 rows=1003 width=0)'
'        Index Cond: (row_data @> '{"id": 78}'::jsonb)'

select * from audits.logged_actions where row_data->'id' @> '78'

'Bitmap Heap Scan on logged_actions  (cost=23.77..3755.65 rows=1003 width=956)'
'  Recheck Cond: ((row_data -> 'id'::text) @> '78'::jsonb)'
'  ->  Bitmap Index Scan on logged_actions_row_data_pk_idx  (cost=0.00..23.52 rows=1003 width=0)'
'        Index Cond: ((row_data -> 'id'::text) @> '78'::jsonb)'

Adding conditions for table names doesn't change the index used.

[3nids]

wow, thanks a lot for this great feedback. I didn't know you could index only a single column of a hstore.

[nineinchnick]

I don't know if it's possible in hstore. I'm only testing jsonb. I just think the numbers should be similar for hstore.

[3nids]

anyway, I might also try your jsonb version. thanks again.