[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)