3 Steps to Unlock Postgres Performance

Learning how large the speed gap is between an in-memory database and a relational database is eye-opening. A simple way to show the enormous raw data processing differential between Tarantool and PostgreSQL is to examine how many transactions the systems can perform per second, per CPU core.

Tarantool can easily be added to an existing Postgres stack using its Postgres connector, and the system can process up to one million transactions per second on a single core — whereas the successful execution of one million transactions per second in Postgres and other relational databases requires an extremely powerful server running from 2 to even 723 cores.

The efficient use of CPU also enables Tarantool to run on smaller devices, such as IoT devices, that wouldn’t adequately run Postgres. This also solves latency and scaling issues under heavy workloads.

1. Scaling Postgres Data: Cache and ACID Transactions

Relational database installations often follow a similar trajectory as traffic grows. If they began with a single node, replicas and shards are added when volume increases (the former for reads and the latter for writes). This expensive setup, based upon powerful servers, scales somewhat but eventually runs into latency problems.

After this, we find teams usually try a traditional in-memory cache, such as Redis or Memcached. The problem is that now the setup loses ACID transactions and can have other issues with keeping the cache database and the relational database harmonized — not to mention it doesn’t have secondary indexes.

At this point in the process, Tarantool is the logical solution to replace the traditional in-memory database. Note that even if Tarantool were implemented simply as a cache, it would outrun Memcached. It also includes many elements of a traditional database system. For example, it features fully ACID-compliant transactions including rollbacks, even when executing primarily in RAM using its Memtx engine. Additionally, Tarantool has successfully fixed some classic DBMS/cache combination problems like cold start and other data synchronization issues. It also has secondary indexes that Memcached and Redis do not support natively.

2. Scripting Capabilities (Sophisticated Stored Procedures) to Process Postgres Data

Stored procedures don’t always scale well in relational databases. Fortunately, Tarantool is one of the most scriptable databases on the market and it can assist with these scaling issues. It runs an application server in addition to its database management system, which enables it to be scripted with LuaJIT — the superior Lua implementation.

This application server doesn’t simply allow the execution of a series of database actions but rather effectively brings a complete backend language into the database. This means that it could potentially replace an entire Node.js or PHP implementation. In fact, the full backend of an application can be constructed using only NGINX and Tarantool.

The Tarantool application server is not sandboxed but rather has full access to network and external services. With the application server, it is possible, for example, to automatically remove old data from a database or to send an HTTP request and save the data in the database. Data can even be directly served out of the database via a REST API.

Note that in addition to LuaJIT, Tarantool application server modules can be written in C,
and there are connectors providing Tarantool functionality for Node.js, PHP, Go, Java, .NET, R, Erlang, Perl, and Python. In addition, SQL statements are supported, which can be pure SQL or SQL wrapped in Lua.

3. Federation of Postgres Data With Other Data Sources

Tarantool enables connections to Postgres databases but also to numerous other databases, as well. Its SQL capability alone allows it to work with Oracle, MySQL, SQL Server, and DB2. When connecting to multiple databases, the data from all can be federated and Tarantool enables the use of partial datasets — in many cases, down to the field level.

You can also connect your front-end business intelligence solutions, including Tableau, IBM Cognos, or QlikView, to Tarantool. For example, in Tableau, you can connect through the Web Data Connector. It is much faster to replicate your disk-based relational data in Tarantool and then pull it into a BI tool than it is to draw it straight into your BI tool from your relational databases.

Used in this way, Tarantool is an ideal tool to prepare online data. With a REST API or HTTP interface, you can quickly call your data from any application or service.

Summary

PostgreSQL allows users to get advanced features compared to many open-source relational databases. With Tarantool, Postgres users add cache speed, faster transactional capabilities, fewer servers for optimal performance, and the potential to unite data across different stores. This is how to quickly optimize your PostgreSQL performance.

References

1. Tarantool quick test

2. Asynchronous processing with in-memory databases or how to handle one million transactions per second on a single CPU core

3. How to save one million dollars on databases with Tarantool

4. How To Speed Up Your MySQL With Replication To In-Memory Database

5. When "clever" goes wrong: How Etsy overcame poor architectural choices

6. Tarantool: A Lua-based database engine and in-memory data grid

7. How to speed up your MySQL with replication to in-memory database