在 Heroku 上，我可以在 5 MB 的 PostgreSQL 中存储多少条记录? [英] How many records can I store in 5 MB of PostgreSQL on Heroku?
问题描述
我要将记录存储在一个包含 2 个字段的表中:
I'm going to store records in a single table with 2 fields:
id -> 4 个字符
id -> 4 characters
password_hash -> 64 个字符
password_hash -> 64 characters
我可以在 Heroku 上的 5mb PostgreSQL 中存储多少条类似上述记录的记录?
How many records like the one above will I be able to store in a 5mb PostgreSQL on Heroku?
P.S.:给定一个包含 x 列和长度为 y 的表 - 如何计算它将在数据库中占用的空间?
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?
推荐答案
磁盘空间占用
计算磁盘空间并非易事.你必须考虑:
Disk space occupied
Calculating the space on disk is not trivial. You have to take into account:
每个表的开销(很小，基本上是系统目录中的条目，在 Heroku 上可能不会影响您).
The overhead per table (small, basically the entries in the system catalog, may not affect you on Heroku).
每行 (HeapTupleHeader) 和每个数据页 (PageHeaderData) 的开销.有关手册中的页面布局的详细信息.
The overhead per row (HeapTupleHeader) and per data page (PageHeaderData). Details about page layout in the manual.
空间因数据类型对齐而丢失.
Space lost to data type alignment.
空位图的空间.8 列或更少的表格有效免费，与您的情况无关.
Space for a NULL bitmap. Effectively free for tables of 8 columns or less, irrelevant for your case.
死行 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:
短字符串(最多 126 个字节)的存储要求是 1 个字节加上实际的字符串，其中包括案例中的空格填充字符.更长的字符串有 4 个字节的开销而不是 1 个
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
系统目录中所有类型的更多详细信息pg_type.
More details for all types in the system catalog pg_type.
数据库编码，特别是字符类型.UTF-8 最多使用四个字节来存储一个字符(但 7 位 ASCII 字符总是只占用一个字节，即使在 UTF-8 中也是如此.)
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.)
其他可能会影响您的案例的小事情，例如 TOAST - 这应该不会影响您使用 64 个字符串.
Other small things that may affect your case, like TOAST - which should not affect you with 64 character strings.
找到估计值的一个简单方法是创建一个测试表，用虚拟数据填充它并使用 数据库对象大小函数::
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'));
包括索引:
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
添加主键后:
ALTER TABLE test ADD CONSTRAINT test_pkey PRIMARY KEY(a);
SELECT pg_size_pretty(pg_total_relation_size('test')); -- 6760 kB
因此，我预计最多大约 44k 行没有主键，大约 36k 行有主键.
So, I'd expect a maximum of around 44k rows without and around 36k rows with primary key.
这篇关于在 Heroku 上，我可以在 5 MB 的 PostgreSQL 中存储多少条记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
