I am running several servers with Postgres 8.3 that are used to house location data from thousands of devices. Location updates are quite frequent, so our tables rapidly become fairly large (often about 2GB per day of growth). We've been using Postgres for close to 10 years now and have been very happy until recent performance issues with larger data sets. Our primary location table is clustered by "reporttime" (bigint). Many of the queries we need to perform are of the nature : "get me all positions from a given device for yesterday". Similar queries are "get me the most recent 10 positions from a given device". These are pretty simple and straightforward queries that run surprisingly quickly either on small tables or tables that have been clustered by reporttime. If the tables are large and haven't been actively clustered for a while, the simplest looking queries will take close to a minute to execute. Unfortunately, the clustering operation now takes far too long to run in any reasonable maintenance window. On smaller datasets clustering will take from 30 minutes to an hour and on our large datasets several hours (gave up at our 4 hour maintenance window limit). Obviously I would love to have Postgres support "online" clustering, however I need to figure a way around this problem now or start planning the port to another database server. We have tried the "SELECT INTO ... ORDER BY REPORTTIME" trick instead or running cluster, but since the tables are quite large that is still taking too long (although quicker than clustering). I have spent more time than I would like looking into clustering options and other load balancing techniques. I was thinking that I'd like to take one server set down for clustering, while failing over to the secondary database set. The problem is I don't see an efficient or easy way to then synchronize the sizable amount of updates that will have occurred since the clustering started back to the now clustered primary database without significantly affecting write performance. If it is difficult to solve, porting to Oracle or *gasp* SQL Server will be an easier solution. I have spent a lot of time Googling, and so far no obvious solutions jump to mind besides either hoping (or helping) online clustering become a reality on Postgres, or to migrate to a different DB engine. I'd really appreciate any thoughts or suggestions. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
