Hibernate and PostgreSQL

Module Objectives!

 Provide the historical background for PostgreSQL and

Hibernate
 Identify the problem being solved by O-R Mapping
 Provide an overview and short tutorial on Hibernate
• Showing how to use Hibernate together with PostgreSQL
 Discuss and demonstrate advanced topics and optimization
techniques for using PostgreSQL together with Hibernate
A Brief History of RDBMS and
Object Oriented Programming
Objectives!
 Understand the history and evolution of PostgreSQL and
Hibernate
 Understand the names and players
 Understand the Genesis of these two technologies and the
technologies that proceeded them
 Identify “The great impedance mismatch” that is being solved
 Identify the common problem that arises
 Hibernate deployments (with PostgreSQL)

• Application Server overloaded

• Database overloaded
• Slow database queries

Developers: DBAs:
“this database “quit sending all that
is a dog” inefficient SQL”
 About Hibernate

• Object-relational persistence framework

• Supports collections, object relations, composite types
• HQL query language, caching, JMX support

Application
Tier Persistent
Objects
Hibernate Transactions Connections Sessions Queries Criteria

Connectivity JDBC JNDI JTA

Data
Tier
© 2011 EnterpriseDB. All rights reserved. 7
RDBMS History!
 1970 Edgar Codd of IBM
• Wrote many papers to solve the searchable data problem for Database
Management Systems!
• Eventually a paper one called:!
– “A Relational Model of Data for Large Shared Data Banks”!
 1973 to 1979 Stonebraker and Eugene Wong created project
INGRES based on Codd’s paper
• Created at U. Cal Berkeley!
 1978 onward Many of the members of INGRES become part of
RDBMS companies such as Sybase , Illustra, Informix
 1978 Larry Ellison releases first version of Oracle based on
System R
© 2011 EnterpriseDB. All rights reserved. 9
 RDBMS History Continued!
 In 1986 Michael Stonebraker led the development of
PostgreSQL at Berkley
– Stands for “Post Ingres”
 Worked on it in Academia for 8 years
 Found life in the Open Source Community in the mid
1990’s

© 2012 EnterpriseDB. All rights reserved.

 Object Oriented Programming!
 Started in 1960’s as a way to encourage the re-use and
maintainability of code
 Developed for the purpose of making programs more maintainable
 Software was wearing out from patches
• After a period time code wears out
• Nobody can maintain it
• Objects have both data and behavior
 Objects define interfaces to data through “methods”
 Programmer never access the object data directly always go
through the object’s defined interfaces
 Defined interfaces allow programmers to interface easily with each
other to build larger programs

© 2012 EnterpriseDB. All rights reserved.

 Other OOP Concepts!
 Inheritance
 Operator Overloading
 Identity
 Polymorphism
 Method overloading

© 2012 EnterpriseDB. All rights reserved.

 Object Oriented Programming !!
 Allows for complex data types and structures:
• Plane
• Car
• House
• Family
 Create data representations that are not always easily
expressed in a Relational Terms

© 2012 EnterpriseDB. All rights reserved.

Object Oriented Programming!
 Allows for very complex data relationships
 Object Oriented Languages!
 In late 1980’s C++ becomes first commercial Object Oriented
programming language
• Many famous commercial programs written in C++ / C
– Microsoft Office, Oracle, PostgreSQL, OS X
• Powerful and Fast
• Complex and Error Prone many difficult memory
management issues
• Portability issues
 In mid 1990’s Java hits the scene
• Addressed memory management issues in C++
• Addresses ease of use and portability issues
• Sacrifices some power and some performance
 In late 1990’s C# becomes a competitive response to Java
The great Impedance Mis-Match!
 Data in Objects are always accessed and updated via
methods
• No such method concept in RDBMS
 Object Oriented Programming based on Software
Engineering best practice
 RDBMS based on Mathematics
 In OO Programming you traverse objects via their
relationship
• Navigational Access
 In RDBMS you join data rows of tables
 Object-Relational Impedance Mis-Match!
Area Object Oriented RDBMS
Programming
Data Accesses Via Getter and Setter Direct Access.
Methods Constraints on Tables
and Columns

Navigating Data Navigation through Join data tables together

pointers in Object
Graphs

Basic Types Java, C++: Strings, VCHAR, TABLES

Collections, BLOBS
Objects

