On 12/16/15 12:15 AM, Jeff Janes wrote:
But also, while loading 1.5 million records into a table with 250 million records is horribly, rebuilding all the indexes on a 251.5 million record table from scratch is even more horrible. I don't know if suspending maintenance (either globally or just for one session) and then doing a bulk fix-up would be less horrible, but would be willing to give it a test run.
I would think that's something completely different though, no? If you're doing that wouldn't you want other inserting/updating backends to still maintain the index, and only do something special in the backend that's doing the bulk load? Otherwise the bulk load would have to wait for all running backends to finish to ensure that no one was using the index. That's ugly enough for CIC; I can't fathom it working in any normal batch processing.
(Doing a single bulk insert to the index at the end of an INSERT should be safe though because none of those tuples are visible yet, though I'd have to make sure your backend didn't try to use the index for anything while the command was running... like as part of a trigger.)
-- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers