# Evaluation of NoSQL in the Energy Marketplace with GraphQL Optimization

Michael Howard P.Eng  Department of Computer Science
Maseeh College of Engineering and Computer Science
Portland State University
email<EMAIL_ADDRESS>
###### Abstract

The growing popularity of electric vehicles in the United States requires an
ever-expanding infrastructure of commercial DC fast charging stations. The
U.S. Department of Energy estimates 33,355 publicly available DC fast charging
stations as of September 2023. In 2017, 115,370 gasoline stations were
operating in the United States, much more ubiquitous than DC fast chargers.
Range anxiety is an important impediment to the adoption of electric vehicles
and is even more relevant in underserved regions in the country. The peer-to-
peer energy marketplace helps fill the demand by allowing private home and
small business owners to rent their 240 Volt, level-2 charging facilities. The
existing, publicly accessible outlets are wrapped with a Cloud-connected
microcontroller managing security and charging sessions. These
microcontrollers act as Edge devices communicating with a Cloud message
broker, while both buyer and seller users interact with the framework via a
web-based user interface. The database storage used by the marketplace
framework is a key component in both the cost of development and the
performance that contributes to the user experience. A traditional storage
solution is the SQL database. The architecture and query language have been in
existence since the 1970s and are well understood and documented. The
Structured Query Language supported by the query engine provides fine
granularity with user query conditions. However, difficulty in scaling across
multiple nodes and cost of its server-based compute have resulted in a trend
in the last 20 years towards other NoSQL, serverless approaches. In this
study, we evaluate the NoSQL vs. SQL solutions through a comparison of Google
Cloud Firestore and Cloud SQL MySQL offerings. The comparison pits Google’s
serverless, document-model, non-relational, NoSQL against the server-base,
table-model, relational, SQL service. The evaluation is based on query
latency, flexibility/scalability, and cost criteria. Through benchmarking and
analysis of the architecture, we determine whether Firestore can support the
energy marketplace storage needs and if the introduction of a GraphQL
middleware layer can overcome its deficiencies.

###### Index Terms:

Non-relational, relational, MySQL, mitigate, Firestore, SQL, NoSQL,
serverless, database, GraphQL

##  I Introduction

The era of internal combustion vehicles is coming to a close. Electric vehicle
(EV) sales are on a steady upward trajectory. EV-Volumes [15] report that in
2022, sales increased by 55% over the previous year, while the entire auto
industry declined by -0.5% during that same period. A major barrier to the
ownership of electric vehicles is range anxiety. Commercial charging
facilities are not yet as ubiquitous as gasoline stations, opening up a market
for private individuals to assist by sharing their personal charging
facilities. In this paper, we detail how the Cloud framework for a peer-to-
peer energy marketplace might work to provide users with a means to share and
rent their private charging outlets. Components of the framework include the
Edge microcontrollers, web server, API, Cloud Functions, Publication-
Subscription service, and a message broker. We then dig deep into the database
component, which is the main research question.

###  I-A Motivations

A critical step in the energy marketplace is to persistently store data
objects that represent the Edge charging devices. Users, charging session
history, billing details, ratings and reviews, amenities, and tourist
information are additionally persisted. The workflows for the marketplace
users include registering a new charger device, searching for available
devices, and managing a charging session, as seen in Figure 1.

Figure 1: The energy marketplace supporting: 1. Device registration, 2. Data
storage, 3. Device search and reserve, 4. Session management, and 5. Session
details.

Requirements for persistent data include low read and write latency, the
ability to scale horizontally as data grows, the cost, and the ease of
evolving the shape of the data. Multiple users may query persistent storage,
asking for data objects that contain multiple fields within a given range.
Read-operation latency will be critical in this scenario. The user must be
able to request a charger within a given latitude and longitude radius and may
need to include the maximum distance to a restroom, the minimum user rating,
the type of charger, etc. The query engine must be able to process these
patterns of field conditionals. If it can process only a subset, the resulting
data reduction must be efficient enough so that the user response is not
noticeably delayed.

Another motivation for investigating database technology is flexibility and
scalability. The energy marketplace will grow iteratively, adding features
that require additional persistence. A fixed schema or data shape may require
a migration (or a new table) of all existing persisted data to match the
updated schema prior to adding the new data objects. A flexible schema may
reduce the burden on administrators and reduce administration complexity,
since new fields can be incrementally stored as the marketplace framework and
feature set evolve. Since the number of users participating in the marketplace
is also evolving, persistent storage must be able to horizontally scale to
accommodate them. For example, an early marketplace pilot may have 50 users
within a single geographical zone. An individual Cloud node with failover and
redundancy can safely store all required persistent data and support all
incoming query requests within the zone. However, as users spread out
geographically, storage nodes will need to scale into these new zones. Also,
as data and user request volume exceed what can be processed by a single node,
storage must partition and scale across a node cluster.

The high-level goals for the persistent storage for the marketplace are as
follows:

  1. 1.

Storage cost.

  2. 2.

Compute cost.

  3. 3.

Query support.

  4. 4.

Query latency.

  5. 5.

Ease of scaling.

  6. 6.

Ease of data-structure evolution.

Cost is a significant motivation, driving a design goal of the framework to
avoid perpetual, unmanaged compute. The deployment of server-based compute
services may increase billing, as the compute resources are dedicated and
always available. Additionally, the type of compute and operating system
patches must be managed by the developer. A serverless persistent storage
model provides a pricing model in which the billing only reflects what is
used. Thus, queries and storage contribute directly to billing, but idle time
does not.

###  I-B Research Question

The aforementioned requirements for persistent storage raise the question of
what will work best to support the energy marketplace. A deep dive
investigation is beneficial prior to developing the marketplace framework and
leads us to the research question addressed in this paper: Will a serverless,
NoSQL, non-relational, document-model database (i.e. Google Firestore [9])
support the query, cost, and flexibility needs for persistent storage in a
peer-to-peer energy marketplace? We will endeavor to answer this question
through benchmarking latency, estimating costs, and analyzing the
architecture. To provide a comparison with a well-established solution,
benchmarking and analysis are compared side-by-side against a relational, SQL,
and perpetual server provided through Google Cloud SQL [12].

###  I-C Context of the Study

Multiple related studies utilize a third-party benchmarking tool such as
Yahoo! Cloud Serving Benchmark (YCSB) [33] to establish latency under variable
load [3, 14, 22, 25]. In this study, we will focus on a more customized way to
measure the response time of the database.

For both the relational and non-relational databases, we develop client
applications that emulate portions of the energy marketplace data flow.
Queries running from these clients perform read-and-write operations that
characterize latency performance in specific load scenarios. This experimental
setup allows the persistent storage component of the marketplace to be
developed and tested in isolation.

###  I-D Objectives and Contributions

The objectives of this paper are as follows:

  1. 1.

Characterize query latency for both relational and non-relational databases.

  2. 2.

Evaluate the cost of both solutions.

  3. 3.

Explore the ease of evolving the stored data structure.

  4. 4.

Explore the ease of horizontal scaling.

  5. 5.

Evaluate inserting a GraphQL middleware layer and its effect on client
simplicity, latency, safety, and portability.

Achieving these objectives provides a characterization of the strengths and
weaknesses of Firestore’s persistent storage solution, both standalone and
comparatively against the more established SQL solution. This insight into
Firestore’s performance and capabilities contributes a known data storage
platform to support the future energy marketplace framework.

###  I-E Overview of the Paper

After initially introducing the topic, the background of electric vehicle (EV)
charging and related research are covered in Section II. Next, we discuss the
system architecture of the Firestore and MySQL storage solutions, along with
GraphQL, in Section III. Section IV presents the experiments performed against
both solutions along with their corresponding experimental data. The
discussion of the experimental results, the regression model and future work
is detailed in Section V. Finally, we wrap up the paper with the conclusion in
Section VI.

##  II Background

###  II-A EV Charging

Electric vehicle (EV) ownership has increased significantly in the United
States. At the end of 2022, there were 2,442,300 EV registrations compared to
a combined 248,529,800 diesel and gasoline vehicles [31]. Thus, electric
vehicles now represent approximately 1% of all vehicles on the road.

Accelerating electric vehicle sales requires an acceleration in the deployment
of the charging infrastructure. What if, by 2030, there was a peer-to-peer
marketplace such that consumers could turn into producers and sell units of
energy? The growing popularity of solar panels [26] allows the generation of
energy and the reselling of energy purchased through a utility grid. Peer-to-
peer sales will help meet the growing demand for electric vehicle charging and
offer customers alternatives as part of the evolving sharing economy. Airbnb
[1] provides a peer-to-peer service as an alternative to traditional hotels,
while Uber [29] is a popular alternative to conventional taxi services. The
popularity of these services reveals a willingness of the public to use peer-
to-peer alternatives, and this proposal will focus on the development of a
distributed energy marketplace system.

Although most of the charging can be done at home to facilitate local
commutes, a publicly available network of chargers is still necessary for
intercity commutes and to accommodate those who do not have charging
facilities at home. As a result of this demand, electric vehicle charging
companies have rapidly formed and deployed charging networks in the United
States. Figure 2 shows the top companies at the end of 2021 ranked by the
total number of DC fast chargers.

Figure 2: Top 10 fast charging companies sorted by number of locations [7].
The data was sourced from the US Department of Energy as of Dec 31, 2021 [30].

