DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Databases
  4. 3 Steps to Unlock Postgres Performance

3 Steps to Unlock Postgres Performance

PostgreSQL allows users to get advanced features compared to many open-source relational databases. This is how to quickly optimize your PostgreSQL performance.

Evan Bates user avatar by
Evan Bates
·
Sep. 12, 17 · Opinion
Like (8)
Save
Tweet
Share
9.86K Views

Join the DZone community and get the full member experience.

Join For Free

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

Database engine PostgreSQL Relational database Tarantool Data processing In-memory database Application server application

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Express Hibernate Queries as Type-Safe Java Streams
  • ChatGPT: The Unexpected API Test Automation Help
  • AWS Cloud Migration: Best Practices and Pitfalls to Avoid
  • How to Secure Your CI/CD Pipeline

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: