haukinger@gmx.de wrote:
> I'm working on a database that needs to handle insertion of
> about 100000 large objects (50..60GB) a day. It should be
> able to run 200 days, so it will become about 10TB
> eventually, mostly of 200..500KB large objects.
> How does access to large objects work ? I give the oid and
> get the large object... what is done internally ? How (if at
> all) are the oid's indexed ?
I cannot tell you if PostgreSQL will work well with that amount of data;
I leave this to people more experienced with large databases.
Do you have a backup strategy?
Large objects are stored in a system table pg_largeobject in chunks of
2KB.
The oid is a handle for the large object; when you store the oid in
a user table, you basically store a reference or a pointer to that
large object. If you delete the row in that user table, the large object
will still be there, and if you delete the large object, the oid in the
user table will be 'orphaned'. So you need to make sure that you keep
large objects and references to them in sync.
For your quesion - how are large objects accessed - see the
documentation
of the C functions in
http://www.postgresql.org/docs/current/static/lo-interfaces.html
You can create a new large object, which will return the oid of the new
large object. You have to open a large object by oid before you can read
or modify it - this will give you a 'handle' with which you can access
the contents of the large object much like a file in C: you can position
a 'location pointer' and read or write a number of bytes.
There are functions to import and export a large object from and to the
file system.
There are similar functions for most client interfaces.
Yours,
Laurenz Albe