Re: Index over only uncommon values in table

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 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...

Hope this helps.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Index-over-only-uncommon-values-in-table-tp5759735p5759748.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.