Since the charging networks shown in Figure 2 are still far from as ubiquitous
as gas stations, we propose supplementing with private property owners who
wish to rent their charging facilities through a peer-to-peer energy
marketplace. Figure 3 shows the architecture to implement the marketplace. A
seller will register their 240 volt level-2 charging outlet via an Internet of
Things (IoT) microcontroller. The IoT device communicates with a Message
Broker within the Cloud framework, which is registered with a dedicated
Publication-Subscription (PubSub) service. PubSub provides a serverless
synchronization mechanism between a message broker communicating with the IoT
devices and the business logic running through Cloud Functions. A Cloud
Function may subscribe to notifications provided by the message broker
representing a set of devices and also publish a new message to one or more
devices.

Figure 3: A Cloud framework to implement the energy marketplace. A seller
registers their IoT device allowing buyers to search and reserve.

Both buyers and sellers interact with the marketplace through a web client.
Once a seller registers an IoT device, the Web API creates a corresponding
database object. All requests are processed through Cloud Functions. The buyer
will search for all chargers within a given latitude-longitude radius. Within
the search results, the buyer chooses the desired device, reserves it, and
navigates to its location to start the charging session. Once the session is
completed, the device sends a detailed log that is stored in the database.
Additionally, Cloud Functions trigger payment processing via an external
payment gateway (e.g. Stripe [27]).

A significant question in this design is what persistent storage needs are for
this marketplace. Our focus in this paper is the data flow caused by the
aforementioned workflow and performance of the database. At first, we do not
know all the data storage requirements. For example, when this framework is
initially rolled out, only charger details such as:

⬇

name

address

latitude

longitude

type

charging history: date, duration

energy consumed

will be persistently stored. As development progresses, we will potentially
add:

⬇

charger reservations and availability

billing information

user ratings and comments

amenities

nearby tourism information

user profiles

Given that the data fields and shape will evolve, a strict schematized
database may cause additional administrator complexity, since existing data
tables require a migration on a schema change.

Another concern is scalability. If the marketplace has 10 sellers and 10
buyers, scaling is not an issue. If it grows to 1,000,000 sellers, the
database must be able to horizontally scale across multiple nodes with
physical locations across multiple zones and regions. A NoSQL database with a
document model allows for this scaling with little administration.

Query latency and performance are also a concern. Create, update, and delete
operations may tolerate eventual consistency. However, a read operation needs
to query multiple fields, some within a range. If a buyer searches for the
level-2 charger type along with a latitude, longitude, and user rating range;
a SQL query engine can easily handle this and return only matching records.
However, a NoSQL database such as Firestore will only be able to query a
single field range at a time, requiring additional data reduction at the
mobile application client.

###  II-B Related Work

The related work surveyed in this section provides an architecture and
experimental evaluation background for the research topic of this paper. We
start with research on evaluating persistent storage for IoT device
management. The experimental setup, evaluation, and technologies used were
valuable to guide our experiments and evaluation. Our topic and our
experiments use the surveyed research as a starting point without duplicating
it. Next, other research papers were surveyed that evaluated NoSQL databases
against each other or against SQL. These provide a methodology and
benchmarking toolkit that is considered in the design of our experiments.

The next group of papers focuses on the specific technology used in our
experiments. Firestore, MySQL, and Spanner papers were surveyed and establish
how performance is evaluated and the ability of each to scale. Spanner
provides a physical storage layer for the Firestore service and is thus
included. Finally, papers discussing approaches to augmenting MySQL so that it
can scale were included. These provide background to the challenges faced with
horizontal scaling of an SQL database.

####  II-B1 Performance Evaluation of IoT Data Management

Eyada et al. [18] is a paper focused on evaluating database latency and size
as they are applied to large volumes of IoT sensor data. Both the MySQL and
MongoDB databases were evaluated while hosted in AWS EC2 instances. The
experiments changed the workload, the compute resources, and the number of
sensors. The resulting latency values were fed into a predictive model
equation developed with linear and nonlinear regression methods. The results
favor MongoDB for latency, while MySQL did not increase latency dramatically
with increased sensor data.

####  II-B2 Benchmarking with YCSB

Pandey [22] provides a comparison of relational and non-relational database
platforms by comparing MySQL and MongoDB. The benchmark suite Yahoo! Cloud
Serving Benchmark (YCSB) was used to vary the workloads in each database and
capture latency and throughput measurements. All measurements favored the
MongoDB solution. However, the authors acknowledge and discuss the lack of
strict atomicity, consistency, isolation, and durability (ACID) properties
with non-relational, as well as the missing join operations for queries. The
authors further conclude that MongoDB outperforms MySQL in sharding, security,
performance, and availability.

Another paper discussing NoSQL platforms is by Khazaei et al. [14]. The
authors explore some popular NoSQL databases and describe the characteristics
of this solution. The discussion includes the loosening of consistency,
availability, partition tolerance (CAP) theorem, and the resulting basically
available, soft-state, eventually consistent (BASE) systems. The authors
further compare multiple benchmarking suites including YCSB, PixMix, GRIDMix,
and CALDA and conclude by choosing YCSB for its flexibility.

####  II-B3 Google Firestore

The first paper written on the Firestore NoSQL database is by Kesavan et al.
[25] and is co-authored by a group of researchers within Google. This paper
digs into the architecture of Google’s Firestore service, specifically
addressing how it scales across many nodes, provides real-time notification
capability, is easy to use, and provides a pay-as-you-go billing model. The
authors present benchmark data showing little increase in query latency given
a large number of stored documents and increased document size and data shape.
They concluded that Firestore provides a convenient ecosystem with low barrier
of entry for developers to rapidly prototype, deploy, iterate, and maintain
applications.

Brito et al. [8] discuss the key benefits and characteristics of GraphQL, a
front-end programming interface and query language that wraps various data
sources. They then evaluated the benefits in practice by migrating seven
systems. The migration of the client APIs is discussed, and their resulting
implementations reduced the returned data set by 94% in terms of number of
fields.

####  II-B4 Oracle MySQL

MySQL, a relational database, is widely used by most small and medium-sized
applications [6]. It was initially released in 1995 and was developed by
Oracle Corporation. In Bannon et al. [24], the authors dig into the MySQL
server architecture and discuss the relational database management system
(RDBMS) in generic terms that can be applied to other SQL systems. The RDMBS
is subdivided into application, logical, and physical layer software that runs
directly on the node containing physical storage. SQL requests are processed
locally through a pre-compiler to extract the SQL statement, a query parser to
convert to a parse tree structure, and an optimizer utilizing stored indices
where possible.

####  II-B5 Sharding a Relational Database

Create, read, update, and delete (CRUD) operations are all supported through a
Structured Query Language (SQL) interface. These operations must adhere to a
predefined schema that defines each column of the target table. Schematization
provides a sanity check to ensure that incoming row insertions are conforming
to the desired data shape and column types. Note that, under certain
circumstances, it may be considered a restriction. Any changes to a table’s
schema require the entire table to be migrated to the new shape. Such a
restriction may provide development hurdles as an application is rolled out
iteratively and persistent data requirements evolve.

The support for SQL query processing is a benefit. The language has been in
existence since the early 1970s [4], leading to a large user support group,
extensive documentation, and teaching materials [28]. A huge advantage of the
language is the ability to combine multiple inequality predicates within a
single query. For example, a _select_ statement may contain multiple columns
with $\geq$ conditions. The statement is executed within a single operation
and allows a response set to be minimized to only the desired matching
records. We will see later how this is significant to the energy marketplace.
SQL also supports _join_. Queries to different tables are combined within a
single operation. The _join_ allows data referenced through a relation to be
accessed from their respective tables.

Although the external client may contain application and interface code, the
remaining server architecture is largely monolithic and designed to run
alongside physical storage within a single node. As table sizes and concurrent
requests grow, the owner organization will typically upgrade hosting hardware,
i.e. faster processors, more memory, and additional disk storage, as described
by Dong and Li [6]. The authors start their research by identifying the issue
that many organizations have built persistent storage around a relational
database management system (RDBMS) such as MySQL. As the volume of information
has seen an explosive increase, these systems are difficult to expand. To
address this, most organizations upgrade the database server hardware, migrate
to a NoSQL or perform sharding such that the smaller partitions may be
distributed.

The authors implemented a novel middleware application that allows multiple
MySQL databases to interact as a single distributed entity. A MySQL interface
was exported to external clients. Incoming queries were parsed and executed
locally and then sent to the appropriate node in the distributed cluster of
MySQL servers. The middleware ran a sharding algorithm that allowed data
slices to be sent to the appropriate data nodes. A set of rules within the
middleware configuration files defines the algorithm. The master node
implemented direct and semi-direct table join strategies to support queries
across the distributed nodes. After performing some functional tests, the
authors concluded that their distributed database had a slower response time
than a single database queried for the same data. However, the advantage of
their system is evident with massive datasets that cannot fit within a single
MySQL server.

Yadav and Rahut [32] authored a paper that describes how Meta redesigned their
MySQL datastore replication protocol to use a modified version of Raft [21]
instead of traditional semi-synchronous replication. Raft is a consensus
algorithm that uses an elected leader. It imposes restrictions that only
servers with the most up-to-date data can become leaders. Data is sharded into
many MySQL databases, utilizing a primary and many replicas. Raft enables the
control plane and data plane operations to be part of the same replicated log.
Membership and leadership are moved inside the MySQL server, resulting in
provable correctness during promotions and membership changes. This paradigm
does not make sharding any easier, but rather optimizes the replication across
multiple existing MySQL replicated databases.

