The limitations of PostgreSQL in financial services

The limitations of PostgreSQL in financial services

RoachFest 2023

Two days of inspiration, collaboration, and connection

Register Now

PostgreSQL has more than 35 years of active development under its belt making it one of the most powerful and reliable relational database management systems (RDBMS). Even today it’s the fourth most popular database in the world, backed by a global community of dedicated supporters.

It’s a good fit for business critical workloads because PostgreSQL delivers a highly stable foundation and is ACID compliant. This is also why it’s often used in financial services and for use cases that handle money.

In this post, we are going to look at when PostgreSQL may no longer be suited for your financial service applications and business needs.

Horizontal scale for transactions per second

One of the well known limitations of PostgreSQL is its lack of support for horizontal (out, not up) scaling. If you are building an application such as a payment system that will be processing hundreds of thousands of transactions per second, you should prepare for scale. You don’t want your development team to have to manually shard the database to help it achieve scale.

Conceptually, sharding PostgreSQL may seem simple. However, everywhere you set up a new database instance will require ongoing maintenance such as changing your schema or taking the database offline for upgrades. And, for a more fault tolerant system, you will need to set up replication.

A sharded system limits your development speed which creates additional costs, wastes developer hours, and introduces risk to the business.

RELATED Why sharding is bad for business

Companies that truly understand the overhead and opportunity costs of manual sharding — both fintechs and large multinational banks — have given up on this slow and painstaking methodology. Instead, they have switched to distributed SQL databases, which offer built-in horizontal scalability and replication.

Distributed architecture for truly distributed transactions

You either have a global customer base now, or you likely want to. As discussed above, setting up and managing disparate PostgreSQL instances in several locations is not the ideal solution. Plus, you aren’t able to control data residency which can become an important factor when running a distributed system and adhering to various financial service compliance regulations.

Some organizations try to overcome the management challenges of distributed database instances by using a managed PostgreSQL solution. Managed PostgreSQL is provided by all the major cloud providers – AWS RDS for PostgreSQL, Google Cloud SQL for PostgreSQL or Azure Database for PostgreSQL – among others.

However, all of these systems can be considered “half-distributed” in the sense that they’re able to distribute reads across machines in a cluster, but depend on a single write node which creates a write bottleneck. This is not an ideal solution for a system that needs to process distributed transactions (i.e. payments, account balances) that are constantly arriving in near-real time.

By contrast, a distributed SQL database like CockroachDB is “fully distributed” which eliminates the write bottleneck when scaling. CockroachDB can scale-out both writes and reads across low-cost machines resulting in better price-performance, improved resiliency, and lower latency.

Planned downtime and unplanned outages

There are a number of events that can cause unplanned downtime. Catastrophic storms impacting data centers, large influx of unexpected traffic to your app, cloud provider system outages, and so on. Even though PostgreSQL is a resilient system, it was not designed to handle black swan events.

In these situations, even though your data might be replicated and there’s a failover procedure in place, an outage can impact data consistency and you can even lose data. With PostgreSQL, transactional consistency is only guaranteed in a single instance, not across a distributed environment.

RELATED The Costs of Planned vs Unplanned Downtime

Additionally, planned downtime, which is often required for PostgreSQL updates and upgrades, can be just as costly in the long run. Even if you only have to take the system down once or twice a year, every minute counts and can impact your bottom line. Plus, no amount of downtime is acceptable for systems that handle people’s money.

Why Stash migrated from PostgreSQL to CockroachDB

When Stash engineers started designing their new banking infrastructure, they built an initial design on PostgreSQL. A lot of the engineers were already familiar with PostgreSQL and knew that it delivered data consistency and a resilient foundation, which were both crucial capabilities for their system that would handle money.

[ case study ]

Full sp(r)eed ahead: an operationally efficient payment infrastructure

read case study →

However, a mission-critical banking platform has two other important requirements: 1) achieving five-nines of availability and 2) building on a multi-region, multi-cloud foundation. They never wanted their customers to worry about incorrect balances or run into latency issues when spending money. Looking to the future, it also made sense for Stash to build a platform that could easily scale to multiple regions and replicate data across those regions for a stronger fault-tolerant setup.

While these requirements are technically possible with PostgreSQL, the Stash team knew it would be complicated to set up and even harder to maintain. One of the Principal Engineers on the project suggested taking a look at CockroachDB, a cloud-native distributed database that met their requirements. Fast forward to today, where CockroachDB serves as a system of record (or source of truth) for their entire banking infrastructure.

[Read the full story on why Stash choose CockroachDB]

In summary

We’ve seen dozens of companies make the switch from PostgreSQL to CockroachDB. Whether they are like Stash and want a solution that can grow with them in the future, like Shipt that wanted to build an idempotent payment system designed for correctness, or like Stake who couldn’t suffer another outage due to a trading frenzy.

PostgreSQL and CockroachDB are compatible and our customers report that making the switch is relatively straightforward. No hours of refactoring code, no learning a new query language. If you are interested in migrating off of PostgreSQL to CockroachDB, get in touch.

About the author

Cassie McAllister linkedin link

Cassie is a Senior Product Marketing Manager at Cockroach Labs. Her focus is on vertical marketing and telling customer stories. She's been in the database world for the past 5 years and previously worked in communications for cybersecurity companies. In her free time, you can find her at the beach, sipping wine, or skiing down a mountain.

Keep Reading

Comparing CockroachDB and PostgreSQL

It would be wrong to begin a comparison blog post about PostgreSQL without first acknowledging that it is one of the …

Read more
Why Fortune 50 banks are leaving traditional RDBMS for CockroachDB

In the world of finance, changing databases is usually pretty rare. When you’re in charge of other people’s money — …

Read more
Fueling the startup economy: Crowdfunding on a transaction-oriented system

This is the story of how Australian-based Birchal created a crowdfunding platform on CockroachDB 

Early stage capital is …

Read more