Vol. I · A Postgres Handbook

The Query & the Schema

A reading-room reference for writing PostgreSQL by hand — selects to window functions, with the schema in plain view.

01

Chapter 1

Advanced PostgreSQL

Beyond the standard surface. JSONB, arrays, full-text search, partitions, and the features that make Postgres feel limitless.

In this chapter

350

queries, easy → advanced

  1. 001JSONB: store and query JSON dataRead
  2. 002ARRAY operations in PostgreSQLRead
  3. 003HSTORE: key-value pairsRead
  4. 004Table Partitioning: range partition by dateRead
  5. 005Materialized Views: cached query resultsRead
  6. 006Views: virtual tables for access controlRead
  7. 007Triggers: auto-update updated_at timestampRead
  8. 008Stored Functions: salary raise with validationRead
  9. 009UPSERT with ON CONFLICTRead
  10. 010COPY command: bulk load CSV dataRead
  11. 011Row-Level Security (RLS)Read
  12. 012EXPLAIN ANALYZE: understand query executionRead
  13. 013Transactional DDL with SAVEPOINTRead
  14. 014WINDOW: compare each month to previous month (MoM growth)Read
  15. 015Pivot table using crosstab (tablefunc extension)Read
  16. 016Running balance (financial ledger query)Read
  17. 017Find the Nth highest salary (multiple methods)Read
  18. 018Detect and handle deadlocks with advisory locksRead
  19. 019Logical replication subscription setupRead
  20. 020GENERATE_SERIES: number generator utilityRead
  21. 021Recursive fibonacci using CTERead
  22. 022Dynamic SQL with EXECUTERead
  23. 023Table inheritance in PostgreSQLRead
  24. 024Efficient pagination with keyset (cursor-based)Read
  25. 025Custom aggregate functionRead
  26. 026Table function: set-returning functionRead
  27. 027Using EXPLAIN to optimize a slow queryRead
  28. 028Implement audit log with triggerRead
  29. 029Optimistic locking with version columnRead
  30. 030Partitioning: list partition by statusRead
  31. 031LISTEN/NOTIFY for async messagingRead
  32. 032PostgreSQL-specific: range typesRead
  33. 033PostgreSQL: enum typesRead
  34. 034Window function frame modes: ROWS vs RANGERead
  35. 035PostgreSQL: check constraints and exclusion constraintsRead
  36. 036Full-text search with ranking and highlightingRead
  37. 037Immutable function for expression indexesRead
  38. 038PostgreSQL: COPY between databases via pipeRead
  39. 039Monitoring: lock contention and blocking queriesRead
  40. 040Create a basic ViewRead
  41. 041Create or Replace ViewRead
  42. 042Create a Materialized ViewRead
  43. 043Refresh Materialized ViewRead
  44. 044Basic Transaction (BEGIN / COMMIT)Read
  45. 045Transaction RollbackRead
  46. 046Savepoints in TransactionsRead
  47. 047Create a new Role (User)Read
  48. 048Grant table permissionsRead
  49. 049Revoke permissionsRead
  50. 050Row-Level Security (RLS)Read
  51. 051Theory: What is PostgreSQL?Read
  52. 052Theory: PostgreSQL vs MySQL - Data Types & JSONRead
  53. 053Theory: ACID PropertiesRead
  54. 054Theory: What is MVCC?Read
  55. 055Theory: What is VACUUM?Read
  56. 056Theory: PostgreSQL vs MySQL - ConcurrencyRead
  57. 057Theory: What is WAL?Read
  58. 058Theory: PostgreSQL vs MySQL - Index TypesRead
  59. 059Theory: EXPLAIN and ANALYZERead
  60. 060Theory: What are CTEs?Read
  61. 061Theory: PostgreSQL vs MySQL - Table InheritanceRead
  62. 062Theory: What is TOAST?Read
  63. 063Theory: What is a Materialized View?Read
  64. 064Theory: DDL vs DML vs DCL vs TCLRead
  65. 065Theory: PostgreSQL vs MySQL - DDL in TransactionsRead
  66. 066Theory: Logical vs Physical ReplicationRead
  67. 067Theory: What is a Schema?Read
  68. 068Theory: What are Window Functions?Read
  69. 069Theory: Table PartitioningRead
  70. 070Theory: PostgreSQL vs MySQL - Standard ComplianceRead
  71. 071Theory: Connection Pooling (PgBouncer)Read
  72. 072Theory: FillfactorRead
  73. 073Theory: Foreign Data Wrappers (FDW)Read
  74. 074Theory: Roles vs UsersRead
  75. 075Theory: Execution Plan NodesRead
  76. 076Theory: Correlated vs Uncorrelated SubqueriesRead
  77. 077Theory Deep Dive: ACID ComplianceRead
  78. 078PostgreSQL vs MySQL: ACID ComplianceRead
  79. 079Theory Deep Dive: MVCC implementation detailsRead
  80. 080PostgreSQL vs MySQL: MVCC implementation detailsRead
  81. 081Theory Deep Dive: WAL architectureRead
  82. 082PostgreSQL vs MySQL: WAL architectureRead
  83. 083Theory Deep Dive: Vacuuming mechanicsRead
  84. 084PostgreSQL vs MySQL: Vacuuming mechanicsRead
  85. 085Theory Deep Dive: Autovacuum tuningRead
  86. 086PostgreSQL vs MySQL: Autovacuum tuningRead
  87. 087Theory Deep Dive: Replication slotsRead
  88. 088PostgreSQL vs MySQL: Replication slotsRead
  89. 089Theory Deep Dive: B-Tree internalsRead
  90. 090PostgreSQL vs MySQL: B-Tree internalsRead
  91. 091Theory Deep Dive: Hash indexes vs B-TreeRead
  92. 092PostgreSQL vs MySQL: Hash indexes vs B-TreeRead
  93. 093Theory Deep Dive: GiST index usesRead
  94. 094PostgreSQL vs MySQL: GiST index usesRead
  95. 095Theory Deep Dive: GIN index for full-textRead
  96. 096PostgreSQL vs MySQL: GIN index for full-textRead
  97. 097Theory Deep Dive: BRIN index for time-seriesRead
  98. 098PostgreSQL vs MySQL: BRIN index for time-seriesRead
  99. 099Theory Deep Dive: Table bloat causesRead
  100. 100PostgreSQL vs MySQL: Table bloat causesRead
  101. 101Theory Deep Dive: Connection pooling strategiesRead
  102. 102PostgreSQL vs MySQL: Connection pooling strategiesRead
  103. 103Theory Deep Dive: PgBouncer vs Pgpool-IIRead
  104. 104PostgreSQL vs MySQL: PgBouncer vs Pgpool-IIRead
  105. 105Theory Deep Dive: Transaction isolation levelsRead
  106. 106PostgreSQL vs MySQL: Transaction isolation levelsRead
  107. 107Theory Deep Dive: Read Committed vs SerializableRead
  108. 108PostgreSQL vs MySQL: Read Committed vs SerializableRead
  109. 109Theory Deep Dive: Phantom reads in PostgreSQLRead
  110. 110PostgreSQL vs MySQL: Phantom reads in PostgreSQLRead
  111. 111Theory Deep Dive: Logical decodingRead
  112. 112PostgreSQL vs MySQL: Logical decodingRead
  113. 113Theory Deep Dive: Foreign Data Wrappers (FDW)Read
  114. 114PostgreSQL vs MySQL: Foreign Data Wrappers (FDW)Read
  115. 115Theory Deep Dive: Role-based access control (RBAC)Read
  116. 116PostgreSQL vs MySQL: Role-based access control (RBAC)Read
  117. 117Theory Deep Dive: Row-Level Security (RLS)Read
  118. 118PostgreSQL vs MySQL: Row-Level Security (RLS)Read
  119. 119Theory Deep Dive: Table Partitioning boundsRead
  120. 120PostgreSQL vs MySQL: Table Partitioning boundsRead
  121. 121Theory Deep Dive: Declarative partitioningRead
  122. 122PostgreSQL vs MySQL: Declarative partitioningRead
  123. 123Theory Deep Dive: JSONB vs JSONRead
  124. 124PostgreSQL vs MySQL: JSONB vs JSONRead
  125. 125Theory Deep Dive: TOAST table internalsRead
  126. 126PostgreSQL vs MySQL: TOAST table internalsRead
  127. 127Theory Deep Dive: PostgreSQL memory architectureRead
  128. 128PostgreSQL vs MySQL: PostgreSQL memory architectureRead
  129. 129Theory Deep Dive: shared_buffers tuningRead
  130. 130PostgreSQL vs MySQL: shared_buffers tuningRead
  131. 131Theory Deep Dive: work_mem limitsRead
  132. 132PostgreSQL vs MySQL: work_mem limitsRead
  133. 133Theory Deep Dive: maintenance_work_memRead
  134. 134PostgreSQL vs MySQL: maintenance_work_memRead
  135. 135Theory Deep Dive: Checkpointing processRead
  136. 136PostgreSQL vs MySQL: Checkpointing processRead
  137. 137Theory Deep Dive: Background writer (bgwriter)Read
  138. 138PostgreSQL vs MySQL: Background writer (bgwriter)Read
  139. 139Theory Deep Dive: Statistics collectorRead
  140. 140PostgreSQL vs MySQL: Statistics collectorRead
  141. 141Theory Deep Dive: EXPLAIN ANALYZE readingRead
  142. 142PostgreSQL vs MySQL: EXPLAIN ANALYZE readingRead
  143. 143Theory Deep Dive: Nested Loop Join pros/consRead
  144. 144PostgreSQL vs MySQL: Nested Loop Join pros/consRead
  145. 145Theory Deep Dive: Hash Join mechanicsRead
  146. 146PostgreSQL vs MySQL: Hash Join mechanicsRead
  147. 147Theory Deep Dive: Merge Join prerequisitesRead
  148. 148PostgreSQL vs MySQL: Merge Join prerequisitesRead
  149. 149Spring Boot & PG Integration: Application Properties #1Read
  150. 150Spring Boot & PG Integration: Application Properties #2Read
  151. 151Spring Boot & PG Integration: Application Properties #3Read
  152. 152Spring Boot & PG Integration: Application Properties #4Read
  153. 153Spring Boot & PG Integration: Application Properties #5Read
  154. 154Spring Boot & PG Integration: Application Properties #6Read
  155. 155Spring Boot & PG Integration: Application Properties #7Read
  156. 156Spring Boot & PG Integration: Application Properties #8Read
  157. 157Spring Boot & PG Integration: Application Properties #9Read
  158. 158Spring Boot & PG Integration: Application Properties #10Read
  159. 159Spring Boot & PG Integration: Application Properties #11Read
  160. 160Spring Boot & PG Integration: Application Properties #12Read
  161. 161Spring Boot & PG Integration: Application Properties #13Read
  162. 162Spring Boot & PG Integration: Application Properties #14Read
  163. 163Spring Boot & PG Integration: Application Properties #15Read
  164. 164Spring Boot & PG Integration: Application Properties #16Read
  165. 165Spring Boot & PG Integration: Application Properties #17Read
  166. 166Spring Boot & PG Integration: Application Properties #18Read
  167. 167Spring Boot & PG Integration: Application Properties #19Read
  168. 168Spring Boot & PG Integration: Application Properties #20Read
  169. 169Spring Boot & PG Integration: Application Properties #21Read
  170. 170Spring Boot & PG Integration: Application Properties #22Read
  171. 171Spring Boot & PG Integration: Application Properties #23Read
  172. 172Spring Boot & PG Integration: Application Properties #24Read
  173. 173Spring Boot & PG Integration: Application Properties #25Read
  174. 174Spring Boot & PG Integration: Application Properties #26Read
  175. 175Spring Boot & PG Integration: Application Properties #27Read
  176. 176Spring Boot & PG Integration: Application Properties #28Read
  177. 177Spring Boot & PG Integration: Application Properties #29Read
  178. 178Spring Boot & PG Integration: Application Properties #30Read
  179. 179Spring Boot & PG Integration: Application Properties #31Read
  180. 180Spring Boot & PG Integration: Application Properties #32Read
  181. 181Spring Boot & PG Integration: Application Properties #33Read
  182. 182Spring Boot & PG Integration: Application Properties #34Read
  183. 183Spring Boot & PG Integration: Application Properties #35Read
  184. 184Spring Boot & PG Integration: Application Properties #36Read
  185. 185Spring Boot & PG Integration: Application Properties #37Read
  186. 186Spring Boot & PG Integration: Application Properties #38Read
  187. 187Spring Boot & PG Integration: Application Properties #39Read
  188. 188Spring Boot & PG Integration: Application Properties #40Read
  189. 189Spring Boot & PG Integration: Application Properties #41Read
  190. 190Spring Boot & PG Integration: Application Properties #42Read
  191. 191Spring Boot & PG Integration: Application Properties #43Read
  192. 192Spring Boot & PG Integration: Application Properties #44Read
  193. 193Spring Boot & PG Integration: Application Properties #45Read
  194. 194Spring Boot & PG Integration: Application Properties #46Read
  195. 195Spring Boot & PG Integration: Application Properties #47Read
  196. 196Spring Boot & PG Integration: Application Properties #48Read
  197. 197Spring Boot & PG Integration: Application Properties #49Read
  198. 198Spring Boot & PG Integration: Application Properties #50Read
  199. 199Spring Data JPA & PG: UUID generation (Part 1)Read
  200. 200Spring Data JPA & PG: Array mapping (Part 2)Read
  201. 201Spring Data JPA & PG: Enum handling (Part 3)Read
  202. 202Spring Data JPA & PG: Dialect configuration (Part 4)Read
  203. 203Spring Data JPA & PG: N+1 query problem (Part 5)Read
  204. 204Spring Data JPA & PG: Entity Graph (Part 6)Read
  205. 205Spring Data JPA & PG: FetchType.LAZY (Part 7)Read
  206. 206Spring Data JPA & PG: Batch inserts (Part 8)Read
  207. 207Spring Data JPA & PG: GenerationType.SEQUENCE (Part 9)Read
  208. 208Spring Data JPA & PG: Audit fields (Part 10)Read
  209. 209Spring Data JPA & PG: Pessimistic Locking (Part 11)Read
  210. 210Spring Data JPA & PG: JSONB mapping (Part 12)Read
  211. 211Transaction Management: Propagation & Isolation #1Read
  212. 212Transaction Management: Propagation & Isolation #2Read
  213. 213Transaction Management: Propagation & Isolation #3Read
  214. 214Transaction Management: Propagation & Isolation #4Read
  215. 215Transaction Management: Propagation & Isolation #5Read
  216. 216Transaction Management: Propagation & Isolation #6Read
  217. 217Transaction Management: Propagation & Isolation #7Read
  218. 218Transaction Management: Propagation & Isolation #8Read
  219. 219Transaction Management: Propagation & Isolation #9Read
  220. 220Transaction Management: Propagation & Isolation #10Read
  221. 221Transaction Management: Propagation & Isolation #11Read
  222. 222Transaction Management: Propagation & Isolation #12Read
  223. 223Transaction Management: Propagation & Isolation #13Read
  224. 224Transaction Management: Propagation & Isolation #14Read
  225. 225Transaction Management: Propagation & Isolation #15Read
  226. 226Transaction Management: Propagation & Isolation #16Read
  227. 227Transaction Management: Propagation & Isolation #17Read
  228. 228Transaction Management: Propagation & Isolation #18Read
  229. 229Transaction Management: Propagation & Isolation #19Read
  230. 230Transaction Management: Propagation & Isolation #20Read
  231. 231Transaction Management: Propagation & Isolation #21Read
  232. 232Transaction Management: Propagation & Isolation #22Read
  233. 233Transaction Management: Propagation & Isolation #23Read
  234. 234Transaction Management: Propagation & Isolation #24Read
  235. 235Transaction Management: Propagation & Isolation #25Read
  236. 236Transaction Management: Propagation & Isolation #26Read
  237. 237Transaction Management: Propagation & Isolation #27Read
  238. 238Transaction Management: Propagation & Isolation #28Read
  239. 239Transaction Management: Propagation & Isolation #29Read
  240. 240Transaction Management: Propagation & Isolation #30Read
  241. 241Transaction Management: Propagation & Isolation #31Read
  242. 242Transaction Management: Propagation & Isolation #32Read
  243. 243Transaction Management: Propagation & Isolation #33Read
  244. 244Transaction Management: Propagation & Isolation #34Read
  245. 245Transaction Management: Propagation & Isolation #35Read
  246. 246Transaction Management: Propagation & Isolation #36Read
  247. 247Transaction Management: Propagation & Isolation #37Read
  248. 248Transaction Management: Propagation & Isolation #38Read
  249. 249Transaction Management: Propagation & Isolation #39Read
  250. 250Transaction Management: Propagation & Isolation #40Read
  251. 251Database Migrations in Spring Boot #1Read
  252. 252Database Migrations in Spring Boot #2Read
  253. 253Database Migrations in Spring Boot #3Read
  254. 254Database Migrations in Spring Boot #4Read
  255. 255Database Migrations in Spring Boot #5Read
  256. 256Database Migrations in Spring Boot #6Read
  257. 257Database Migrations in Spring Boot #7Read
  258. 258Database Migrations in Spring Boot #8Read
  259. 259Database Migrations in Spring Boot #9Read
  260. 260Database Migrations in Spring Boot #10Read
  261. 261Database Migrations in Spring Boot #11Read
  262. 262Database Migrations in Spring Boot #12Read
  263. 263Database Migrations in Spring Boot #13Read
  264. 264Database Migrations in Spring Boot #14Read
  265. 265Database Migrations in Spring Boot #15Read
  266. 266Database Migrations in Spring Boot #16Read
  267. 267Database Migrations in Spring Boot #17Read
  268. 268Database Migrations in Spring Boot #18Read
  269. 269Database Migrations in Spring Boot #19Read
  270. 270Database Migrations in Spring Boot #20Read
  271. 271Database Migrations in Spring Boot #21Read
  272. 272Database Migrations in Spring Boot #22Read
  273. 273Database Migrations in Spring Boot #23Read
  274. 274Database Migrations in Spring Boot #24Read
  275. 275Database Migrations in Spring Boot #25Read
  276. 276Database Migrations in Spring Boot #26Read
  277. 277Database Migrations in Spring Boot #27Read
  278. 278Database Migrations in Spring Boot #28Read
  279. 279Database Migrations in Spring Boot #29Read
  280. 280Database Migrations in Spring Boot #30Read
  281. 281Database Migrations in Spring Boot #31Read
  282. 282Database Migrations in Spring Boot #32Read
  283. 283Database Migrations in Spring Boot #33Read
  284. 284Database Migrations in Spring Boot #34Read
  285. 285Database Migrations in Spring Boot #35Read
  286. 286Database Migrations in Spring Boot #36Read
  287. 287Database Migrations in Spring Boot #37Read
  288. 288Database Migrations in Spring Boot #38Read
  289. 289Database Migrations in Spring Boot #39Read
  290. 290Database Migrations in Spring Boot #40Read
  291. 291Database Migrations in Spring Boot #41Read
  292. 292Database Migrations in Spring Boot #42Read
  293. 293Database Migrations in Spring Boot #43Read
  294. 294Database Migrations in Spring Boot #44Read
  295. 295Database Migrations in Spring Boot #45Read
  296. 296Database Migrations in Spring Boot #46Read
  297. 297Database Migrations in Spring Boot #47Read
  298. 298Database Migrations in Spring Boot #48Read
  299. 299Database Migrations in Spring Boot #49Read
  300. 300Database Migrations in Spring Boot #50Read
  301. 301Backend Deployment: Docker & K8s #1Read
  302. 302Backend Deployment: Docker & K8s #2Read
  303. 303Backend Deployment: Docker & K8s #3Read
  304. 304Backend Deployment: Docker & K8s #4Read
  305. 305Backend Deployment: Docker & K8s #5Read
  306. 306Backend Deployment: Docker & K8s #6Read
  307. 307Backend Deployment: Docker & K8s #7Read
  308. 308Backend Deployment: Docker & K8s #8Read
  309. 309Backend Deployment: Docker & K8s #9Read
  310. 310Backend Deployment: Docker & K8s #10Read
  311. 311Backend Deployment: Docker & K8s #11Read
  312. 312Backend Deployment: Docker & K8s #12Read
  313. 313Backend Deployment: Docker & K8s #13Read
  314. 314Backend Deployment: Docker & K8s #14Read
  315. 315Backend Deployment: Docker & K8s #15Read
  316. 316Backend Deployment: Docker & K8s #16Read
  317. 317Backend Deployment: Docker & K8s #17Read
  318. 318Backend Deployment: Docker & K8s #18Read
  319. 319Backend Deployment: Docker & K8s #19Read
  320. 320Backend Deployment: Docker & K8s #20Read
  321. 321Backend Deployment: Docker & K8s #21Read
  322. 322Backend Deployment: Docker & K8s #22Read
  323. 323Backend Deployment: Docker & K8s #23Read
  324. 324Backend Deployment: Docker & K8s #24Read
  325. 325Backend Deployment: Docker & K8s #25Read
  326. 326Backend Deployment: Docker & K8s #26Read
  327. 327Backend Deployment: Docker & K8s #27Read
  328. 328Backend Deployment: Docker & K8s #28Read
  329. 329Backend Deployment: Docker & K8s #29Read
  330. 330Backend Deployment: Docker & K8s #30Read
  331. 331Backend Deployment: Docker & K8s #31Read
  332. 332Backend Deployment: Docker & K8s #32Read
  333. 333Backend Deployment: Docker & K8s #33Read
  334. 334Backend Deployment: Docker & K8s #34Read
  335. 335Backend Deployment: Docker & K8s #35Read
  336. 336Backend Deployment: Docker & K8s #36Read
  337. 337Backend Deployment: Docker & K8s #37Read
  338. 338Backend Deployment: Docker & K8s #38Read
  339. 339Backend Deployment: Docker & K8s #39Read
  340. 340Backend Deployment: Docker & K8s #40Read
  341. 341Backend Deployment: Docker & K8s #41Read
  342. 342Backend Deployment: Docker & K8s #42Read
  343. 343Backend Deployment: Docker & K8s #43Read
  344. 344Backend Deployment: Docker & K8s #44Read
  345. 345Backend Deployment: Docker & K8s #45Read
  346. 346Backend Deployment: Docker & K8s #46Read
  347. 347Backend Deployment: Docker & K8s #47Read
  348. 348Backend Deployment: Docker & K8s #48Read
  349. 349Backend Deployment: Docker & K8s #49Read
  350. 350Backend Deployment: Docker & K8s #50Read