####  II-B6 Google Spanner

Although Spanner is not used directly for the experiments in this paper, it is
relevant since it provides the storage engine to Firestore. Corbett et al.
[16] is a collaboration of 23 authors within Google to discuss the mechanism
and benefits of Spanner’s distributed database service. The authors continue
to describe the architecture with a focus on distributed storage and timestamp
management. They claim that the database is “semi-relational” with schematized
tables, yet it still supports an SQL query interface. Below this layer, data
is physically stored as key-value associated pairs across a large distributed
network defined by a universe master, multiple zone masters and span servers.
The paper presents benchmark results for commit time of 1 to 200 participants
(ranging from 14.6 to 122.5 milliseconds). They conclude with a plug for the
TrueTime timestamp API that is the “linchpin” of Spanner’s advanced feature
set.

##  III System Architecture

###  III-A Firestore

The first set of experiments is centered on a non-relational NoSQL database.
Firestore was chosen for its flexible billing structure and serverless
architecture. Firestore uses a document model [20] within its DBMS. The first
benefit of the document model is being schema-free. Documents may be inserted
with unstructured data, allowing changes in data shape (i.e. different fields
and types) without requiring a migration of the whole database. The
unstructured format is especially helpful when an application such as the
energy marketplace is first ramping up and the persistent data requirements
may not be fully understood initially. Thus, a schema-free paradigm allows
incremental changes to what persisted as the application matures. The
unstructured format contributes to backward compatibility and database
performance, since it does not need to be taken offline to perform migrations.

Another benefit of the document model is horizontal scaling. Since the
documents exist independently of each other, it is possible to distribute a
collection across multiple physical compute nodes. As document number and
access traffic increases, this architecture allows for easy scaling with
additional nodes. Scaling is nearly linear and utilizes distributed hash
tables (DHT) that organize pairs of [key, value] into storage buckets [23].
These buckets may span unlimited server nodes.

Figure 4 shows a high-level overview of the Firestore stack. Client access is
provided through software development kit (SDK) libraries, which come in two
flavors. The “Server” SDK is trusted and used in applications running within a
Google Cloud service such as a Compute VM or a Cloud Function. It requires a
privileged environment, foregoes authentication, and provides automatic
retries with backoff.

Figure 4: Simplified diagram of the Firestore stack [25].

The second SDK is “Mobile and Web”, which is intended for untrusted third-
party devices. Both flavors of the SDK abstract the connection to the
Firestore API. They support blind writes and transactional writes based on
optimistic concurrency control while connected. Queries multiplex over the
same long-lived connection to the Frontend task. The Frontend tasks live in
the same region as the database. The co-existence means that client SDK
requests arrive at the closest-to-the-user Google point of presence, then get
routed to the Frontend task after the database location is fetched from the
Firestore metadata.

After a Frontend task receives the SDK request, it sends a remote procedure
call (RPC) to a Query Matcher. Figure 5 expands on the Real-time Cache block
from Figure 4 and shows the flow from multiple client users. The In-memory
ChangeLog ensures consistency through the check of timestamps. Approved
commits are forwarded to the Backend task.

Figure 5: Query processing through the Real-time Cache [25].

Firestore executes all queries using secondary indices. Each field in a
document automatically generates an ascending and descending ordered index,
all on a per-collection basis. Automatic indexing may be excluded for a
specific field if so configured. Write operations are slightly more expensive
latency wise, due to the need of updating multiple indices.

Google Cloud Spanner [16] is used for the database storage engine. Each
Firestore database maps to a directory within some number of pre-initialized
Spanner databases in the corresponding region. Each directory has two tables,
_Entities_ and _IndexEntries_ containing the Firestore database data.
Firestore documents are stored in the _Entities_ table, one document per row,
with the contents (up to 1 megabyte) in a single column and the document key
as the primary ID for the row. Each index is stored within a single row of
_IndexEntries_. To handle distribution, load balancing and scaling, Spanner
performs automatic splitting and merging of rows into _tablets_. This process,
similar to the sharding [3] performed by other relational database management
systems (RDBMS), allows flexible horizontal scaling in an unlimited cluster of
nodes.

###  III-B MySQL

As a relational database, data is stored in table objects that support
relations between them, i.e. a data column in one table may point to a record
in another table. These relations support the normalization of data: A data
item exists only in one table, yet may be referenced repeatedly from other
tables in a one-to-many relationship. Data normalization provides two main
benefits. The first is that the database may take less physical storage
because data duplication is not required. Second, write operations may require
less latency since new data is only written to a single table and then
referenced elsewhere if needed.

Another benefit of the RDBMS model is the strict adherence to the ACID
properties of a transaction. These are atomicity, consistency, isolation, and
durability. The MySQL Transaction Management layer allows data manipulation
operations that ensure that the database does not have the results of a
partial operation [22]. Figure 6 graphically illustrates the Transaction
Management sitting between the Query Processor and Storage Management.

Figure 6: Detailed heterogeneous conceptual MySQL architecture [24].

###  III-C GraphQL

GraphQL is not a product on its own, but rather a query-language
specification. The implementation used in this study is Apollo Server [2], an
open-source implementation compatible with any GraphQL client. The server
provides a middleware between the client and the database API. It performs the
following tasks on each query received:

  1. 1.

Parse: converts the query to an abstract syntax tree.

  2. 2.

Validate: checks that the query matches the schema.

  3. 3.

Execute: begins at the root of the tree and traverses, each field being a node
in the tree. Nodes at the same level execute their resolvers concurrently.

All fields to query, variables passed in as query arguments, and response
results are strongly typed. GraphQL configuration involves defining an API:
including defining types for each field (including condition arguments and
return results) and the resolver functions that are correspondingly called
when the field is included in the client query.

The GraphQL query structure is designed to resemble the expected results
structure. All desired values are returned with a single query of the GraphQL
API. The GraphQL server middleware then takes care of fetching the query
fields from potentially multiple sources and endpoints. Thus, the API acts as
an aggregator that can greatly simplify the client complexity and provide a
single endpoint enforced with strong typing for the client to send the query.

Figure 7: Data tree and resolver sequence for a typical query as used in
Section IV-D.

Figure 7 shows a query that is used in the experiments in Section IV-D. Rather
than structuring the fields at the same level in the tree (which would cause
the corresponding resolver functions to be called simultaneously), they are
nested. Nesting allows the resolver of each child field to apply to only the
data set returned from its parent. Since Firestore cannot support multiple
field range queries, Figure 7 demonstrates how the top field in the GraphQL
query calls the Firestore API, but subsequent child field resolvers only
filter the parent field’s data. Since GraphQL abstracts the back-end database,
there exists flexibility to switch from NoSQL to SQL (or any other persistent
storage solution) in the future.

##  IV Experiments

The experiments provide a comparative benchmark of read, update, and create
performance between relational and non-relational database engines. In all
cases, $n$ represents the number of documents in a non-relational collection
or the records in a relational table. The set of documents or records
(chargers) in the response is represented by $R$ and $r$ is the size (number
of chargers) of $R$. Instead of using a third-party benchmarking tool, these
experiments use a custom client that performs read, update, and create
operations that mimic workflows in the energy marketplace. The registration of
a new IoT device, reading its persistent data, and searching for specific
combinations of fields are shown in Figure 1 and are covered in these
experiments. In addition to performing operations to mimic the marketplace
workflow, we also hit the databases with high-volume operations to determine
the limits of read-data rate and read-create operation rate.

The client, as described in Figure 3, is a mobile Edge device that sends
request messages to the Cloud framework. However, for the purposes of the
experiments described in this section, we used a workstation-based custom
client. This client emulates a user sending requests through their mobile
device and encapsulates all the database library code for both Firestore and
MySQL, as well as the workflow scenarios. All of the following experiments
that perform iterative operations do so sequentially. Both the iterative read
and iterative create experiments wait for each operation to complete prior to
executing the next. The sequential execution allows the characterization of
the read and create operation rate, but does not take into account contention
from multiple clients. In a production energy marketplace scenario, read and
update requests may be coming in from many clients simultaneously, which we do
not characterize in these experiments.

###  IV-A Non-Relational, NoSQL Database

The test database is divided into six collections ranging from $10^{1}$ to
$10^{6}$ documents. These documents represent mock data for the energy
marketplace. Each is a sample registration of a charger device with _name_ ,
_id_ number, _latitude_ , _longitude_ , _address_ and charger _type_. All have
a unique key string in addition to the listed fields. Each mock data set is
generated with a random generator algorithm that cycles each field between a
range of accepted values. The fields _latitude_ , _longitude_ , and _name_
cycle between five values, the _address_ cycles through four street names with
random house numbers, while the _type_ cycles between two levels. The
_latitude_ values range from $43.1$ to $47.9$ while the _longitude_ ranges
from $-124.9$ to $-120.1$. Table I shows the range of all fields, including
cardinality. The smaller the cardinality number, the less unique the values in
its distribution. Thus, the _type_ field with a cardinality of one has a high
level of repetition. The client tests update performance through a single
update to each collection (i.e. reserving a new charger), followed by rapid
sequential creates of all documents. These experiments measure both the time
it takes to execute a single update and how many create operations per second
are supported by the database. Read performance is measured through a single
document read of each collection followed by a rapid sequential single read of
all documents. The time to execute a single read operation is measured
followed by the attainable read operations per second. Next, the client
performs a bulk read of all documents in each collection. The time to execute
provides the data rate that can be read.

