You Don't Need Every Feature of Your Previous Database

August 08, 2017

In a March blog post I talked about the mismatch between what people expect from Postgres in terms of hints, and what exists. In this blog post I would like to cover the more general case of when people should expect feature parity with their previous database, and when such expectations are unreasonable.

First, imagine if every database had the features of every other database — that would be great for compatibility but terrible for usability. You would have so many ways of doing something, with slightly different behaviors, that development and administration would be much more difficult. Of course, Perl has its "There's more than one way to do it," but at least that allows tradeoffs between clarity and conciseness, and allows different programming styles. Having all features in every database would have few of those benefits. Also consider that some features are mutually exclusive, so this would be impossible. Therefore, we have to make tradeoffs in the features each database system supports.

Let's think of Postgres compatibility at three levels: sql, tuning, and monitoring. At the sql level, you should expect parity between Postgres and your previous database. The syntax might be different, but all the capabilities should be similar, e.g. if your application used save-points with the previous database, Postgres should support that, and in almost all cases, it does.

For tuning, parity is more murky. The internals of database systems differ greatly, so the tuning requirements will differ — and let's be frank — the less tuning you have to do to attain good performance, the better, so having a tuning item missing in Postgres might be good thing. Postgres might have good performance without requiring that tuning knob. Postgres might require tuning that wasn't required in your previous database system — that is a negative. So, if Postgres doesn't have a tuning knob you had in your previous database, that might be good (Postgres auto-tunes it), or it might be bad (you can't adjust Postgres to achieve good performance for your workload). (I previously covered the tradeoffs of adding performance settings.)

Monitoring has a similar murky outlook. Sometimes monitoring is required to meet organizational needs, e.g. what are the currently running queries. For most organizational monitoring needs, Postgres has the required features, whether it is log_line_prefix, pg_stat_statements, or external tools like pgBadger or PGAudit.Often, Postgres tooling isn't as polished or as easy to use as tools in more-established database systems, but the tools exist, and are often more flexible.

Just like tuning, sometimes monitoring that was required in your previous database system isn't required in Postgres. For example, Postgres's streaming replication is rock-solid, so there is no need to monitor for streaming replication corruption. Similarly, many organizations built elaborate monitoring of their previous database systems to check for optimizer plan and statistics changes. These are often to avoid optimizer bugs in their previous systems that caused problems. However, you can't assume this kind of monitoring is necessary for Postgres. Postgres isn't bug-free, but it might require different monitoring than what was required in the previous system.

In summary, Postgres is different from your previous database system. Hopefully it supports all the necessary features your applications require, but it will not exactly match your previous tuning and monitoring requirements. It is necessary to approach Postgres with an open mind to make the best use of your new database system.

Bruce Momjian is a Senior Database Architect at EnterpriseDB. 

This post originally appeared on Bruce's personal blog.

Share this