Database Caching Guide

PokerTracker 4 version 4.11.x
June 2, 2014
  • Overview

    A Database Cache is a table of pre-calculated information used to improve database performance, the cache accomplishes this job by storing and retrieving calculated data within a high-performance repository.  If you are not familiar with how a database stores information, it is easy to understand if you visualize a series of spreadsheet tables that are tied together using multiple reference points.  The more data that is stored in each table, the longer it will take to calculate the data contained within the tables.  To solve this speed problem, database designers typically use a Cache of pre-calculated results and temporary storage points to assure a fast response from the database server; the alternative would be to have the database server calculate the results of multiple tables on demand which would require excessive review of every data point within the database query and sub-par performance.  

    In PokerTracker 4 the Database Cache is used to improve the speed of the reports and graphs, but the benefits of the Cache is most obvious to users of the HUD.  If a stat uses columns that are uncached then the HUD updates for that stat will be delayed as new hands are imported; this is because uncached database queries are managed separately from cached queries.

    An uncached column in PokerTracker 4 will be calculated on the fly which will delay the display and update of that stat in the HUD, but the rest of the cached HUD values will appear separately as soon as they're available.  The HUD can load cached and uncached stats separately, but reports and graphs are not loaded in separate stages. An uncached stat will adversely affect the loading times required to generate a report or graph.

  • Stats and Columns

    A stat is a mathematical calculation of data acquired from PokerTracker 4 columns. Most cached stats are calculated by taking the database column that represents the count of an Action taken, divided by the database column that represents the total number of opportunities for this action.  

    Custom Stat designers use the term “Column” for two separate yet related purposes.  PokerTracker 4 columns can exist separately from Database Columns.  PokerTracker 4 Columns can be found within the Columns page in the Statistics Configuration window.  Database columns are part of the database itself, this type of column is only created once a stat has become Cached. 

    Databases are composed of tables, these tables are then composed of database columns, this is very similar to a spreadsheet as we explained earlier in this guide.  Stats are PokerTracker 4 “objects” that do not exist in the database, instead Stats are also composed of  PokerTracker 4 Columns.  This allows multiple stats to use the same building blocks, but these blocks aren't necessarily stored in the database.  When the cache is enabled for a Custom Column, the Database Cache creates new Database Columns that match their respective PokerTracker 4 Columns.

  • PokerTracker 4’s Database Cache

    We will use the 3Bet Preflop stat as an example to explain how the Cache works in PokerTracker 4. The 3Bet Preflop stat is calculated by dividing the number of times that the player 3Bet which is stored in the column named ‘cnt_p_3bet’ by the number of opportunities the player had to 3Bet which is stored in the column ‘cnt_p_3bet_opp’.  We divide the result of this expression by 100 to allow the data to be displayed as a percentage.  

    (cnt_p_3bet / cnt_p_3bet_opp) * 100  =  3Bet Preflop %

    The columns cnt_p_3bet and cnt_p_3bet_opp used in this expression are both cached in the database to allow PostgreSQL to pre-calculate the columns so this can be displayed in the PokerTracker 4 HUD in a fraction of a second. 

    Most PokerTracker 4 columns used by default built-in stats are cached, the exception to this rule are any stats that require post processing are not cached.  For example By Time stats such as Real Hours are not cached, this is the stat commonly used in reports that are grouped By Date.  

    Custom stats that use default columns are typically cached, however custom stats that require the use of custom columns could not be cached prior to the release of PokerTracker 4.11; this is the first version of PT4 that allowed custom PokerTracker 4 columns to be cached in the database.  This new caching feature means that HUD and report performance will be faster than ever before for users who incorporate home made custom stats or free stats taken from the PokerTracker Download Warehouse.  Additionally users of the popular third party Premium HUDs such as CoffeeHUD and ProPokerHUD will see a significant improvement in their in-game experience once they upgrade to 4.11 or greater. 

    Lets look at the example using a popular custom stat from the PokerTracker Download Warehouse called Delayed (Turn) CBet

    (cnt_t_delayed_cbet / cnt_t_delayed_cbet_opp) * 100

    This stat uses two custom columns, cnt_t_delayed_cbet is a measurement of the frequency that the player delayed his or her CBet until the Turn, and cnt_t_delayed_cbet_opp is a measurement of the total number of opportunities that the player had to delay his or her CBet until the turn.  Neither of these columns exist in the default PokerTracker 4 database schema, therefore these columns are uncached in all versions of PokerTracker 4.10.9 or earlier. If however this stat was imported into PokerTracker 4.11 or later, then the columns that are needed to calculate this stat will be cached in the database.  You can verify if a column has been added to the cache by reviewing it’s status in the Statistics Window.  Click Configure > Statistics, then chose between Cash Games or Tournaments, Players or Hands, and then select Columns.  Search for the PokerTracker 4 column you wish to review, and confirm that the Cache check box has been selected.  

    PokerTracker 4 Cache

    Custom Stat developers should be aware that PokerTracker 4 will enable the cache for all  columns that can be cached when importing the custom stat, however the developer must manually enable the Cache checkbox on their own computer when defining new columns. 

    • When a Column Cannot be Cached

      Not every column can be cached in the database.  If a custom stat is created that contains a custom column that cannot be cached then an error message will appear stating that a stat is not cacheable.   PokerTracker 4 will review all custom stats imported and automatically enable the cache if the column is cacheable. 

      When a column is not cacheable there is no notice offered to the user, the error message shown below is intended to help custom stat developers.  Casual importers of pre-defined Custom Stats that include custom columns are expected to rely on the experience of the custom stat developer who is responsible for caching decisions.

      Unable to cache column

    • Cache Exceptions

      The following expressions formatting options are not cacheable:

      (1) Expressions that contain subquerys cannot be cached.  Here's an example expression with a subquery:

      exists (select 1 from cash_hand_player_statistics chps where chps.flg_hero and chps.id_hand = cash_hand_player_statistics.id_hand)

      Subqueries cannot be cached because this type of query ties multiple database columns across different tables together, this would result in massive performance failures in even the smallest databases during import and cache updating that would negatively affect our users’ experience. If subqueries were cached then each time that a cached column that contained a subquery must be updated then the majority of the database would need to be re-read.  This would have to occur for every player in the database, which would result in Cache performance that is too slow to be used in-game while playing.  With a database as small as just 100 players this would require pulling the player statistics table 100 times that each cache update is required at the end of a hand.  Theoretically this subquery may take only a millisecond per player; with 100 players that would add 100ms per hand during import which is a reasonable amount, with 10,000 players that would add 10 seconds per hand during import which is clearly an unreasonable option to consider.  

      PokerTracker 4 columns that contain subqueries are still permitted even though they cannot be cached, but you should expect the performance of any stat that needs a custom column that includes a subquery expression to perform as poorly as it did prior to the release of 4.11.

      (2) A reference to cash_hand_summary can only be cached if there is also a reference to cash_hand_player_statistics.

      (3) A reference to cash_hand_player_combinations can only be cached if there is also a reference to cash_hand_player_statistics.

      (4) A reference to cash_limit can only be cached if there is also a reference to cash_hand_player_statistics.

      (5) A reference to cash_table_session_summary can only be cached if there is also a reference to cash_hand_player_statistics.

      Expression examples (2) - (5) cannot be cached because PokerTracker 4 must retain the ability to break cacheable stats down to each absolute position as well as in/out of position for post flop stats, by stake, and by date. If PokerTracker 4 does not have an explicit player_statistics reference, then the player_statistics table cannot be joined and this join is needed to determine the position stake and date details for the resulting stat.  

      This expression can be cached because it contains both cash_hand_summary and also  cash_hand_player_statistics:

      sum(if[cash_hand_player_statistics.flg_f_saw and cash_hand_summary.amt_mgr > 0, 1, 0])

      This expression cannot be cached because it only contains cash_hand_summary without the necessary cash_hand_player_statistics column:

      sum(if[cash_hand_summary.amt_mgr > 0, 1, 0])

      (6) Any reference to lookup_positions.  The lookup table cannot be used in the custom cache, direct tests on cash_hand_player_statistics.position should be used instead.  PokerTracker 4’s developers are aware that this will require a little extra work to build EP and MP positions, but it is necessary for the stats to be able to work properly.  Other lookup tables are acceptable, most notably lookup_actions_p, lookup_actions_f, lookup_actions_t, and lookup_actions_r.

      (7) Tournament resulting stats such as ROI and ITM cannot be cached because only columns based on hands can be cached.  

      (8) Stats which are only used for grouping purposes to break reports down into multiple rows are not cached, these are commonly referred to as “Group By” stats.  This category of stats are only designed for use in reports, they are not designed to be used within HUDs.