TABLE I: The mock data distribution. The data generator creates sets of mock data, one for each $n$. The $id$ field sequentially increases for every mock charger. The $name$ field has a set of five names which are randomly selected. The $address$ field randomly selects a house number the given range and then randomly selects one of four street names. The $latitude$ and $longitude$ randomly select between the range. The $type$ randomly selects between two.  Field |  Range |  Cardinality
---|---|---
$id$ |  1-$n$ |  996,402
$name$ |  Howard, Gomez, Singh, Shipman, Durnin |  4
$address$ |  10000-11900: Cedar Ct, 118th Ave, 119th Ave Maple Hill Ln |  7,341
$latitude$ |  43.19 - 47.9 |  4
$longitude$ |  -124.9 - (-120.1) |  4
$type$ |  level1 or level2 |  1

Finally, pattern querying measures the combinations of reading. We define a
specific query pattern with equality conditions for the _name_ and _type_
fields and inequality ranges for both _latitude_ and _longitude_. Two queries
execute against each collection:

  * •

Read all documents and search for matches.

  * •

Read documents matching a single field range query (_latitude_) and search the
subset for remaining matches.

####  IV-A1 Reads

For the first experiment, a single document was read from each collection
using the document key (primary index). The 6 collection sizes ($n$) are:

  1. 1.

$n$ = 10 documents

  2. 2.

$n$ = 100 documents

  3. 3.

$n$ = 1000 documents

  4. 4.

$n$ = 10000 documents

  5. 5.

$n$ = 100000 documents

  6. 6.

$n$ = 1000000 documents

For each, the document key _1_11899NW118thAve_ was used to perform a single
read operation. Figure 8 shows that the time to complete each read operation
is reasonably consistent across the collection size range. The distributed
hash table (DHT) lookup of the primary key explains this consistency.

Figure 8: A single read operation performed against each of the 6 collections.
The time to complete the operation is shown in milliseconds (ms).

The next experiment involves iteratively reading every document in each
collection. The _id_ field, a secondary index, is used to perform a simple
read operation. The _id_ field is a sequential integer assigned to each
document. It has no direct meaning for the charger represented in the
document, but allows for an easy sequential query of all documents in the
collection. Note that this field is automatically indexed by the Firestore
service to increase the performance of their query engine.

Figure 9: Iterative read operations performed against each of the 6
collections using a secondary index. Each unique _id_ field is successively
queried. Time to complete all the operations is shown in milliseconds (ms) on
a logarithmic scale. There were repeated system timeouts while iteratively
querying the $10^{6}$ collection, thus we only show five collections here.

Figure 9 shows the time taken to iteratively read all the documents. A
sequential read like this does not directly represent a user workflow in the
marketplace, but allows us to characterize the read operation rate of the
database (shown in Table II). Time complexity consists of the query time for
each _id_ field across the $n$ documents in the collection. Thus, the total
time is

$T_{C}(n)=O(n+O_{D}(n))$ (1)

where
$D$: A single read operation via an _id_ query.
$C$: Iteration across the entire collection.

Since the query of each document utilizes a pre-generated secondary index, the
query engine performs a binary search to quickly match the _id_ value in the
index. This binary search has the worst-case time complexity of
$T(n)=O(\log{n})$ for the first lookup and sequential after that. Substituting
this into Equation 1, we can simplify to:

$T(n)=O(n+\log{n})=O(n)$ (2)

The time complexity is linear. Note that $r=1\forall n$ since every iteration
returns exactly one document.

The final read experiment involves a read of all the documents in each
collection. The operation does not use a primary or secondary index. Figure 10
shows the time taken to read all documents by collection. In this scenario,
$r=n\forall n$ since all documents in the collection are always returned.

Figure 10: A read all operation performed against each of the 6 collections.
All documents are read in a single operation. Time to complete all the
operations is shown in milliseconds (ms) on a logarithmic scale.

The time to read all documents is directly proportional to the number of
documents returned. A bulk query such as this is not in any user workflow, but
allows characterization of the data rate as seen in Table II. The time
complexity for this operation is linear and is represented by Equation 3.

$T(n)=O(n)$ (3)

We finish the read analysis with the performance metrics shown in Table II.
The time in milliseconds to perform a single read operation with a known key
(primary index) is taken directly from Figure 8. Operations per second are
calculated by dividing the number of iterative operations in Figure 9 by the
time to complete. The kilobytes per second data rate uses an average document
size of 76 bytes, multiplies by the number of documents, and divides by the
time taken to complete a read-all operation as shown in Figure 10.

TABLE II: A summary of all read metrics. The first column represents the number of documents in the collection. The second is the time taken for a single read operation with a known key. The third is the operations performed per second while iteratively reading all documents via a secondary key. The fourth column represents data rate during a read-all operation. | n | ms/oper | ops/s | kB/s
---|---|---|---|---
| 10 | 188 | 10.917 | 9.708
| 100 | 182 | 20.593 | 109.390
| 1,000 | 179 | 19.247 | 340.643
| 10,000 | 168 | 19.250 | 503.893
| 100,000 | 188 | 24.759 | 644.269
| 1,000,000 | 236 |  | 554.681
Mean |  | 190.167 | 18.953 | 360.420

####  IV-A2 Updates and Creates

For the next set of experiments, we will focus on characterizing the update
and create operations. These largely mirror Section IV-A1 and much of the
background explanation applies. The first experiment performs a single update
operation on an existing document in each collection. A known key,
_1_11899NW118thAve_ , is used in all cases. Note that for all create
operations, a previously generated and randomized set of mock data is used to
represent each charger device.

Figure 11: A single update operation performed against each of the six
collections. Time to complete the operation is shown in milliseconds (ms).

Figure 11 shows the time in milliseconds for the operations to complete. As
with reads, a single update completes in consistent time regardless of the
size of the collection. Next, Figure 12 shows the iterative create of all
documents in the collections. As with reads, this scenario does not represent
a user workflow, but allows us to characterize the create operation rate, as
shown in Table III. A high create operation rate is not critical for the
energy marketplace, but is a useful metric when comparing NoSQL vs. SQL. The
time complexity for all $n$ operations to complete follows that of the read
scenario where $T(n)=O(n+\log{n})$ or linear time. Note that a create-all
operation is not possible through this client interface and is not tested
here.

Figure 12: An iterative create operation performed against each of the 6 collections. All $n$ documents are created in sequence. Time to complete all the operations is shown in milliseconds (ms) on a logarithmic scale. TABLE III: A summary of the update and create metrics. The first column represents the number of documents in the collection. The second is the time taken for a single update operation with a known key. The third is the operations performed per second while iteratively creating all documents. | n | Update ms/oper | Create ops/s
---|---|---|---
| 10 | 175 | 13.755
| 100 | 155 | 27.211
| 1,000 | 169 | 31.625
| 10,000 | 155 | 33.789
| 100,000 | 172 | 16.469
| 1,000,000 | 136 | 29.448
Mean |  | 160.333 | 25.383

Table III summarizes the milliseconds per update operation as shown in Figure
11 and the create operations per second that are possible as shown in Figure
12. Although the update and create results largely mirrored the read, we see a
slight performance improvement.

###  IV-B Reads with Conditions

The set of experiments that most mirrors real-world scenarios is the querying
of complex field patterns. Multiple range queries (inequality condition) of
secondary indices are not allowed in Firestore. Therefore, to read the
documents that match all the query conditions, some combination of querying
the database and data reduction at the client must be performed. For these
experiments, the following query conditions are used:

⬇

name: Howard

latitude: min 47.5 and max 48.0

longitude: min -122.5 and max -122.1

type: level2

The first of the query experiments reads all documents without a query. The
client is an Edge device coded in JavaScript that utilizes the Firestore
development kit. Client-side searching is performed to narrow down the
documents in each collection that match the given query parameters. This is
the most brute-force method and is shown here for comparison. Since we always
receive every document, $\forall n\;r=n$. Also, the search performance on the
client side will have some variability due to the processor capability.

Figure 13: A read all performed against each of the 6 collections. All
documents are read in a single operation, then reduced locally to those
matching the query conditions. Time to complete all the operations is shown in
milliseconds (ms) on a logarithmic scale.

Figure 13 shows the time to read all documents and reduce matches very closely
with the time to read all from Figure 10. The reduction time on the test
machine (M1 processor) is negligible. Thus, the response set size $r$ is the
defining factor for time complexity, which is linear time or $T(n)=O(n)$.

The next experiment performs a conditional query using the _latitude_ field.
It defines a range between the minimum and maximum values. The Firestore query
engine can only handle a single field range; thus we query only with
_latitude_. Firestore performs a binary search on the pre-generated _latitude_
index and returns an inflated $r$ set of documents that must be searched
locally on the client to match _name_ , _type_ and _longitude_. Figure 14
shows the time spent querying and subsequent data reduction.

Figure 14: Query the _latitude_ range in each of the 6 collections. All
_latitude_ -matching documents are read in a single operation, then reduced
locally to those matching the non-_latitude_ query conditions. Time to
complete all the operations is shown in milliseconds (ms) on a logarithmic
scale.

