Does Bit Column Order Matter in Tables?
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:
- MyBitColumn1 – 1 bit used
- (7 bits wasted for alignment to get to the next byte)
- SomeOtherColumn – any other datatype, but not a bit
- MyBitColumn2 – 1 bit used
- (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:
- MyBitColumn1 – 1 bit used
- MyBitColumn2 – 1 bit used
- (6 bits wasted for alignment)
- 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
CREATE DATABASE TestColumnAlignment; GO USE TestColumnAlignment; GO CREATE 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, 1 FROM 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, 1 FROM GENERATE_SERIES(1, 10000000); GO EXEC sp_BlitzIndex @Mode = 2; |
sp_BlitzIndex shows that both tables have the same size:
If we add another bit column to both tables:
|
1 2 3 4 5 6 7 8 |
ALTER TABLE dbo.Disorganized ADD Bit6 BIT; ALTER TABLE dbo.Organized ADD Bit6 BIT; GO EXEC 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:
|
1 2 3 4 5 |
UPDATE dbo.Disorganized SET Bit6 = 1; UPDATE dbo.Organized SET Bit6 = 1; GO EXEC 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.
3 Comments. Leave new
I’ve had a few arguments with oracle and pg DBAs about use of boolean properties (they were all opposed) – I wonder if this is what their opposition is based on. I think they are great for filtered indexes and for statuses that can only ever be in two states that devs won’t be tempted to extend to multiple statuses later and have a very confusing property name
I don’t know if newer versions are better, but Oracle at least as recently back in the 10g days needed the physical ordering of bit columns to be done manually, which was sad given their pricing when Microsoft handled it automatically, even back then.
[…] Brent Ozar performs an experiment: […]