What are Databases?
A database is a store where structured data is written, updated and retrieved.
A website stores the email and name of each registered user in a database, fetches them to check whether a user has already registered, and makes an update when the user changes the profile picture.
A database is not the application server. The business and application logic sits in a separate server, because a code change and a restart are easier there. A move from Postgres to MySQL then leaves the business logic unchanged.
A database is an abstraction on top of the file system. A file holds unstructured data and is read entirely or block by block. A database has an ID and types such as integer, long and string, and each type occupies a certain amount of space.
A database is expected to be durable. A tsunami or a hurricane can still destroy the hardware, but the chance of losing data is much lower than in memory.
An in-memory database is faster than disk, costs more, and loses its updates in a crash.
Storage and Retrieval
A database stores rows of structured data in tables, builds indexes on them, and reads them back through SQL queries.
The hardware is magnetic tape or solid state disk. One layer above it are the algorithms: B+ trees, which create the indexes in relational databases, log-structured merge trees, pages, hash tables and pointers.
A table is a structured store with columns, and each column has a data type. A profile table has an ID as an integer, the names and email as strings, and the date of birth as a date.
An SQL statement is broken down this way:
- From: the table being read.
- Where: the rows of interest, such as users from India.
- Group by: clusters, such as by date of birth.
- Having: an aggregation, such as clusters of more than 100 users.
- Order: ascending or descending.
- Limit: the top 5 results.
The query optimizer finds the most optimal way to execute the query. It uses heuristics, and it knows where the hash tables and indexes exist.
What is a NoSQL database?
A NoSQL database is any database that does not support SQL or relational tables. It is a catch-all term.
NoSQL databases usually mean key value stores or document-oriented databases: a key, and a document or value for that key. It is a persistent variation of a hash map.
Whether a database holds the latest copy of the data depends on the application. Certain types of data are okay with being lost, such as the number of views on a post. A creator who sees the views go down from 1,000 to 990 has not suffered tremendous damage.
Usually a database is the single source of truth for any data-related operation. It is expected to have data that is as consistent as possible, and to be available, which means it is responsive to requests. If the database is down, the whole application is down.
A database management system decides what data is persisted where, and manages the read replicas and any coordination. The management system is the manager, and the database nodes are the employees that store the data.
Types of Databases: Graph, TimeSeries and Object
Graph, time series and object-oriented databases each store data in the shape of the operations run on it.
A graph database stores its data internally as nodes and edges, so that graph queries such as an A* search or a breadth-first search are efficient. Relational databases with a layer written on top are also used as graph databases. They are not optimized for graph operations, so they are usually not as good as Neo4j.
A time series database stores values such as the number of sales per day. It is good at aggregation operations and it compresses the time values. With a long timestamp of 64 bits, each subsequent value is off by only one second, so the database saves on timestamp storage. It suits metric data, instrumentation data and monitoring.
An object-oriented database stores objects instead of tuples. It is usually implemented using a relational database. It helps programmers write and read data quicker, and the application layer does little transformation when storing object-oriented data.
What database should you choose?
The database for a project is chosen by the application being written and by the trade-offs each database gives.
Postgres has high consistency and high durability. It provides nothing off the shelf for availability: when a Postgres instance goes down, a read replica has to be ready to take its place.
Cassandra has a cluster architecture. If one of the nodes goes down, the rest of the nodes take up its responsibility, so fault tolerance is inbuilt. It is not highly consistent unless it is configured to be.
Neo4j is a graph database and is excellent for graph queries. It is a poor counter for the likes on a Facebook post.
A database is an abstraction that manages data reads and writes while the engineer focuses on the business logic. Elasticsearch does not support SQL, and a third-party library converts SQL queries to Elasticsearch queries.
When the growth of the application is unknown, a familiar database is the choice. When it is known, the choice is the database with the best trade-offs.