21.5. Support for historical information and temporal queries

Conventional databases store a snapshot of the domain model. Any change at the time t of some object leads to the unavailability of the state of this object at the previous time. The most interesting thing is that, in fact, in most developed DBMS, the previous state of the object is stored in the change log, but there is no possibility of access by the user.

Of course, you can explicitly enter an explicit temporal attribute into the stored relationships and maintain its values ​​at the application level. Moreover, in most cases they do. Not without reason in SQL standard special data types date and time appeared. But there are several flaws in this approach: the DBMS does not know the semantics of the time field of a relationship and cannot control the correctness of its values; additional storage redundancy appears (the previous state of the data object is stored both in the main database and in the change log); Relational DBMS query languages ​​are not suitable for working with time.

There is a separate line of research and development in the field of temporal databases. This area explores data modeling issues, query languages, data organization in external memory, etc. The main thesis of temporal systems is that for any data object created at time t1 and destroyed at time t2, all its states in the time interval [t1, t2] are saved (and are available to users) in the database.

Studies and prototypes of temporal DBMS are usually performed on the basis of some relational DBMS. As in the case of deductive databases, a temporal DBMS is a superstructure above the relational system. Of course, this is not the best way to implement from the point of view of efficiency, but it is simple and allows you to perform quite deep research.

An example of a cardinal (but maybe premature) solution to the problem of temporal databases is Postgres. This system was designed and developed by M. Stonebreaker for research and training of students at the University of Berkeley, and he fearlessly went in it for the most daring experiments.

The main features of the memory management system in Postgres are, firstly, that it does not keep the usual journaling of database changes and instantly ensures the correct state of the database after the system is recalled, with the loss of RAM state. Secondly, the memory management system supports historical data. Requests can contain temporary characteristics of objects of interest. Implementing these two aspects are related.

The main solution is that with modifications of a tuple, changes are made not at the place of its storage, but a new entry is made where the modified fields are placed. This record contains, in addition, data characterizing the transaction that made the changes (including the time of its completion), and is stitched to the list to the changed tuple. The system supports unique identification of transactions and there is a special transaction table stored in stable memory. Thus, after failures, you simply should not pay attention to the tail records of the lists related to the unfinished transaction. Synchronization is supported based on the usual two-phase capture protocol.

A separate component of the system archives database objects. He assembles the expanded lists of changed tuples and writes them into the archive storage area. Requests can also be addressed to this area, but only for reading.

The system is focused on the use of optical discs with one-time recording and stable RAM (at least a small amount). In the presence of such technical means, it benefits in efficiency even when operating in the traditional mode compared to the logging scheme. However, it is possible to work on traditional equipment, then the efficiency of the system is slightly inferior to traditional schemes.

The corresponding possibilities of working with historical data are embedded in the Postquel language (and this is its main difference from the latest versions of Quel). Possible selection of information stored in the database at a specified time, in the specified time interval, etc. In addition, it is possible to create versions of relationships and allow their subsequent modification to reflect changes in the main options.