База данных

=> CREATE DATABASE data_databases;
CREATE DATABASE
=> \c data_databases
You are now connected to database "data_databases" as user "postgres".

Размер БД

=> SELECT pg_size_pretty(pg_database_size('data_databases'));
 pg_size_pretty 
----------------
 7038 kB
(1 row)

=> SELECT pg_database_size('data_databases') AS oldsize \gset

Схемы

=> CREATE SCHEMA app;
CREATE SCHEMA
=> CREATE SCHEMA postgres;
CREATE SCHEMA

Таблицы

Таблицы для схемы postgres:

=> CREATE TABLE a(s text);
CREATE TABLE
=> INSERT INTO a VALUES ('postgres');
INSERT 0 1
=> CREATE TABLE b(s text);
CREATE TABLE
=> INSERT INTO b VALUES ('postgres');
INSERT 0 1

Таблицы для схемы app:

=> CREATE TABLE app.a(s text);
CREATE TABLE
=> INSERT INTO app.a VALUES ('app');
INSERT 0 1
=> CREATE TABLE app.c(s text);
CREATE TABLE
=> INSERT INTO app.c VALUES ('app');
INSERT 0 1

Изменение размера БД

=> SELECT pg_size_pretty(pg_database_size('data_databases'));
 pg_size_pretty 
----------------
 7110 kB
(1 row)

=> SELECT pg_database_size('data_databases') AS newsize \gset

Размер изменился на:

=> SELECT pg_size_pretty(:newsize::bigint - :oldsize::bigint);
 pg_size_pretty 
----------------
 72 kB
(1 row)

Путь поиска

С текущими настройками пути поиска видны таблицы только схемы postgres:

=> SELECT * FROM a;
    s     
----------
 postgres
(1 row)

=> SELECT * FROM b;
    s     
----------
 postgres
(1 row)

=> SELECT * FROM c;
ERROR:  relation "c" does not exist
LINE 1: SELECT * FROM c;
                      ^

Изменим путь поиска.

=> ALTER DATABASE data_databases SET search_path = "$user",app,public;
ALTER DATABASE
=> \c
You are now connected to database "data_databases" as user "postgres".
=> SHOW search_path;
     search_path      
----------------------
 "$user", app, public
(1 row)

Теперь видны таблицы из обоих схем, но приоритет остается за postgres:

=> SELECT * FROM a;
    s     
----------
 postgres
(1 row)

=> SELECT * FROM b;
    s     
----------
 postgres
(1 row)

=> SELECT * FROM c;
  s  
-----
 app
(1 row)