Pit Row: Getting Your Postgres Database to the Starting Line

May 07, 2014

Pay a visit to Pit Row at a race like the Daytona 500 and you’ll find teams crawling around the stock cars carrying bolt blasters, socket wrenches and super-sensitive listening devices. In the data center, the database administrator setting up his first Postgres database does much the same kind of work though the hotrod is the data environment, the tools are configuration settings and the crawling around is done through a keyboard.

The goal is the same—firing up for optimal performance. And just like a stock car, the database needs some tuning up before it can hum and roar at the starting line.

Here we’ll explore what Postgres users encounter as they deploy new databases. This is the third installment of our blog series that explores the common challenges new Postgres users encounter and some the strategies we employ to help. EnterpriseDB has created bundles of targeted services and training to help organizations as they progress.

Bottom of the Stack

Postgres has a long history of evolutionary development, so upgrades are designed to integrate into the existing code without creating edge cases. This has implications for hardware because Postgres continues to support older hardware even as the database advances. But if your hardware is new, you’ll need to take a proverbial wrench to the out-of-box settings to optimize Postgres’ performance with these newer machines. This can also mean digging into the operating system configurations for the hardware as well to make sure these three wheels are rolling in unison.

Next up on the stack is factoring in whether the new Postgres database will function in a virtualized environment. Deploying with VMware, for example, means adjusting the disk subsystem and factoring in the intricacies of tuning for network attached storage or a storage area network. Directly attached storage works differently from virtually stored systems and getting some coaching can help avoid a breakdown in performance.

Securing the Track

Once DBAs have their new Postgres database plugged into the hardware and OS settings and have mapped it to the right storage architecture, it’s time to turn to security configurations. Aligning database access with the corporate authentication controls with LDAP, for example, can get tricky. Then there’s the Health Insurance Portability and Accountability Act (HIPAA). That bit of legislation triggered a host of new DBA best practices to address database security and downstream data usage.

Log file security, for example, is relatively simple to address but is too often overlooked. This is an issue when taking into account the implications of Postgres’ role-based security configurations, for example. Controlling access to the objects in your database is one thing, but DBAs have to ask in setting these controls, “Can someone see into log files? Do I know which log files are exposed?”

Determining how to deploy SSL technology is another area of security that can light up our proverbial support switchboard. DBAs have to assess the risk of whether someone could intercept data between the application and the database. In some cases, companies have to deploy SSL to protect information from the threat of, say, a disgruntled employee. Companies in the financial, government, insurance and healthcare industries typically don’t have a choice. And for applications that connect to the database over the Internet, SSL is often a standard feature. But for many companies, deploying SSL technology means having to weigh the security benefits against the loss of performance inherent with this technology. Here’s where having a personal Postgres pit crew really comes in handy. We’ve had extensive conversations on this and we’ve developed advanced best practices.

When the App Developer Joins

Security can also be a challenge when it’s time to write applications for the new Postgres database. It’s important to note that EDB’s Postgres Plus Advanced Server has SQL Protect, a layer of security against SQL injection attacks that has been integrated into the core community PostgreSQL code. Developers have to make some big decisions when writing apps with parameters for SQL injection protections. They also face some tough choices when writing apps utilizing Postgres’ role-based access capability.

It’s easy to write apps that trust everyone and give equal access, but you typically want to grant the least amount of access possible. That’s when things get tricky. Coordinating the application design with the database schema and ensuring the application works properly is a matter of high finesse. Getting some additional education on stored procedures makes a huge difference, whether using PL/pgSQL or external things like C-based stored procedures. It’s possible to write some really bad PL/pgSQL code without realizing it and crushing the performance of the database as a result.

When the app developer gets going with Postgres, the big issues aren’t all about security. Writing apps to optimize performance with Postgres can take some finesse, but in this case, there is a powerful tool in Postgres Plus Advanced Server that helps significantly. Postgres Enterprise Manager, which helps DBAs manage and tune multiple Postgres deployments from a single console, adds value for the developer by analyzing code for performance optimization.

Kick Start with a Postgres Pit Crew

For DBAs and developers revving up to deploy new Postgres databases and applications, we’ve developed a program called Kick Start with an easily consumable combination of on-demand training modules and flexible consulting services to ensure success with these initial projects.

For more information, fill out the Contact Us form or visit: http://bit.ly/1h47iI3

Marc Linster is Senior Vice President or Products and Services at EnterpriseDB.

Share this

Relevant Blogs

More Blogs