8 Unusual Ways to Use Databases
We're all about using database management systems like DynamoDB, Redis, Neo4j, and Firestore to solve uncommon problems (as well as more standard ones). In this article, we delve into unusual database uses, and how they benefit projects.
There are many options when it comes to choosing database management system software for client projects, from relational databases and non-relational databases to databases that support semi-structured data and structured data. We often decide on the simplest solution.
For example, if we know our project data contains relationships between datasets, we opt for an SQL (structured query language) solution that offers the necessary management tools.
On the other hand, if a project prioritizes speed and an easy-to-use database (at least at the outset), that’s where modern NoSql databases like MongoDB come into play. But what happens if we have an uncommon problem and aren’t sure which database is the best choice?
Below, we outline unusual usages of popular database management systems, and a handful of valuable uses for less well-known database management software.
1. Postgres for handling JSON
Postgres is a mainstream relational database management system, known for being free and open source. But many users don’t know that they can use Postgres for storing full JSON objects, meaning it’s not just a string column that contains full JSON as text, but a special field that allows the creation of individual queries to manage or retrieve data from properties inside a JSON object.
When’s that useful? First of all, the data has many properties, but it’s much lighter than a “normal” database table that has a column for every value. With JSON, only one column is returned, where all the information is written in JSON format. The second advantage is by not specifying what data is stored inside JSON, that creates a small, non-relational part of the relational dataset.
However, this solution has limits, like not checking if the data sources are unique or not null (although that could be verified beforehand, using application code). But at the same time, if we’re developing a product or service that doesn’t rely on validation, we don’t need to check those rules – for example, an online store that has many specifications for each product.
If you’re interested in reading about managing JSON objects in Postgres, we recommend digging into the official database documentation.
It’s also worth mentioning that Microsoft SQL Server (MS SQL Server) also supports JSON objects, and by referencing Microsoft’s documentation, it’s straightforward to work with – maybe even easier than Postgres.
2. Storing geospatial data inside MongoDB
MongoDB is one of the most common non-relational databases (often called NoSQL databases) and has a top advantage: A dedicated field type for storing geospatial data. This type of column contains two values: object (for example point, line, polygon, multipolygon, or even custom polygon with excluded parts specified) and coordinates of the selected type.
With this type of data, it’s easy to manage the distance between points, the area of a polygon, or check if the provided point is inside the polygon. MongoDB enhances that by providing special indexes for geospatial data like “2dsphere”, allowing us to run queries on geometrics mimicking earth-like spheres, and “2d”, which executes queries on simple two-dimensional planes.
Many operations previously mentioned can be run using database indexes and special queries with query operators like “geoIntersects”, “geoWithin”, “near” and “nearSphere”. These tools allow us to rely only on databases, without using application code and writing custom functions. If you’re interested in this topic, we encourage you to check out MongoDB’s official documentation.
3. DynamoDB for big datasets
DynamoDB is a NoSQL database, and one of the cheapest databases in the AWS environment. But that doesn’t mean we use this type of database for every project, just to save money. DynamoDB was designed to handle massive datasets, and because of that, we never recommend this solution for small or medium CRUD projects.
With CRUD projects, we know that most of our database operations read data with filters and sorting. DynamoDB doesn’t provide easy tools for managing that, and using this database requires workarounds, data duplication, or completely moving data management logic to the application site (which visibly slows it down).
So, when is DynamoDB useful?With usage of its unique feature called “partition key”, that specifies where data is stored in the database. By doing that, it’s possible to partition data by date, day hour, or any other value. Developing apps with DynamoDB requires us to understand the partition key, and how it’s different from the primary key in relational databases.
As the name suggests, the partition key points to the database partition data is stored, allowing us to execute just one query for easy access to a chosen partition, and sort the data if the sort key is used as well.
That’s why DynamoDB is ideal for storing user operations inside the system. When used in that way, DynamoDB provides scalability without worrying about partitioning the database.
4. Redis for storing data in memory
For us, Redis is the dark horse of databases. In fact, it offers almost everything we want from a database, while maintaining fast operations. Redis achieves that by storing records in RAM. Note: Keep RAM usage and relying on RAM in mind.
One of the most common uses of this tool is maintaining consumer/producer operations, while keeping jobs inside Redis. The role of the server is to create and execute jobs, with validation if jobs run correctly, or to re-run if there’s an error. You can find an example of this type of database usage by taking a look at Nest.js’ official documentation.
Another great feature offered by the Redis database is sorted sets. That allows us to create pre-sorted data for every field where we require sorting, and it’s possible to use pagination, too. Using this approach, Redis is a top tool for handling statistics or leaderboard sets.
Redis is also useful for storing highly requested application data. As mentioned previously, there are limits to this approach when it comes to RAM, but if we know our data will never reach breaking point, Redis is a valuable addition for speeding up many processes.
Additionally, Redis can handle streams, full text search, or even time series.
5. Neo4j for searching through graphs
Neo4j is one of the newest databases, and classified as graph database management software. Its approach to data is different from other tools. We can’t say this is a completely relational database system, because it doesn’t have a primary or foreign key, but at the same time, it’s not unrelational, because it has connections between datasets through nodes.
So, what exactly is Neo4j? A database that visualizes records through graphs. Neo4j's official website says the database is commonly used for problems like internal business processes, real-time transaction applications, metadata, and advanced analytics.
When we think about graph relations, a common anti-pattern described by Bill Karwin called “Naive tree” springs to mind. When discussing it, Karwin said there’s always some kind of issue mimicking graph trees through databases.
Even though Karwin’s book “SQL Antipatterns: Avoiding the Pitfalls of Database Programming'' outlined options to work around these issues using SQL, we don’t consider them bulletproof. Why? Because his approach needs a lot of implementation on the application site and is easily broken by anyone.
That's why Neo4j is an excellent option for storing data that naturally forms a tree structure, like a comments section, or finding people we “might know” on social media websites by looking at friends of our friends. Another great use for this graph database? A recommendation system to find similarities between buyers.
6. Firestore for handling chat
Firestore is a NoSQL database provided by the Firebase platform. The main advantages of this tool are scalability, support for offline environments, and real-time updates – the most important benefit.
That’s why Firebase is a top choice for creating chats for applications. Offline environments allow users to read previously cached data in chats without the need to go online. But the most important part of this database is real-time updates. When we use real-time updates and native SDK listeners, we can create fast and responsive systems – perfect for managing chat.
7. CouchDB for working offline
For many programmers, CouchDB may seem like an inferior version of MongoDB. Both these databases are based on JSON file representation, and even though MongoDB is a better solution when it comes to rapidly growing applications, there are still cases when CouchDB is a useful choice.
One of the advantages of CouchDB is support for Android and iOS apps. CouchDB also offers a lesser-known feature: The ability to work offline on local versions of database applications. So, even when an application is offline, it doesn’t mean data is lost forever.
Additionally, CouchDB synchronizes local and production databases when an internet connection is established.
8. Autotune in Azure SQL
Azure SQL Database has well-known benefits like easy management of database scalability and straightforward creation of database backups. Azure also provides a tool called Autotune, saving programmers time. How?
If we think about bad practices when developing database solutions, one of the most common issues is poorly managed indexes. When there are too many indexes, our database swells, wasting space and requiring resources to support (often leading to higher costs). But when indexes are badly selected, our database is even slower.
The worst-case scenario is that our database gets huge and is really slow. With that in mind, it’s recommended programmers manually check which fields should have indexes and which shouldn’t (if they aren’t used) – once every couple of weeks. That approach relies on the systematic work of at least one specialist.
Autotune offers a solution via automated monitoring and adjustment of indexes, with no intervention required from a specialist.
Using the potential of databases
Summing up, there are instances when newer and fresher database solutions like Neo4j, MongoDB, and Redis benefit our projects. But first, we check whether popular databases like Postgres offer the appropriate tools for our problem.
Moreover, with the most popular databases, it’s likely that someone’s had the same or a similar issue as us, and already asked about it on question and answers website Stack Overflow – so we look there first.
On the other hand, when new databases are developed, they may well take into account well-known issues with existing tools, and find ways to omit them or offer a better solution. No matter what, before selecting database for our project we always keep the KISS principle in mind – “keep it simple, stupid” – and never make our projects more complicated than they need to be.