EDB Postgres Advanced Server

Postgres Optimized for the Enterprise

EDB Postgres Advanced Server

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

Title: 
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

Comparing PostgreSQL and EDB Advanced Server

Features marked:  are compatible with Oracle®; Yes are security related

 
Feature
 
PostgreSQL EDB Postgres Advanced Server

General

  Includes all PostgreSQL features and updates, plus additional:
    - Security features
    - Performance features
    - Developer features
    - DBA features
    - Database compatibility with Oracle
 
Enterprise Security Features
 
Password Profiles ⊕ Yes   Yes
EDB SQL/Protect Yes   Yes
Session Tag Auditing Yes   Yes
Server Procedural Language Protection (EDB*Wrap) ⊕ Yes   Yes
 
Enterprise Performance Features
 
Scalable Partitioning   Yes
Partition By... Range
List
Range
List
Hash
Query Optimizer Hints ⊕   Yes
 
Enterprise Developer Features
 
Hierarchical Queries ⊕   Yes
21 pre-defined re-usable Function Packages   Yes
Synonyms (public and private) ⊕   Yes
Extended Functions and Operators ⊕   Yes
Explicit Commit/Rollback in Stored Proc. ⊕   Yes
Bulk Collect and Binds ⊕   Yes
User defined object types ⊕   Yes
Nested Tables ⊕   Yes
User Defined Exceptions ⊕   Yes
User defined packages and functions ⊕   Yes
Associative Arrays ⊕   Yes
VARRAYs ⊕   Yes
edb_stmt_level_txn ⊕   Yes
EDB*Plus ⊕   Yes
ECPGPlus   Yes
 
Enterprise DBA Features
 
Resource Management   Yes
Declarative Partitioning Syntax ⊕   Yes
EDB*Loader ⊕   Yes
SQL Session/System wait diagnostics ⊕   Yes
Extended Catalog Views ⊕   Yes
 
Database Compatibility for Oracle (also includes all features in table with the ⊕ symbol)
 
PL/SQL Support in Stored Procedures and Triggers ⊕   Yes
PL/SQL Debugger ⊕   Yes
EDB*OCI ⊕   Yes

  

Common Features

Go to: Different Features

 
Common Database Features
 
PostgreSQL Postgres Advanced Server
Compatible with EDB Postgres Tools: Postgres Enterprise Manager
Postgres Failover Manager
Postgres Backup & Recovery
Postgres Cloud Management
Postgres Replication Server
Postgres Data Adaptors
Postgres Migration Assessment Tool
Postgres Migration Toolkit
 
