Several modern day problems need to deal with large amounts of spatio-temporal data. As such, in order to meet the application requirements, more and more systems are adapting to the speciifcities of those data. The most prominent case is perhaps the data storage systems, that have developed a large number of functionalities to eficiently support spatio-temporal data operations. This work is motivated by the question of which of those data storage systems is better suited to address the needs of industrial applications. In particular, the work conducted, set to identify the most eficient data store system in terms of response times, comparing two of the most representative of the two categories (NoSQL and relational), i.e. MongoDB and PostgreSQL. The evaluation is based upon real, business scenarios and their subsequent queries as well as their underlying infrastructures, and concludes in confirming the superiority of PostgreSQL. Specifically, PostgreSQL is four times faster in terms of response time in most cases and presents an average speedup around 2 in first query, 4 in second query and 4,2 in third query in a five node cluster. Also, we observe that the average response time is significantly reduced at half with the use of indexes almost in all cases, while the reduction is significantly lower in PostgreSQL.
The volumes of spatial data that modern-day systems are
generating has met staggering growth during the last few years.
Managing and analyzing these data is becoming increasingly
important, enabling novel applications that may transform science
and society. For example, mysteries are unravelled by harnessing
the 1 TB of data that is generated per day from NASA’s Earth
Observing System [
Distributed database systems have been proven instrumental
in the efort to dealing with this data deluge. These systems are
distinguished by two key-characteristics: a) system scalability:
the underlying database system must be able to manage and
store a huge amount of spatial data and to allow applications to
eficiently retrieve it; and, b) interactive performance: very fast
response times to client requests. Some systems that natively
meet those requirements for spatial data are: Hadoop-GIS [
The plethora of available systems and underlying technologies have left the researchers and practitioners alike puzzled as to what is the best option to employ in order to solve their big spatial data problem at hand. The query and data characteristics only add to the confusion. It is imperative for the research community to contribute to the clarification of the purposes and highlight the pros and cons of certain distributed database platforms. This work aspires to contribute towards this direction by comparing two suchlike platforms for a particular class of requirements, i.e. those that the response time in complex spatio-temporal queries is of high importance.
In particular, we compare the performance in terms of
response time between a scalable document-based NoSQL
datastoreMongoDB [
The results show that PostgreSQL with the PostGIS extension, outperforms MongoDB in all queries. Specifically, PostgreSQL is four times faster in terms of response time in most cases and presents an average speedup around 2 in the first query, 4 in the second query and 4,2 in a third query in a 5 node cluster. The 5 node cluster setup outperforms the one node implementation 1MarineTrafic is an open, community-based maritime information collection project, which provides information services and allows tracking the movements of any ship in the world. It is available at: https://www.marinetrafic.com 2Amazon Web Services, https://aws.amazon.com/ in each system although the reduction is much more noticeable in PostgreSQL. Finally the results demonstrate that indexing afects response times in query execution by reducing them at half almost in all cases while the reduction is significantly lower with the use of indexes in PostgreSQL.
The document is structured as follows: Section 2 provides details about the related work in spatio-temporal systems and benchmark analysis; Section 4 describes the technology overview; Section 4 describes the evaluation of spatio-temporal database systems used; Section 5 presents the experimental results while Section 6 presents the final conclusions of this study and future work. 2
The volume of spatial data is increasing exponentially on a daily basis. Geospatial services such as GPS systems, Google Maps and NASA’s Earth Observing system are producing terabytes of spatial data every day and in combination with the growing popularity of location-based services and map-based applications, there is an increasing demand in the spatial support of databases systems. There are challenges in managing and querying the massive scale of spatial data such as the high computation complexity of spatial queries and the eficient handling the big data nature of them. There is a need for an interactive performance in terms of response time and a scalable architecture. Benchmarks play a crucial role in evaluating the performance and functionality of spatial databases both for commercial users and developers.
In [
Another benchmark for spatial database evaluation is
presented in [
In [
In [
A computing system for processing large-scale spatial data
called GeoSpark, is presented in [
SMASH [
Finally in [
In order to evaluate the set of spatio-temporal queries, two different systems are employed and compared: MongoDB and PostgreSQL with PostGIS extension.
MongoDB [
On the other hand, PostgreSQL is an open source objectrelational database system (ORDBMS). There is a special extension available called PostGIS that integrates several geofunctions and supports geographic objects. PostGIS implementation is based on "light-weight" geometries and the indexes are optimized to reduce disk and memory usage. The interface language of the PostgreSQL database is the standard SQL. PostGIS has the most comprehensive geofunctionalities with more than one thousand spatial functions. It supports geometry types for Points, LineStrings, Polygons, MultiPoints, MultiLineStrings, MultipPolygons and GeometryCollections. There are several spatial operators for geospatial measurements like area, distance, length and perimeter. PostgreSQL supports several types of indexes such as: BTree, Hash, Generalized Inverted Indexes (GIN) and Generalized Search Tree (GiST) called R-tree-over-GiST. The default index type is BTree that can work with all datatypes and can be used for equality and range queries eficiently. For general balanced tree structures and high-speed spatial querying, PostgreSQL uses GiST indexes that can be used to index geometric data types, as well as full-text search.
Feature ship_id latitude, longitude status speed course heading timestamp
Description Unique identifier for each ship Geographical location in digital degrees Current position status Speed over ground in knots Course over ground in degrees with 0 corresponding to north Ship’s heading in degrees with 0 corresponding to north
Full UTC timestamp (1) Find coordinates of diferent amount of vessels from 1/May/ 2016 - 31/July/2016 (entire time window) within the whole bounded area, Q1
Specifically, Q1 fetches coordinates for an increased amount of vessels as shown in Table 2. The query is performed for all unique vessels in the dataset, for half of them, for 1/4 and finally for 1/8 of them, in order to examine the scalability of the database systems. Q2 fetches coordinates of vessels for diferent time windows; 1 day, 10 days, 1 month and 2 months as shown in Table 3. Q3 fetches coordinates for diferent geographical polygons as shown in Table 4. The polygons (polygon1.F, polygon2.F, polygon1.S, polygon2.S) are bounding boxes within Mediterranean Sea. Figure 1 shows a graphical representation of these polygons with the trajectories of the vessels inside. We used QGIS 3, a tool that visualize, analyses and publish geospatial information. 4.3
We have deployed a MongoDB cluster that contains a master node server (primary) and four replication slaves (secondaries) in Replica Set mode. One way to achieve replication in MongoDB 3QGIS: A Free and Open Source Geographic Information System, https://qgis.org/en/site/ Polygons polygon2.S polygon1.S polygon2.F polygon1.F is by using replica set. While MongoDB ofers standard primarysecondary replication, it is more common to use MongoDB’s replica sets. A replica set is a group of multiple coordinated instances that host the same dataset and work together to ensure superior availability. In a replica set, only one node acts as primary that receives all write operations while the other instances called secondaries and apply operations from the primary. All data are replicated from the primary to secondary nodes. If the primary node ever fails or becomes unavailable or maintained, one of the replicas will automatically be elected through a consortium as the replacement. After the recovery of failed node, it joins the replica set again and works this time as a secondary node.
The problem with replica set configuration is the selection
of a member to perform a query in case of read requests. The
question is which node to choose, the primary or a secondary
and if a request queries a secondary, which one should be used. In
MongoDB it is possible to control this choice with read preferences
solution. When an application queries a replica set, there is the
opportunity to trade of consistency, availability, latency and
throughput for each kind of query. This is the problem that read
preferences solve: how to specify the read preferences among
above trade ofs, so the request falls to the best member of the
replica set for each query. For the distribution of queries, the
read preference provides the solution. The categories of read
preferences are: i) PRIMARY: Read from the primary, ii) PRIMARY
PREFERRED: Read from the primary if available, otherwise read
from a secondary, iii) SECONDARY: Read from a secondary, iv)
SECONDARY PREFERRED: Read from a secondary if available,
otherwise from the primary and finally v) NEAREST: Read from
any available member. Because our goal is to achieve maximum
throughput and an evenly distribution of load across the members
of the set we used NEAREST preference and we set the value
secondary_acceptable_latency_ms very high in 500ms. This value
is used in MongoDB to track each member’s ping time and queries
only the "nearest" member, or any random member that is no
more than the default value of 15ms "farther" than it. In general,
load balancing and query distribution consist one of the most
important factors in distributed systems. An even distribution of
load within the nodes of the system reduces the probability that a
node turns to a hotspot and his property also acts as a safeguard
to the system reliability [
Additionally, we have deployed a PostgreSQL cluster that contains a master server and four slaves in Streaming Replication mode. Slaves keep an exact copy of master’s data, apply the stream to their data and staying in a "Hot Standby" mode, ready to be promoted as master in case of failure. Streaming replication is a good tactic for data redundancy between nodes but for load balancing a mechanism is required that splits the requests between the copies of data. For this reason we use Pgpool-24, a 4Pgpool-2, http://www.pgpool.net/mediawiki/index.php/Main_Page middleware that works between PostgreSQL servers and a PostgreSQL database client. Pgpool-2 examines each query and if the query is read only, it is forwarded to one of the slave nodes otherwise in case of write it is forwarded to the master server. With this congfiuration the read load splits between the slave nodes of the cluster, achieving thus an improved system performance. Pgpool-2 provides the following features, Connection Pooling: connections are saved and reused whenever a new connection with the same properties arrives, thus reducing connection overhead and improving system throughput, Replication: replication creates a real time backup on physical disks, so that the service can continue without stopping servers in case of a disk failure, Load Balancing: the load on each server is reduced by distributing SELECT queries among multiple servers, thus improving system’s overall throughput and Limiting Exceeding Connections: connections are rejected after a limit on the maximum number of concurrent connections. 4.4
The dataset used for the experiments was initially in CSV format. As we mentioned above, in MongoDB the geographical representation needs to follow the GeoJSON format structure in order to be able to set a geospatial index on the geographic information, thus the first step was the conversion of the data into the appropriate format. For data ingestion we used the mongoimport tool to import data into MongoDB database. The total size the dataset occupied in the collection in MongoDB is 116 GB and each record has a size of about 275 bytes.
In PostgreSQL there is a copy mechanism for bulk loading
data that can achieve a good throughput. The data must be in
CSV format and the command can accept a number of delimiters.
The next step after data loading into database, is the conversion
of latitude and longitude columns to PostGIS POINT geometry.
These columns must be converted into geometry data which
subsequently can be spatially queried. We created a column called
the_geom using a defined PostGIS function, which in essence
contains the POINT geometry created from latitude and longitude
of each record. The spatial reference ID (SRID) of the geometry
instance (latitude, longitude) is 4326 (WGS84). The World
Geodetic System (WGS) is the defined geographic coordinate system
(three-dimensional) for GeoJSON used by GPS to express
locations on the earth. The latest revision is WGS 84 (also known as
WGS 1984, EPSG:4326) [
The reason for this behavior is that the data stored in MongoDB are in GeoJson format and each record consist of many extra characters and a unique auto created id called ObjectId. Thus, each record is significant bigger in size than it was in its original CSV format. On the other hand, in PostgreSQL the data ingested in database as CSV, with the addition of the_geom column that contains the POINT geometries of each latitude and longitude. 4.5
To benchmark MongoDB and PostgreSQL we deployed each database system on Amazon Web Services (AWS) EC2 instances. For storing the dataset, we used the S3 bucket provided also by AWS. The configuration used is described below:
MongoDB. The MongoDB cluster consists of 5 x r4.xlarge instances within a Virtual Private Cloud (Amazon VPC). One node is the primary and the other played the role of the replicas. We installed MongoDB 3.6.5 version in Replica Set mode. Each instance operates on Amazon Linux 2 AMI OS and consist of 4 CPUs x 2.30 GHz, 30.5 GB DDR4 RAM, 500 GB of general purpose SSD storage type EBS, up to 10 Gigabit network performance and IPv6 support. Amazon Elastic Block Store (Amazon EBS) provides persistent block storage volumes for use with Amazon EC2 instances in the AWS Cloud. Each EBS volume is automatically replicated within its Availability Zone to protect from component failures, thus ofering high availability and durability. Also the instances are EBS-optimized which means that they provide additional throughput for EBS I/O and as a result an improved performance.
PostgreSQL. Exactly the same configuration is used in PostgreSQL. We installed PostgreSQL 9.5.13 and PostGIS 2.2.1 in streaming replication mode. One node is the master while the others play the role of slaves. Also an extra instance for Pgpool-2 was deployed. 5
This section contains a detailed experimental evaluation that examines the run time performance of the spatio-temporal queries Q1, Q2 and Q3 in MongoDB and PostgreSQL through seven experiments. Five consecutive separate calls are conducted, in order to gather the experimental results and collect the average values concerning response time of above queries in diferent case scenarios. We compare the response time in a 5-node cluster versus a 1-node implementation. We also test the settings when indices are used against the case that they are not.
For Q1 a regular BTree index is created in MongoDB and PostgreSQL for attribute "ship_id". The size of index varies between the diferent database systems. In MongoDB the index size is about 6 GB while in PostgreSQL the size is 3,1 GB. For Q2 we implemented also a BTree index of size 6 GB in both DBMSs for attribute "timestamp". Finally, for Q3 we implemented an index in field "$geometry" in MongoDB with size 6 GB. As mentioned above the data are stored in MongoDB as GeoJSON and the "$geometry" field that is created contains the coordinates values, latitude and longitude. Because these data are geographical, we create a 2dsphere index type which supports geospatial queries. In PostgreSQL we created an index of type GiST in field the_geom which contains the POINT geometry created from latitude and longitude of each record with size 8,2 GB. For high-speed spatial querying, PostgreSQL uses GiST indexes that can be used to index geometric data types. The index size varies between the two database systems even for the same attribute that performed. The two systems store data diferently and the concept of "index" is diferent too.
Figure 2 illustrates the average response time concerning the set of queries Q1, Q2 and Q3 in 5 node cluster between MongoDB and PotgreSQL. It’s quite clear that PostgreSQL outperforms MongoDB by a large extent in all queries. The response time is almost 4 times faster in some cases (Q2, Q3) comparing to MongoDB. Only in Q1 the response time presents smaller fluctuations between the DBMSs. Exactly the same behavior is observed in the 1 node implementation as shown in Figure 3. The response time is significant lower in case of PostgreSQL.
Subsequently, we compared the average response time in the set of queries for the 5-nodes cluster versus the 1-node implementation for the two database systems as shown in Figure 4 and Figure 5 respectively. The results show that the average response 5481 10822 21644 43288 1 day MongoDB PostgreSQL MongoDB PostgreSQL MongoDB PostgreSQL 3,000 ) 2,500 sce (e 2,000 m i T sen 1,500 sop eR1,000 500 0 3,000 ) 2,500 sce (e 2,000 m i T sen 1,500 sop eR1,000 500 0 3,000 ) 2,500 sce (e 2,000 m i T sen 1,500 sop eR1,000 500 0 1,000 )ce 800 (se iTm 600 seon sep 400 R 200 0 (a) (a) (a) (a)
MongoDB 1 node MongoDB 5 nodes 5481 10822 21644 43288 1 day 3,000 2,000 1,000 0 (b) 1,200 1,000 800 600 400 200 0 MongoDB_NoIndex MongoDB_BTree MongoDB_NoIndex MongoDB_2D_sphere 1 day 4.6 4.4 4.2 4 3.8 3.6 3.4 3.2 1 day 5481 10822 21644
43288 time presents a small reduction when the experiments are performed in a 5-node MongoDB cluster versus the 1-node implementation. The reduction is much more noticeable in the case of PostgreSQL. The reason is that in the MongoDB replica set, the client requests are distributed using the read preference-Nearest option that can achieve a maximum throughput and an evenly distribution of load across the members of the set. In case of PostgreSQL, with the use of Pgpool-2 the load of read requests is distributed between the nodes of the cluster in a much more efective way, thus improving system’s overall throughput.
In the next set of experiments we examined how indexes afect the response time in queries execution. As shown in Figure 6 the reduction is significantly lower, almost at half with the use of indexes in PostgreSQL. An index allows the database server to ifnd and retrieve specific rows much faster than without an index. In Figure 7 concerning MongoDB we excluded Q1 because the response time was extremely high in case of no index (> 4 hours). In the remaining queries, also the response time is significantly reduced. Eficient query execution in MongoDB is supported using indexing. MongoDB can use indices to limit the number of documents it must inspect otherwise a scan operation is performed in every document in a collection, to select those documents that match the query statement (collection scan). Figure 8 presents the average speedups of PostgreSQL comparing to MongoDB in 5 node cluster with the use of indexes. As it shown the average speedup concerning Q1 is almost 2, 4 in case of Q2 and 4,2 in Q3.
In this paper, we analyzed and compared the performance in terms of response time between two diferent database systems, a document-based NoSQL datastore, MongoDB, and an opensource object-relational database system, PostgreSQL with PostGIS extension. Each database system was deployed on Amazon Web Services (AWS) EC2 cluster instances. We employed a replica set and a streaming replication cluster setup for MongoDB and PostgreSQL system respectively. For the evaluation between the two systems, we employed a set of spatio-temporal queries that mimic real world scenarios and present spatial and temporal predicates, with the use of a dataset which was provided to us by the community based AIS vessel tracking system (VTS) of MarineTrafic. 300 200 100 0
The performance is measured in terms of response time in diferent case scenarios; in a 5 node cluster versus 1 node implementation and with the use of indexes versus not. The results are show that PostgreSQL outperforms MongoDB in all cases and queries and presents an average speedup around 2 in first query, 4 in second query and 4,2 in third query in a five node cluster. Also, the replica set mode implementation of 5 nodes in MongoDB as well as the streaming replication of 5 nodes in PostgreSQL outperforms the one node implementation in each system respectively. Subsequently, as demonstrated by the experimental results, the average response time is significantly reduced at half, almost in all cases with the use of indexes while again the reduction is significantly lower in PostgreSQL. Finally, the dataset size occupied in the system db, reduced 4x in case of PostgreSQL, since it stores data in a more efective way.
Our future plan include the extension of our system architecture to what it is called Shared Cluster. A Shared Cluster consists of shards which in turn contain a subset of the sharded data. Sharding is a method for distributing data across multiple machines. Every machine contains only a portion of the shared data and each machine replicated to secondaries nodes for data redundancy and for fault tolerance reasons. We plan to evaluate and compare the two types of cluster and draw conclusions of which system is best for diferent cases.
The research work was supported by the Hellenic Foundation for Research and Innovation (HFRI) and the General Secretariat for Research and Technology (GSRT), under the HFRI PhD Fellowship grant (GA. no. 2158).
This work has been developed in the frame of the MASTER project, which has received funding from the European Union’s Horizon 2020 research and innovation programme under the Marie Skłodowska-Curie grant agreement No 777695.
This work was supported in part by MarineTrafic which provided data access for research purposes.