Now we can see the table with 40kB: test=# \dt fooīoth indexes are the same size (40kB). Let's create a small table with 1000 rows and check it out: CREATE TABLE foo ASĬASE WHEN x>0.5 THEN 1::smallint ELSE 0::smallint END AS si,ĬASE when x>0.5 THEN true ELSE false END AS bįROM ( SELECT random() AS x FROM generate_series(1,1e3) ) AS t Should I use the PostgreSQL bit string?įirst, the size of a smallint is two bytes which is twice the size of a bool: SELECT.If you have many boolean NOT NULL values and want to optimize space on disk: Configuring PostgreSQL for read performance.Calculating and saving space in PostgreSQL., pg_column_size(row(text 'abcdef', int2 '1', now())) AS text7_int2_ts - worst caseĬhar_bool | char_int2 | text7_bool_ts | text7_int2_ts , pg_column_size(row(text 'abcdef', TRUE, now())) AS text7_bool_ts In a worst case scenario, when mixing with types that require 8-byte alignment like bigint or timestamp / timestamptz: SELECT pg_column_size(row("char" 'a', FALSE )) AS char_bool ![]() ![]() So another byte is consumed every odd time (literally). It can only start at an even offset from the start of the tuple data. Smallint (like other integer types and unlike boolean) also has special needs for alignment padding. Just to address the storage angle in addition to what you posted as answer:īoolean requires 1 byte on disk, smallint requires 2.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |