Hello, Thanks for sharing your experiences with the problem. W dniu 2013-10-09 00:47, "Gabriel E. Sánchez Martínez" pisze:
Partioning seems to be a good idea if a single table would be too big to fit in your server's file cache, and also for management, since you can drop partitions of old data and create new ones without having to reindex and lock. Does your data partition nicely by date, for example? If most of the inserts are new data and old data is read-mostly, then partitioning may make sense because you would not have to reindex old partitions. In fact, you could very well not have an index on the hot, write-mostly partition of, say, the current month, until the write activity on that table diminishes, which would make inserts faster. If, on the other hand, your writes are scattered across many partitions, a single large table with an index may be a better solution.
Changes are scattered, so single large table already is not a good solution. I like the idea of hot, write-mostly partition, because I might as well use only two partitions and merge changes from small table to the large one once the processing is done. Rows are grouped by some key and when I start processing some group I could move all rows from large table in a batch (INSERT INTO .. SELECT .. WHERE group=x; DELETE FROM WHERE group=x). This way the read only part of the system will continue work without problems and processing should be much faster.
Although this will not solve the problem of neverending vacuums on large table, postgres could easily become the bottleneck. I am using 8.3 for this, but I will make an upgrade at some point, however I don't think it will change the design.
Thanks -- Regards, Grzegorz -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
