3

It is my first question on Database Administration Stack Exchange. Please be kind if something is not done the nice way..

I would like to be advised concerning the way I'm working on my database, in order to make it as ACID as possible.

I am working on a Web Application using Flask Framework (Python) and a PostgreSQL (9.4) database. I am using SQLAlchemy as ORM.

I am doing Financial Analysis on Stocks handled by my Hedge Fund. My work consists in 3 parts:

  • updating the database daily
  • doing animal mathematics on it
  • looking for best results
  • presenting the results on the web app

Here is my database:

security table (the column active only means whether the stock should be updated or not):

 id |   ticker       | active 
----+----------------+--------
  1 | 3993 HK Equity | t      
  2 | A2A IM Equity  | t      
  3 | AA UN Equity   | f      
  4 | AA/ LN Equity  | f      
  5 | AAL LN Equity  | t      
  6 | AALB NA Equity | t      

valuation table

 security_id |    date    | px_close |  px_open |  px_high |  px_low | volume 
-------------+------------+----------+----------+----------+---------+--------
          42 | 2012-11-06 |   44.757 |   45.099 |   45.336 |  43.918 | 157295 
          42 | 2012-11-07 |   45.099 |   44.791 |   45.099 |  44.226 | 162671 
          42 | 2012-11-08 |   44.245 |   45.051 |    45.08 |  44.241 |  56763 
          42 | 2012-11-09 |   43.994 |   44.307 |   44.672 |  43.771 | 123424 
          43 | 2012-11-06 |  143.994 |  144.255 |  144.601 | 143.785 |  54598 
          43 | 2012-11-07 |  143.885 |  143.629 |  144.108 | 143.524 |  39380 
          43 | 2012-11-08 |  144.392 |  144.056 |  144.928 | 143.866 |  60509 
          43 | 2012-11-09 |  144.008 |  144.563 |   144.61 | 143.913 |  62784 

regression table (I have a lot of other tables like this one, e.g polynomial_regression, learning_clustering, multiple_regression, etc. but it is for the example):

 security_id | driver_id | security_field | driver_field | lag |     percentile   |      percentage    |      1w_change     |        r2
-------------+-----------+----------------+--------------+-----+------------------+--------------------+--------------------+-------------------
       10260 |       622 | px_open        | px_low       |   0 | 2.39085239085239 |  0.233283156908672 |  -1.65479076267458 |  0.76137019793836
       10260 |       622 | px_open        | best_eps     |   0 | 25.8333333333333 |  0.070876900435926 |   4.62480252764613 | 0.593456515505473
       10260 |       622 | px_high        | px_close     |   0 | 2.18295218295218 |  0.211301222947739 |  -7.67181960128018 | 0.756561105159073
       10260 |       622 | px_high        | px_open      |   0 | 3.01455301455301 |  0.173091398167856 |  -2.06843453731421 | 0.755248026072118
       10260 |       622 | px_high        | px_high      |   0 |  2.5987525987526 |  0.189682461742673 |  -10.5755212601686 | 0.748422363395702
       10260 |       622 | px_high        | px_low       |   0 |  2.5987525987526 |  0.206663840524837 |  -3.72904823112292 | 0.758691421914131
       10260 |       622 | px_high        | best_eps     |   0 | 26.7857142857143 | 0.0642618785351929 |  0.600880162491535 | 0.586932757156747
       10260 |       622 | px_low         | px_close     |   0 | 2.18295218295218 |  0.241074941757577 |  -3.72991088758724 | 0.758602680862882
       10260 |       622 | px_low         | px_open      |   0 | 3.01455301455301 |  0.193588738372212 | -0.616152379637857 | 0.753488068588711
       10260 |       622 | px_low         | px_high      |   0 | 2.80665280665281 |   0.21139288442443 |  -4.97343018089906 | 0.743185969928886

Main of the operations done on the database are made via the Flask App, but not always. It's why I would like to have my database as 'strong' as possible (I mean here ACID). What's more, I obviously trust more Postgres that Flask.

