Postgres is widely seen as an excellent open source database, with critical maturity and features that allow organizations to scale and achieve high availability. We’ve seen many companies excel using Postgres. Along the way, we’ve also seen mistakes that can slow down and affect Postgres performance or even derail Postgres efforts. We’ll list the most common Postgres mistakes here, along with a recommendation to avoid each of them.
Mistake #1: Failing to Test Adequately Before Migrating
This is one mistake you really don’t want to make, as it can cause unforeseen issues and downtime. Before migrating your production databases from one database type to another, be sure to test in a non-production environment that closely mirrors your production environment. It doesn't matter that you are migrating from Oracle to Postgres or migrating from MySQL to Postgres; every time before the migration you need to test before. Make sure to test your plsql functions to make sure they’re ported properly from the existing to the new database. Once you’ve tested migration in a non-production environment and have resolved any issues you encountered, you can move to production with more confidence.
Mistake #2: Skipping the Manual
Postgres has excellent documentation—you should use it! Current Postgres documentation can be found here. Of course, you’ll want to start with the latest version of Postgres (if you’re starting fresh) and take a look at the PDF. The documentation is lengthy, but we recommend looking through the table of contents so you know what’s there for your future needs. The Getting Started section does what it says on the tin, and you can also find SQL syntax, backup and restore guidance, high availability setup, and server configuration information. The docs are fairly comprehensive and well-organized. Another great option is EDB Postgres documentation delivered in our tutorials.
Mistake #3: Failing to Share Information
This one is probably no surprise. We often see a Postgres champion at a company set up and implement Postgres in an open stack solution or Postgres on Kubernetes. Often, their work is excellent, and they build a great system. Then that person transitions out of the company. They might leave some wiki docs behind, but other people haven’t been immersed in the set up and implementation of the system. So now no one knows anything about this open stack/postgres solution. No skills were built out around it, so it withers. The person who fills their slot often doesn’t understand what’s there, and sets out to build a new solution.
The best solution to this is to get your DevOps team and DBAs immersed in the process. At least two people should implement your intended architecture, so that no one is irreplaceable (and everyone gets to go on vacation once in a while!). Training sessions can be very helpful in getting a whole team of people to understand Postgres best practices and buy into the goal of transitioning to Postgres. We learned early on that a successful implementation service should include one person to implement your desired solution, and one person to train the team and manage the process and any questions along the way. That’s why our Implementation Service always has two Postgres consultants working with you.
Mistake #4: Migrating the Hardest Databases First
When learning to swim, you wouldn’t dive head-first into the deep end. But that’s what some companies unintentionally do when making a move to Postgres. We’ve helped loads of companies migrating from Oracle to Postgres, and are seeing more and more companies interested in moving from MongoDB to Postgres, MySQL to Postgres and migrating from MSSQL Server or DB2 & Sybase / ASE to Postgres.
Often, the choice of which database to start with is totally random, based on usage, or based on the size of the database. Deciding based on size or usage is better than nothing, but can be misleading. Your smallest databases may be the ones with logic or features that don’t have a 1:1 correlation between your existing and target database.
To avoid this mistake, use a tool like the Migration Portal, which assesses the compatibility of your existing database to Postgres. Bonus: it’s free! Once you have an idea which of your databases are most and least compatible with Postgres, start with the one that looks easiest. You’ll gain experience that will help you when it comes time for the more challenging cases.
If you want assistance, we have services to help you assess and migrate your databases, which brings us to...
Mistake #5: Not Getting Help
Very often we see companies struggle to implement Postgres because they simply don’t have sufficient Postgres expertise in their company. They undoubtedly have smart DevOps folks and DBAs, but they have to do extensive research to figure out what’s out there, and what best practices are for Postgres. Here are some of the problems we’ve seen result from that:
- Architecture designs can be suboptimal (or nonexistent!).
- Companies choose to mix-and-match database types (for instance MongoDB and Postgres) because, for instance, they didn’t know Postgres could handle document-based data such as JSON. As a result, employees must ramp up on both types instead of becoming more expert in one.
- Location and mapping use cases fail to implement PostGIS, which was tailor made for geospatial data sets and results in much faster data retrieval.
- Postgres databases aren’t given enough resources. Under-allocating memory is a common mistake we see. Defaults in Postgres are lowest-barrier-to-entry values, and need to be turned up for production systems.
The solution to this one is easy; if you don’t have at least a couple of people well-versed in Postgres who are available to plan an approach and implement Postgres, reach out for help from Postgres experts and professionals. It’ll speed you along your Postgres journey, and help you avoid costly mistakes that your team will want to correct a year from now. EDB is a leader in Postgres development and implementation, and we have many Services like Enterprise Architecture and Backup Best Practices that can help along the way.
Mistake #6: Not Making it a Priority
Like many infrastructure improvements companies want to make, sometimes a transition to Postgres is ‘prioritized,’ but never makes it to the top of the list. It’s understandable. Customers always want new capabilities, and organizations want to deliver new products and features.
On the flip side of that, Postgres can help achieve numerous benefits, such as:
- Cost savings: We see companies spend hundreds of thousands (and more) on database management. Management of Postgres databases often costs 1/10 of what other databases cost.
- Time savings: As databases age, they take more and more time to maintain. Ask each team in your engineering/operations group how much time they spend maintaining older databases and add it up (a quarterly estimate often makes sense). Then realize that most people underestimate the amount of time they spend on tasks, often by a factor of 2 or more. Two weeks spent now may save you two months over the course of the year.
- Stability: Postgres has deep features and maturity that makes it an ideal choice for many organizations. Tool sets provide replication, backup capabilities, failover mechanisms, and management on par with any database, and far exceeding some. The open source framework is tried-and-tested, and open source updates are rolled into each new version of EDB Advanced Server, should you need additional capabilities like Oracle compatibility or additional security enhancements.
- Break free from vendor lock-in
Lack of (actual) prioritization is a tricky problem to solve, but not insurmountable. First, think about your company goals. Is your company user-focused above all else? Postgres can decrease transaction times, help ensure data integrity, potentially minimizing “Something went wrong” errors, and minimize downtime. Is your company more focused on time savings and efficiency? Could you move two or more types of databases to Postgres and minimize the number of database types you use? Or maybe you have a database or two that take a lot of time to maintain. Cutting either could help your people cut down on troubleshooting, research, and context switching.
We know this is a tough one to solve. That’s why we have a Postgres Strategy service dedicated to helping you build a customized business case to move to Postgres.
Conclusion
With all of the possible pitfalls you could make migrating to Postgres or starting fresh on it, you might feel a bit daunted. Don’t be—Postgres is one of the most highly used databases for a reason, and there are many resources out there that can help, from web forums and tutorials, to helpful services from Postgres experts. If you want help getting started, our Quick Deploy service is very affordable and can help you get a production database in place with two standbys, using best practices—all in 3 days.
Even if you do make a mistake or two along the way, take heart in Albert Einstein’s assertion that “Anyone who has never made a mistake has never tried something new.”