Search Postgresql Archives

Array, bytea and large objects

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I am trying to assess the db issues surrounding several constructs allowed in PG 8.3, including ARRAY, BYTEA and large objects (LO).

We store a lot of data as encrypted XML structures (name-value pairs mostly) that can be updated many times during its lifetime (most updates occur over several days and then the data tends to change no more), as well as storing images and uploaded files (these rarely change and are only inserted/deleted). We currently use LO for all of these. We mostly use the JDBC library for access to PG.

First, LOs seem to allow an OID column to be added to any number of tables, but is it true that the actual large object data is stored in a single table (pg_largeobject?). If so, wouldn't this become a bottleneck if LOs were used frequently? Even vacuuming and vacuumlo must create a lot of pressure on that one table if LOs are used extensively. And can you backup a table with an OID column and get only those LOs referenced in the dump?

Does the JDBC library support LO streaming? Can I receive data, compress, encrypt and stream into the database as well as do the opposite when reading it back?

If I have an "unlimited" number of name-value pairs that I'd like to get easy access to for flexible reports, could I store these in two arrays (one for name, the other for value) in a table so that if I had 10 name-value pairs or 200 name-value pairs, I could store these into a single row using arrays so I could retrieve all name-value pairs in a single SELECT from the db? How are these arrays stored -- does it use an underlying type like LO or BYTEA?

How big can an LO get?  Is it 2GB?
How many LO fields can I have in a database? It seems that the LO may even be implemented as an OID with one or more BYTEA storage structure in the pg_largeobject table (loid,pageno,data). Is that true?

How big is a "page"? Maybe an LO is more efficient than a BYTEA if it's bigger than one page?

How big can a BYTEA get?  Is it 1GB?
At what size does it make more sense to store in LO instead of a BYTEA (because of all the escaping and such)? How many BYTEA fields can I have in a database? Are the BYTEA fields stored in the same table as the rest of the data? I believe this is yes, so a backup of that table will include the binary data, too, correct?

How big can an ARRAY get?  Is it 1GB?
How many ARRAY fields can I have in a table or database? Are there limits?
Are the ARRAY fields stored in the same table as the rest of the data?

Sorry for all the questions, but I'm trying to research it but the info is not always clear (and perhaps some of the stuff I find is not even true).

I am wondering if when my encrypted XML data is small, should I choose to store it in a table using BYTEA so that each "record" in my application (which uses the encrypted XML name-value storage) is not forced to be in a single pg_largeobject table, and use LO when my data reaches a threshold size? Thoughts?

Thanks,
David

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux