Changes between Version 1 and Version 2 of AdvancedTopic
- Timestamp:
- 07/02/26 10:19:15 (3 days ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
AdvancedTopic
v1 v2 16 16 When a C compiler lays out a structure in memory, it aligns each member to its natural alignment boundary. The alignment requirement of a type is typically its size: 17 17 18 | Type | Size (bytes) | Alignment (bytes) | 19 |------|--------------|-------------------| 20 | `char` | 1 | 1 | 21 | `short` | 2 | 2 | 22 | `int` | 4 | 4 | 23 | `float` | 4 | 4 | 24 | `double` | 8 | 8 | 25 | `void*` (pointer) | 8 (64‑bit) | 8 | 18 || Type || Size (bytes) || Alignment (bytes) || 19 || `char` || 1 || 1 || 20 || `short` || 2 || 2 || 21 || `int` || 4 || 4 || 22 || `float` || 4 || 4 || 23 || `double` || 8 || 8 || 24 || `void*` (pointer) || 8 (64‑bit) || 8 || 26 25 27 26 The compiler inserts **padding** between members to satisfy these alignment requirements. Consider this struct: … … 248 247 After `ANALYZE`, `REINDEX`, and `VACUUM`: 249 248 250 | Database | Size | 251 |----------|------| 252 | Original (unoptimised) | 13,459,142,335 bytes (~12.54 GB) | 253 | Optimised (post‑maintenance) | 13,674,971,663 bytes (~12.73 GB) | 254 | '''Difference''' | '''+215,829,328 bytes (~0.19 GB, ~1.6% increase)''' | 249 || Database || Size || 250 || Original (unoptimised) || 13,459,142,335 bytes (~12.54 GB) || 251 || Optimised (post‑maintenance) || 13,674,971,663 bytes (~12.73 GB) || 252 || '''Difference''' || '''+215,829,328 bytes (~0.19 GB, ~1.6% increase)''' || 255 253 256 254 === Table‑by‑Table Breakdown 257 255 258 | Table | Original Data | Optimised Data | Original Index | Optimised Index | Data Δ | Index Δ | 259 |-------|--------------|----------------|----------------|-----------------|--------|---------| 260 | `student_answer` | 3,194 MB | 3,404 MB | 799 MB | 1,035 MB | +210 MB | +236 MB | 261 | `exercise_submission` | 1,327 MB | 1,327 MB | 221 MB | 290 MB | 0 MB | +69 MB | 262 | `exam_attempt` | 509 MB | 509 MB | 266 MB | 344 MB | 0 MB | +78 MB | 263 | `survey_response` | 551 MB | 551 MB | 401 MB | 528 MB | 0 MB | +127 MB | 264 | `member` | 105 MB | 102 MB | 58 MB | 69 MB | -3 MB | +11 MB | 256 || Table || Original Data || Optimised Data || Original Index || Optimised Index || Data Δ || Index Δ || 257 || `student_answer` || 3,194 MB || 3,404 MB | 799 MB || 1,035 MB || +210 MB || +236 MB || 258 || `exercise_submission` || 1,327 MB || 1,327 MB || 221 MB || 290 MB || 0 MB || +69 MB || 259 || `exam_attempt` || 509 MB | 509 MB || 266 MB || 344 MB || 0 MB || +78 MB || 260 || `survey_response` || 551 MB || 551 MB || 401 MB || 528 MB || 0 MB || +127 MB || 261 || `member` || 105 MB || 102 MB || 58 MB || 69 MB || -3 MB || +11 MB || 265 262 266 263 '''Key observation''': The data size remained almost unchanged. The increase is almost entirely in the **indexes**. … … 268 265 === Query Performance 269 266 270 | View | Original Time | Optimised Time | Improvement | 271 |------|--------------|----------------|-------------| 272 | `v_student_registration_state` | ~2,268 ms | ~370 ms | '''6× faster''' | 273 | `v_teaching_assistant_eligibility` | ~923 ms | ~383 ms | '''2.4× faster''' | 267 || View || Original Time || Optimised Time || Improvement || 268 || `v_student_registration_state` || ~2,268 ms || ~370 ms || '''6× faster''' || 269 || `v_teaching_assistant_eligibility` || ~923 ms || ~383 ms || '''2.4× faster''' || 274 270 275 271 The query optimisations (parameterised functions) were the **real success**. … … 277 273 == Analysis: Why the Storage Optimisation Didn't Work 278 274 279 === 1. PostgreSQL Uses 1‑Byte Alignment275 ==== 1. PostgreSQL Uses 1‑Byte Alignment 280 276 281 277 Unlike C compilers which align to the type's size, PostgreSQL uses a **1‑byte alignment** for most columns in the heap. The exception is for `FLOAT` and `DOUBLE` types, which have stricter alignment requirements. For `INTEGER`, `SMALLINT`, and `"char"`, the alignment is effectively 1 byte, so: … … 284 280 * The padding saved by moving `SMALLINT` after `INTEGER` is minimal (1‑2 bytes per tuple at most). 285 281 286 ###2. Indexes Are Separate Structures282 ==== 2. Indexes Are Separate Structures 287 283 288 284 The index B‑tree is **not affected** by the table's column order. The index stores only the indexed columns (plus a reference to the heap row). Therefore: … … 291 287 * Index size is determined by the index key definition, not the table layout. 292 288 293 ###3. The Primary Key Column Order Changed289 ==== 3. The Primary Key Column Order Changed 294 290 295 291 The optimised schema changed the order of columns in the primary key for several tables. For example: … … 306 302 This likely caused the index to become **larger** and **less dense**. 307 303 308 ###4. No Domains Were Used304 ==== 4. No Domains Were Used 309 305 310 306 In the final optimised DDL, the domains were **commented out** and replaced with `SMALLINT`. For example: … … 348 344 == References 349 345 350 * PostgreSQL Documentation: [ Tuple Layout](https://www.postgresql.org/docs/current/storage-page-layout.html)351 * PostgreSQL Documentation: [ Indexes](https://www.postgresql.org/docs/current/indexes.html)352 * "Column Tetris" – [ The Art of PostgreSQL](https://theartofpostgresql.com)346 * PostgreSQL Documentation: [https://www.postgresql.org/docs/current/storage-page-layout.html Tuple Layout] 347 * PostgreSQL Documentation: [https://www.postgresql.org/docs/current/indexes.html Indexes] 348 * "Column Tetris" – [https://theartofpostgresql.com The Art of PostgreSQL] 353 349 354 350 ---
