2

Right now I'm working on developing a DB schema for an application which requires versioning several different sets of numerical data which must be joined together for use in views. The application requires that we're able to 'look back' and find the state of what these multiple variables were like at an arbitrary point in time.

I've built up a few view definitions using subqueries to fetch the various inputs along arbitrary join criteria as well as checking the version against a time_created field. This isn't a strict SCD2 implementation, but it's close. (link to Slow Changing Dimensions page in Wikipedia)

You can see a similar ( albeit greatly simplified ) implementation to our schema here: DB Fiddle link. The real schema has 6 subqueries/joins.

This works very well for my purposes, except that when we start scaling it up into the thousands of 'purchase' records we incur a significant query cost. A query against 2000+ rows is taking ~1s, which is unsuitable for our application.

The first solution that springs to mind is creating a table which joins a 'purchase' to it's various versioned input tables, to be created by the application when a purchase is created. This should be much more efficient, but requires more schema complexity.

Can anyone offer me advice on how to best plan my schema/queries so as to be most optimal for this kind of setup?

Admittedly this is a cross-post from SO, but it received zero interest there over 24 hours, so I figured it may be better suited for the DBA stackExchange. Apologies in advance if this causes any offence. The original can be found here.

  • It's great that you pointed back to your StackOverflow post from here (many people think that random cross-posting is OK), but could you also point forward to here from your old SO post - saves any duplication of effort! – Vérace Apr 19 '18 at 3:45
  • 1
    What is SCD2? Any link? – ypercubeᵀᴹ Apr 19 '18 at 8:49
  • 1
    Do you have any indexes on the tables, except the ones added by primary keys? – ypercubeᵀᴹ Apr 19 '18 at 9:00
  • 1
    Thnx! I edited the question (and added a link to the wikipedia page, for those that don't know what it is). – ypercubeᵀᴹ Apr 19 '18 at 21:10
  • 1
    As for the performance, try adding an index on (time_created), on both tax and item_cost tables and see if the index is used in the plan and whether you get better performance. If your query is more complex (6 joins as you say), it would be better to provide it (edit the question and add it). The table definitions as well (either in the fiddle or in the question). – ypercubeᵀᴹ Apr 19 '18 at 21:12
0

I found a somewhat satisfactory solution to this issue. I feel a bit foolish for overlooking this, but you could put it down to a case of 'premature optimization' on my behalf. I had omitted the end_date columns, as per the academic example in Wikipedia, in my SCD tables. I figured this was not particularly necessary since all you need to do to 'rewind' your table and find the correct SCD row is find the first row with a start_date less than or equal to the date you're querying against when the table is sorted. But this requires a table sort to function correctly.

The major bottleneck in my query was caused by sorting a moderately large ( 2500+ row ) table by a timestamp. I managed to resolve this issue by adding an 'end_date' column and changing the query to use ( simplified example ):

LEFT JOIN LATERAL ( SELECT * FROM tax t
  WHERE p.time_created >= t.time_created AND p.time_created <= COALESCE(t.time_ended, '9999-12-30'::date)) current_tax ON true
....

This cuts the query down from ~1sec to <100ms. I tried adding indexes against fields in the 2500+ row table, but this didn't seem to have an enormous effect on the query time, but perhaps I had made some errors there. This seems more optimized at any rate.

An updated DBFiddle can be seen here.

I would love some advice on how to better represent this, or how to better account for null values in the time_ended column.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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