database design - How many records can I store in 5 MB of PostgreSQL on Heroku?

Question

I'm going to store records in a single table with 2 fields:

• id -> 4 characters

• password_hash -> 64 characters

How many records like the one above will I be able to store in a 5mb PostgreSQL on Heroku?

P.S.: given a single table with x columns and a length of y - how can I calculate the space it will take in a database?

Answer 1

Disk space occupied

Calculating the space on disk is not trivial. You have to take into account:

• The overhead per table (small, basically the entries in the system catalog, may not affect you on Heroku).

• The overhead per row (HeapTupleHeader) and per data page (PageHeaderData). Details about page layout in the manual.

• Space lost to data type alignment.

• Space for a NULL bitmap. Effectively free for tables of 8 columns or less, irrelevant for your case.

• Dead rows after UPDATE / DELETE.

• Size of index(es). You'll have a primary key, right? Index size is similar to that of a table with just the indexed columns and less overhead.

• The actual space requirement of the data, depending on the respective data types. Details for character types (incl. fixed length types) in the manual:

  The storage requirement for a short string (up to 126 bytes) is 1 byte plus the actual string, which includes the space padding in the case of character. Longer strings have 4 bytes of overhead instead of 1

  More details for all types in the system catalog pg_type.

• The database encoding in particular for character types. UTF-8 uses up to four bytes to store one character (But 7-Bit-ASCII characters always occupy just one byte, even in UTF-8.)

• Other small things that may affect your case, like TOAST - which should not affect you with 64 character strings.

Calculate with test case

A simple method to find an estimate is to create a test table, fill it with dummy data and measure with database object size functions::

SELECT pg_size_pretty(pg_relation_size('tbl'));

Including indexes:

SELECT pg_size_pretty(pg_total_relation_size('tbl'));

A quick test shows the following results:

CREATE TABLE test(a text, b text);
INSERT INTO test -- quick fake of matching rows
SELECT chr((g/1000 +32)) || to_char(g%1000, 'FM000')
     , repeat (chr(g%120 + 32), 64)
FROM   generate_series(1,50000) g;

SELECT pg_size_pretty(pg_relation_size('test'));       -- 5640 kB
SELECT pg_size_pretty(pg_total_relation_size('test')); -- 5648 kB

After adding a primary key:

ALTER TABLE test ADD CONSTRAINT test_pkey PRIMARY KEY(a);

SELECT pg_size_pretty(pg_total_relation_size('test')); -- 6760 kB

So, I'd expect a maximum of around 44k rows without and around 36k rows with primary key.