> Just out of curiosity, how do you replicate that amount of data? When I started working here, we used Slony-I to replicate our aggregate fact tables. A little over a year ago our data volume had grown to the point that the Slony was regularly unable to keep up with the data volume and around this time Slony hit us with some crazy rollback based data loss bug. We elected to move our aggregate fact tables off of slony, but left metadata tables on Slony.
So I wrote a custom in house replication engine into our aggregation process. Replication is accomplished in parallel via piped copy statements, and so far it's been fast enough to keep up with the data volume. Without getting into too much detail, an import process might look like this: - Obtain and transform data into a usable state - Import Data, and earmark it to be reaggregated - Reaggregate (summarize) the changed data - Copy the new data to all databases in the replication set - Integrate new data into the aggregate fact tables > I'd be very interested in a developers view of running and maintaining a > database this size. > Mostly what choices is made during development that might have been > different on a smaller database. > I'm also curious about the maintenance needed to keep a database this > size healthy over time. Ok, so all of this comes from what might be termed as an "ETL Developer" point of view. I pretty much only work on the data warehouse's import/summarization process and look into performance issues. Design: - Natural keys are pretty well strictly forbidden. Don't make a VARCHAR your primary key, please. ;-) - The Data determines partition criteria and the application layer is partition aware. - It's important to have aggregate tables to support common queries. Joins are ok, but repeatedly aggregating thousands of rows together on the fly really takes too much time. - Aggregation processes have to be designed with care. - Parallel processing is important, especially if you ever have to reprocess large amounts of data (for example due to incorrect initial data) Maintenance: - Autovacuum might be more trouble than it's worth. We frequently have mysteriously hung queries that are eventually traced back to being blocked by Autovacuum. The application layer knows exactly what and when is changing... it could *probably* take over this duty. - Pg upgrades are a major PITA and require absurd amounts of downtime for the data processing part of the warehouse. - Queries that have been working for long periods of time and suddenly stop working or hang are usually broken by statistics issues. - Partitioning is important because it allows the aggregate tables to be clustered, backed up, and archived individually If anyone wants to chat with me or direct me to resources about running Postgres on distributed file systems (or other grid computing solutions) please let me know. Yes, I'm aware of the proprietary solutions, but they've been ruled out on account of performance problems with aggregates and absurd licensing costs. Also, any grid computing solution that I write can easily be applied across the company and thus save us (quite literally) millions per year. Again, if you'd like more information about any particular topic, just ask. :) -Mark -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
