Living Data
This is a novel approach to a database design that is sorely needed. Evolution in data storage has been significant but in many ways fairly the same general approach. Flat files was one of the first ways data was stored. A database was the next evolution. Being able to take advantage of indexing data providing a means to quickly access data without having to seek through its entirety. The purpose of this article is not to take a tour through the history of the database, but please bare with me I assure you its worth its while. There was a time when the DBA was the all powerful ruler of the database and analysts as well as engineers had little control over the schema and the data at all. One major issue when dealing with big data is handling data locality. There are two different types of databases that are often mutually exclusive: OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing).
Let’s say you had a database of books and their respective contents. Now let’s imagine there is a store/library thing maybe somewhere in between Gartner and Safari Online. You have access to millions of resources but there are different packages what you have access to depending on your plan and what resources you have purchased. To access an individual item is fairly simple, the application layer just needs to ensure that you have access to that single resource. However, if you wanted to perform a fulltext search throughout all of the resources you have access to this can be a very complex process. No matter how you perform it there is waste with pagination and caching or time to reprocess the data-set. Theoretically you could write stored procedures for a SQL database and attempt with a single query to access the exact results for the exact page requested. Suffice to say such a store procedure would be awful to engineer, test, and maintain. It is without question that at times creating stored procedures may be the most efficient way to extract data. Very few companies will do this anymore at all. They will move towards large caches and application layers. With the gradual push to doing more and more computation in a serverless manner the latency and processing time grows exponentially. The pros and cons of serverless architecture are outside of the scope of this article, however it is clear that if the serverless process is connecting directly to a database (something I generally advise against) the overhead involved in openning the sockets and connecting to the database puts additional burdens on the database and the network infrastructure. Let’s instead adjust our approach and use a very unconventional approach to truly blur the lines where application and database exist.
PL/Java is a free open-source extension for PostgreSQL that allows stored procedures, triggers, and functions to be written in the Java language and executed in the backend. I’m not suggesting that this is novel or going to solve big problems. This extension will allow engineers to create code that interacts directly with the database that is a great deal more comprehensive and accessible than what any SQL variant will offer. This may have a tad of a performance gain, but ultimately it would minimize the network traffic.
Without knowing the internals of databases as I have spent a nice amount of time understanding how several of the major databases are written and function end-to-end. The one thing you need to know is that the query inputted at some point in time is parsed and that helps the database determine what data needs to be accessed and ultimately what that query plan is. If you are somewhat familiar with the interpreted languages like Python everytime you run an application written in Python the code is converted into the low-level machine code appropriate for the current system. The runtime environment may be able to cache and optimize this overtime and improve performance. A database is similar in many ways as it is really an application (of course). Now imagine that in addition to understanding the query language, like SQL that the database also understood the actual Python of Java code. I don’t just mean that it executes the Python or Java code like in the PL/Java extension where the database has no idea what the extension is doing. Rather, in this case the database would be able to compile the application code down into something that the database can understand just as much as a query.
Here is a free online archive of the LOTR series text. This is an example database schema for handling our sophisticated library system.
Book
* id: bigint
* author: bigint
* pages: int
Author
* id: bigint
* name: varchar(100)
* locationId: bigint
Page
* id: bigint
* bookId: bigint
* chapterId: bigint
* page: int
Chapter
* id: bigint
* bookId: bigint
* index: int
Word
* id: bigint
* word: varchar
* root: bigint
WordRoot
* id: bigint
* text: varchat
WordPage
* id: bigint
* wordId: bigint
* pageId: bigint
* index: int
WordChapter
* id: bigint
* wordId: bigint // Word's Id
* chapterId: bigint // Chapter's Id
* index: bigint // Word's order from beginning of Chapter as origin
Location
* id: bigint
* name: text
* latitude: double
* longitude: double
With the schema present one could find words or their root within a given proximity to each other. I’m going to use the classical fantasy series The Lord of the Rings by J.R.R. Tolkien. Maybe we want to find a conversation with Saruman and Gandalf. Perhaps we hope to accomplish this by looking for the words “Saruman” and “Gandalf” with a maximum number of words distance. For this first example let’s say that the user may choose purchase each chapter of the book on its own, each book of the series on its own, or the entire series at one time. The task of finding the words within the specified proximity isn’t too complex. Keep in mind that the end of sections like with series of asterisks, or a separate chapter should be disqualified from the distance search. Meaning that if the word “Saruman” is found close to the end of chapter 1, and “Gandalf” is at the beginning of chapter 2, even the distance between the two words may be within the specified threshold the result must be excluded because the chapter break clearly separates the context. Let’s look at the simple steps involved here:
- Find all instances of word “Saruman” and “Gandalf” within X words.
- Take all results from #1 and ensure that there are no breaks between the two words.
- Obtain what access the user has granted.
- Filter out any results that the user does not have access to.
- Sort the results by relevance.
- Paginate the results.
Now to write this as a single query will be incredibly complex and very likely subject to errors. Not to add to the complexity, but very often services like this will still like you to know how many results may have been found from items that you may not yet own. This provides an incentive to sell you those items. The type of queries needed here are complex and demanding.
If this task was limited only to the Lord of the Rings series and was a tool for the incredible geek that wants to search through Middle Earth with great preicision, this is not that bad. Truthfully, if this was limited to a three books you might say drop the database and just store the text in memory. Let’s approach it from that perspective for the moment.