Access Type Via Object Type VIA Login Credentials

Relationship
Culture Differences Live in the Application Live in the DBA world
Developer World
 Impedance Mis-Match Example!
Where is your friend’s name?
 Pointed to in the Object world
 Reached via associative access
in Relational World

© 2012 EnterpriseDB. All rights reserved.

 Object Oriented Database Systems!
 In the 1990s’s a new DBMS systems are introduced
 Data is stored as Objects
 DBMS stores Data the Way programs use and access data
 Never became more than niche market
• Very popular for complex object models with low concurrency
demands
 Issues around:
• Variants of programming languages
• The entrenchment and acceptance of RDBMS
• Tools
• Standards
• Reporting
• Locking

© 2012 EnterpriseDB. All rights reserved.

Java the Worldʼs Most Popular Language!
Object Relational Mapping!
 Object Relational Mapping
• An attempt to map Object Oriented Data structures to
Relational Data Structures
• Creates the effect of an Object Database
 Many attempts to create an Object-Relational Database
standard for Java
• JDO
– Java Data Objects
• EJB
– Enterprise Java Beans
• A variety of non standard third party products
 Object Relational Mapping!

© 2012 EnterpriseDB. All rights reserved.

 What can happen – What did happen!

© 2012 EnterpriseDB. All rights reserved.

 History of Object Relational Mapping!
 2000 Sun released Java EE 1.2
• Included a standard for Object Relational Mapping technology in
EJB
• Complex
• Mapping only for Corba Objects to relational data
 2002 Sun released Java EE 1.3
• Object Relational Mapping becomes more complex via EJB 2.0
• Spec approaches 1000 pages
 2004 Gavin King leads the development of Hibernate
• Simple Open Source O-R Mapping Infrastructure based on
POJO’s
 2004 Gavin and Team Join Jboss
 2004 Java EE 1.4
• Even more complex OR Mapping
 2005 Gavin Joins EJB 3.0 expert group

© 2012 EnterpriseDB. All rights reserved.

 History - Continued!
 2006 Java EE 5 and EJB 3.0, Java Persistence API 1.0
• A rewrite of EJB. Largely based on Hibernate
 Market recognizes this a significant move forward
• All Application Servers now support the Java
Persistence API

© 2012 EnterpriseDB. All rights reserved.

Java Enterprise Computing History!
Hibernate and JPA!
 JPA – Java Persistnace API
• Was developed using Hibernate
 Hibernate implements the JPA standard
 Hibernate is a super set of JPA
Conclusions!
 RDBMS are here to stay.
• Their use has been growing and will continue to grow
 Object Oriented Programming is here to stay
• Java is the number #1 Object Oriented Programming
Language
 PostgreSQL is the most Advanced Open Source Database
• Is likely to become the popular and used Open Source
Database
 Hibernate is the defacto. Standard for performing O-R
mapping
Using Hibernate
Objectives!!
 Learn how to get hibernate
 Options for setting hibernate up and installing on your system
 Learn how to work with an existing PostgreSQL database
 Learn how to work with a new PostgreSQL database
 How to use the Hibernate traditional API / Configuration
 Understand to use the Hibernate Java Persistence API
 Get Hibernate running with PostgreSQL
To do Hibernate Development Best!
 An Integrated Development Environment (IDE)
 Hibernate Runtime Libraries
 A database
 Hibernate Runtime Libraries
 An Application Server (not always but typically)
 A common deployment w/ Hibernate!
Web Clients

Application Server

Application
A
Persistent
Objects

Hibernate Runtime Libraries

Configuration
To Be used for JDBC
this class

Database
 Hibernate can be used in two forms!
 Hibernate Classic API
• Relational tables are bound to Java classes Java Classes
via XML Mapping file
• Columns are bound to Class fields via XML Hibernate Hibernate via
Mapping file Classic JPA
• Keeps the Java Code cleaner an easier to
read.
Hibernate Core
 Hibernate with java.persistence API
• A standard for doing Java ORM JDBC
• Mapping of Tables to Classes is done via
Java annotations
• It is a standard and other implementations
exist
– Eclipse Link
– Open JPA
Our Schema for a Car Rental Shop!

CREATE TABLE "customers" (

"customer_id" integer unique not null,
"customer_name" character varying(50) not null,
"phone" character(8) null,
"birth_date" date not null,
"balance" decimal(7,2)
);