As expected, querying an index and reducing the smaller $r$ yields lower
latency times than querying all documents to reduce. Table IV shows the
reduction in latency achieved by the conditional read of _latitude_ , followed
by the data reduction.

TABLE IV: Summary of conditional read latency. The first column is the collection queried. The second column represents the latency required to read all documents with data reduction. The third reads only the documents matching the _latitude_ condition with data reduction. The fourth represents the latency difference between the two.  n |  Read All
Filter (ms) |  Read Latitude Filter (ms) |  Difference (ms)
---|---|---|---
10 |  300 |  171 |  -129
100 |  273 |  182 |  -91
1,000 |  508 |  234 |  -274
10,000 |  3,213 |  855 |  -2,358
100,000 |  31,425 |  7,718 |  -23,707
1,000,000 |  459,787 |  66,763 |  -403,024

###  IV-C Relational, SQL Database

The next set of experiments focuses on the SQL database and how the
performance differs from NoSQL. The terminology we use to describe the
experiments changes slightly from the NoSQL scenario in Section IV-A.
“Collections” and “documents” are now replaced with “tables” and “records” (or
“rows”). “Fields” are replaced with “columns”. The same mock data from our
NoSQL experiments is inserted into 6 tables that span $10^{1}$ to $10^{6}$
records. The largest difference in the experimental procedure is that queries
always return exactly the matching results. There is no need for the scenario
described in Section IV-B where a larger-than-needed result size $r$ is
returned and further reduced client-side to the matching records.

####  IV-C1 Reads, Updates and Creates

The single and sequential read, update, and create operations show similar
results to Firestore in Section IV-A. We only summarize. A summary of the read
metrics, Table V, shows that milliseconds per operation and operations per
second remain fairly constant with increasing $n$. However, the data rate
increases proportionally to increasing $n$ due to the almost constant read
time.

TABLE V: A summary of time per operation, operations per second and data rate in kilobytes for each of the 6 table sizes. The ops/s values are derived from the iterative read experiments while kB/s comes from the read all.  | n | ms/oper | ops/s | kB/s
---|---|---|---|---
| 10 | 84 | 38.911 | 26.477
| 100 | 82 | 54.975 | 274.118
| 1,000 | 105 | 55.630 | 1,879.032
| 10,000 | 86 | 63.180 | 12,944.444
| 100,000 | 90 | 62.519 | 48,240.166
| 1,000,000 | 106 | 48.751 | 624,664.879
Mean |  | 92.167 | 53.994 | 114,671.519
TABLE VI: A summary of time per operation (update) and operations per second (create) for each of the 6 table sizes. | n | Update ms/oper | Create ops/s
---|---|---|---
| 10 | 101 | 26.385
| 100 | 80 | 52.329
| 1,000 | 88 | 49.317
| 10,000 | 95 | 53.332
| 100,000 | 96 | 53.975
| 1,000,000 | 91 | 38.955
Mean |  | 91.833 | 45.715

We complete the summary of the update and create metrics in Table VI. The
average create operation rate was approximately 46 operations per second
regardless of the size of the table $n$. With both the update time per
operation and the create operations per second approximately constant, we
conclude that both updates and creates to a SQL, relational database may be
performed in constant time or $T(n)=O(1)$.

####  IV-C2 Reads with Conditions

The final set of benchmarks is based on the same conditions listed in Section
IV-B. These results are the most significant, since they show the scenario in
which the SQL database outperforms the NoSQL. This experiment represents the
marketplace scenario where a user queries a _latitude, longitude_ range along
with charger _type_ and seller _name_ as conditions. Figure 15 shows a very
slight increase in completion time as the size of the table $n$ increases. The
increase in time can be attributed to the larger response size $r$. Note that,
unlike with the non-relational, multi-field range queries, only the matching
records are returned. As the response set grows larger (i.e. $n=10^{6}$), the
execution time increases slightly. The increase is proportional to $r$, but is
kept minimal since only records that match the query conditions are returned.

Figure 15: Query with name=Howard, type=level2, 47.5 $\leq$ latitude $\leq$
48.0, -122.5 $\leq$ longitude $\leq$ -122.1. Time to complete all the
operations shown in milliseconds (ms) across all 6 tables.

The default behavior of MySQL is to not generate indexes for each column in
addition to the PRIMARY key. The lack of auto-generated indexes was confirmed
both on the Cloud SQL instance and on a local MySQL test server. The _SHOW
INDEX_ and _EXPLAIN_ SQL commands confirm that no indexes were either
generated or used during the course of executing the queries in the
experiments above.

###  IV-D GraphQL with NoSQL

In order to characterize the effect that GraphQL middleware has on latency, a
GraphQL server defines an API to reproduce the Firestore query and reduction
in Figure 14. The client sends the GraphQL query:

⬇

query {

latitude(

num: 1000000,

latmin: 47.5,

latmax: 48.0,

longmin: -122.5,

longmax: -122.1,

name: ’Howard’,

type: ’level2’)

{

longitude {

name {

type {

id

name

address

latitude

longitude

type

}

}

}

}

}

to the GraphQL API and converts to a tree syntax as shown in Figure 7. The top
level field is _latitude_ and triggers a resolver with the Firestore database
query. The results populate the data tree, which is then traversed with each
child field (_longitude_ , _name_ and _type_) resolver that further filters
based on the field query conditions. The time to resolve the queries for all
six collections is shown in Figure 16. The time complexity for this query is
near-linear, although there is a drop in latency at $n=100$. The drop occurs
since the connection setup time contributes more to the latency in the $n=10$
case.

Figure 16: GraphQL query with name=Howard, type=level2, 47.5 $\leq$ latitude
$\leq$ 48.0, -122.5 $\leq$ longitude $\leq$ -122.1. Time to complete all the
operations shown in milliseconds (ms) across all 6 collections.

##  V Discussion and Future Work

In Section I, we state that the evaluation of the persistent storage solution
would be based on:

  1. 1.

Latency performance.

  2. 2.

Cost.

  3. 3.

Flexibility and scalability.

We discuss these criteria for both the Firebase and MySQL solutions in the
following sections.

###  V-A Latency

In this section, we show an analysis to estimate latency based on the size of
the data set $n$ and the size of the response set $r$. The analysis is
performed on experimental latency results from both the Firestore and MySQL
databases with the aim of providing a predictive model for each. The models
estimate latency in milliseconds.

The goal of the model is to find a relationship between three variables: $l$
(latency), $n$ (data size), and $r$ (response size). The dependent variable
$l$ is related to the independent variables $n$ and $r$. A Multiple Linear
Regression could model this relationship accurately if the predictors are
uncorrelated. However, Table I shows that the mock data set is based on a
small pattern that repeats randomly. As such, we expect a high correlation
between $n$ and $r$ (data-based multicollinearity) and may need to focus only
on a single predictor. The Multiple Linear Regression takes the general form
of:

$Y=\beta_{0}+\beta_{1}X_{1}+\beta_{2}X_{2}$ (4)

$Y$ is the output or dependent variable. The $X$ terms represent the
corresponding input or independent variables. Each predictor (input) has a
corresponding slope coefficient $\beta$. The first $\beta$ ($\beta_{0}$)
represents the intercept constant. In our case, we can restate this equation
more specifically as Equation 5. The determiner method [5, 17, 18] is used to
solve this equation as shown in Equation 6.

$l=\beta_{0}+\beta_{1}n+\beta_{2}r$ (5)

$\begin{vmatrix}l&n&r&1\\\
\sum_{i=1}^{n}l&\sum_{i=1}^{n}n&\sum_{i=1}^{n}r&n\\\
\sum_{i=1}^{n}nl&\sum_{i=1}^{n}n^{2}&\sum_{i=1}^{n}nr&\sum_{i=1}^{n}l\\\
\sum_{i=1}^{n}rl&\sum_{i=1}^{n}nr&\sum_{i=1}^{n}r^{2}&\sum_{i=1}^{n}r\end{vmatrix}=0$
(6)

Rather than solving Equation 6 by hand, we use the Microsoft Excel Data
Analysis tool [19] to calculate the $\beta$ coefficients on the following
data.

####  V-A1 Firestore Analysis

We will focus only on the read operation utilizing the _latitude_ range
condition shown in Table VII. The other fields such as _longitude_ , _name_
and _type_ must be filtered on the client side and are included in the latency
values. The read and filter is the most applicable scenario in the energy
marketplace. Create, update, and delete operations occur less frequently and
are not as relevant to the marketplace performance and user experience.

TABLE VII: Firestore latency for read operations utilizing _latitude_ range condition from Figure 14. Both $n$ and $r$ are predictor variables and $l$ is in milliseconds. Latency ($l$) | Documents ($n$) | Results ($r$)
---|---|---
171 | 10 | 3
182 | 100 | 20
234 | 1000 | 181
855 | 10000 | 1970
7718 | 100000 | 20028
66763 | 1000000 | 199958

We performed regression analysis on the Table VII data in three passes: using
both $n,r$ as predictor variables, $n$ only, and $r$ only. Table VIII
summarizes the results and shows the $\beta_{0},\beta_{1},\beta_{2}$
coefficients for each pass as well as the standard error (SE) on the predictor
coefficients $\beta_{1},\beta_{2}$.

