Re: I: arrays and block size

Поиск
Список
Период
Сортировка
От Sam Mason
Тема Re: I: arrays and block size
Дата
Msg-id 20090202204118.GJ3008@frubble.xen.chris-lamb.co.uk
обсуждение исходный текст
Ответ на I: arrays and block size  (Scara Maccai <m_lists@yahoo.it>)
Список pgsql-general
On Mon, Feb 02, 2009 at 09:48:37AM -0800, Scara Maccai wrote:
> > I need to store a lot of int8 columns (2000-2500) in a table.
> > I was thinking about using int8[]

An array of ints sounds like the way to go here as you wouldn't be able
to have that many columns.  TOAST is one non-obvious implementation
artifact that may cause extra work to be done and may cause confusion.
Tuples in PG can be a maximum of 8KB and hence 2000 INT8 values would
only just fit and 2500 values wouldn't.  TOAST docs are available here:

  http://www.postgresql.org/docs/current/static/storage-toast.html

> , and I would like to know:
> > 1) is there a max size for arrays? I guess I could have 1 GB "worth"
> > of values,

Not that I'm aware of; as a quick test:

  CREATE TABLE arrtest ( v INT8[] );
  INSERT INTO arrtest SELECT ARRAY(SELECT generate_series(1,10000000));

worked (very slowly) for me, but 100000000 failed with an out of memory
error (32bit build).

> > but I would like a confirmation
> > 2) there won't be any updates, only inserts and selects; since I
> > will be using ZFS would there be any benefit in changing the block
> > size (for example to 32K) in postgresql?

No idea, maybe someone else?

> > Would it be better to use
> > ZFS compression instead of postgresql's (TOAST)? That is: should I
> > EXTERNAL for that array column and set compression=on for ZFS? I
> > tried ZFS compression=on a 200 columns table and it worked faster
> > than with compression=off....

Oh, I guess you know about TOAST.  Otherwise I can't help much, sorry!


--
  Sam  http://samason.me.uk/

В списке pgsql-general по дате отправления:

Предыдущее
От: Oleg Bartunov
Дата:
Сообщение: Re: chinese parser for text search !
Следующее
От: wstrzalka
Дата:
Сообщение: Re: Pet Peeves?