logo

drewdevault.com

[mirror] blog and personal website of Drew DeVault git clone https://hacktivis.me/git/mirror/drewdevault.com.git

In-praise-of-Postgres.md (5237B)

    

  1. ---
  2. title: In praise of PostgreSQL
  3. date: 2021-08-05
  4. outputs: [html, gemtext]
  5. ---
  7. After writing [Praise for Alpine Linux][0], I have decided to continue writing
  8. more articles in praise of good software. Today, I'd like to tell you a bit
  9. about [PostgreSQL][1].
  11. [0]: gemini://drewdevault.com/2021/05/06/Praise-for-Alpine-Linux.gmi
  12. [1]: https://www.postgresql.org
  14. Many people don't understand how old Postgres truly is: the first release[^1]
  15. was in July of 1996. It used this logo:
  17. [^1]: The first release of Postgre**SQL**. Its lineage can be traced further back.
  19. ![A "logo" which depicts the word "PostgreSQL" in a 3D chrome font bursting through a brick wall from space. No, seriously.](https://l.sr.ht/Ye7j.jpg)
  21. After 25 years of persistence, and a better logo design, Postgres stands today
  22. as one of the most significant pillars of profound achievement in free software,
  23. alongside the likes of Linux and Firefox. PostgreSQL has taken a complex problem
  24. and *solved* it to such an effective degree that all of its competitors are
  25. essentially obsolete, perhaps with the exception of SQLite.
  27. For a start, Postgres is simply an incredibly powerful, robust, and reliable
  28. piece of software, providing the best implementation of SQL.[^2]
  29. It provides a great deal of insight into its own behavior, and allows
  30. the experienced operator to fine-tune it to achieve optimal performance. It
  31. supports a broad set of SQL features and data types, with which I have always
  32. been able to efficiently store and retrieve my data. SQL is usually the #1
  33. bottleneck in web applications, and Postgres does an excellent job of providing
  34. you with the tools necessary to manage that bottleneck.
  36. [^2]: No qualifiers. It's straight-up the best implementation of SQL.
  38. Those tools are also exceptionally well-documented. The [PostgreSQL
  39. documentation][2] is *incredibly* in-depth. It puts the rest of us to shame,
  40. really. Not only do they have comprehensive reference documentation which
  41. exhaustively describes every feature, but also vast amounts of prose which
  42. explains the internal design, architecture, and operation of Postgres, plus
  43. detailed plain-English explanations of how various high-level tasks can be
  44. accomplished, complete with the necessary background to *understand* those
  45. tasks. There's essentially no reason to ever read a blog post or Stack Overflow
  46. answer about how to do something with Postgres — the official docs cover
  47. every aspect of the system in great depth.
  49. [2]: https://www.postgresql.org/docs/current/index.html
  51. The project is maintained by a highly disciplined team of engineers. I have
  52. complete confidence in their abilities to handle matters of performance,
  53. regression testing, and security. They publish meticulously detailed weekly
  54. development updates, as well as thorough release notes that equips you with
  55. sufficient knowledge to confidently run updates on your deployment. Their git
  56. discipline is also legendary — here's the [latest commit][3] at the time
  57. of writing:
  59. [3]: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=aa769f80ed80b7adfbdea9a6bc267ba4aeb80fd7
  61. ```
  62. postgres_fdw: Fix issues with generated columns in foreign tables.
  64. postgres_fdw imported generated columns from the remote tables as plain
  65. columns, and caused failures like "ERROR: cannot insert a non-DEFAULT
  66. value into column "foo"" when inserting into the foreign tables, as it
  67. tried to insert values into the generated columns.  To fix, we do the
  68. following under the assumption that generated columns in a postgres_fdw
  69. foreign table are defined so that they represent generated columns in
  70. the underlying remote table:
  72. * Send DEFAULT for the generated columns to the foreign server on insert
  73.   or update, not generated column values computed on the local server.
  74. * Add to postgresImportForeignSchema() an option "import_generated" to
  75.   include column generated expressions in the definitions of foreign
  76.   tables imported from a foreign server.  The option is true by default.
  78. The assumption seems reasonable, because that would make a query of the
  79. postgres_fdw foreign table return values for the generated columns that
  80. are consistent with the generated expression.
  82. While here, fix another issue in postgresImportForeignSchema(): it tried
  83. to include column generated expressions as column default expressions in
  84. the foreign table definitions when the import_default option was enabled.
  86. Per bug #16631 from Daniel Cherniy.  Back-patch to v12 where generated
  87. columns were added.
  89. Discussion: https://postgr.es/m/16631-e929fe9db0ffc7cf%40postgresql.org
  90. ```
  92. [They're all like this](https://git.postgresql.org/gitweb/?p=postgresql.git).
  94. Ultimately, PostgreSQL is a technically complex program which requires an
  95. experienced and skilled operator to be effective. Learning to use it is a costly
  96. investment, even if it pays handsomely. Though Postgres has occasionally
  97. frustrated or confused me, on the whole my feelings for it are overwhelmingly
  98. positive. It's an incredibly well-made product and its enormous and
  99. still-growing successes are very well-earned. When I think of projects which
  100. have made the most significant impacts on the free software ecosystem, and on
  101. the world at large, PostgreSQL has a place on that list.