The other option is large objects. The large object features in PostgreSQL work well
enough, but they are also a little bit clunky. Files are kept in the pg_largeobject system
catalog in small pages.??? A pointer is kept in the referring table to the OID (object ID) of
the file. Up to 2 GB of data may be stored in a large object. This method is fast, and has
good APIs, but there are drawbacks. There is no per-table or per-object access control;
* Short for ???byte array.???
?? Assuming fairly uniform binary data, the BYTEA quoting rules lead to an average expansion ratio of 1:2.9.
??? The size is defined in LOBLKSIZE. It defaults to 2 KB.
Large/Binary Objects | 103
the pg_largeobject catalog is global to the database, and accessible by anyone with
permission to connect to the database. The large object mechanism is also slightly deprecated
in favor of in-table storage, as the TOAST storage technique allows values of
up to 1 GB in length to be stored directly as attributes within the table.
My recommendation is to use filesystem storage for all binary objects if you use
PostgreSQL. Although the database might be the more proper place for this type of
data, it just does not work well enough yet. If you have to use the database, large
objects actually perform pretty well. Avoid BYTEA at all costs.
MySQL
MySQL does a fairly good job with binary data. LOB-type columns (including the
TEXT types) can store up to 4 GB of data, using the LONGBLOB type.
Pages:
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162