Re: Storing images in PostgreSQL databases (again)

marco.bizzarri@gmail.com ("Marco Bizzarri") writes:
> Hi.
>
> I can provide some "insight" on the difference between the two interfaces.
>
> AFAIK, the difference is in size of the file you can store, and in the
> interface you have when you want to access.
>
> The size is not important (I think), since you are far below the limit.
>
> For the interface, the bytea gives you a "query" based interfaces,
> while largeobject are able to provide a file based interface.
>
> With Large Object, you can avoid reading the whole object with one
> read, or you can even move inside the Large Object, which can be
> useful if you have large files stored.
>
> I think there are differences also in how the space is reclaimed, but
> my PostgreSQL - Fu stops here.

That seems a reasonable explanation...

There is another thing that is worth observing for the "store data as
an ordinary column" idea...

Very Large Columns are stored in what is called a TOAST table.

Consider the following table:

tbig@[local]:5832=# \d foo
                          Table "public.foo"
  Column  |  Type   |                    Modifiers
----------+---------+--------------------------------------------------
 id       | integer | not null default nextval('foo_id_seq'::regclass)
 name     | text    |
 contents | text    |
Indexes:
    "foo_pkey" PRIMARY KEY, btree (id)

tbig@[local]:5832=# vacuum verbose foo;
INFO:  vacuuming "public.foo"
INFO:  index "foo_pkey" now contains 3 row versions in 2 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "foo": found 0 removable, 3 nonremovable row versions in 1 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 12 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  vacuuming "pg_toast.pg_toast_113203"
INFO:  index "pg_toast_113203_index" now contains 54 row versions in 2 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "pg_toast_113203": found 0 removable, 54 nonremovable row versions in 14 pages
DETAIL:  0 dead row versions cannot be removed yet.
There were 5 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM

tbig@[local]:5832=# select id, name, length(contents) from foo;
 id | name  | length
----+-------+---------
  1 | file1 | 1860342
  2 | file2 | 1860342
  3 | file3 | 1860342
(3 rows)

The "contents" columns contain 1.8MB of data.

Note that if you run a query that doesn't access the "contents"
columns, they never get drawn in.  What the table 'physically' looks
like is rather like:

 id | name  | toast pointer
----+-------+--------------
  1 | file1 | 1341234
  2 | file2 | 3241341
  3 | file3 | 3421892

[where those pointers point into the "toast" table].

You can get decent efficiency out of that...
--
(reverse (concatenate 'string "ofni.sesabatadxunil" "@" "enworbbc"))
http://cbbrowne.com/info/linuxdistributions.html
"...[Linux's] capacity to talk via any medium except smoke signals."
-- Dr. Greg Wettstein, Roger Maris Cancer Center