Comments (6)
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.
from audit-trigger.
The index on such a column would be quite large I suppose.
I would see this as an option when calling audit_table.
from audit-trigger.
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.
from audit-trigger.
wow, thanks a lot for this great feedback.
I didn't know you could index only a single column of a hstore.
from audit-trigger.
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.
from audit-trigger.
anyway, I might also try your jsonb version. thanks again.
from audit-trigger.
Related Issues (20)
- audit_table(regclass) function chokes in v9.2 after running audit.sql to set up HOT 6
- How do I use to audit tables in different schemas ? HOT 5
- Adding trigger to user table HOT 1
- Function error when inserting new row. HOT 1
- Query to display the data
- The logged_actions table is not updating HOT 2
- Audit failing with ERROR out of memory HOT 2
- Cannot add tables with CamelCase HOT 2
- auditing changes to tables with hstore columns is not working HOT 1
- storing TG_RELID (oid) is not "portable" across backup/restore HOT 1
- How to store UserName who is Updating the value of a table For Update Trigger?
- Enhancement: View displaying list of tables/schemas for which auditing is enabled HOT 1
- Convert to extension
- Statement only trigger not consistent with ORMs HOT 1
- Is it possible to log variables used in "where" clause? HOT 2
- Recommended security configuration question
- Why do comments say the transaction id "May wrap, but unique paired with action_tstamp_tx" HOT 3
- make CREATE INDEX idempotent
- Normalize client query-level columns to avoid redundancy HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from audit-trigger.