This is the query to retrieve info about:

• Total Size

• Total Size of all Indexes

• Table Size

• Index Size

• Estimated table row count

   SELECT i.relname "Table Name",indexrelname "Index Name",
   pg_size_pretty(pg_total_relation_size(relid)) As "Total Size",
   pg_size_pretty(pg_indexes_size(relid)) as "Total Size of all Indexes",
   pg_size_pretty(pg_relation_size(relid)) as "Table Size",
   pg_size_pretty(pg_relation_size(indexrelid)) "Index Size",
   reltuples::bigint "Estimated table row count"
   FROM pg_stat_all_indexes i JOIN pg_class c ON i.relid=c.oid 
   WHERE i.relname='uploads'
  

Maybe for someone it will be useful.

There is no general way to answer this (other than try it on your test server and see). GIN supports many different operators, like tsvector or trigrams, and they have different characteristics. In newer versions, GIN indexes use compression which can be pretty impressive when the same key value shows up over and over again. But that level of compression depends on the ordering of the rows.

For example, if I index a single text column with a lot of duplicate values (~50 million rows, with ~1.5 million distinct values, using the btree_gin extension for the GIN), I get 2010 MB for the b-tree index and 435 MB for the GIN index. So no, the GIN is not always bigger. But in general (i.e. other than with btree_gin) you don't index the same types of data with GIN as you do with b-tree, so a direct comparison of sizes does not make much sense.

Since you did not specify the index type, I'll assume default B-tree indexes. Other types can be a lot different.

Here is a simplistic function to compute the estimated minimum size in bytes for an index on the given table with the given columns:

CREATE OR REPLACE FUNCTION f_index_minimum_size(_tbl regclass, _cols VARIADIC text[], OUT estimated_minimum_size bigint)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _missing_column text;
BEGIN

-- assert
SELECT i.attname
FROM   unnest(_cols) AS i(attname)
LEFT   JOIN pg_catalog.pg_attribute a ON a.attname = i.attname
                                     AND a.attrelid = _tbl
WHERE  a.attname IS NULL
INTO   _missing_column;

IF FOUND THEN
   RAISE EXCEPTION 'Table % has no column named %', _tbl, quote_ident(_missing_column);
END IF;


SELECT INTO estimated_minimum_size
       COALESCE(1 + ceil(reltuples/trunc((blocksize-page_overhead)/(4+tuple_size)))::int, 0) * blocksize -- AS estimated_minimum_size
FROM  (
   SELECT maxalign, blocksize, reltuples, fillfactor, page_overhead
        , (maxalign  -- up to 16 columns, else nullbitmap may force another maxalign step
         + CASE WHEN datawidth <= maxalign  THEN maxalign
                WHEN datawidth%maxalign = 0 THEN datawidth
                ELSE                            (datawidth + maxalign) - datawidth%maxalign END  -- add padding to the data to align on MAXALIGN
          ) AS tuple_size
   FROM  (
      SELECT c.reltuples, count(*)
           , 90 AS fillfactor
           , current_setting('block_size')::bigint AS blocksize
           , CASE WHEN version() ~ '64-bit|x86_64|ppc64|ia64|amd64|mingw32'  -- MAXALIGN: 4 on 32bits, 8 on 64bits
                  THEN 8 ELSE 4 END AS maxalign
           , 40 AS page_overhead  -- 24 bytes page header + 16 bytes "special space"
           -- avg data width without null values
           , sum(ceil((1-COALESCE(s.null_frac, 0)) * COALESCE(s.avg_width, 1024))::int) AS datawidth  -- ceil() because avg width has a low bias
      FROM   pg_catalog.pg_class     c
      JOIN   pg_catalog.pg_attribute a ON a.attrelid = c.oid
      JOIN   pg_catalog.pg_stats     s ON s.schemaname = c.relnamespace::regnamespace::text
                                      AND s.tablename  = c.relname
                                      AND s.attname    = a.attname
      WHERE  c.oid = _tbl
      AND    a.attname = ANY(_cols) --  all exist, verified above
      GROUP  BY 1
      ) sub1
   ) sub2;
END
$func$;

Call examples:

