skip to Main Content

there’s an aspect of Postgres memory allocation about large data object I don’t understand.

In more details, statistics about memory allocation don’t match with allocation calculated considering involved tables

PostgreSQL version: 12

I have 5 tables using lob fields in my db, implemented as oid and related large objects.

If I execute this query

select pg_database_size ('<db_name>')

dbms tells me that size is 2.8 gb

Db size

If I execute this query

SELECT
    table_schema || '.' || table_name AS table_full_name,
    pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS size
FROM information_schema.tables
ORDER BY
    pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC;

dbms tells me that large objects table is 2.6 gb large

memory statistics

Dry running vaccumlo, I can find which tables are using large objects, they are 5

  • string_value in al_system_conf
  • email_body in als_email_record
  • body in als_mail_history
  • content in als_resource
  • doc_content in pm_document

Considering that large objects are stored in page of 2048 kb, I can estimate memory consumption of each table, that gives me

memory allocation per

If I dry run vacuumlo, it says there are not orphans

vacuumlo result

So, how can I find how the 2.6 gb are allocated?
How can I free them?

2

Answers


  1. Chosen as BEST ANSWER

    Ok guys, problem solved. I tried to execute vacuumdb with -f switch and it worked! Executing without -f remove orphan BUT keeps memory allocated for the table. If you want to release memory you have to speciffy that paramter. Doing so, we shrink db size from 2.8GB to 140 MB


  2. The problem must be with your estimate of the size of the large objects, based on the chunk size. How do you know the number of chunks a single large object has? You are probably just underestimating the size of your large objects, and everything else is in order.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search