Search Postgresql Archives

Re: Index over only uncommon values in table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Jun 18, 2013, at 1:49 PM, David Johnston <polobo@xxxxxxxxx> wrote:

> Steven Schlansker-3 wrote
>> At some point, the code changes, and CURRENT_VERSION gets incremented. 
>> Rows then slowly (over a period of days / weeks) get "upgraded" to the new
>> current version, in batches of thousands.
>> 
>> This is what I mean by a very slowly changing mostly-constant value.
> 
> This seems insane without knowing the details.  This seems like it would be
> more of a cache invalidation problem.  What percentage of your rows are
> being updated multiple times without ever being queried for other reasons?

I am open to suggestions of how to do it better.  The problem I face is that
doing any sort of updates in one big go -- whether it be by ALTER TABLE
statements or large UPDATE queries -- is all but unworkable.  It takes days
or weeks depending on what the update is, so any locking causes the entire
system to grind to a halt.  And there is nothing more depressing than losing
5 days of work on a huge UPDATE because something hiccuped.

Hence, allowing "outdated" versions in the table, which then over time get upgraded
in reasonably-sized batches.

> 
> I was going to say that table partitioning (INHERITS) seems like a
> possibility; then I thought maybe not; now I'm back to suggesting you
> consider it.
> 
> Every version of the extractor would get its own table.  To "upgrade" you
> remove the record from the older table and add it to the newer one.  Maybe
> even consider calling the these "version_upgraded" to distinguish them from
> records originally insert using the newest version.  Or have "original
> version" as the partition key and a second "current version" field that
> varies.  Not sure how the planner would be able to use constraint exclusion
> to limiting the scanning though…
> 

Interesting idea.  I have been trying to avoid making code changes require
schema changes as well -- it is very nice to not have to make schema changes for every
code deployment.  The code may get changed multiple times in the same day, if I am
busy hacking on it.  Having to muck around with table inheritance and changing partition
definitions on code deployments seems unpleasant.  Perhaps I am overestimating the work
involved, but I am very much trying to keep the deployment process as brain-dead-simple 
as possible.

Thanks for the input.
Steven



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux