Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a situation where I will have one web session per row, but this session will generate dozens of updates to an hstore field in that row, one k/v at a time. At the end, I will have a completed structure. The keys will be more or less the same per session, but will evolve over time. The values will be mixed, with some being unique per session (e.g. email) and some having lots of repetition (e.g. male/female).

I am running under the assumption that lock contention will only be present at the row level, which is fine. What other gotchas should I look out for? I have zero experience with hstore, so I really want to make sure I understand what I am in for. Any feedback is appreciated.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

With lots of UPDATEs, each one, no matter how small, will cause the entire row to be rewritten into a new version, as a consequence of the MVCC mechanism.
Then the old version of the row will be picked up at some point by autovacuum when it's certain that no transaction may need it, and its space will be flagged as reusable .
The constant turnover of disk space for old and new rows leads to fragmentation, especially if the rows are large in size.
Additionally, the whole set of operations are logged into the WAL files (unless the table is unlogged).

For these reasons, high frequency UPDATEs of large columns is a worst case scenario for postgres.

So, if that session data doesn't really require durable storage in the first place, a mixed disk-memory specialized key/store engine like Redis is likely to perform way better.

Otherwise, this other question: What fillfactor for caching table? has good information and advice on how to mitigate the difficulties.

share|improve this answer
    
Great response. Based on what I have learned here I will use a different store until the session is finished, at which time I will then store in hstore (updates should largely cease at that point). Thanks!! –  Joel Clark Nov 20 '13 at 22:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.