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