Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
DROP TABLE IF EXISTS public.with_enum;
DROP TABLE IF EXISTS public.with_text;
DROP TYPE IF EXISTS my_enum;
CREATE TYPE public.my_enum AS ENUM ('axyzxyz', 'bxyzxyz', 'cxyzxyz', 'dxyzxyz');
CREATE TABLE public.with_enum (
id serial NOT NULL PRIMARY KEY,
blah integer,
thecolumn my_enum
);
CREATE TABLE public.with_text
(
id serial NOT NULL PRIMARY KEY,
blah integer,
thecolumn text
);
INSERT INTO public.with_enum(blah, thecolumn)
SELECT random() * 10000, (CASE (random() * 3)::integer WHEN 0 THEN 'axyzxyz' WHEN 1 THEN 'bxyzxyz' WHEN 2 THEN 'cxyzxyz' ELSE 'dxyzxyz' END)::my_enum FROM generate_series(1,5000000);
INSERT INTO public.with_text(blah, thecolumn)
SELECT random() * 10000, CASE (random() * 3)::integer WHEN 0 THEN 'axyzxyz' WHEN 1 THEN 'bxyzxyz' WHEN 2 THEN 'cxyzxyz' ELSE 'dxyzxyz' END FROM generate_series(1,5000000);
SELECT table_schema,
table_name,
pg_size_pretty(total_bytes) AS total,
pg_size_pretty(index_bytes) AS index,
pg_size_pretty(toast_bytes) AS toast,
pg_size_pretty(total_bytes-index_bytes-COALESCE(toast_bytes,0)) AS table
FROM (
SELECT nspname AS table_schema,
relname AS table_name,
pg_total_relation_size(c.oid) AS total_bytes,
pg_indexes_size(c.oid) AS index_bytes,
pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r' AND nspname = 'public'
) a;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment