We know that changing the value of an indexed column is not immediate. What then, to sort without him? There is a record label which has a column with the date of their last change, and it changes, often changes, very ... and sorting is always done only by it.

Any ideas how to implement the required functionality, with the ability to use indexes?

Even as an option, pg_prewarm will google up - it will allow to "warm up" the base from the start by loading an evil column into the RAM.

    1 answer 1

    We know that changing the value of an indexed column is slower.

    No, we do not know. Why "slow"? Normally updated. The ratio of reading and writing matters: maybe it is really better for your load profile to be sorted on demand if you read much less often than you write. He even began to google with surprise. I did not find anything relevant about the "slow" update of indices in pg, and had never heard of it before. Btree can not only search well, but also add new index entries.

    For intensive recording, it will not be superfluous to configure the auto-vacuum more aggressive, so that dead lines are cleared more actively.

    pg_prewarm is only at the time of the cold start of the DBMS. And for a normally working server, all the hot data will be in shared_buffers. Separately, the column can not be heated. In shared_buffers there are data pages, default by 4kb (can only be changed during compilation). Each page contains some records of the entire table (except for toast, which are stored in separate pages) or index records.

    Maybe you have several hundred thousand transactions per second, and updating the indexes really becomes a problem, but I don’t know about it because I haven’t been able to work with such a load. But why then do you mention pg_prewarm and how are you going to load a separate column into memory? And it’s strange that I haven’t heard anything like that at conferences. Tell me more about "meededlenno", very interesting. Links to pgsql-hackers are very welcome.

    • I saw the alarm here: dba.stackexchange.com/questions/50520/… - Alster
    • I ran a test for update 100,000 rows in two tables, in one of which I put an index on a variable column (int). It seems no difference. And if there is, then apparently not great, and this is good =) - Alster