[HN Gopher] Why I Enjoy PostgreSQL - Infrastructure Engineer's P... ___________________________________________________________________ Why I Enjoy PostgreSQL - Infrastructure Engineer's Perspective Author : shayonj Score : 102 points Date : 2022-01-17 21:10 UTC (1 hours ago) (HTM) web link (www.shayon.dev) (TXT) w3m dump (www.shayon.dev) | fabian2k wrote: | It's not exactly what the post is about, but transactional DDL is | a feature I don't actually know how to live without. The idea | that schema changes could fail somewhere in the middle is | terrifying, and I'm quite happy Postgres just solves this. | mulmen wrote: | Having worked with Oracle it is indeed terrifying. You can make | it safe-er but it's a lot clunkier and you have to think it | through. With Postgres it mostly just works. | shayonj wrote: | Love transactional DDL! Have only used on side projects for | now. | tofflos wrote: | I thought an infrastructure engineer would be more interested in | the provisioning process of setting up and configuring | authentication, backups, clusters, monitoring, replication and | such. The features listed in the article cater more to a database | administrator. | xchaotic wrote: | Same impression- no discussion on fault tolerance, HA and | infrastructure in general. | shayonj wrote: | thats a good call out - i suppose infra eng can mean | different things. This was a bit more operationally focused i | agree. I was thinking of talking about HA and replication, | but also wanted something relatively short, perhaps another | post :). Thanks for reading. | jedberg wrote: | Just like your home, as long as you remember to VACUUM | frequently, PostgreSQL will treat you well. | macNchz wrote: | Indeed in ~10 years of administering postgres dbs from 0 - 10TB | the only issues that have approached "nightmare" status were | from punting vacuum jobs on large/fast growing tables until it | got to be too late. | shayonj wrote: | I like that analogy :) | fabianlindfors wrote: | Great article! For anybody interested in this topic, I've been | working on a schema migration tool which automates zero-downtime | migrations using many of the techniques mentioned: | https://github.com/fabianlindfors/reshape. It also uses some | other incredible Postgres features, like updatable views and | schemas. | | It was discussed here on HN about a week back: | https://news.ycombinator.com/item?id=29825520 | iratewizard wrote: | I would add to this list how Postgres makes solving problems in | side projects fun. Write a little pl/pgsql rather than building a | trigger or service worker system. Play with all the postgis stuff | when you're messing around with maps. I've never felt frustrated | when toying around and having Postgres playing support. | shayonj wrote: | I have definitely seen that as well. | | I have yet to play with postgis specifically, i have read many | good things. Thinking of un-archiving some old side projects | and experimenting with postgis! | skeletal88 wrote: | If you get your GIS data into PostGis, then you should try | QGis, to visualize it. It helped also fix some bug and find | some unexpected stuff, for example - that in postgis (and gis | databases in general?) the order of coordinates is different | from what you expect from using a gps in everyday life. | | Now.. which order was the correct one? I have forgotten after | not doing gis stuff for some years now. | code_biologist wrote: | The coordinate order is (X, Y) just like in math (and XYZ | if you want to use a vertical coordinate) which differs | from our colloquial (latitude, longitude). This is easier | to remember if you remember PostGIS supports many non- | lat/lon non-WGS84 coordinate systems. | code_biologist wrote: | PostGIS is amazing. The types of queries you can write | continually blow my mind. One warning is that ultra fancy | queries can be pretty slow if you're going beyond a million | rows. Some tips: | | * Materialized views with indexing are an easy way to solve | many speed issues where you'd like to use a fancy query | quickly. | | * The GEOGRAPHY data type is great for data integrity, but | often slower for queries. I've made sure our primary data is | stored as GEOGRAPHY then with expression indices [1] on | casting to GEOMETRY then done spatial joins and filters on | the casted column where fast queries are needed. | | * Source data is often very high resolution. If you don't | need it, simplifying high accuracy data (1m or whatever) to | something much lower resolution (500m, 1km, or whatever) in a | derived view or table using PostGIS' simplification functions | can greatly improve spatial predicate performance. | | [1] https://www.postgresql.org/docs/14/indexes- | expressional.html ___________________________________________________________________ (page generated 2022-01-17 23:00 UTC)