Get Size of Large Object in Postgresql Query

Get size of large object in PostgreSQL query?

Not that I've used large objects, but looking at the docs: http://www.postgresql.org/docs/current/interactive/lo-interfaces.html#LO-TELL

I think you have to use the same technique as some file system APIs require: seek to the end, then tell the position. PostgreSQL has SQL functions that appear to wrap the internal C functions. I couldn't find much documentation, but this worked:

CREATE OR REPLACE FUNCTION get_lo_size(oid) RETURNS bigint
VOLATILE STRICT
LANGUAGE 'plpgsql'
AS $$
DECLARE
    fd integer;
    sz bigint;
BEGIN
    -- Open the LO; N.B. it needs to be in a transaction otherwise it will close immediately.
    -- Luckily a function invocation makes its own transaction if necessary.
    -- The mode x'40000'::int corresponds to the PostgreSQL LO mode INV_READ = 0x40000.
    fd := lo_open($1, x'40000'::int);
    -- Seek to the end.  2 = SEEK_END.
    PERFORM lo_lseek(fd, 0, 2);
    -- Fetch the current file position; since we're at the end, this is the size.
    sz := lo_tell(fd);
    -- Remember to close it, since the function may be called as part of a larger transaction.
    PERFORM lo_close(fd);
    -- Return the size.
    RETURN sz;
END;
$$; 

Testing it:

-- Make a new LO, returns an OID e.g. 1234567
SELECT lo_create(0);

-- Populate it with data somehow
...

-- Get the length.
SELECT get_lo_size(1234567);

It seems the LO functionality is designed to be used mostly through the client or through low-level server programming, but at least they've provided some SQL visible functions for it, which makes the above possible. I did a query for SELECT relname FROM pg_proc where relname LIKE 'lo%' to get myself started. Vague memories of C programming and a bit of research for the mode x'40000'::int and SEEK_END = 2 value were needed for the rest!

Obtaining the size of lobject in PostgreSQL

This function has been efficient enough for me, you may want to try it with you data:

CREATE OR REPLACE FUNCTION lo_size(oid) RETURNS integer 
AS $$ 
declare 
 fd integer; 
 sz integer; 
begin 
 fd = lo_open($1, 262144);
 if (fd<0) then
   raise exception 'Failed to open large object %', $1;
 end if;
 sz=lo_lseek(fd,0,2);
 if (lo_close(fd)!=0) then
   raise exception 'Failed to close large object %', $1;
 end if;
 return sz;
end; 
$$ LANGUAGE 'plpgsql'; 

Another option is select sum(length(data)) from pg_largeobject where loid=the_oid but it requires read access to pg_largeobject which I think has been suppressed in pg 9.0+ for non-superusers

Get size for Bytea column in postgreSQL

You can use the length() function to get the length in bytes:

select length(bytea_column) as filesize
from file_info;

If you want to format the output you can use pg_size_pretty()

select pg_size_pretty(length(bytea_column)) as filesize
from file_info;

That will adapt the "kb", "mb" output based on the size. If you always want MB, you can use:

select concat(length(bytea_column) / 1048576.0, ' MB') as filesize
from file_info;

How to get size of PostgreSQL jsonb field?

For the number of bytes used to store:

select id, pg_column_size(datab) from data.items;

For the number of elements on the jsonb object:

select id, jsonb_array_length(datab) from data.items;

postgresql list and order tables by size

select table_name, pg_relation_size(quote_ident(table_name))
from information_schema.tables
where table_schema = 'public'
order by 2

This shows you the size of all tables in the schema public if you have multiple schemas, you might want to use:

select table_schema, table_name, pg_relation_size('"'||table_schema||'"."'||table_name||'"')
from information_schema.tables
order by 3

SQLFiddle example: http://sqlfiddle.com/#!15/13157/3

List of all object size functions in the manual.

---

Related Topics