TABLE VIII: Summary of regression analysis based on $n$ as a predictor, $r$ and $n,r$ together. The coefficient values of the model are shown, as well as the standard error for the $n$ and $r$ coefficients. Variables | $\beta_{0}$ | $\beta_{1}$ | SE($\beta_{1}$) | $\beta_{2}$ | SE($\beta_{2}$)
---|---|---|---|---|---
$n$ | 340.139 | 0.066 | 0.0004 | – | –
$r$ | 341.394 | – | – | 0.333 | 0.002
$n,r$ | 394.892 | -2.761 | 1.118 | 14.141 | 5.593

Using both $n,r$ in the regression results in a negative $n$ coefficient and a
standard error of 5.59, both likely due to the correlation between $n$ and
$r$. With only $n$ as the predictor, the standard error is a low 0.0004. Using
only $r$ as the predictor results in a slightly higher standard error of
0.002. The differences in slope, $\beta_{1},\beta_{2}$, between the dual
predictor case and the single predictors further confirm the high correlation
between $n$ and $r$. Given that a model with a single predictor $n$ yields the
smallest standard error, the most accurate predictive model can be represented
as:

$l=340.139+0.066n$ (7)

####  V-A2 MySQL Analysis

Now we do the same for MySQL read operations querying a specific combination
of conditions. In this scenario, all four conditions are added to the query
such that only matching records are returned. This contrasts with the
Firestore results from Section V-A1 above, where the query engine could only
support a single field range condition.

TABLE IX: MySQL read operation latency while querying _latitude, longitude_ range and _name, type_ equality condition from Figure 15. Both $n$ and $r$ are predictor variables and $l$ is in milliseconds. Latency ($l$) | Records ($n$) | Results ($r$)
---|---|---
101 | 10 | 1
80 | 100 | 0
84 | 1000 | 2
122 | 10000 | 32
156 | 100000 | 403
905 | 1000000 | 3874

Performing the same analysis on Table IX results in the coefficients and
standard errors shown in Table X.

TABLE X: Summary of regression analysis based on $n$ as a predictor, $r$ and $n,r$ together. The coefficient values of the model are shown, as well as the standard error for the $n$ and $r$ coefficients. Variables | $\beta_{0}$ | $\beta_{1}$ | SE($\beta_{1}$) | $\beta_{2}$ | SE($\beta_{2}$)
---|---|---|---|---|---
$n$ | 90.816 | 0.0008 | 0.00002 | – | –
$r$ | 90.58 | – | – | 0.21 | 0.005
$n,r$ | 92.457 | 0.006 | 0.003 | -1.33 | 0.818

Again, as we saw in the Firestore case, the correlation between $n$ and $r$
resulted in both a negative slope coefficient on one predictor and a higher
standard error when calculating a regression with both predictors. With a
single predictor regression, $r$ has a standard error of 0.005 while $n$ is
smaller at 0.00002. Thus, the three-pass regression leads to the most accurate
predictive model of:

$l=90.816+0.0008n$ (8)

####  V-A3 GraphQL Analysis

For the GraphQL analysis, we will introduce a new predictor variable
$r^{\prime}$ that represents the set of matched results after filtering
through the tree. For this query, the resolvers for each of the four fields
are chained. The root field, _latitude_ , sends a request to the Firestore API
and populates the data tree with $r$ charger nodes, as shown in Table XI. Each
of the next three resolvers (_longitude_ , _name_ and _type_) then traverse
the tree and only return those nodes matching the conditions. The final set of
nodes returned after the traversal is $r^{\prime}$.

TABLE XI: GraphQL latency for read operations utilizing _latitude_ range condition from Figure 16. Predictor variables are $n$, $r$ and $r^{\prime}$ while $l$ is in milliseconds. Latency ($l$) |  Documents ($n$) |  Latitude Results ($r$) |  Matched Results ($r^{\prime}$)
---|---|---|---
174 |  10 |  3 |  1
131 |  100 |  20 |  0
362 |  1000 |  181 |  2
729 |  10000 |  1970 |  32
4150 |  100000 |  20028 |  403
41000 |  1000000 |  199958 |  3874
TABLE XII: Summary of regression analysis based on $n$ as a predictor, $r$, $r^{\prime}$ and $n,r,r^{\prime}$. The coefficient values of the model are shown, as well as the standard error for the $n$, $r$, and $r^{\prime}$ coefficients. Var |  $\beta_{0}$ |  $\beta_{1}$ |  SE($\beta_{1}$) |  $\beta_{2}$ |  SE($\beta_{2}$) |  $\beta_{3}$ |  SE($\beta_{3}$)
---|---|---|---|---|---|---|---
$n$ |  205.35 |  0.04 |  0.0001 |  – |  – |  – |  –
$r$ |  206.15 |  – |  – |  0.20 |  0.0007 |  – |  –
$r^{\prime}$ |  192.67 |  – |  – |  – |  – |  10.53 |  0.055
$n,r,r^{\prime}$ |  147.00 |  1.98 |  0.34 |  -10.0 |  1.82 |  15.80 |  5.05

After performing a four-pass regression (we now include $r^{\prime}$ as a
predictor), the coefficients and standard errors are shown in Table XII. The
results are similar to those of both Firestore and MySQL in that using $n$ as
a single predictor yields the lowest standard error while combining the
predictors results in a negative coefficient as well as higher standard errors
due to the correlation between the predictors. Thus, our most accurate
predictive model is as follows:

$l=205.35+0.04n$ (9)

###  V-B Cost

To objectively compare the cost of both solutions, we focus on the worst-case
scenario storing $10^{6}$ charger data objects over one month. We calculate
the cost to create, store, and read the entire dataset. Each charger object
create and read is performed in an individual operation. The average raw data
object size is 76 bytes, giving a stored data size of 76 MB. Here is the
breakdown:

####  V-B1 Firestore

Firestore only bills per use, that is, per CRUD operation and per unit of
storage [10]. Note that the experiment is performed in a single day, yet the
service remains up for one month. Although the raw data added to the $10^{6}$
collection is 76 MB, the Firestore stored data quota shows that 1.9 GB were
used. The indexes for each field and the auxiliary metadata consume the extra
storage for the collection.

TABLE XIII: Firestore cost estimate to create, store and read $10^{6}$ documents (76 MB) in one month. The second column is the free quota per day, while the third is the price per unit thereafter. The fourth column is the amount billed after the free tier, while the fifth column represents the total cost. | Free | Price | Used | Total
---|---|---|---|---
Document writes | 20,000 | $0.09/100,000 | 980,000 | $0.882
Document reads | 50,000 | $0.03/100,000 | 950,000 | $0.285
Document storage | 1 GB | $0.15/GB/month | 0.9 GB | $0.135
Internet ingress | all | 0 | 0 | $0.00
Internet egress | 10 GB | $0.12/GB | 0 | $0.00
Total |  |  |  | $1.302

As shown in Table XIII, the total cost of a one-month experiment is $1.30.
Note that this cost is only incurred during use. CRUD operations and data
storage are only billed after the free daily quota is exceeded.

####  V-B2 MySQL

Cloud SQL MySQL follows a different billing model. Costs are incurred for the
dedicated compute in conjunction with the corresponding storage [13]. In our
experiments, the service is provisioned with 1 vCPU, 614.4 MB memory, and 10
GB SSD storage for one month.

TABLE XIV: MySQL cost estimate to create, store and read $10^{6}$ documents (76 MB) for one month. The majority of the billing (excluding network egress) is per resource, not per transaction. | Free | Price | Used | Total
---|---|---|---|---
vCPU | 0 | $30.149/unit | 1 | $30.15
Memory | 0 | $5.11/GB | 0.614 | $3.14
Storage | 0 | $0.17/GB | 10 | $1.70
Internet ingress | all | 0 | 0 | $0.00
Internet egress | 0 | $0.19/GB | 0.076 | $0.01
Total |  |  |  | $35.00

Table XIV shows that our costs to run this service for one month are $35.00.
As we are provisioning a perpetual server (not serverless), costs are incurred
regardless if the service is used or not. The $10^{6}$-record table represents
76 MB of raw data, but consumes 1.12 GB of database storage due to index and
metadata overhead. MySQL’s overhead is significantly less than Firestore’s 1.9
GB needed to store $10^{6}$ charger objects. In our experiments, database
access is restricted to a limited scenario, resulting in Firestore costing
less than the MySQL solution. However, in real-world use, it is possible that
the number of transactions will increase to a point where a transaction-based
pricing model is more costly than a perpetual resource-based one. Keeping the
same experimental setup and increasing both reads and writes to 1,000,000 per
day (each) results in a monthly Firestore cost of $35.20 which, thereafter,
exceeds Cloud SQL MySQL. Figure 17 shows the growth of Firestore costs as the
number of transactions increases, while MySQL remains more constant.

Figure 17: A monthly cost comparison between Firestore and Cloud SQL MySQL.
The x-axis represents the number of read and write transactions (each) per
day. Only the data transferred is factored into the cost. Other storage costs
for index and metadata are ignored in this comparison.

The described limited scenario is suitable for a development environment. The
experiments established a billing period of one month with a large number of
read and write transactions occurring in a single day. Outside of these
transactions, Firestore does not incur any costs and is thus convenient (and
economical) for a start-and-stop style of iterative development. The cost can
also be reduced with Cloud SQL by stopping the compute service while it is not
being used for active development. However, IP address reservations and
storage charges still continue even while the compute instance is paused. In a
production environment, a $35/month overhead to run a MySQL server is nothing.
Schema-enforced typing may also reduce developer costs by reducing the need
for type checking in the application code.