The update of my database is made everyday at 3am. New values of the day before are inserted, and new active stocks are updated from the beginning of the times (and other stocks, e.g the ones just after dividends, etc.).

Then, 2h later, when the update is done, a Flask script is computing all the new regressions pairwise, and inserting the results. The thing is that between the update of the values, and the update of the regressions, the database is not in a 'stable' state: new values have been inserted, but the corresponding regressions doesn't exist yet.

Would you think I should translate all my mathematical analysis from Python to SQL, and create Triggers launching it whenever a new value is inserted?

  • My problem is that I don't know the 'speed' of SQL when it deals with stats and maths, compared to other language. I know that basic functions (correlation, R^2, ...) are already implemented in SQL, but I am using far more 'advanced' (I mean 'complex' here..) functions (even if I have not represented it here).
  • If you think it is not a good idea, would you have other tips I could use to stress the 'well-behaviour' of my database, despite of using external tools as the Python App?

Thank you very much for your advises, Edouard

  • 1
    "Financial Analysis on Stocks handled by my Hedge Fund". This doesn't sound like an ideal starter project. Pl/PgSQL is slow at maths, I wouldn't recommend that. But you shouldn't need PL/PgSQL; I'd just implement the maths as a bulk update SQL query. – Craig Ringer Mar 14 '16 at 5:55
  • @CraigRinger 'as a bulk update': do you mean that you are using other storing systems as pickle, or a noSQL database? How do you manage to keep a good 'organisation' of your results this way? – Edouard Berthe Mar 14 '16 at 5:59
  • No, I mean that where people tend to use triggers, individual queries, etc, it can often instead be better to do SQL operations that affect many rows at once - that usually performs best, and can be simpler to reason about when it comes to transaction logic etc. Your description is really too general for me to comment usefully beyond that - no code, no description of steps taken, no schema, etc. – Craig Ringer Mar 14 '16 at 6:04
  • 1
    If you have the logic in Python, you could write stored functions in PL/python and then call them from SQL. – a_horse_with_no_name Nov 27 '16 at 20:11
3

Welcome to DBA Stack Exchange!

My problem is that I don't know the 'speed' of SQL when it deals with stats and maths, compared to other language. I know that basic functions (correlation, R^2, ...) are already implemented in SQL, but I am using far more 'advanced' (I mean 'complex' here..) functions (even if I have not represented it here).

As a rule of thumb, for basic aggregations (Grouping, Joining, Summation) on large datasets (millions of rows) SQL will perform better. I'd recommend you leverage SQL's strengths here use a hybrid approach. Prep your data with basic aggregations as much as possible, but leave the higher-level mathematics and analysis logic in Python.

Would you think I should translate all my mathematical analysis from Python to SQL, and create Triggers launching it whenever a new value is inserted?

No.

As stated before, you can do basic aggregate functions, but I expect you will find it tedious if not outright impossible to model the more 'complex' statistical function from a feature-rich language such as Python into a constrained language such as SQL. Here is a decent article exploring this concept. Even if we are successful translating Python functions into SQL, our success is going to highly dependent on whether the database is optimized to deliver a set-based representation of all the data inputs needed for those calculations at the time of insert.

The thing is that between the update of the values, and the update of the regressions, the database is not in a 'stable' state: new values have been inserted, but the corresponding regressions doesn't exist yet.

Let's be sure we aren't dogmatically promoting database ACIDity at the potential cost of performance, and the assured cost associated with increased code complexity. I DO understand the importance of ACIDity, but from your description this sound like an analytic workflow to me. We have a 3AM bulk load of data, which is subsequently analyzed outside of SQL, and then re-inserted into the database.

To summarize.. YEs, SQL will perform better on basic aggregations across large datasets, but higher-level statistical analysis is best left to Python. I'm not sure it's worth the risk translating Python functions into SQL triggers for the sake of ACIDity.

P.S. I know this is a late answer to your question, so let us know what you eventually decided to implement!

  • 1
    Another option would be to write stored functions in Postgres using Python – a_horse_with_no_name Nov 27 '16 at 20:12

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.