Addressing computational problems in science often involves customized algorithmic approaches, which can lead to overlooking well-established solutions in data management and storage. When scientific datasets grow, these customized approaches may struggle to query data eficiently. Efective data management is essential for ensuring accurate and fast analysis of scientific data. Describing changes in the GeoMine software, this paper highlights the potential for improvements in data-driven science. GeoMine enables spatial-geometric searches in three-dimensional molecular space, facilitating tasks such as pharmaceutical drug discovery by finding similar geometric patterns in protein-ligand complexes. The original GeoMine application utilized a relational database solely for fundamental data storage and combined it with a tailored algorithmic pattern-matching strategy, leaving room for improvements. This work presents a technical overview of database and workflow optimizations in GeoMine to handle the increasing data size. Our improvements focus on moving the main computational tasks from the application level to the database system and optimizing the database utilization. A new query design, better utilization of indexes, and optimizations in textual queries led to a 15x speedup in our experiments, reducing the mean runtime of queries to under 8 seconds. The presented improvements are essential for GeoMine to be ofered as a service-oriented web application. The success of these improvements highlights the significance of database optimization in science, demonstrating the potential and necessity of proper data management.
Mining huge datasets is a central task in research. Analyzing molecular interactions between
proteins and small organic molecules is essential for understanding disease treatments and
advancing medical research. This includes searching for spatial similarities and geometric
arrangements, which can provide vital insights into the functional aspects of proteins. Results
can be used for further research, for example, in pharmaceutical drug discovery or
biotechnology [
GeoMine [
With this work, we investigate the potential of adopting a database-driven architecture, focusing on the database as the main part of query execution and reducing application-side processing. We were able to reduce the mean runtime in our experiments from about 2 minutes per query to less than 8 seconds, utilizing changes in the workflow and database optimizations. As we present in this work, a substantial performance enhancement has been achieved by shifting to a more database-centric method.
The paper is organized as follows: Section 2 provides an overview of the field of work, the data structure, and the query design; Section 3 details the improvements made to the query workflow and database optimizations; Section 4 presents and discusses the experimental results; Section 5 concludes the paper and outlines future work.
Data management in scientific research involves the systematic collection, organization, storage,
and sharing of data to facilitate its reusability and ensure the reproducibility of research findings.
In the context of our work, which focuses on querying structured data sets, the storage aspect
is particularly important. In the scientific domain, many existing applications are designed
for single-user usage, often locally storing data in various formats or utilizing object stores
with limited retrieval possibilities [
Protein-ligand interactions are of particular interest in biomolecular and pharmaceutical
research. Ligands are small molecules that can interact and bind to the generally much larger
proteins. Protein complexes can contain multiple pockets of varying sizes, partly containing
ligands. Drug molecules used as pharmaceuticals are generally designed to target specific
proteins. Researchers can gain valuable insights by investigating specific three-dimensional
structures and searching for potential candidates to bind with these proteins.
Protein Data Bank The PDB [
Discovering similar structures across distinct complexes or finding molecules that bind to a
specific pocket of interest is a major task in medical research. GeoMine is able to construct
comprehensive databases derived from the PDB and supports exploring these databases with a
web-based search interface. [
The preprocessing and database creation procedures employ components of the NAOMI
library [
The central part of the search and unique key feature is the ability to specify geometric
properties, for instance, distances and angles between any points, such as atoms. Further,
point properties can be specified, such as an atom’s chemical element and interactions between
points. This way, precise structural motifs (structural patterns) in protein-ligand complexes
can be searched. While GeoMine’s predecessor PELIKAN was a single-user application based
on an integrated SQLite [
Database Design For our experiments in Section 4, we used a PostgreSQL15 database created with the PDB dataset from October 2022. For querying the dataset, the database can be considered read-only. The database requires approximately 165GB of disk space.
For the geometric search, we focus on two tables. The first table, the point table, comprises all atoms and other definable points, such as the center of aromatic rings. It contains 340,716,693 searchable entries. These points are distributed across 1,382,853 distinct pockets, which serve as containers for groups of points. The largest pocket identified in our dataset contains 20,306 points, while the smallest pocket only holds 9 points. Each entry in the point table has a unique identifier, references the containing pocket, and contains various other fields with properties per point. Some properties, such as the accessible surface area of an atom, are floating point numbers. Other attributes, such as the chemical element, contain only a few distinct values, represented as integers or short strings.
The second table, the interaction table, stores pre-calculated interactions [18]. These interactions represent noteworthy connections between two points, for example, hydrogen bonds. 13,018,225 point pairs are stored here.
Query Creation When creating a query, users can specify multiple constraints. The most fundamental categories encompass Textual and Numerical Searches, wherein metadata filters at the protein structure or pocket level can be defined. Users can directly pre-select several structures or create various filters, such as the minimum number of particular chemical elements or a certain molecular weight range for the ligand. It also enables filtering using patterns that describe a local environment using the chemical substructure language SMARTS strings [19].
The central search element and origin of GeoMine’s name are geometry-based searches. To build the query, users may interactively select points in the web front end [21] (see Figure 1), utilizing an arbitrary PDB file as a template structure or define them without a template.
Users may select an arbitrary number of points, which can be filtered based on diferent properties. Moreover, the specification of distance ranges between two points and angles between specified distances is possible. Further, interactions between points, as stored in the interaction table, can be added to the query. Together they resemble an atomic substructure, which will be searched for. Each pocket can be examined individually as the interactions between one ligand and an individual pocket in a protein are of interest.
Query Execution The initial approach for query execution was first described for the
predecessor tool PELIKAN by Inhester et al. [
structure 1H1S angles for the query
algorithmic focused, with all major computational steps performed within the application
(see Figure 2a), as the original PELIKAN software was designed to be a standalone desktop
application. In the original approach of GeoMine [
This step yields a list of all matching proteins and their pockets. 2. Obtaining all point pairs - For each point pair in the query, all possible results are returned, and distances, as well as interaction constraints, are checked. 3. Clique detection - An algorithm reconstructs the coherent component graph for all obtained point pairs and checks all defined angle constraints. 4. Less restrictive SMARTS filters for points were applied to the now-generated results.
Steps two and three of the query processing presented particular challenges. All point and point pair constraints were queried individually in the database. Since a single constraint for a point pair is often not very specific, it leads to big intermediate results. Only by chaining several constraints the number of points is suficiently reduced. The need to cross-verify each point with all matching points in its pocket demanded significant computational resources, especially if the filter for the points were unspecific. The list of potential pockets needed to be recreated for each pair, as only pockets which contained results in prior pair subqueries remained in the search space. This caused the search to be strictly sequential and required the serialization and deserialization of long pocket-ID lists for the SQL WHERE clauses. As the application and database system are separate processes or running on separate servers, the required repeated transfer of these lists also afected the performance. Because some point-to-point constraints were specific (less frequent in the dataset) and others were unspecific (frequent in the dataset), a hand-crafted scoring function was utilized to estimate the best ordering of queries, starting ...
Clique Detection
& Angle Filter SMARTS
Filter Results
Comprehensive Query point1 JOIN point2 JOIN
point3 JOIN point4 DB SMARTS
Filter
Results
with the most specific queries to reduce the search space early [
Further, an additional algorithm was required since the results from the preceding steps consisted only of point pairs. The Bron-Kerbosch algorithm [22], a graph-based backtracking algorithm for clique detection, was used. This algorithm recursively verified whether all discovered point pairs constituted a complete graph and checked for angle constraints. This demanded substantial computational efort, taking several hours on large potential result sets.
This research aims to achieve optimal performance and ease of setup across various
environments. Alongside the contributions of this work, the application has transitioned to a
containerized setup for cloud environments. The optimizations presented in this work are
essential for facilitating the deployment of a scalable application. In this section, we will distinguish
between the original approach in GeoMine [
The most significant change from the original approach was the redesign of the SQL query generation. Sequential processing of each constraint within a query led to severely limited query-level parallelism and long processing times as described in Section 2.3. Therefore, all SQL queries are now designed to make use of PostgreSQL’s internal planning and optimization. In contrast to the original approach, where each point-to-point constraint was queried separately, a single comprehensive query containing all attributes and constraints for geometrical patterns is now constructed, see Figure 2b. This reduces overhead by eliminating the need to repeatedly serialize extensive lists of pocket IDs or create temporary tables. To achieve this, the point table joins itself as often as points were specified in the query, usually 5-15 times. As a match occurs inside a single pocket, we only need to join points within the same pocket. With information about the distribution of properties like the chemical element, the RDBMS can estimate which part of the query restricts the search space the most and improve the join order. The original approach required running the checks on all points within all remaining pockets, not being able to skip points that were not matched in earlier subqueries. Intermediate results now remain within the database system and do not require serialization for application transfer. Additionally, merging all constraints (points, distances, and interactions) into one query eliminates the need for clique detection, as the output of the RDBMS is a connected and valid result.
Among all the geometric properties, only the angle checking between point pairs remains a separate step in the application, as this increases the complexity of the query without showing the benefits of an early reduced search space in our tests. Textual and numerical filters remain in a separate query to allow prior filtering, as SMARTS patterns require in-application processing. Allowing the RDBMS to determine the join order and the parallel execution resulted in a significant speedup of benchmark queries. The results are detailed in Section 4.
In the original approach, a single extensive index structure was created, covering 15 out of 17 table columns. Although PostgreSQL allows for the construction of multi-column indexes with a large number of attributes, these structures are only efective in certain situations due to their size and depending on the used attributes. However, using multiple single-column indexes and allowing PostgreSQL to combine them as recommended in the documentation [23] did not achieve the desired performance improvement.
Only the combination of several attributes could substantially reduce the number of yielded
points. The best-found solution for our workload was a balanced compromise between index
size and utilization, including only the most frequently used columns in a multi-column index.
We identified two separate cases for index usage. Firstly, the earliest scheduled subquery focused
solely on the attributes, disregarding their pocket, in cases without textual and numerical filters.
Secondly, an index for subsequent subqueries was needed to filter for pocket IDs required for the
join. In almost all instances, the optimizer determined to filter for the pocket ID in the second
subquery. In some instances, a parallel index scan was performed. Filtering by the pocket ID
reduced the search space best in these cases since the most restrictive subquery had already
been executed as the first scheduled subquery. Therefore, we introduced a second index with
the pocket identifier positioned first in the index. For both structures, we utilized PostgreSQL’s
default B-Tree index as other index structures seemed not beneficial in our tests. As pockets
usually contain only a few hundred points, spatial indexes, like r-trees provided by PostGIS [
The initial step of the workflow involves filtering structures based on textual and numerical attributes. These filters target various properties, the most important being the PDB identifiers used to select a pre-defined or user-defined subset of protein structures. A short alphanumeric code identifies each structure.
Previously, an SQL ILIKE (case insensitive match) statement with a wildcard match at the beginning and end of the string was executed to check for the desired properties. For the PDB codes, we could make two changes. We could discard the wildcards in the query unless explicitly desired, which enables the utilization of a search index. And as the codes are not case-sensitive, we can replace the ILIKE with a LIKE, allowing for a case-sensitive search and resulting in a substantial speedup, as demonstrated in Section 4.
To evaluate the impact of each modification suggested for GeoMine, several experiments were derived from the original GeoMine approach ex01 (see Table 1). Experiments ex02 to ex05 each contain only one of the improvements, ex06 contains all improvements, while experiments ex07 to ex10 contain all except one. This way, we show which change impacts the performance most, as diferent improvements benefit from each other.
For evaluating the performance across diferent workloads, we used a set of nine queries
already used in previous work [
We used a PostgreSQL15 database system. All data was stored on an SSD. Unless otherwise specified, a dedicated server with 400GiB RAM and 80 Cores was used (PostgreSQL 128GB sharedbufers , 16 parallel workers). Podman [24] was used to deploy the system. Each experiment was repeated five times. The GeoMine application was executed on the same node as the PostgreSQL database. We configured PostgreSQL to utilize less memory than available, as GeoMine required a high amount of working memory for some workloads. Additionally, we conducted tests on commodity systems by employing two setups (small/medium) using virtual servers. Both setups stored data on SSDs and were equipped with 12 cores and 24GB RAM, resp. 18 cores and 48GB RAM.
Figure 3 shows the mean runtime of the nine test queries for each experiment as depicted in Table 1. Each change led to better performance, with the highest performance gain occurring 609.7s 630.4s 570.3s when all changes were applied together. The required time for performing all nine queries decreased from 1033sec of the original approach (ex01) to 68sec with all improvements (ex06).
The new query design (ex04) had the most substantial impact on performance, particularly visible in the long-running queries. Also, the transition from the ILIKE to the LIKE statement notably reduced runtime. The performance gain is most noticeable on the medium-running queries containing a long list of PDB IDs for a preselection. The experiments 02 and 03, the new index and no wildcards in the PDB ID selection showed only a small improvement. However, experiment 09, which contains all changes except the wildcard improvement, shows that it has an impact on the overall runtime, presumably benefiting from the switch to the LIKE statement. The changes in index structures showed less impact than expected, demonstrating that PostgreSQL can handle indexes with an inflated number of columns. However, the performance was drastically worse if no index was used or index structures did not combine multiple attributes. For instance, combining one index per attribute led to an increase of the sum of the mean runtimes from 68sec (ex06) to 134sec.
Unspecific Queries Some of the used queries include a protein filter to reduce the number of searched pockets. When removing these filters and searching the whole dataset, the original approach reached its set limits (needing more than 100GB RAM or 1h time) on some of these and other queries with less restrictive geometric filters. With the improved approach, some queries with extensive intermediate results could now be computed for the first time, often within minutes.
Alternative Setups As large database instances are not always accessible, for example, due to cost constraints in cloud environments, we also conducted our experiment on two smaller virtual servers. As shown in Figure 4a, the performance gains were also visible on these smaller server instances. These tests were performed on shared hardware, so they can only show a general trend rather than precise comparative data. However, they demonstrate the feasibility Ex01 Dedicated
Ex01 Medium itranaVEx06EDx0e1diScamteadll 7.5s
Ex06 Medium 9.3s
Ex06 Small 9.6s 0 of processing on shared virtual servers. Additionally, we observed a substantial speedup while transitioning from PostgreSQL10 to PostgreSQL15 as displayed in Figure 4b. Combined with our improvements, we achieved a speedup factor of 32.
GeoMine is a unique application for geometric searches in large collections of protein-ligand complexes with high relevance for life-science research. We showed that it was possible to achieve a large speedup on our query processing by moving major parts of the processing from a custom-written logic inside the software to a PostgreSQL database system. Additionally, diferent approaches in database optimization contributed to further performance gain. Overall, these achievements are critical for the practical use of the system handling the growing dataset. Some queries could be executed for the first time on our setup due to these changes. In this work, we focused on optimizations of the database and query design. We demonstrated the substantial benefits of database optimizations in scientific applications, achieving a fifteen-fold speedup in GeoMine. Coupled with a halving of the runtime through the use of a newer PostgreSQL version, we managed to reduce the average runtime from minutes to seconds.
Looking ahead, we plan to explore additional database paradigms, such as distributed or column-based systems, and establish schema changes for further optimizations. The caching of intermediate results, as well as determining the join order by extended statistics or by utilizing machine learning, may potentially provide additional benefits. This way, we aim to achieve even better performance for searching scientific data with a service-oriented web service.
This work was supported by the German Federal Ministry of Education and Research as part of CompLS and de.NBI (031L0172 and 031L0105). and reliable binding site detection and descriptor calculation with dogsite3, Journal of Chemical Information and Modeling 63 (2023) 3128–3137. doi:10.1021/acs.jcim. 3c00336, pMID: 37130052. [18] T. Inhester, S. Bietz, M. Hilbig, R. Schmidt, M. Rarey, Index-based searching of interaction patterns in large collections of protein–ligand interfaces, Journal of Chemical Information and Modeling 57 (2017) 148–158. [19] I. Daylight Chemical Information Systems, Smarts-a language for describing molecular patterns, 2007. [20] A. S. Rose, A. R. Bradley, Y. Valasatava, J. M. Duarte, A. Prlić, P. W. Rose, NGL viewer: web-based molecular graphics for large complexes, Bioinformatics 34 (2018) 3755–3758. doi:10.1093/bioinformatics/bty419. [21] K. Diedrich, J. Graef, K. Schöning-Stierand, M. Rarey, GeoMine: interactive pattern mining of protein–ligand interfaces in the Protein Data Bank, Bioinformatics 37 (2020) 424–425. doi:10.1093/bioinformatics/btaa693. [22] C. Bron, J. Kerbosch, Algorithm 457: finding all cliques of an undirected graph, Communications of the ACM 16 (1973) 575–577. [23] PostgreSQL 15, Documentation, 2023. URL: https://www.postgresql.org/docs/15/. [24] Containers, podman, 2023. URL: https://podman.io/.