7 ms·

I've been building a Postgresql extension in the last months for some functionality that was needed and have learned a ton about the internal workings of this d

I've been building a Postgresql extension in the last months for some functionality that was needed and have learned a ton about the internal workings of this database. All very scary and complicated sounding stuff but I feel privileged to be able to do this because the things you learn are just pure gold. My attitude before this was that of the ideal customer of a cloud database, someone who was scared of sql and preferred to hide behind the complexity of a ORM. Not anymore, now I write thousands of lines of sql and laugh to myself like a maniac.

nonethewiser 3y ago
That’s a big jump - hiding behind ORMs to learnings some inner workings of Postgres. Do you have an example of something that you previously saw as a black box but now understand? Perhaps something simpler than you anticipated, or just something you never even knew existed before digging into the details?
- KrugerDunnings 3y ago
  There is just so much, I've thought of writing some blog post about it but there is already a lot of content out there because pg is a big community with a lot of people doing interesting things and I don't know if what I know is unique and not just parroting others peoples stuff. But ok sure I can give you some pointers to cool stuff. Sometimes it are just architectural patterns that are easy to do like upsert statements in sql using `on conflict`, or writing a task queue on the cheap using `for update skip locked limit 1`. Replace 90% of your crud api controllers with `postgrest` and use row level security for everything, AI is not going to steal your job category theory is. From a operations and scaling perspective it pays dividend to learn about `explain analyse` but did you know they also have `stats` that can help the analyser optimise queries based on statistical relations of different columns. I've also been looking into how to "branch" a database instead if just doing a backup, it does require some ZFS tricks but that is also just pure power and not something you find with a cloud file system. There are also just a ton of extension for timeseries or vector embeddings, or write your own in Rust using `pgx`. Like I said way too much to write here, I don't use all of this in production but I just keep finding these gems while working on my own project. There is a strong OSS ecosystem with lots of teams giving there own spin on pg and that is welcomed if you have a opinion of your own and still want to learn from others. You need motivation to go this deep but in contrast to other esoteric knowledge I have mastered companies are also willing to pay for it.
  - jrumbut 3y ago
    Yeah a number of pieces of software people use after a 5 minute tutorial are like this, a ton of depth that we frequently recreate crappy versions of within our apps. The Apache webserver (and most other webservers) is another example like Postgres where it has a ton of useful features no one uses. Some of these less popular features don't work at massive scale but in my experience they work great for smaller teams where adding a new tech to the stack is more painful.
  - bjornsing 3y ago
    > I've also been looking into how to "branch" a database instead if just doing a backup, it does require some ZFS tricks but that is also just pure power and not something you find with a cloud file system. In the cloud you can just snapshot/clone the underlying EBS volume and you’ll get a “branch” of any database on any file system, right?
    - muskmusk 3y ago
      Assuming no-one is using the database yes. Also not trivial to set up and use plus vm startup is somewhat slow. If your want a full featured product (in the postgres space) that does it then look at neon.tech (no affiliation).
  - kiwicopple 3y ago
    for what it's worth, what you describe is the architecture philosophy of supabase (disclosure: i'm the ceo) supabase is essentially a Postgres database with PostgREST on top, and we recommend pushing down a lot of the logic and security into the database. We took this philosophy with our pg_graphql extension (which uses pgx) and it is faster than other graphql implementations, simply it's co-located with your data, solving the n+1 problem. pl_rust just reached 1.0, and it is now a "trusted language" so you can expect to see it arriving on a few cloud providers soon. We are releasing something this week with the RDS team which will make it easier to write key parts of your application code in trusted languages. There are certainly trade-offs, and I don't know if _everything_ should be in the database. But in data-intensive cases it makes a lot of sense.
juancn 3y ago
Databases are worth the time to understand deeply. Postgres in particular is a magnificent beast.

nonethewiser 3y ago

That’s a big jump - hiding behind ORMs to learnings some inner workings of Postgres. Do you have an example of something that you previously saw as a black box but now understand? Perhaps something simpler than you anticipated, or just something you never even knew existed before digging into the details?

KrugerDunnings 3y ago
There is just so much, I've thought of writing some blog post about it but there is already a lot of content out there because pg is a big community with a lot of people doing interesting things and I don't know if what I know is unique and not just parroting others peoples stuff. But ok sure I can give you some pointers to cool stuff. Sometimes it are just architectural patterns that are easy to do like upsert statements in sql using `on conflict`, or writing a task queue on the cheap using `for update skip locked limit 1`. Replace 90% of your crud api controllers with `postgrest` and use row level security for everything, AI is not going to steal your job category theory is. From a operations and scaling perspective it pays dividend to learn about `explain analyse` but did you know they also have `stats` that can help the analyser optimise queries based on statistical relations of different columns. I've also been looking into how to "branch" a database instead if just doing a backup, it does require some ZFS tricks but that is also just pure power and not something you find with a cloud file system. There are also just a ton of extension for timeseries or vector embeddings, or write your own in Rust using `pgx`. Like I said way too much to write here, I don't use all of this in production but I just keep finding these gems while working on my own project. There is a strong OSS ecosystem with lots of teams giving there own spin on pg and that is welcomed if you have a opinion of your own and still want to learn from others. You need motivation to go this deep but in contrast to other esoteric knowledge I have mastered companies are also willing to pay for it.
- jrumbut 3y ago
  Yeah a number of pieces of software people use after a 5 minute tutorial are like this, a ton of depth that we frequently recreate crappy versions of within our apps. The Apache webserver (and most other webservers) is another example like Postgres where it has a ton of useful features no one uses. Some of these less popular features don't work at massive scale but in my experience they work great for smaller teams where adding a new tech to the stack is more painful.
- bjornsing 3y ago
  > I've also been looking into how to "branch" a database instead if just doing a backup, it does require some ZFS tricks but that is also just pure power and not something you find with a cloud file system. In the cloud you can just snapshot/clone the underlying EBS volume and you’ll get a “branch” of any database on any file system, right?
  - muskmusk 3y ago
    Assuming no-one is using the database yes. Also not trivial to set up and use plus vm startup is somewhat slow. If your want a full featured product (in the postgres space) that does it then look at neon.tech (no affiliation).
- kiwicopple 3y ago
  for what it's worth, what you describe is the architecture philosophy of supabase (disclosure: i'm the ceo) supabase is essentially a Postgres database with PostgREST on top, and we recommend pushing down a lot of the logic and security into the database. We took this philosophy with our pg_graphql extension (which uses pgx) and it is faster than other graphql implementations, simply it's co-located with your data, solving the n+1 problem. pl_rust just reached 1.0, and it is now a "trusted language" so you can expect to see it arriving on a few cloud providers soon. We are releasing something this week with the RDS team which will make it easier to write key parts of your application code in trusted languages. There are certainly trade-offs, and I don't know if _everything_ should be in the database. But in data-intensive cases it makes a lot of sense.

juancn 3y ago

Databases are worth the time to understand deeply. Postgres in particular is a magnificent beast.