我可以在Heroku的5 MB PostgreSQL中存储多少条记录？ [英] How many records can I store in 5 MB of PostgreSQL on Heroku?

问题描述

• id - > 4个字符

  p>



• password_hash - > 64个字符

如上所述的记录将能够存储在Heroku的5mb PostgreSQL中？

PS：给出一个单列x列和长度为y的表 - 如何我计算数据库中需要的空间？

解决方案

占用的磁盘空间

计算磁盘上的空间并不是微不足道的。您必须考虑到：

• 每个表的开销（小，基本上系统目录中的条目可能不会影响




• 每行的开销（HeapTupleHeader）和每个数据页（PageHeaderData）。有关手册中页面布局的详细信息。




• 数据类型对齐 a>。




• NULL位图 。




• 在更新后的死线有效地为8列或更少的表格免费 / DELETE 。




• index（es） 。你会有一个主键，对吧？索引大小类似于具有索引列和较少开销的表的大小。




• 数据的实际空间要求取决于相应的数据类型。 手册中的字符类型（包括固定长度类型）的详细信息：

  
  

  一个短字符串（最多126个字节）的存储需求是1个字节
  加上实际的字符串，包含
  字符的空格填充。较长的字符串具有4个字节的开销而不是1

  
  

  系统目录中所有类型的更多详细信息 pg_type 。




• 数据库 encoding ，特别是字符类型。 UTF-8最多可使用四个字节来存储一个字符（但7位ASCII字符始终只占用一个字节，即使是UTF-8）。




• 其他可能影响您案件的小事情，例如 TOAST - 这不应该影响你的64个字符串。

用测试用例计算< h3>

找到估计的一个简单方法是创建一个测试表，用虚拟数据填充并用数据库对象大小功能：：

  SELECT pg_size_pretty（pg_relation_size（'tbl'））; 
  

包含索引：

  SELECT pg_size_pretty（pg_total_relation_size（'tbl'））; 
  

快速测试显示以下结果：

  CREATE TABLE测试（一个文本，b文本）; 
 INSERT INTO测试 - 快速假匹配行
 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

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?

解决方案

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.

这篇关于我可以在Heroku的5 MB PostgreSQL中存储多少条记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！