CREATE TABLE "cars" (

"car_id" character(10) not null,
"description" character varying(80) not null,
"duration" interval
);

CREATE TABLE "rentals" (

"car_id" character(10) not null,
"rental_date" date not null,
"renturn_date" date not null,
"customer_id" integer not null
);
 Hibernate Classic!
Car.java
public class Car { CREATE TABLE "cars" (
private String car_id; "car_id" character(10) not null,
private String description; "description" character varying(80) not null,
"duration" interval
public String getCar_id() { );
return car_id;
}

public void setCar_id(String car_id) { Car.hmb.html

this.car_id = car_id;
} <hibernate-mapping>
public String getDescription() { <class name=”Car" table="cars">
return description; <id name="car_id" column="car_id">
} <generator class="native"/>
</id>
public void setDescription(String description) { <property name="description”
this.description = description; field=“description column=descrption/>
} </class>
</hibernate-mapping>
}
Java Persistence API!
 Eliminates Need for Maping Files
 Mapping handled by Java “Annotations”
 Simpler
 Hibernate and all other providers have Annotations beyond
the JPA standard
 Annotations were a recent addition to the Java Language
 Designed to eliminate having so much XML code to work
with.
 Sample Annotations!
@Entity Cars is a Persistent Class to be Mapped to a table

@Table Map Car to this table in this database

@Entity!
@Table(name = "cars", catalog = "carrentals", schema = "")!
public class Car implements Serializable {!

@Id !
@Column(name = "car_id")!
@GeneratedValue(strategy = GenerationType.AUTO)!
private String carId;!

@Column(name = "description")!
private String description;!

@Column(name = "duration")!
private Object duration;!

@Column Map this field to this Column

