Re: What is the right way to deal with a table with rows that are not in a random order? - Mailing list pgsql-general

ORM should'nt be a factor as you have many configureable properties available in hibernate.cfg.xml or hibernate.properties

hibernate.connection.driver_class	jdbc driver class
hibernate.connection.url	jdbc URL
hibernate.connection.username	database user
hibernate.connection.password	database user password
hibernate.connection.pool_size	maximum number of pooled connections

Hibernate's own connection pooling algorithm is however quite rudimentary. It is intended to help you get started and is not intended for use in a production system or even for performance testing. You should use a third party pool for best performance and stability. Just replace the hibernate.connection.pool_size property with connection pool specific settings. This will turn off Hibernate's internal pool. For example, you might like to use C3P0.
C3P0 is an open source JDBC connection pool distributed along with Hibernate in the lib directory. Hibernate will use its C3P0ConnectionProvider for connection pooling if you set hibernate.c3p0.* properties. If you'd like to use Proxool refer to the packaged hibernate.properties and the Hibernate web site for more information.
Here is an example hibernate.properties file for C3P0:

hibernate.connection.driver_class = org.postgresql.Driver
hibernate.connection.url = jdbc:postgresql://localhost/mydatabase
hibernate.connection.username = myuser
hibernate.connection.password = secret
hibernate.c3p0.min_size=5
hibernate.c3p0.max_size=20
hibernate.c3p0.timeout=1800
hibernate.c3p0.max_statements=50
hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect

https://www.hibernate.org/214.html

Martin Gainty
______________________________________________
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

> From: darkwater42@gmail.com
> Date: Thu, 28 May 2009 15:03:32 -0400
> Subject: Re: [GENERAL] What is the right way to deal with a table with rows that are not in a random order?
> To: pgsql-general@postgresql.org
> CC: simon@2ndquadrant.com
>
> On Thu, May 28, 2009 at 4:14 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
>
> > Partition the table, then scan the correct partition.
>
> If I do that, will Postgres figure out the "right thing" to do if the
> parent table is queried instead?  Also, what are the performance
> implications then for doing queries that span all the partitions,
> which will be the norm for our application?
>
> The application in question is a kind of data warehousing thing (of
> astronomical stars), and there's an ORM in the middle, so it's not
> easy for us to hand-tune how individual queries are specified.
> Instead, we have to structure the database and the indexes so that
> things generally perform well, without having to tweak specific
> queries.
>
> Users can specify fairly arbitrary search criteria.  All of the
> queries should perform well.  By "well", I mean within 10 seconds or
> so.  Scanning all of the 150 million rows takes much longer than 10
> seconds, unfortunately.
>
> Any one of these "solutions" will cause Postgres to do an index scan
> in the problematic case where Postgres is deciding to a sequential
> scan.  The index scan performs snappily enough:
>
>    - Using "order by" on the query.
>
>    - Changing the search value for the column to a value that occurs
> less frequently.
>
>    - Fetching the value to search for via a sub-query so that Postgres
> can't determine a priori that the
>      value being searched value occurs so commonly.
>
> Unfortunately, as I mentioned, due to the ORM, none of these solutions
> really work for us in practice, as opposed to at a psql prompt.
>
> |>ouglas
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

---

Hotmail® has ever-growing storage! Don’t worry about storage limits. Check it out.