Re: [rfc] overhauling pgstat.stat

(2013/09/05 3:59), Alvaro Herrera wrote:
> Tomas Vondra wrote:
>
>> My idea was to keep the per-database stats, but allow some sort of
>> "random" access - updating / deleting the records in place, adding
>> records etc. The simplest way I could think of was adding a simple
>> "index" - a mapping of OID to position in the stat file.
>>
>> I.e. a simple array of (oid, offset) pairs, stored in oid.stat.index or
>> something like that. This would make it quite simple to access existing
>> record
>>
>> 1: get position from the index
>> 2: read sizeof(Entry) from the file
>> 3: if it's update, just overwrite the bytes, for delete set isdeleted
>> flag (needs to be added to all entries)
>>
>> or reading all the records (just read the whole file as today).
>
> Sounds reasonable. However, I think the index should be a real index,
> i.e. have a tree structure that can be walked down, not just a plain
> array. If you have a 400 MB stat file, then you must have about 4
> million tables, and you will not want to scan such a large array every
> time you want to find an entry.

I thought an array structure at first.

But, for now, I think we should have a real index for the
statistics data because we already have several index storages,
and it will allow us to minimize read/write operations.

BTW, what kind of index would be preferred for this purpose?
btree or hash?

If we use btree, do we need "range scan" thing on the statistics
tables? I have no idea so far.

Regards,
--
Satoshi Nagayasu <snaga(at)uptime(dot)jp>
Uptime Technologies, LLC. http://www.uptime.jp