Re: Storing images in PostgreSQL databases (again)

marco(dot)bizzarri(at)gmail(dot)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(at)[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(at)[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(at)[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