5 megabytes of database data (on Postresql) is about how many rows in one table?

Question

PostgreSQL

I found out that Heroku's db limits are 5mb.
The project is also not gigantic, so I want to know how much is 5mb?

Answer 1

Wrong question.
Depends on the type and number of columns in the table. Each database stores the same text fields or indexes in its own way.
Here is an example of how much 5 million lines weigh with text, index and without
211 mb - just a table
287 mb - with a text field
394 mb - with an index by int field
545 mb - with an index by text field

virtex_old2=# create table test (id serial, amount numeric, customer numeric);
CREATE TABLE
virtex_old2=# INSERT INTO test(amount,customer)  SELECT amount, customer FROM (SELECT generate_series(1,5000000) as id, (random()*10000)::int AS amount, (random()*10000)::int AS customer) as aa;
INSERT 0 5000000
virtex_old2=# SELECT nspname || '.' || relname AS "relation",
virtex_old2-#     pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
virtex_old2-#   FROM pg_class C
virtex_old2-#   LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
virtex_old2-#   WHERE nspname NOT IN ('pg_catalog', 'information_schema')
virtex_old2-#     AND C.relkind <> 'i'
virtex_old2-#     AND nspname !~ '^pg_toast'
virtex_old2-#     AND relname = 'test';
  relation   | total_size
-------------+------------
 public.test | 211 MB
(1 row)
virtex_old2=# ALTER TABLE test
  ADD COLUMN comment text NOT NULL DEFAULT 'text is here';
ALTER TABLE
virtex_old2=#
virtex_old2=# SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
    AND relname = 'test';
  relation   | total_size
-------------+------------
 public.test | 287 MB
 
virtex_old2=# CREATE INDEX ON test (customer);
CREATE INDEX
virtex_old2=# SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
    AND relname = 'test';
  relation   | total_size
-------------+------------
 public.test | 394 MB
(1 row)
virtex_old2=# CREATE INDEX ON test (comment);
CREATE INDEX
virtex_old2=# SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
    AND relname = 'test';
  relation   | total_size
-------------+------------
 public.test | 545 MB
(1 row)

Answer 2

Take all the fields in the database, find out how much they weigh, and find out how many records