10 new tricks your old database can do

You might be surprised by the powerful “NoSQL” features lurking in your old Oracle, SQL Server, MySQL, or PostgreSQL database

In all the excitement about NoSQL databases, distributed databases, data warehouses, and GPU-accelerated databases these days, it’s easy to forget that traditional relational databases still store and serve the mission-critical information for many, if not most, line-of-business applications. Oracle Database, Microsoft SQL Server, MySQL/MariaDB, and PostgreSQL may trace their origins back to the 1980s, but they are all still under active development, and not just to fix bugs and tweak performance.

In this article we’ll discuss many of the ways that traditional SQL databases are improving. Some involve supporting different kinds of data, along with the indexes and search functions to use them. Others involve speeding up access to frequently used tables. Still others expand the databases beyond their own tables, beyond a single server, and beyond SQL queries.

Full-text search

Standard relational database indexes tend to use short fields or even hashes, for performance reasons. Full-text search is different. Full-text search is handled with a different kind of index, typically something like an inverted list of words and their record and offset locations. Often there’s a list of stop words that are ignored, and an algorithm for stemming to generate other forms of each word. Some full-text search engines support Boolean operations, fuzzy search, or proximity search.

Oracle Database, SQL Server, MySQL/MariaDB, and PostgreSQL all offer full-text search on specifically designated text fields, for example CHAR, VARCHAR, or TEXT columns that have FULLTEXT indexes (to use the MySQL terms). You can also use an external full-text search engine such as Elasticsearch or Solr to index and search text in a database.

JSON data

To continue reading this article register now