EDB Postgres Advanced Server

EDB Postgres Advanced Server is EDB’s enhanced Postgres database designed to meet the needs of the digital enterprise.

  • EDB Advanced Server incorporates all of PostgreSQL’s features with additional enterprise-class functionality for enhanced performance and security requirements for enterprise workloads.
  • EDB Advanced Server features improve DBA and developer productivity.
  • Oracle users benefit from EDB Advanced Server’s native database compatibility with Oracle, including PL/SQL, built-in packages, and many DBA tools to accelerate and simplify migrations.

 

 

Overview

Architecture

EDB Postgres Advanced Server Architecture

EDB Advanced Server for DBAs

EDB Advanced Server for DBAs

Special features for DBAs you won't find in PostgreSQL include the Resource Manager which prioritizes CPU and I/O resources protecting business critical process from low priority loads. The pg_prewarm cache restart manager provides consistent and seamless performance across database offline maintenance cycles while SQL Session/System wait diagnostics provide detailed statistics to help diagnose and fix performance bottlenecks. EDB*Loader inserts bulk data with parallel file loads and flags problem data for postload corrections. When these DBA centric database features are combined with EDB's mission critical tools, Postgres Advanced Server DBAs are able to manage more databases more effectively across the enterprise.

EDB Advanced Server for Developers

EDB Advanced Server for Developers

Postgres developers appreciate being able to write database procedures using their client application code language (Java, .NET, C, C++, Javascript, Python and more) and easily debugging that server side code using Enterprise Manager's debugger. Ready to run function packages reduce code size, errors and development time and custom packages let developer build their own time-saving re-usable function packages. Native support for JSON, JSONB (fast binary), Key-Value pairs and XML allow developers to use single tool for structured and unstructured applications while Data Adaptors for Hadoop, MongoDB and MySQL eliminate data silos. Finally a suite of advanced programming features reduce application development time and code complexity (e.g. hierarchical queries, shorthand synonyms, User defined object types provide object oriented data model support, nested tables, and user defined exceptions).

 

Performance and Resource Management

Performance and Resource Management

EDB Resource Manager provides the capability to control system resource usage by Advanced Server processes. This includes CPU resource throttling and dirty buffer throttling -  a method to control the dirty rate of shared buffers by Advanced Server processes.

Postgres Advanced Server also provides a variety of query optimizer hints to influence the choice of access plans when the developer knows more about the data and record structures than the optimizer's assumptions.

Security

Security

When it comes to security, large organizations and those dealing with any kind of financial information have the most to gain using Postgres Advanced Server. Password policy management inside the database, enhanced auditing for compliance and a SQL firewall (SQL/Protect) to guard against data destruction and theft are all additional features we have developed into Postgres Advanced Server. Further, we provide cookbooks for Data Encryption (non-transparent / transparent) and PCI Compliance, as well as procedural language protection (EDB*Wrap) to guard sensitive information and intellectual property embedded in code.

Postgres supports LDAP, Kerberos and Active Directory to protect data from unauthorized access, while allowing authorized users to access data effortlessly.

The Secure Technology Implementation Guide (STIG) for EDB Advanced Server, published by the U.S. Department of Defense (DoD), provides a guide to define how EDB Postgres can be deployed and configured to meet security requirements for government systems.

Database Compatibility for Oracle

Database Compatibility for Oracle

EDB provides a mature technology platform to migrate applications from Oracle Database to EDB Postgres Advanced Server. Key components include:

  • SPL - A native implementation of a stored procedure language that supports the key constructs of PL/SQL
  • APIs that support key capabilities of Oracle’s JDBC, ODBC, .NET, OCI and Pro*C interfaces
  • Packages that provide the key functionality of the most popular Oracle packages
  • Declarative partitioning to ease the migration from Oracle
  • DBA tools that make it easy to migrate established work processes and skill set to EDB Advanced Server

Enhancements for Advanced Server

Enhancements for Advanced Server

EDB has enhanced Postgres at all levels of the architecture to improve enterprise capabilities and simplify migration.

