| 1 | [[PageOutline]] |
| 2 | |
| 3 | = Performance = |
| 4 | |
| 5 | = PostgreSQL tips = |
| 6 | |
| 7 | == Total size of BLOBs == |
| 8 | BLOBs are stored in the `pg_largeobject` in postgres. Each BLOB is divided into rows (pages) of 2048 bytes typically. More info [http://www.postgresql.org/docs/8.4/static/catalog-pg-largeobject.html here] |
| 9 | |
| 10 | Query below computes the space that all BLOBs take. |
| 11 | {{{ |
| 12 | SELECT pg_size_pretty(count(loid) * 2048) FROM pg_largeobject; |
| 13 | }}} |
| 14 | |
| 15 | == Size of individual BLOBs == |
| 16 | {{{ |
| 17 | SELECT loid, |
| 18 | pg_size_pretty(count(*) * 2048) |
| 19 | FROM pg_catalog.pg_largeobject |
| 20 | GROUP BY loid |
| 21 | ORDER BY count(*) DESC; |
| 22 | }}} |
| 23 | |
| 24 | == Size of tables == |
| 25 | {{{ |
| 26 | SELECT tablename, |
| 27 | pg_size_pretty(size) AS size_pretty, |
| 28 | pg_size_pretty(total_size) AS total_size_pretty |
| 29 | FROM (SELECT *, pg_relation_size(schemaname||'.'||tablename) AS size, |
| 30 | pg_total_relation_size(schemaname||'.'||tablename) AS total_size |
| 31 | FROM pg_tables) AS TABLES |
| 32 | WHERE TABLES.schemaname = 'public' |
| 33 | ORDER BY total_size DESC; |
| 34 | }}} |