 Sample Annotations - Relationships!

@Entity!
@Table(name = ”rentals", catalog = "carrentals", schema = "")!
public class Rental implements Serializable {!

@Id !
@Column(name = ”rental_id")!
private String carId;!

@OneToOne!
@JoinColumn(name=“car_id”)!
private Car car;!

@OneToOne!
@JoinColumn(name=“customer_id”);!
private Customer customer;!

No longer using primative types. Mapping to other objects. When an a Rental!

Is used in a program hibernate creates Car and Customer objects dynamically.
Other Relationships!
 Relationships are the real Art of Object Relational Mapping Samples:
• ManyToMany (Employee to Projects)
• OneToMany (Manager to Employees)
• OneToOne
• One Direction
• Bi-Direction Many to Many
relationships require a
separate join table.

@Entity
public class Employee {
@Id private int id;
private String name;
@ManyToMany
@JoinTable(name=“EMP_PROJ”,
joinColumns=@JoinColumn(name=“EMP_ID”),
inverseJoinColumns(name=“PROJ_ID”))
private Collection<Project> projects;
 Programming with Hibernate!
/* Code Snipet to list all the cars. */

import java.util.List;

import org.hibernate.Session;
import javaapplication3.Car;

import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

…….
/* Always start with a session : Sessions are single threaded units of work */

Session session = sessionFactory.openSession();

session.beginTransaction(); /* Transactions don’t have to come hibernate but most come
from some where */

List cars = session.createQuery("from Car").list(); /* Hibernate use the HQL Query language */

session.getTransaction().commit();

for (int i = 0; i < cars.size(); i++) {

Car theCar = (Car) cars.get(i);
System.out.println("Id: " + theCar.getCar_id() +
" Title: " + theCar.getDescription());
}
}

Session.close(); /* sessions need to be closed*/

 Using Hibernate requires a Config File!
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0
//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>

<!– Database Configuration Files -->

<property name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>
<property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
<property name="hibernate.connection.url">jdbc:postgresql://localhost:5432/devel</property>
<property name="hibernate.connection.username">devel</property>
<property name="hibernate.connection.password”>devel</property>

<!-- Enable Hibernate's current session context -->

<property name="current_session_context_class">org.hibernate.context.ManagedSessionContext
</property>

<!-- Echo all executed SQL to stdout -->

<property name="show_sql">true</property>

<!– Classes to MAP -->

<mapping resource="car.hbm.xml"/>
<mapping resource="customer.hbm.xml"/>

</session-factory>
</hibernate-configuration>
 Development Configuration Options!
IDE Choices App. Server Databases
Choices

RUNTIME LIBRARIES FROM HIBERNATE.ORG

Configuration options!
 JBOSS Developer Studio
• Bundles Everything together
• Many GUI Tools
• Const $100.00
• Fastest Way to get started
 NetBeans comes with a version of Hibernate
• Not quite as feature rich as JBOSS Developer Studio
• Still Very good
Where to get Hibernate!
 www.hibernate.org
• Where the Hibernate community lives
• Contains hibernate and nothing else
• No development tools
• No application server
• Contains the Hibernate Tutorial
• Contains many peripheral projects
– Hibernate Shards
– Hibernate Tests
 Pros
• The latest greatest releases
• Samples, Tutorials, very active community
 Command line installer
 Built entirely around Maven
JBoss Tools!
 A complete JBOSS development environment
 Comes with Eclipse
 Outstanding tools for Hibernate
 Everything is bundled
Getting NetBeans!
Configuration For This Class!

NetBeans Bundled
Hibernate Libraries

Java 1.6
 Ways to do an O-R Project with Hibernate!
 Select Hibernate Classic vs. JPA
 Decide on appropriate approach:
1. Work with existing relational schema and build persistent
Java Classes
2. Have classes auto-generated from database (JBOSS
Tools)
3. Start with Persistent classes let Hibernate Create the
tables
What can happen – What did happen!
Lab 1!
 Install NetBeans
 Connect to Postgres
• See Lab Instructions
 Create a connection to PostgreSQL
 Open Existing Hibernate Project JavaApplicaton3
• Run against database
Common Issues with Postgres and
Hibernate
Hibernate and PostgreSQL!

 Techniques used in PostgreSQL deployments

 How Hibernate can leverage those techniques
 Common errors and resolutions
work_men adjustments for large queries!
 If Hibernate is creating large queries with sorts
 Look at adjusting work_mem
• Amount of memory in KB to be used by internal sorts and
hash tables before switching to temporary disk files.
• Minimum allowed value is 64 KB.
• It is set in KB and the default is 1024 KB (1 MB).
 Per Process setting
• Measure against memory on machine vs. shared_buffers
and other caching requirements
Adjusting Work_mem!

Work_mem Work_mem Work_mem

PostgreSQL Shared Buffer Cache Write-Ahead Log

fsync

fsync
Updates are Causing Havoc!
• The update statements send all of the columns even if they did
not change
– The Postgres logs show the following lines when just
updating the column “filler” to “HelloWorld”

LOG: execute <unnamed>: update tellers set bid=$1,

filler=$2, tbalance=$3 where tid=$4
DETAIL: parameters: $1 = '1', $2 = 'HelloWorld',
$3 = '0', $4 = '1'
LOG: execute S_2: COMMIT

• This causes some problems...

 Updates are Causing Havoc (cont.)!
• Why is this a problem?
– The application server and the database are doing more work
than necessary
• A much longer SQL statement must be constructed by
Hibernate
• There is more network traffic as this is sent to the database
• The database needs to parse a much bigger SQL statement

• Effect of curing this symptom (Minor)

– Modern hardware has the CPU and network bandwidth to
handle this additional overhead
– Probably only noticeable with a large number of transactions
 Updates are Causing Havoc (cont.)!
• Why is this still a problem?
– Foreign Key Integrity checks are fired unnecessarily
• Adds significantly more load to the database for each
update
• This grows for each Foreign Key on the table
• The amount of work increases as the size of the related
table increases

• Effect of curing this symptom (Moderate)

– If there are only a few Foreign Keys, this may not affect
performance
– If the related tables are small and updates are somewhat
frequent, the buffers will be in the database cache
 Updates are Causing Havoc (cont.)!
• How is this solved?
– Use the Dynamic Update annotation

@Entity
@Table(name = "tellers")
@org.hibernate.annotations.Entity(
selectBeforeUpdate = true,
dynamicInsert = true, dynamicUpdate = true)
@NamedQueries(
{@NamedQuery(name = "Tellers.findAll",
query = "SELECT t FROM Tellers t"),
@NamedQuery(name = "Tellers.findByTid",
query = "SELECT t FROM Tellers t WHERE t.tid = :tid"),
@NamedQuery(name = "Tellers.findByBid",
query = "SELECT t FROM Tellers t WHERE t.bid = :bid"),
@NamedQuery(name = "Tellers.findByTbalance",
query = "SELECT t FROM Tellers t
WHERE t.tbalance = :tbalance")})
public class Tellers implements Serializable {
 Replication!
• Replicate using Slony to additional
servers
– Asynchronous single
master multiple slave
• Load Balance reads using pgpool-II
• All writes go to the master node
• Connection pooling is also handled by
pgpool-II
 The problem!

CREATE TABLE tab ( id INTEGER PRIMARY KEY, somefield text NOT NULL );
INSERT INTO tab(id, somefield) VALUES (1,'fred');

Hibernate Some other writer

BEGIN; SELECT id, somefield FROM tab WHERE
id = 1; COMMIT

BEGIN; UPDATE tab SET somefield =

'foo' WHERE id = 1; COMMIT;

BEGIN; UPDATE tab SET somefield = 'baz'

WHERE id = 1; COMMIT;

Hibernate application commits with out knowledge of the

other app. Update.

© 2011 EnterpriseDB. All rights reserved. 60

 Resolve with a Trigger!

CREATE OR REPLACE FUNCTION zz_row_version() RETURNS

TRIGGER AS $$
BEGIN
IF tg_op = 'UPDATE' AND NEW.rowversion = old.rowversion
AND ROW(NEW.*) IS DISTINCT FROM ROW (old.*)

THEN
-- Row is being updated by an application that does not know
-- about row versioning. It's changed data in the row, but hasn't
-- incremented the version. We'll do that for it. NEW.rowversion :=
NEW.rowversion + 1;
END IF;
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

© 2011 EnterpriseDB. All rights reserved. 61

 Replication (cont.)!
• The possibility of row version
problems exists
public class Accounts implemen...
– The application can read a private static final long ...
row from the slave server @Id
@Basic(optional = false)
before a new version is @Column(name = "aid")
replicated private Integer aid;
@Column(name = "bid")
• Optimistic Concurrency Control can private Integer bid;
@Column(name = "abalance")
be used to solve these problems private Integer abalance;
– Row versions are handled @Column(name = "filler")
private String filler;
by the application by @Version
adding a version column to @Column(name = "version")
the tables private Integer version;
Database Advanced Features!
• Some of the advanced features in Postgres will speed up the
queries
– Windowing Functions, Hierarchical Queries, Spatial,
etc
• HQL does not support it and throws an exception

unexpected token: OVER near line 1, column 121 [SELECT insurance.insur...

Database Advanced Features (cont.)!
• Send native SQL to the database

String SQL_QUERY = "SELECT insurance_name, id, invested_amount, avg(i...

+ "invested_amount - avg(invested_amount) OVER(PARTI...
+ "FROM insurance ";
Query query = session.createSQLQuery(SQL_QUERY)
.addScalar("insurance_name", Hibernate.STRING)
.addScalar("id", Hibernate.LONG)
.addScalar("invested_amount", Hibernate.LONG)
.addScalar("a", Hibernate.DOUBLE)
.addScalar("diff", Hibernate.DOUBLE);

• This adds database specific code inside the application

Database Advanced Features (cont.)!

• Use a database view

CREATE VIEW insurance_diff AS

SELECT insurance_name, id, invested_amount,
avg(invested_amount) OVER(PARTITION BY insurance_name),
invested_amount - avg(invested_amount)
OVER(PARTITION BY insurance_name) AS diff
FROM insurance

• Allows the use of standard HQL

– Prevents database specific code inside the application
• Puts the database specific code inside the database
 Hibernate - Partitioning!
 A table is getting very large and the database administrator
partitioned the table but now the application can no longer insert
into the table

© 2011 EnterpriseDB. All rights reserved. 66

 Table Partitioning (cont.)!
 What is the problem?
• From the database command line, psql, a row can be inserted!
• Hibernate is expecting the row count to be 1, but Postgres is returning 0!
 How can the row be inserted into the database but return a
message that 0 rows have be updated?
• Postgres uses inherited tables and triggers to implement table partitioning!
• No rows are actually inserted into the base table, so the database is
behaving correctly!

© 2011 EnterpriseDB. All rights reserved. 67

 Table Partitioning!

In Postgres, tables are partitioned through table

inheritance.

Trades

Each base month

Feb. 2012

Mar. 2012
Jan. 2012

Apr. 2012
Table is a child of the
Trades Table

© 2011 EnterpriseDB. All rights reserved. 68

 Partitioning - how is this problem solved!
 Change the Postgres Trigger Function to return a row

There is a problem with this approach.

© 2011 EnterpriseDB. All rights reserved. 69

 Partitioning!
 Returning a row from the trigger function leaves a row in the
base table which is not wanted
• Create an After trigger to remove the row!

Not very scalable. Two inserts and 1 delete for every intert.

© 2011 EnterpriseDB. All rights reserved. 70

 Partitioning a better way!
 Use @SQLInsert annotation to Supress the row count

© 2011 EnterpriseDB. All rights reserved. 71

 Complicated Data Models!
 There are a large number of
joins necessary to get basic
information
 The data model is extremely
flexible so any “widget” can
be stored

© 2011 EnterpriseDB. All rights reserved. 72

 Complicated Data Model!
 Denormalization is not always bad
• But be careful not to have multiple versions of the truth!
 Reduces the number of database calls or joins to get the data
for the user
 A common technique is using Materialized Views or OLAP
cubes
• These are not native constructs in Postgres !
• But...they can be created with native Postgres constructs like Rule and
Triggers!

© 2011 EnterpriseDB. All rights reserved. 73

 Sample Query Generated by Hibernate!
Nested Loop Left Join (cost=64842.810..65843.240 rows=1 width=8178) (actual
time=2581.858..11580.000 rows=124558 loops
Nested Loop Left Join (cost=64842.810..65842.910 rows=1 width=7534) (actual
1/3 of query plan time=2581.844..11279.250 rows=124558 loops=1)
Nested Loop Left Join (cost=64842.810..65842.620 rows=1 width=7484) (actual
time=2581.831..11059.455 rows=124558 loops=
Nested Loop Left Join (cost=64842.810..65842.110 rows=1 width=7401) (actual
15 joins. time=2581.811..10474.692 rows=124558 loops=1)
Join Filter: ((yankee_tango.six_papa = two_hotel.six_mike) AND (two_hotel.xray_oscar =
charlie_tango.echo))
10 nested loops Nested Loop Left Join (cost=64839.780..65837.260 rows=1 width=7385) (actual
time=2581.627..6737.003 rows=124558 loops=1)
Filter: ((charlie_seven.juliet_charlie five NULL) OR charlie_seven.juliet_charlie)
Nested Loop Left Join (cost=64839.780..65471.120 rows=200 width=7204) (actual
Many misaligned row time=2581.603..6195.580 rows=124942 loops=1)
Nested Loop (cost=64839.780..65406.150 rows=200 width=6730) (actual time=2581.575..5233.738
counts rows=124942 loops=1)
Hash Left Join (cost=64839.780..65026.220 rows=200 width=4830) (actual time=2581.542..4144.099
rows=124942 loops=1)
Hash Cond: (yankee_tango.golf_kilo = uniform_kilo.xray_alpha)
Nested Loop Left Join (cost=64838.370..65023.700 rows=200 width=4217) (actual
time=2581.440..4015.898 rows=124942 loops=1)
Nested Loop (cost=64838.370..64965.360 rows=200 width=4126) (actual time=2581.419..3513.905
rows=124942 loops=1)
Hash Join (cost=64838.370..64863.740 rows=200 width=4043) (actual time=2581.396..3009.644
rows=124942 loops=1)
Hash Cond: (india_uniform.hotel = xray_lima.tango_kilo)
Seq Scan on golf_victor india_uniform (cost=0.000..19.380 rows=798 width=1775) (actual
time=0.012..0.261 rows=798 loops=1)
Hash (cost=64835.870..64835.870 rows=200 width=2268) (actual time=2581.344..2581.344
rows=124942 loops=1)
1

© 2011 EnterpriseDB. All rights reserved. 74

 Some good parameters to know about!
 default_statistics_target
• If row counts are different consider raising it. Default is 100, Max is 100!
 random_page_cost
• Can be adjusted to help use indexes more readily!
 work_mem
• To be considered for any sorting or ORDER BY Queries!
 join_collapse_limit
• For plans with many joins!

Have improved a number of Hibernate generated queires via these parameters.

© 2011 EnterpriseDB. All rights reserved. 75

Database Advanced Features!
• Some of the advanced features in Postgres will speed up the
queries
– Windowing Functions, Hierarchical Queries, Spatial,
etc
• HQL does not support it and throws an exception

unexpected token: OVER near line 1, column 121 [SELECT insurance.insur...

Database Advanced Features (cont.)!
• Send native SQL to the database

String SQL_QUERY = "SELECT insurance_name, id, invested_amount, avg(i...

+ "invested_amount - avg(invested_amount) OVER(PARTI...
+ "FROM insurance ";
Query query = session.createSQLQuery(SQL_QUERY)
.addScalar("insurance_name", Hibernate.STRING)
.addScalar("id", Hibernate.LONG)
.addScalar("invested_amount", Hibernate.LONG)
.addScalar("a", Hibernate.DOUBLE)
.addScalar("diff", Hibernate.DOUBLE);

• This adds database specific code inside the application

Data Model!
 Watching out for the N+1 Problem
Summary!
• Fetching strategies
• Caching
• Setup connection pooling
• Reduce the overhead of updates
• Partition large tables
• Optimize the data model
• Use advanced database features
• Replication
 A Look at EnterpriseDB Solutions!
Products and Tools
• Advanced database server software
• Deep Oracle compatibility
• Bundled development and management tools

Technical Support and Services

• Expert consulting
•High Availability
•Replication
•Performance Tuning
•Disaster Recovery
• Around the clock support
•Remote management and monitoring

Professional Training
• Learn PostgreSQL from the experts
• Web and on-site training
• Training for developers and DBAs
Postgres Plus Standard Server!

Advanced Security
• Built-in SQL firewall
• Stored code obfuscation

Advanced Data Management

• Advanced replication
• Oracle-Postgres Replication
• SQL Server-Postgres Replication

Advanced Manageability
• Automatic software updates
• Effortless patch management

Production Support
• 24x7, around-the-clock support
• Help from the Postgres experts
 Postgres Plus Advanced Server!
Advanced Performance Oracle Compatibility Advanced Scalability
• Infinite Cache/memory caching • PL/SQL compatible • Infinite Cache/memory caching
• High-speed, parallel loader • Data dictionary views • High-speed, parallel loader
• Bulk collect/bind • Pro*C support • Bulk collect/bind
• Advanced performance monitoring • Migration tools

Advanced Security Advanced Data Management Advanced Manageability Production Support

• Built-in SQL firewall • Advanced replication • Automatic software updates • 24x7, around-the-clock support
• Data auditing • Oracle-Postgres Replication • Effortless patch management • Help from the Postgres experts
• Stored code obfuscation • SQL Server-Postgres Replication
• Database links
 Postgres Plus Certifications

EnterpriseDB, Postgres Plus and Dynatune are trademarks of

EnterpriseDB Corporation. Other names may be trademarks of their 83
respective owners. © 2010. All rights reserved.
Training/Certification Roadmap

Trainings Certifications

Introduction to PostgreSQL Postgres Plus Associate

Administration Certification

Advanced PostgreSQL Postgres Plus Professional

Administration Certification

Postgres Plus Master

Postgres Plus Advanced Server Certification
 Pre-requisites

I. Postgres Plus Associate Certification

Pre-requisites: No pre-requisites
Pricing:
• It can be taken individually at a price of $100.
• It is also offered complimentary along with the Introduction to PostgreSQL
Administration class.
• Retakes cost $100 per attempt.

II. Postgres Plus Professional Certification

Pre-requisites:
• Clearing the Postgres Plus Associate Certification &
• Completion of the Advanced PostgreSQL Administration class.
Pricing:
• It is offered complimentary along with the Advanced PostgreSQL Administration
class.
• Retakes cost $100.00 per attempt.
Pre-requisites (contd.)

III. Postgres Plus Master Certification

Pre-requisites:
• Clearing the Postgres Plus Associate Certification,
• Clearing the Postgres Plus Professional Certification &
• Completion of the Postgres Plus Advanced Server class.
Pricing:
This is currently under review and not offered at this time. Please
check back in the near future.
 EnterpriseDB Training Coordinator

• For any further queries related to PostgreSQL Trainings/

Certifications, please write to:
trainingcoordinator@enterprisedb.com
Thank you for your time!