optimizing with open source technology postgres - daseq · providing enterprises with the...

© 2013 EDB All rights reserved 8.1. 1

Optimizing with Open Source Technology Postgres

Mark Jones

[email protected]

Sales Engineering, EMEA


Providing enterprises with the cost-performance benefits of Postgres by offering the products, resources, support and dependability required for high-level commercial applications.

•170+ employees

•2,500+ customers across all market segments

•Global presence with offices in North America, Europe, Asia

•Fast growing: over 60% YOY sales growth in 2013

•Strong financial banking:

© 2013 EnterpriseDB. All rights reserved.


POSTGRESinnovation

ENTERPRISE reliability

24/7 support

Services & training

Enterprise-class features & tools

Indemnification

Product road-map

Responsive, dependable & controlled

Thousands of developers

Fast development

cycles

Low cost

No vendor lock-in

Advanced features

POSTGRES

PLUS ADVANCED SERVER

Get The Best Of Both Worlds


Synchronized with and contributing to the PostgreSQL community

Postgres Plus: A Super-Set of Features

Postgres PlusAdvanced Server

EDB DEVELOPMENT

Community Needs

Enterprise Needs

PostgreSQL

Contributions


Postgres Plus: everything in PostgreSQL PLUS...

5

Security:• Fine Grained Audit Logging• Row Level Security (VPD)• SQL Injection attack guard• Server-side code protection• EAL2 certification

Bundled Tools:• Oracle & SQL Server to

Postgres replication • Enterprise management,

monitoring, and tuning• Oracle & SQL Server to

Postgres Migration Tools• Update Monitor

Oracle Compatibility:• 60% of customers migrate

in <2 weeks• PL/SQL, OCI support• Oracle SQL extensions• User defined objects• Function packages• Database links• Oracle-like tools:

EDB*Loader, EDB*Plus, EDB*Wrap

Performance:• Partitioning:

-400x faster writes -76x faster selects

• SQL Profiler--find / fix slow SQL workloads

• Bulk Data Loader--2x faster• Index Advisor—speeds up

slow queries• Query Hints--optimizer control• DynaTune—auto-tunes for memory upgrades• Bulk Collect/Fetch/Binding of arrays• Dynamic runtime statistics reveals SQL wait bottlenecks

ORACLE COMPATIBILITYPostgres Plus

Advanced Server PostgreSQLPERFORMANCE


Oracle Compatibility


• Run applications written for Oracle virtually unchanged

• No need to re-train Oracle DBAs and developers • Support for PL/SQL language and OCI interoperability• Replication for easy sharing of data

7

Oracle Compatibility


Survey: Re-Use of Oracle DBA Skills


Compatibility Means:• SQL extension support

− Decode, NVL, Substr, NVL2− Date/time functions: add_months, extract, next_day

• PL/SQL support− REF Cursors, Implicit and explicit cursors− Looping, variable declarations, conditional statements− Collections: Associative Arrays, Varrays, Nested tables− Bulk binding− Named parameters− User Defined Exceptions− Explicit Transaction Control

− within a stored procedure− (not supported by PostgreSQL)

• Tools− EDB*Plus – SQL*Plus look-a-like− EDB*Loader – SQL*Loader equivalent− EDB*Wrap – similar to the PL/SQL wrapper

9CONFIDENTIAL© 2011 EnterpriseDB. All rights reserved.


Compatibility (continued)• Features

− Packages− Stored procedures− Functions− Triggers− Hints− Database Links− Hierarchical Queries− Synonyms – Public and Private− Sequences− Rownum− Object types

− Create type … as object− Create type … as table− Create type …as varray− Constructor and collection methods

− Users/Roles− Dynamic SQL

10CONFIDENTIAL© 2011 EnterpriseDB. All rights reserved.


Compatibility (continued)• Data Types

− Integer, number, char, double precision, float, varchar2, blob, clob, xmltype, rowid

• Built-in Packages− DBMS_:

− SQL, LOB, JOB, PIPE, ALERT, OUTPUT, UTILITY, PROFILER

− UTL_:− FILE, MAIL, SMTP, ENCODE, TCP

• Oracle-like Data Dictionary− ALL_, DBA_, USER_ views− Most commonly accessed views

• Diagnostics - DRITA− System and session waits

− Not exposed in PostgreSQL − Part of Advanced Server

− Statspack-like reporting 11CONFIDENTIAL© 2011 EnterpriseDB. All rights reserved.


Oracle Migration Assessment


Assessing Ease of Migration

13

Analyze Oracle Schema Layout:1

Static Analysis of Oracle Schemaand database layout

2 Review features used in Oracle client application:

Static Analysis of Source Code

Developer Interviews

Supported features in PPAS & required work-arounds (database)

Supported features in PPAS & required work-arounds (database)

Supported features in PPAS & required work-arounds (application)

Supported features in PPAS & required work-arounds (application)


Assessing Ease of Migration (con’t)

14

• Score of 1 – 10 assigned based on analysis of steps 1 and 2

− 1 difficult – 10 minimal effort

• Work arounds for identified missing features

• Time estimates given in person weeks

4 Migration Plan

Detailed report describing supported features along with elaboration of issues and recommended fixes to ensure seamless migration of production database. (database & application)

Detailed report describing supported features along with elaboration of issues and recommended fixes to ensure seamless migration of production database. (database & application)

3 Evaluate results along 5 axis using proven algorithms:


Database Migration Toolkit

• Online Migration Toolkit enables point and click migration from Oracle

• Automatically Migrates:

Data Schemas Stored Procedures Triggers Functions

Sequences Packages Views Database Links Synonyms


Migration Example

optimizing with open source technology postgres - daseq · providing enterprises with the...

Documents