Postgres Enterprise Manager
Postgres Failover Manager
Postgres Backup & Recovery
Postgres Cloud Management
Postgres Replication Server
Postgres Data Adaptors
Postgres XA Connector
Postgres Migration Assessment Tool
Postgres Migration Toolkit
Full ACID Compliance Yes Yes
Multi-Version Concurrency Control (MVCC) Yes Yes
Strong conformance to ANSI-SQL:2008 standard Yes Yes
ANSI Constraints Yes Yes
Per-Column Collations Yes Yes
Column Trigger Yes Yes
Primary/Foreign keys support Yes Yes
GIN & GiST Indexes Yes Yes
Block Range Indexes (BRIN) Yes Yes
Function based Indexes Yes Yes
Composite Indexes Yes Yes
K-Nearest-Neighbor Indexes Yes Yes
Index-only Scans Yes Yes
Full Text Search Yes Yes
UPSERT (Update or Insert) Yes Yes
SQL Aggregates with CUBE, ROLLUP and GROUPING SETS Yes Yes
Triggers and Stored Procedures Yes Yes
Views Yes Yes Yes
Materialized Views Yes Yes Yes
View Security Barriers Yes Yes Yes
SQL USAGE privilege on types Yes Yes Yes
ANSI Standard SQL GRANT/REVOKE Yes Yes Yes
Column Level Permissions Yes Yes Yes
Row Level Security Yes Yes Yes ⊕
Group/Role Support Yes Yes Yes
SQL/MED Support Yes Yes
Programming language support for database Stored Procedures, Triggers and Functions. pgSQL
Java
C
C++
Perl
Python
Ruby
Javascript
Scheme
Tcl
PHP
pgSQL
Java
C
C++
Perl
Python
Ruby
Javascript
Scheme
Tcl
PHP
Application Client Library interface support libpq
JDBC
C
C++
Perl
Python
Ruby
Scheme
PHP
ODBC
.NET
Lisp
Qt
libpq
JDBC
C
C++
Perl
Python
Ruby
Scheme
PHP
ODBC
.NET
Lisp
Qt
Foreign Data Wrappers HDFS (Hadoop)
MongoDB
MySQL
PostgreSQL
HDFS (Hadoop)
MongoDB
MySQL
PostgreSQL
Unstructured Data Types XML
JSON
JSONB
Key-Value
XML
JSON
JSONB
Key-Value
Unlogged Tables Yes Yes
Range Data Types Yes Yes
Writeable Common Table Expressions Yes Yes
Window Functions Yes Yes
Rules Engine Yes Yes
In-place version upgrades Yes Yes
Streaming Replication & Hot Standby Yes Yes
Logical Decoding Yes Yes
Online Backup and Point In Time Recovery Yes Yes
Online Index Reorganization Yes Yes
Hot restart with pg_prewarm Yes Yes
pg_rewind Yes Yes
PostGIS spatial extensions support Yes Yes
Partitioning of Table and Indexes Yes Yes
Authentication support Yes LDAP
SSL
RADIUS
PAM
Kerberos
GSSAPI
SSPI
Trust
Password
Ident
LDAP
SSL
RADIUS
PAM
Kerberos
GSSAPI
SSPI
Trust
Password
Ident
DB Connection Encryption Yes SSL SSL
DB Connection White Lists Yes Yes Yes
DB Connection Black Lists Yes Yes Yes

 

Function Package Descriptions for EDB Postgres Advanced Server

Return to Feature Differences - Packages

 
Package Name
 
Package Description
DBMS_ALERT Functions that allow asynchronous notification of database events via an Alert. Using this package and triggers, an application can notify itself whenever values of interest in the database are changed.
DBMS_CRYPTO Yes Provides functions to encrypt and decrypt stored data.
DBMS_JOB Has been replaced by DBMS_SCHEDULER but included for compatibility with older Oracle applications.
DBMS_LOB Functions that allow access to and manipulation of Large OBject values.
DBMS_LOCK Provides a function interface to Lock Management services.
DBMS_OUTPUT Allows the sending of messages from stored procedures, packages, and triggers for application or debugging use.
DBMS_PIPE Functions that allow two or more sessions in the same database instance to communicate with one another.
DBMS_PROFILER Provides functions to profile stored procedure workloads and identify performance bottlenecks.
DBMS_SCHEDULER Job scheduler functions for creating and executing unattended repetitive tasks inside the database.
DBMS_SQL Permits the use of dynamic SQL in procedures to allow applications to run SQL statements with unknown parameters (such as table name) until runtime.
DBMS_RANDOM Useful functions to generate random text, numeric and date values.
DBMS_RLS Yes Implements row level security functions in the database blocking users from seeing each other's data in the same application.
DBMS_SESSION Yes Functions with the ability to enable and disable roles.
DBMS_UTLTITY A collection of functions for getting information about various runtime operations and meta data from the database.
UTL_ENCODE Functions to perform Base64 encoding and decoding of data intended for transport between hosts.
UTL_FILE Allows database procedures to read and write operating system text files in an I/O stream fashion.
UTL_HTTP Functions that provide the ability to make HTTP calls to access information on web servers.
UTL_MAIL Provides functions to create,manage, and send email from the database including attachments, CC, and BCC.
UTL_RAW Functions supporting the manipulation of raw data types.
UTL_SMTP Provides functions for sending mail via SMTP according to the RFC821 specification.
UTL_URL Functions for escaping and un-escaping URL strings.
For complete specifications for each supported Function Package see the Database Compatibility for Oracle® Developer's Guide in the Documentation section.

 

Back to top

Resources