How to find the size of a dataset

Learn how to use PostgreSQL to find the exact size of a dataset in your CARTO account.

You can see the size of a dataset in your Datasets Dashboard interface. However that is an estimate of the original dataset’s size that does not include other elements that become part of it in our system, like a TOAST table and indexes.

PostgreSQL offers a way to find the exact size of a dataset in your CARTO account. You can open your dataset from your Datasets Dashboard, set it’s toggle to SQL, replace the default query with this and click Apply:

SELECT pg_size_pretty (pg_total_relation_size('your_dataset_name'))

To see what this “total” size includes, you can use the query below. For your_account_name_or_public enter your account name if you are an Enterprise client; otherwise enter 'public'.

SELECT table_name,
        row_estimate,
        pg_size_pretty(total_bytes) AS total_size,
        pg_size_pretty(index_bytes) AS index_size,
        pg_size_pretty(toast_bytes) AS toast_size,
        pg_size_pretty(table_bytes) AS dataset_size
FROM (
  SELECT *,
          total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes
  FROM (
    SELECT c.oid,
            nspname AS table_schema,
            relname AS table_name,
            c.reltuples AS row_estimate,
            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 like 'your_account_name_or_public'
    AND relname = 'your_dataset_name'
  ) a
) a
ORDER BY total_bytes DESC

This will return a Data View showing your dataset’s name, it’s number of rows, it’s original size as dataset_size, the size of it’s indexes, it’s TOAST size, and the total_size including all of these. You can learn more about these in this tutorial.