Hi all
I have some idea/feature request.
Now, there are several devices available, that can be called "rapid seek
devices" (RSD in future text). I mean SSD disks, some devices like
gigabyte I-RAM and other (semi)profesional "ram disk like" solutions for
example Acard ANS-9010 . Rapid seek because there are not moving parts,
thus non-sequential access (seeks) have no penalty (no moving heads
time). I think it would be cool to have explicitly support for that
kind of devices.
1: Postgresql has its tablespaces, so creating a tablespce on RSD and
put some speed critical indexes/tables there, is optimal from the costs
point (RSD are not cheap, so use it for the speed critical tasks within
single database is reasonable) but it is only a half of the needed.
Query planner act strongly "pro sequentional reads" while it try to
guess best query plan. This bias is fine for classic disk based storage,
but is not necesery in RSD. So, for tables/indexes on RSD, a suboptimal
query plan is likely to be generated (fix me if I am wrong).
So I suggest we should have "random_page_cost" and
"Sequential_page_cost" configurable on per tablespace basis. And query
planner that is aware of it, include situation where tables and
corresponding indexes are on different speed tablespaces.
Imagine this scenario: Default tablespace on sata disk,
random_page_cost=8, sequential_page_cost=3 (from config file),
CREATE TABLESPACE fast /path/to/multiple_15k_rpm_SCSI_hw_RAID
random_page_cost=4 sequential_page_cost=2
CREATE TABLESPACE lightspeed /path/to/SSD_OR_I-RAM random_page_cost=1
sequential_page_cost=1.2
and now scatter your tables / indexes around new tablespaces :-)
2: Many of that RSD devices are not so much reliable (power outage in
ramdisk, weak auxillarity battery in i-ram like devices, block "wear
out" in SSD). While moving only an indexes to this device ( I found
this article showing there IS a big difference having only an indexes on
SSD - http://linux.com/archive/feature/142658.) may be appropriate, and
just "reindex" in worst case, this is not suitable in high availability
enviroment.
So I suggest to have something like this to solve reliability problems
on some RSD:
CREATE TABLESPACE lightspeed /path/to/SSD_OR_i-RAM random_page_cost=1
sequential_page_cost=1.2 TEE name_of_the_slow_tablespace
ie read from fast tablespace, write to both fast and slow, reconstruct
fast from slow if appropriate.
Thanx for your attention.
PS: Execuse my wrong english