Summary description of the key enhancements





Area

Feature

Description

Value to the DBA or developer

Client Interfaces

JDBC

EDB Enhanced JDBC driver (with compatibility for Oracle). Supports in/out, ref cursors, "call" of stored procedures, Oracle data types (struct, varchar2, arrays, ...)

Simplifies application migration; enhances application code reuse

 

ODBC

EDB Enhanced ODBC driver (with compatibility for Oracle). Supports in/out parameters, ref cursors, "call" of stored procedures, varchar2 data type, ADO extension

Simplifies application migration; enhances application code reuse

 

.NET

EDB Enhanced .NET driver (with compatibility for Oracle). Supports in/out parameters, ref cursors, "call" of stored procedures, varchar2 data type, and for Entity Framework 6

Simplifies application migration; enhances application code reuse

 

OCL

Oracle compatible implementation of key OCI (Oracle Client Interface) capabilities

Simplifies application migration; enhances application code reuse

 

ECPG*Plus

Oracle compatible implementation of key ProC (Oracle C interface) capabilities

Simplifies application migration; enhances application code reuse

Optimizer & Planner

Query pruning

Optimized partition triggers and smart partitioning pruning allow Advanced Server to perform dramatically better than PostgreSQL across hundreds or thousands of partitions. INSERT tests (1 million rows) showed 450x improvement while SELECT tests (10 simultaneous clients) showed 75x improvement across one thousand partitions.

Performance optimization for partitioned tables to only access partitions that contain data to be queried.

 

Oracle style Query Hints

Allows influencing the query optimizer's choice of access plans when the developer knows more about the data and record structures than the optimizer's assumptions.

DBA/Developer can direct the planner to use an optimal plan

Executor

DRITA

The Dynamic Runtime Instrumentation Tools Architecture (DRITA) allows a DBA to query catalog views to determine the wait events that affect the performance of individual sessions or the system as a whole. DRITA records the number of times each event occurs as well as the time spent waiting; you can use this information to diagnose performance problems.

Collects and reports on performance for query debugging and performance optimization

 

Infinite Cache

Provides distributed cache in a multi-node environment

Multi-node memory caching for scale out read replicas

 

Resource Manager

EDB Resource Manager allows to control the usage of operating system resources (CPU and IO) used by EDB Postgres Advanced Server processes.

This capability allows you to protect the system from processes that may uncontrollably overuse and monopolize certain system resources.

Utilities

Declarative partitioning

Oracle compatible table partitioning

Partitioning a large table can improve performance, and provides easier and more efficient data management when rolling data on and off.

 

Password Profiles

Size, complexity, reuse and expiration policies

Management of User Passwords to ensure the correct levels of complexity and expiration for secure database access

 

Oracle catalogue/ dictionary

Fifty seven (57) of the most popular Oracle catalog views.

Simplifies application migration; enhances application code reuse

 

Synonyms

Both public and Private shorthand for database object references

Ease of use for developers

Extensions

PL/SQL

Oracle compatible SQL Procedural Language

Simplifies application migration; enhances application code reuse

 

Value-add packages

Implementation of key functionality packages (DBMS_ALERT, DBMS_CRYPTO, DBMS_JOB, DBMS_LOB, DBMS_LOCK, DBMS_MVIEW, DBMS_OUTPUT, DBMS_PIPE, DBMS_PROFILER, DBMS_RANDOM, DBMS_RLS, DBMS_SCHEDULER, DBMS_SESSION, DBMS_SQL, DBMS_UTILITY, UTL_ENCODE, UTL_FILE, UTL_HTTP, UTL_MAIL, UTL_RAW, UTL_SMTP, UTL_URL, DBMAS_AQ (9.6))

Simplifies application migration and enhances application code reuse

 

Language Support

Perl, Python, C/C++, Ruby, PHP, TCL, libpq and more

Wide range of language support

Security

SQL Protect

SQL Injection Protection

Protects your system from malicious SQL Injection techniques

 

Virtual Private Database

Row Level Security

