Contributed by Gary Carter
The newest release of Postgres Plus from EDB is a serious game changer for DBAs and organizations running mixed workloads on their mission critical systems. Postgres Plus Advanced Server 9.4 now features the ability to prioritize enterprise workloads to prevent a single job or group of jobs from monopolizing system CPU or I/O resources and harming the performance of more important jobs.
DBAs no longer have to carefully schedule resource-consuming jobs around the clock. Critical reports can be run when needed without waiting for overnight batch jobs to complete. The status of Customer orders / information no longer need to be 24 hours late. Online transactions won’t suffer when running special jobs that can’t wait a full day. Instead of your database managing your business, you can manage your database for the business.
Postgres Plus’ resource management allows placing governors on selected workloads, and assigns relative priorities to their access to system CPU and I/O resources. This prevents any single process from monopolizing resources to the detriment of other processes.
Now you can guarantee that your mission critical online transaction jobs always have the resources they need. It also means that you don’t have to wait until non-business hours to run other jobs whose results are best utilized during normal business hours. And if your business hours are 24x7, you can now run any job 24x7.
Resource management is a great example of EDB’s leadership in focusing on enterprise features that are absent or not yet available in community PostgreSQL. If your enterprise is struggling with SQL workloads competing for resources throughout your daily business cycle, then download Postgres Plus Advanced Server today and give it a try. You’ll be glad you did.
While resource management is certainly an important feature for DBAs and organizations overall, Postgres Plus Advanced Server 9.4 has other features benefiting application developers or companies using Oracle:
Expanded Database Compatibility for Oracle
Expanded database compatibility for Oracle increases the number of applications that can be easily migrated to Postgres Plus or used in building new apps on Postgres Plus by developers with Oracle skill sets. New features include a more complete implementation of the UTL_HTTP function package, new OLAP capabilities (Cube, Rollup, Grouping Set), Connect By Root, and hash based partitioning.
Even if you don’t use Oracle, application development that requires communication with external Web servers is faster and more efficient with a robust collection of functions in the UTL_HTTP package. These functions make building any application that makes calls to web servers from within the database, easy and fast.
The addition of hash partitioning adds to existing partitioning features like fast partition pruning to support thousands of partitions, faster triggers and declarative partitioning syntax. If your application tends to have many concurrent inserts and updates to a large table then you stand to benefit from hash partitioning which will distribute data among equal sized partitions.
Bulk data loading improvements help ease the migration from Oracle databases and provide more control over data load processes. EDB’s bulk loader now outperforms standard SQL statement for bulk loading of JSONB data. Other new features include:
- Perform encoding conversions on input data
- Use environment variable references within control-file pathnames
- Support for PRESERVE BLANKS
- A DATA option to override the data file specified in the control file
- Support for DISCARDMAX
SQL Protect Enhancements
Postgres Plus Advanced Server has SQL/Protect, a security module that centralizes protection of the database from SQL injection attacks. Unwanted SQL that is forcibly stopped is added to a special table specifying userid, ip, machine name, time, date. Capturing the data this way makes it easy for DBAs concerned with security and SQL injection attacks to drill down into important details for analysis with simple SQL queries instead of parsing through log files.
Finally, it’s important to note that Postgres Plus Advanced Server always incorporates the latest features and fixes found in every release of open source PostgreSQL. Many of EDB’s engineers worked on v9.4 with EDB making various contributions to:
- Logical Change Set Extraction (core work to support log-based replication)
- JSONB Data Type
- Time Delayed Replication
- ALTER SYSTEM
- Materialized Views Refresh Concurrently