On 4/16/2014 2:40 PM, Robert DiFalco wrote:
Thanks Roxanne, I suppose when it comes down to it -- for the current use cases and data size -- my only concern is the "calling" query that will need to use max to determine if a user has already had a call today. For a large user set, for each user I would either have to MAX on the answered timestamp to compare it against today or do an exist query to see if any timestamp for that user is greater or equal than "today".
I didn't go back to look at your original schema- but.. if your 500K records are coming in time ordered... You may be able to track "max" as an attribute on an "SCD" based on the caller/callee table [or the caller/ee table itself if that table is only used by your app] with an update from a post-insert trigger on the appropriate table. Even if they aren't time ordered, you add the overhead of a single comparative in the trigger. Downside is that you fire a trigger and an update for every insert. [or just an update depending on what is driving your load of the 500K records]
Again - the proof on "value" of this overhead is a comparison of the cost for the updates vs the cost on the query to find max() I suspect your once a day query can afford all sorts of other optimizations that are "better" than a trigger fired on every insert. [such as the function index - that was already mentioned] I really suspect you just don't have enough load on the query side (complex queries * # of users) to justify the extra load on the write side (+1 trigger, +1 update / insert) to avoid a (potentially) heavy query load 1x/day.
Another option... if only worried about "today".. then keep only "today's" data in your query table, and migrate historical data nightly to a pseudo archive table for those "every once in a while" questions. I haven't played with table inheritance in Postgres - but that's a capability I might look at if I were doing a pseudo archive table.
Roxanne -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