###  V-C Flexibility and Scalability

Analyzing flexibility and scalability is a more subjective discussion (without
empirical data) based on the system architecture as detailed in Chapter III.
To recap, the NoSQL, non-relational, document-model implemented by Firestore
brings the following benefits:

  1. 1.

Stored documents are schemaless, allowing new fields and a changing data shape
as development of the marketplace progresses.

  2. 2.

Data is stored as a document object with a key-value. Each document has no
relation defined to any other. Thus, horizontal scaling can be easily
performed by distributing documents across multiple nodes, zones, and regions.
This can be done as the database and client demands grow without requiring any
service outage.

  3. 3.

Separating the management layer (DBMS) from the storage engine allows for easy
implementation of a serverless offering. No dedicated perpetual server is
needed. Firestore listens for client requests on a behind-the-scenes shared
compute cluster and only charges per transaction.

In contrast, Cloud SQL MySQL deploys a server-based instance that is always
consuming compute resources along with associated billing. Tables must define
a schema, and any updates to this schema require a migration of the entire
table or the creation of a new table. The migration case will typically
involve service downtime as well as developer effort. Finally, as the table
grows in data size and number of records, distribution across multiple nodes
requires a complex sharding process that involves splitting the table into
child objects and recreating all affected relations. This sharding will also
typically involve downtime for users.

###  V-D Discussion

In this section, we summarize the significant results and compare. In Sections
IV-A and IV-C, we deliberately chose to ignore delete operations and focused
on create, update, and read. The reads were divided into single operations
querying through a primary key and complex queries involving _latitude_ ,
_longitude_ , _name_ and _type_ conditions. In the Firestore case, only one of
these conditions may be an inequality (range). MySQL is able to process
queries that contain multiple range conditions. This restriction means that
Firestore is forced to return a larger response set than needed and perform
further data reduction on the client.

The create, update, and delete operation performance is not as relevant to the
energy marketplace. With only a small group of participants (probably one)
performing any of these operations at a time, it is unlikely that a small
increase in latency would affect the user experience. Atomicity, consistency,
and isolation are more critical due to potentially overlapping updates.
However, read performance is very relevant. Querying small datasets $n$ with
small response sets $r$ is fast regardless of the database used. In the larger
datasets, we begin to see the differences between the NoSQL and SQL solutions.

To illustrate, take the use case of 1,000,000 chargers. The Firestore solution
stores each as a document, while MySQL is a record within a table. MySQL is
able to query these 1 million records for our specific combination of
conditions (two inequality and two equality) and return a matched 3,874
records in $\leq$ 1 second (see Table IX). Firestore queried the same data
distributed as 1 million documents in a collection using only the _latitude_
condition. It took 66 seconds to respond with 199,958 documents, then further
reduce to match the _longitude, name, and type_ conditions to get the desired
3,874 hits. MySQL, with its more sophisticated SQL query engine, clearly has
an advantage here.

Table II shows that, in our experimental setup, Firestore read operations were
able to average 19 operations per second while transferring 360 kilobytes per
second. Table V shows MySQL achieving 54 operations per second with 114,672
kilobytes per second respectively. Clearly, MySQL has the advantage in read
performance, while being averaged across all dataset sizes. Comparing write
operations (create or update) from Table III and VI, we see that Firestore is
able to sustain an average of 25 operations per second while MySQL can do 45
operations per second. Again, the advantage to MySQL. Additionally, the size
of the table consumes server resources and has a finite limit. The storage
ceiling may result in degraded performance at the upper limit that requires
hardware upgrades or sharding to other nodes. Even with the raw latency
advantage demonstrated by MySQL in our experiments, Firestore is preferable
for expanding the marketplace long-term, as it is the more consistent solution
without any known upper limit.

For the purpose of these experiments, all data associated with a charger is
stored in a single document or record. As the marketplace expands, additional
data fields, such as user details, may be required. In Firestore, creating a
new document in a ”Users” collection to store a picture, charging history, and
user details makes the most sense. There is a feature in Firestore that allows
the ”Users” to be a subcollection of the chargers collection. As such, the
charger document may contain a reference to the appropriate ”Users” document.
All CRUD operations would need to be duplicated to access both documents. The
latency numbers would need to increase accordingly. If a subcollection is not
possible, another strategy is to duplicate the data in both documents. Again,
CRUD operations must be duplicated and persistent storage is increased. MySQL
handles this scenario much better through its relations. The ”Users” data can
be located in another table and linked to the charger record through a
relationship. The SQL engine dereferences the relation and automatically
performs a JOIN for queries that target the charger record. The latency,
storage, and development complexity are minimized compared to Firestore.

Cost is a huge factor as described in Section V-B. Our limited, month-long
scenario that created, stored, and read one million data objects resulted in a
Firestore estimated cost of $1.30 while MySQL costs $35.00. The NoSQL document
model database lends itself to serverless operation and shows a significant
advantage in providing a pay-as-you-use service.

The insertion of GraphQL middleware provides a significant simplification of
the client code. A very simple query string is defined that represents the
desired response data structure. This query is sent as a single request to the
GraphQL API, which filters the response data to an exact match of query.
Filtering eliminates the need for data reduction on the client side to sort
through the response data. Additionally, all fields, field arguments, and
response data are strongly typed providing enhanced safety. These types may
evolve over time and can be handled through versioning of the resolver
functions. GraphQL does not provide much latency improvement, as the
bottleneck step of querying a single range field within Firestore still
exists.

###  V-E Future Work

In Section V-D, we discussed how Firestore has an advantage with cost and
scalability, yet it is at a disadvantage with query latency as result sets
increase in size. Placing a high-performance GraphQL middleware layer provides
some optimization, yet does not solve the bottleneck of Firestore’s inability
to query multiple range fields simultaneously.

A strategy for latency optimization is to organize the collections in such a
way as to reduce the size of the result set. Geohashing [11] provides a
framework to numerically combine latitude and longitude values. A single hash
value can be queried from the database within a range that represents the
desired geographical radius.

##  VI Conclusion

The peer-to-peer energy marketplace for EV charging is a hybrid of Edge
devices and Cloud services. Within these services, persistent data storage is
critical. The details of each charger device must be stored along with user
details, session history, billing information, user ratings, amenities, and
tourism information. A Cloud-hosted database can persistently store the data,
support performant queries, and provide atomicity, consistency, isolation, and
durability (ACID) guarantees along with a convenient pay-as-you go billing
model. The nature of the marketplace workflow causes the create, read, update,
and delete (CRUD) operations to not have equal requirements. For example:
create, update, and delete operations are performed by a single user at a
time, thus resulting in eventual consistency being acceptable. Per the
consistency, availability, and partition tolerance (CAP) theorem trade-off,
the marketplace prioritizes availability and partition tolerance. However,
read operation performance is critical, as it involves multiple users querying
the same dataset within a mobile setting. Additionally, these read operations
must be able to support querying multiple range (i.e. inequality condition)
fields simultaneously, overlapping in time and competing for memory and CPU
resources.

This study focuses on two popular database services provided by Google to
solve the persistent storage research problem for the energy marketplace.
Firestore is a NoSQL, document-model, non-relational and serverless database
service while Cloud SQL MySQL is a perpetual server, table-based, relational,
and SQL. Each has its own features and characteristics which we evaluate based
on the following criteria:

  1. 1.

Latency performance.

  2. 2.

Cost.

  3. 3.

Flexibility and scalability.

The SQL database is the established solution, but it has shortcomings in terms
of cost and scalability. The shortcomings lead to our research question: can
the Firestore solution be used to support the energy marketplace framework? We
dig into Firestore’s architecture and identify these benefits:

  1. 1.

Schemaless data storage provides the flexibility to change document content
(data shape) without requiring the complexity and outage to migrate the entire
table. This raises data safety concerns, since a schema is not enforced on
incoming data, leading to possible data errors or security vulnerabilities. In
addition, handling changes in the shape of the data may add additional
complexity in development.

  2. 2.

Each document is stored as a data object independent of the rest. This
granularity without relationship links means that physical storage can easily
be spread over multiple nodes, zones, and regions. Horizontal scaling can take
place without interruption.

  3. 3.

The document model allows for an easy separation of the database management
system (DBMS) and the storage engine with physical storage. This enables a
serverless offering using shared compute and pay-per-transaction pricing.

The more established MySQL architecture exists as a monolithic set of
processes that must exist on a single node. Any distribution requires a
complex sharding of the table(s) and an update of the relations. However,
MySQL provides the following benefits:

  1. 1.

The SQL query engine is powerful and can support multiple column range
conditions within a single query. Response records are an exact match,
preventing the need to perform data reduction at the client.

  2. 2.

Data can be normalized such that only a single instance exists in the database
with multiple relations pointing to it. The normalization allows faster writes
since data does not need to be written in multiple places, as well as reducing
physical storage size.

