Re: Postgres insert performance and storage requirement compared to Oracle

On 2010-10-27 20:51, Merlin Moncure wrote:

>> Yes, I am quite aware of how the o/s page cache works. All else being
>> equal, I more compact database obviously would be preferred. However
>> 'all else' is not necessarily equal. I can mount my database on bzip
>> volume, that must make it faster, right? wrong. I understand the
>> postgres storage architecture pretty well, and the low hanging fruit
>> having been grabbed further layout compression is only going to come
>> as a result of tradeoffs.
>>
Or configureabillity.. Not directly related to overall space consumption
but I have been working on a patch that would make TOAST* kick in
earlier in the process, giving a "slimmer" main table with visibillity
information
and simple columns and moving larger colums more aggressively to TOAST.

The overall disadvantage of TOAST is the need for an extra disk seek if
you actually need the data. If the application rarely needs the large
columns but often do count/filtering on simple values this will eventually
lead to a better utillization of the OS-page-cache with a very small
overhead
to PG (in terms of code) and 0 overhead in the applications that benefit.

Keeping in mind that as SSD-drives get more common the "the extra disk seek"
drops dramatically, but the drive is by itself probably still 100-1000x
slower than
main memory, so keeping "the right data" in the OS-cache is also a
parameter.

If you deal with data where the individual tuple-size goes up, currently
TOAST
first kicks in at 2KB (compressed size) which leads to a very sparse
main table
in terms of visibillity information and count and selects on simple values
will drag a huge amount of data into the cache-layers thats not needed
there.

Another suggestion could be to make the compression of text columns kick in
earlier .. if thats possible. (I dont claim that its achiveable)

Unless the tuple-header is hugely bloated I have problems creating a
situation in my
head where hammering that one can change anything significantly.

* http://www.mail-archive.com/pgsql-hackers(at)postgresql(dot)org/msg159726.html

--
Jesper