5 Use Cases Driving Database Development
Jason Davis, Senior Director I Product Management, EnterpriseDB
The new EDB Postgres™ Platform 2017 from EnterpriseDB® (EDB™) gives enterprises the capability, performance, and reliability they need with the flexibility and ease of provisioning required by modern DevOps. A core component of the Platform is the EDB Postgres™ Advanced Server database, a version of Postgres that EnterpriseDB has enhanced with enterprise-class performance, security, manageability, and developer capabilities.
The new EDB Postgres Platform 2017 rolled out a new version of the database, EDB Postgres Advanced Server 9.6. (Read the press release here.) EDB Postgres Advanced Server 9.6 incorporates the latest open source PostgreSQL release and also includes additional features and capabilities that EnterpriseDB developed based on customer interactions and market demands. For the v9.6 database, EnterpriseDB targeted development efforts to introduce capabilities that support the following five use cases:
1. Migrate More Applications from Oracle
EnterpriseDB introduced Advanced Queuing, otherwise known as DBMS_AQ, in an effort to support a greater range of application migrations from Oracle® to EDB Postgres. This feature enables customers to build business applications to communicate with one another where producer applications enqueue messages and consumer applications dequeue them. Certain classes of applications, such as those used for financial transactions, user profile data, or system of record information, will benefit from database-integrated queues that have transactional semantics (commit and rollback) and a close relationship to the database.
Advanced Queuing benefits all users, not just those migrating applications from Oracle. It does not require that the database operate in Redwood mode, which is an Oracle-compatibility setting in EDB Postgres Advanced Server. This aligns with EnterpriseDB’s efforts to support customers who want to develop in an ANSI—and not necessarily Oracle-compatible—way.
Nested Sub Procedures are a capability in the PL/SQL compatibility layer, which is referred to as SPL or Stored Procedure Language. (EnterpriseDB developed native PL/SQL support for Oracle compatibility.) They are a named procedure defined and used inside another procedure or function. One EnterpriseDB customer, an ISV specializing in financial management applications, utilized this capability in developing new customer offerings. The company now offers the EDB Postgres Platform, with EDB Postgres Advanced Server, as an alternative backend to Oracle in its products.
2. Manage Larger Data Sets
Customers are working with larger databases, and now regularly deploy EDB Postgres Advanced Server to manage OLTP workloads of 10 terabytes or more. Enhancements to partitioning capabilities, including fast pruning for more data types (varchar, smallint) and prepared statements, better enable EDB Postgres Advanced Server 9.6 to support customers with larger data sets. Also, greater optimization of vacuum in the form of what’s called a freeze map allows the system to skip frozen (unchanged) pages when cleaning up system bloat. The result is a better performing database.
3. Run Parallel Queries
With EDB Postgres Advanced Server 9.6, parallel query has become a reality for customers. The Postgres development community, with major contributions from EnterpriseDB engineers, has spent several years developing the foundation for Postgres to support parallel query capabilities. Now, with the rollout of parallel sequential scans, joins, and aggregates, customers have the capabilities to use EDB Postgres Advanced Server for parallel query functions. The result is a performance increase of two to four times that of prior versions for long running queries in a TPC-H benchmark. These capabilities are the first related to parallel query in what can be considered a multi-phase rollout with the next versions to include more capabilities (e.g. sorts, subqueries, and more) to expand parallel computing in Postgres.
4. Build More Robust, Integrated Configurations
The new EDB Postgres Advanced Server introduced features that enable customers to build more robust and flexible configurations using clustering, scale out, and integration features. Synchronous apply confirmation, for example, ensures that changes written to a replica are immediately visible, which allows customers to build truly read scalable clusters. Multiple standby configurations allow flexible topologies so that database administrators (DBAs) can define how many synchronous replicas must confirm the application before committing the transaction.
EnterpriseDB also works closely with the PostgreSQL community to improve Foreign Data Wrappers (FDWs) so they can support more efficient remote system communication. Remote join, sort, and update pushdown now allow queries that spread across multiple machines to be more performant. Developing FDW technology to support these functions represented a major step forward in the effort to develop a horizontally scalable (sharding) solution for Postgres.
5. Improved Monitoring
Customers experienced with commercial database like Oracle or SQL Server expect greater transparency into the database operations than Postgres has provided historically. Hence, EnterpriseDB has dedicated significant research and development into database monitoring. With EDB Postgres Advanced Server 9.6, EnterpriseDB added detailed wait event monitoring in pg_stat_activity, which now exposes over 30 different wait events to understand where the system might be blocked at a given point in time should a blockage occur. And, vacuum progress checking allows the DBA to monitor the vacuum it cleans up bloat in a table.
The combination of these capabilities reflect the commitment of EnterpriseDB to addressing the needs of our customers who are deploying and managing very large databases, and building or migrating their application with business logic located close to the data.
Jason Davis, Senior Director I Product Management, EnterpriseDB.