The experiments benchmark the latency performance under different load
scenarios. The experiments mimic the energy marketplace workflow from an Edge
client. CRU tests were performed against six data sets that increased from
$10^{1}$ to $10^{6}$ individual data structures, each representing a charger
device. Update operations using the primary key averaged 160 ms in Firestore
but only 92 ms on MySQL. The read operations with primary key averaged 190 and
92 ms, respectively. Firestore was only capable of an averaged data rate of 19
operations/s and 360 kB/s, while MySQL could achieve 54 operations/s and
114,672 kB/s. An additional read scenario ignored the primary key and instead
queried with two inequality conditions (_latitude, longitude_) and two
equality (_name, type_). This combination represents a typical workflow in the
marketplace. For up to 10,000 chargers, both databases returned the matched
results in less than 1 second. However, as the size of the data set $n$
increased, Firestore was hampered by the limitation that its query engine
could only support a single inequality at a time. Thus, we must query for
_latitude_ , return a much larger-than-necessary response set $r$, and do a
client-side data reduction. The worst-case dataset size of 1,000,000 took an
incredible 66,763 ms seconds with Firestore while MySQL required only 905 ms.

To optimize the Firestore solution, further experiments were conducted with a
GraphQL middleware between the client and the database. This addition resulted
in a significant simplification of the client code. The client-side query is
written as a string containing the desired fields and their corresponding
conditions. The query is written to match the desired response data shape. The
need to sort through the response data and perform data reduction at the
client is eliminated. Additionally, the query fields, arguments, and response
data are all strongly typed, which enhances code safety. As the data shape
evolves with future marketplace releases, type changes can be handled within
the GraphQL API definition. However, GraphQL does not provide any improvement
in query latency, since the bottleneck step of querying a single range field
in Firestore from the GraphQL resolver function is still present.

MySQL is the clear winner in raw latency performance, especially once complex
combinations of conditions are used while querying. However, for a one-month
billing cycle that involves the writing, storing and reading of 1,000,000
charger structures, Firestore costs were $1.30 while MySQL was $35.00. The
MySQL server was perpetually deployed and incurred costs regardless of whether
there was any user traffic. With the energy marketplace in its early
development stages, the cost difference is a deciding factor. Additionally,
the flexibility of the schemaless document model allows features to be
deployed in stages with the persistent data adapting to match. Finally, as the
marketplace grows (we hope!), the serverless document-model seamlessly scales
horizontally without limit to accommodate both capacity increase and
geographical diversity.

## Acknowledgements

The author would like to thank Dr. Wu-chang Feng, Dr. R. Bruce Irvin and Dr.
David Maier at Portland State University for their assistance, valuable
advice, direction and mentorship provided throughout this study.

## References

  * Airbnb [2022] Airbnb, 2022.  URL https://www.airbnb.com.  Accessed: 2022-08-13.
  * Apollo [2023] Apollo.  Apollo server, 2023.  URL https://www.apollographql.com/docs/apollo-server.  Accessed: 2023-09-09.
  * Cattell [2011] R. Cattell.  Scalable SQL and NoSQL data stores.  _SIGMOD Rec._ , 39(4):12–27, May 2011.  ISSN 0163-5808.  doi: 10.1145/1978915.1978919.  URL https://doi.org/10.1145/1978915.1978919.
  * Chamberlin [2012] D. D. Chamberlin.  Early history of SQL.  _IEEE Annals of the History of Computing_ , 34(4):78–82, 2012.  doi: 10.1109/MAHC.2012.61.
  * D. Hand et al. [2018] D. Hand et al.  Statistical challenges of administrative and transaction data.  _Journal of the Royal Statistical Society. Series A (Statistics in Society)_ , 181(3):pp. 555–605, 2018.  ISSN 09641998, 1467985X.  URL https://www.jstor.org/stable/48547504.
  * Dong and Li [2015] X. Dong and X. Li.  A novel distributed database solution based on MySQL.  In _2015 7th International Conference on Information Technology in Medicine and Education (ITME)_ , pages 329–333, 2015.  doi: 10.1109/ITME.2015.48.
  * EVAdoption [2022] EVAdoption.  US charging network rankings, 2022.  URL https://evadoption.com/ev-charging-stations-statistics/us-charging-network-rankings.
  * G. Brito et al. [2019] G. Brito et al.  Migrating to GraphQL: A practical assessment.  In _2019 IEEE 26th International Conference on Software Analysis, Evolution and Reengineering (SANER)_ , pages 140–150, 2019.  doi: 10.1109/SANER.2019.8667986.
  * Google [2023a] Google.  Firestore, 2023a.  URL https://cloud.google.com/sql/firestore.  Accessed: 2023-06-19.
  * Google [2023b] Google.  Pricing: Firestore, 2023b.  URL https://cloud.google.com/firestore/pricing.  Accessed: 2023-06-22.
  * Google [2023c] Google.  Geo queries, 2023c.  URL https://cloud.google.com/firestore/docs/solutions/geoqueries.  Accessed: 2023-09-10.
  * Google [2023d] Google.  Cloud SQL for MySQL, 2023d.  URL https://cloud.google.com/sql/mysql.  Accessed: 2023-06-19.
  * Google [2023e] Google.  Pricing: Cloud SQL MySQL, 2023e.  URL https://cloud.google.com/sql/pricing#mysql-pg-pricing.  Accessed: 2023-06-22.
  * H. Khazaei et al. [2016] H. Khazaei et al.  How do i choose the right NoSQL solution? a comprehensive theoretical and experimental survey.  _Big Data and Information Analytics_ , 1(2):185–216, 2016.
  * Irle [2023] R. Irle.  The electric vehicle world sales database, 2023.  URL https://www.ev-volumes.com.  Accessed: 2023-06-16.
  * J. Corbett et al. [2013] J. Corbett et al.  Spanner: Google’s globally distributed database.  _ACM Trans. Comput. Syst._ , 31(3), Aug 2013.  ISSN 0734-2071.  doi: 10.1145/2491245.  URL https://doi.org/10.1145/2491245.
  * J. Moon et al. [2019] J. Moon et al.  A heterogeneous IoT data analysis framework with collaboration of edge-cloud computing: Focusing on indoor pm10 and pm2. 5 status prediction.  _Sensors_ , 19(14):3038, 2019.
  * M. Eyada et al. [2020] M. Eyada et al.  Performance evaluation of IoT data management using MongoDB versus MySQL databases in different cloud environments.  _IEEE Access_ , 8:110656–110668, 2020.  doi: 10.1109/ACCESS.2020.3002164.
  * Microsoft [2023] Microsoft.  Analyze data in Excel, 2023.  URL https://support.microsoft.com/en-us/office/analyze-data-in-excel-3223aab8-f543-4fda-85ed-76bb0295ffc4.  Accessed: 2023-06-21.
  * N. Jatana et al. [2012] N. Jatana et al.  A survey and comparison of relational and non-relational database.  _International Journal of Engineering Research & Technology_, 1(6):1–5, 2012.
  * Ongaro and Ousterhout [2023] D. Ongaro and J. Ousterhout.  The Raft consensus algorithm, 2023.  URL https://raft.github.io/.  Accessed: 2023-07-04.
  * Pandey [2020] R. Pandey.  Performance benchmarking and comparison of cloud-based databases MongoDB (NoSQL) vs MySQL (relational) using YCSB.  Technical report, Technical Report. https://doi. org/10.13140/RG. 2.2. 10789.32484, 2020.
  * Pokorny [2011] J. Pokorny.  NoSQL databases: A step to database scalability in web environment.  In _Proceedings of the 13th International Conference on Information Integration and Web-Based Applications and Services_ , iiWAS ’11, page 278–283, New York, NY, USA, 2011. Association for Computing Machinery.  ISBN 9781450307840.  doi: 10.1145/2095536.2095583.  URL https://doi.org/10.1145/2095536.2095583.
  * R. Bannon et al. [2002] R. Bannon et al.  Mysql conceptual architecture.  _Technical report, University of Waterloo_ , 2002.
  * R. Kesavan et al. [2023] R. Kesavan et al.  Firestore: The NoSQL serverless database for the application developer.  In _2023 IEEE 39th International Conference on Data Engineering (ICDE)_ , pages 3367–3379, 2023.
  * Solar Energy Industries Association [2022] Solar Energy Industries Association.  Solar data facts sheet, 2022.  URL https://www.seia.org/research-resources/solar-data-cheat-sheet.  Accessed: 2022-08-13.
  * Stripe [2023] Stripe.  Stripe payment processing platform for the Internet, 2023.  URL https://www.stripe.com.  Accessed: 2023-06-16.
  * Taipalus and Seppänen [2020] T. Taipalus and V. Seppänen.  SQL education: A systematic mapping study and future research agenda.  _ACM Trans. Comput. Educ._ , 20(3), Aug 2020.  doi: 10.1145/3398377.  URL https://doi.org/10.1145/3398377.
  * Uber [2022] Uber, 2022.  URL https://www.uber.com.  Accessed: 2022-08-13.
  * U.S. Department of Energy [2022a] U.S. Department of Energy.  Alternate fueling station locator, 2022a.  URL https://afdc.energy.gov/stations/#/analyze.  Accessed: 2022-08-02.
  * U.S. Department of Energy [2022b] U.S. Department of Energy.  Light-duty vehicle registration counts by state and fuel type, 2022b.  URL https://afdc.energy.gov/vehicle-registration.  Accessed: 2024-01-17.
  * Yadav and Rahut [2023] R. Yadav and A. Rahut.  FlexiRaft: Flexible Quorums with Raft.  _The Conference on Innovative Data Systems Research (CIDR)_ , 2023.
  * Yahoo! [2010] Yahoo!  Yahoo cloud serving benchmark, 2010.  URL https://research.yahoo.com/news/yahoo-cloud-serving-benchmark/.  Accessed: 2023-06-19.