Fine-grained access control using security policies. Fine-grained access control in Virtual Private Database means that access to data can be controlled down to specific rows as defined by the security policy.

 

Password Profiles

Enhanced User Security Profiles

Management of User Passwords to ensure the correct levels of complexity and expiration for secure database access

 

Session Tag Auditing

Enhanced Auditing for Security and Compliance

Advanced Server can log all connections, disconnections, DDL statements, and any statements resulting in an error. edb_audit_tag can specify a string value that will be included in the audit log.

 

EDB*Wrap

Procedural Language Protection from unauthorized users

Protects proprietary source code and programs (functions, stored procedures, triggers, and packages) from unauthorized scrutiny. The EDB*Wrap program translates a file that contains SPL or PL/pgSQL source code (the plaintext) into a file that contains the same code in a form that is nearly impossible to read.

DBA Tools

EDB*Plus

Command line interface that emulates Oracle SQL*Plus

Simplifies application migration; enhances application code reuse

 

EDB*Loader

Oracle compatible bulk data loader

A high-performance bulk data loader that provides an interface compatible with Oracle databases for Advanced Server.

 

SQL Profiler

Find, troubleshoot and optimize slow running SQL queries

On-Demand and scheduled traces and an Output Analyzer to locate and optimize poor running queries.

 

PL/SQL Debugger

Debug PL/SQL

The Debugger is a tool that gives developers and DBAs the ability to test and debug Postgres Plus server-side programs using a graphical, dynamic environment.

 

Dynatune

Provides dynamic tuning of the database server to make the optimal usage of the system resources

Determines how much of the host system's resources are to be used by the database server based upon the host machine's total available resources and the intended usage of the host machine.

 

pg_catcheck

Catalog integrity checker

A catalog integrity checker for PostgreSQL and Postgres Plus Advanced Server.

 

pg_prewarm

Restores the Postgres Servers buffer cache

Provides the ability to preload relation data into either the operating system buffer cache or the PostgreSQL buffer cache at startup for improved performance.

Comparing PostgreSQL and EDB Advanced Server

Find the right database for your needs.

 

Feature Key:

compatible with Oracle®

Description: es security related

 

 

Feature

 

PostgreSQL 9.6

PostgreSQL 10

EDB Postgres Advanced Server

 

Enterprise Security Features

 

Password Profiles Description: es

x

x

Description: es

EDB SQL/Protect Description: es

x

x

Description: es

Session Tag Auditing Description: es

 x

x

Description: es

Server Procedural Language Protection (EDB*Wrap) Description: es

x

x

Description: es

 

Enterprise Performance Features

 

Scalable Partitioning

 x

Description: es

Description: es

Partition By...

x

Description: es

Range

List

Hash

Query Optimizer Hints

 x

x

Description: es

 

Enterprise Developer Features

 

Hierarchical Queries

 x

Description: es

Description: es

21 pre-defined re-usable Function Packages

 x

x

Description: es

Synonyms (public and private)

 x

x

Description: es

Extended Functions and Operators

x

x

Description: es

Explicit Commit/Rollback in Stored Proc.

x

x

Description: es

Bulk Collect and Binds

x

x

Description: es

User defined object types

x

x

Description: es

Nested Tables

x

x

Description: es

User Defined Exceptions

x

x

Description: es

User defined packages and functions

 x

x

Description: es

Associative Arrays

x

x

Description: es

VARRAYs

x

x

Description: es

edb_stmt_level_txn

 x

x

Description: es

EDB*Plus

 x

x

Description: es

ECPGPlus

x

x

Description: es

 

Enterprise DBA Features

 

Resource Management

 x

x

Description: es

EDB*Loader

 x

x

Description: es

SQL Session/System wait diagnostics

 x

x

Description: es

Extended Catalog Views

x

x

Description: es

 

Database Compatibility for Oracle
(also includes all features in table with the
symbol)

 

PL/SQL Support in Stored Procedures and Triggers

 x

x

Description: es

PL/SQL Debugger

 x

x

Description: es

EDB*OCI

 x

x

Description: es

  

 

 

 

Resources