SELECT f_index_minimum_size('my_table', 'col1', 'col2', 'col3');

SELECT f_index_minimum_size('public.my_table', VARIADIC '{col1, col2, col3}');

db<>fiddle here

About VARIADIC parameters:

• Return rows matching elements of input array in plpgsql function

Basically, all indexes use data pages of typically 8 kb block size (rarely 4 kb). There is one data page overhead for B-tree indexes to start with. Each additional data page has a fixed overhead of 40 bytes (currently). Each page stores tuples like depicted in the manual here. Each tuple has a tuple header (typically 8 bytes incl. alignment padding), possibly a null bitmap, data (possibly incl. alignment padding between columns for multicolumn indices), and possibly alignment padding to the next multiple of MAXALIGN (typically 8 bytes). Plus, there is an ItemId of 4 bytes per tuple. Some space may be reserved initially for later additions with a fillfactor - 90 % by default for B-tree indexes.

Important notes & disclaimers

The reported size is the estimated minimum size. An actual index will typically be bigger by around 25 % due to natural bloat from page splits. Plus, the calculation does not take possible alignment padding between multiple columns into account. Can add another couple percent (or more in extreme cases). See:

• Calculating and saving space in PostgreSQL

Estimations are based on column statistics in the view pg_stats which is based on the system table pg_statistics. (Using the latter directly would be faster, but only allowed for superusers.) In particular, the calculation is based on null_frac, the "fraction of column entries that are null" and avg_width, the "average width in bytes of column's entries" to compute an average data width - ignoring possible additional alignment padding for multicolumn indexes.

The default 90 % fillfactor is taken into account. (One might specify a different one.)

Up to 50 % bloat is typically natural for B-tree indexes and nothing to worry about.

Does not work for expression indexes.

No provision for partial indexes.

Function raises an exception if anything but existing plain column names is passed. Case-sensitive!

If the table is new (or in any case if statistics may be out of date), be sure to run ANALYZE on the table before calling the function to update (or even initiate!) statistics.

Due to major optimizations, B-tree indexes in Postgres 12 waste less space and are typically closer to the reported minimum size.

Does not account for deduplication that's introduced with Postgres 13, which can compact indexes with duplicate values.

Parts of the code are taken from ioguix' bloat estimation queries here:

• https://github.com/ioguix/pgsql-bloat-estimation

More gory details i the Postgres source code here:

• https://doxygen.postgresql.org/bufpage_8h_source.html

There's almost no deletion taking place on the foos table, so the bloat is non-existent.

You don't need deletions to get bloat. updates will do so as well. A freshly made index on 10,000,000 ints gives me a size of 214MB, so you do have bloat (or are using weird hardware, perhaps). You can't detect bloat just by thinking about to. Rebuild the index and see if it gets smaller, or use pgstattuple. Of course having a tightly packed index is not very efficient, as then nearly every operation needs to do page splits.

Your mental model of an index does not match how PostgreSQL does things.

An index does not point to a primary key value, it points directly into the table. This is called a "tid", and generally takes 6 bytes.

The data value itself is stored to 8 byte alignment, so even if your data is an int4, it still takes 8 bytes of disk space. Due to this alignment, You could build an index on two int4 columns, and it will still take the same size as one int4 column.

PostgreSQL is always prepared to handle NULL values on each row, as well as variable length data, even if the definition of the table/index precludes such things. So each index tuple has a header which tells us whether that row has any NULL or variable length data, as well as the overall length of that tuple. This takes up some space.

PostgreSQL uses page-organized storage, not just a huge malloc chunk. Each page contains some more overhead, both per-block and per-entry within the block (see typedef struct ItemIdData).

So you get 8 bytes for the data itself (bar_id, aligned), 8 bytes of the tuple header which includes the tid pointer, and 4 bytes for the line pointer within each block, that comes up to 20 bytes. With some overhead of non-leaf pages plus per-block overhead and some space within blocks which is not usable due to alignment or because they are at the ragged end of the data, I get an actual value of 22.4 bytes. The other 4.5 fold would be bloat (although some of the "bloat" may be useful free space, unless your index is going to be absolutely static from now on)