At the PGConf.dev, where Postgres developers get together and strategize the work they wanna do for the next version, I attended a session where Matthias van de Meent talked about changing the way Postgres stores columns. As of right now (Postgres 17), columns are aligned in 8-bit intervals, so if you create a table with alternating columns:

1. MyBitColumn1 – 1 bit used
2. (7 bits wasted for alignment to get to the next byte)
3. SomeOtherColumn – any other datatype, but not a bit
4. MyBitColumn2 – 1 bit used
5. (another 7 bits wasted for alignment)

Matthias pointed out that was inefficient, and that Postgres should separate physical column order from logical column order. Under the hood, it should just store:

1. MyBitColumn1 – 1 bit used
2. MyBitColumn2 – 1 bit used
3. (6 bits wasted for alignment)
4. SomeOtherColumn – any other datatype

Microsoft SQL Server already does this with bits, as the documentation explains:

The SQL Server Database Engine optimizes storage of bit columns. If there are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If there are from 9 up to 16 bit columns, the columns are stored as 2 bytes, and so on.

To demonstrate that, I whipped up a demo script showing two table creations: one with the bit columns scattered around through the table, and one where they’re all grouped together:

CREATE DATABASE TestColumnAlignment;GOUSE TestColumnAlignment;GOCREATE TABLE dbo.Disorganized(Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,TinyInt1 TINYINT,Bit1 BIT,TinyInt2 TINYINT,Bit2 BIT,TinyInt3 TINYINT,Bit3 BIT,TinyInt4 TINYINT,Bit4 BIT,TinyInt5 TINYINT,Bit5 BIT);CREATE TABLE dbo.Organized(Id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,Bit1 BIT,Bit2 BIT,Bit3 BIT,Bit4 BIT,Bit5 BIT,TinyInt1 TINYINT,TinyInt2 TINYINT,TinyInt3 TINYINT,TinyInt4 TINYINT,TinyInt5 TINYINT);INSERT INTO dbo.Disorganized(Bit1, Bit2, Bit3, Bit4, Bit5, TinyInt1, TinyInt2, TinyInt3, TinyInt4, TinyInt5)SELECT 1, 1, 1, 1, 1, 1, 1, 1, 1, 1FROM GENERATE\_SERIES(1, 10000000);INSERT INTO dbo.Organized(Bit1, Bit2, Bit3, Bit4, Bit5, TinyInt1, TinyInt2, TinyInt3, TinyInt4, TinyInt5)SELECT 1, 1, 1, 1, 1, 1, 1, 1, 1, 1FROM GENERATE\_SERIES(1, 10000000);GOEXEC sp\_BlitzIndex @Mode = 2; sp_BlitzIndex shows that both tables have the same size:

If we add another bit column to both tables:

ALTER TABLE dbo.DisorganizedADD Bit6 BIT;ALTER TABLE dbo.OrganizedADD Bit6 BIT;GOEXEC sp\_BlitzIndex @Mode = 2;GO Size still remains the same:

Because SQL Server’s just making a metadata-only change, noting that 1 of the 8 bits in the bit-designated space is now available for use by the new Bit6 column. To really drive that point home, let’s go back and update the new Bit6 column to be 1:

UPDATE dbo.Disorganized SET Bit6 = 1;UPDATE dbo.Organized SET Bit6 = 1;GOEXEC sp\_BlitzIndex @Mode = 2;GO And then check the space used again:

Yep, still 194MB. Good work, Microsoft.

The more I learn about Postgres, the more I appreciate so many little things that Microsoft has done over the years for performance & space optimization. The one that’ll really surprise you is that Postgres still doesn’t have table or index compression yet, although it does offer value-level compression.