Re: PostgreSQL+Hibernate Performance

On Wed, 2008-08-20 at 17:55 +0530, Kranti K K Parisa™ wrote:
> Hi,
>
> Can anyone suggest the performance tips for PostgreSQL using
> Hibernate.
>
> One of the queries:
>
> - PostgreSQL has INDEX concept and Hibernate also has Column INDEXes.
> Which is better among them? or creating either of them is enough? or
> need to create both of them?
>
> and any more performace aspects ?

Hibernate is a library for accessing a database such as PostgreSQL.  It
does not offer any add-on capabilities to the storage layer itself.  So
when you tell Hibernate that a column should be indexed, all that it
does create the associated PostgreSQL index when you ask Hibernate to
build the DB tables for you.  This is part of Hibernate's effort to
protect you from the implementation details of the underlying database,
in order to make supporting multiple databases with the same application
code easier.

So there is no performance difference between a PG index and a Hibernate
column index, because they are the same thing.

The most useful Hibernate performance-tuning advice isn't PG-specific at
all, there are just things that you need to keep in mind when developing
for any database to avoid pathologically bad performance; those tips are
really beyond the scope of this mailing list, Google is your friend
here.

I've been the architect for an enterprise-class application for a few
years now using PostgreSQL and Hibernate together in a
performance-critical context, and honestly I can't think of one time
that I've been bitten by a PG-specific performance issue (a lot of
performance issues with Hibernate that affected all databases though;
you need to know what you're doing to make Hibernate apps that run fast.
If you do run into problems, you can figure out the actual SQL that
Hibernate is issuing and do the normal PostgreSQL explain analyze on it;
usually caused by a missing index.

-- Mark