4 ms·

Postgres Hidden Gems

amerine 9y ago
I love Craig’s posts. I wish we had more interactions @ Heroku! ️
- craigkerstiens 9y ago
  Aww, thanks. Always happy to swing by the office and catch up when you're in town.
qatanah 9y ago
Something to add on the list base on my experience. FILLFACTOR - For UPDATE heavy tables. Increase max_wal_size - For write heavy database.
Klathmon 9y ago
I'm going to echo the comment about aggregate functions. I don't know how "hidden" they are, but they are one hell of a gem! At this point our one application has more SQL code in it than application code. The postgresql json* aggregate functions make querying and formatting data easy enough that it can be done directly in the database and the result basically piped to the output without any need to reformat it. I'll also add CTEs. They might also not be very "hidden" but they are fantastic once you get used to using them.
- sk5t 9y ago
  CTEs are very nice, although their performance characteristics push me over to lateral joins and subqueries by default.
  - Klathmon 9y ago
    lateral joins were already in the list so I skipped them, but I agree. I tend to reach for CTEs first as they are more readable in my opinion, but when there are perf issues, lateral joins are the way to get out of them. They almost feel like cheating!
    - garyclarke27 9y ago
      I agree Lateral Joins are amazing, Ive started using them instead of CTEs a lot recently. Set returning functions are automatically lateral joined and make the logic much easier to follow than sub queries. Performance is great because Postgres happily inlines most SQL functions - unlike my experience with SQL Server (several years ago may have changed) where UDFs where munch slower than sub queries.
dizzystar 9y ago
Definitely a huge fan of window functions. I once read a comment that there are two eras of SQL: Before Windowing and After Windowing, and I have to agree with that. Not mentioned, but pg_x and information schema is a probably my favorite somewhat-hidden feature. It really let's you dive into analysis and can help get out of little binds very quickly. It's not something I use every day, but I'm always glad it's there.
- Klathmon 9y ago
  My favorite usage of the information schema stuff was with unit testing. We have a VERY small ORM-ish library called PG-Promise that we use in a node.js application. It's basically just a nice interface to the database, but it does include some small functionality where you can define the database layout in javascript and it will auto-convert most of your data from JSON to the correct columns and in many cases types. We wrote tests that use information schema to compare those objects and ensure that there aren't any missing or extra on either side, and to make sure that the javascript types match the database types.
MightySCollins 9y ago
Disappointed that there is not a Postgres hidden game which is what I thought when reading the title.

I love Craig’s posts. I wish we had more interactions @ Heroku! ️

craigkerstiens 9y ago
Aww, thanks. Always happy to swing by the office and catch up when you're in town.

Something to add on the list base on my experience. FILLFACTOR - For UPDATE heavy tables. Increase max_wal_size - For write heavy database.

Klathmon 9y ago

I'm going to echo the comment about aggregate functions. I don't know how "hidden" they are, but they are one hell of a gem! At this point our one application has more SQL code in it than application code. The postgresql json* aggregate functions make querying and formatting data easy enough that it can be done directly in the database and the result basically piped to the output without any need to reformat it. I'll also add CTEs. They might also not be very "hidden" but they are fantastic once you get used to using them.

sk5t 9y ago
CTEs are very nice, although their performance characteristics push me over to lateral joins and subqueries by default.
- Klathmon 9y ago
  lateral joins were already in the list so I skipped them, but I agree. I tend to reach for CTEs first as they are more readable in my opinion, but when there are perf issues, lateral joins are the way to get out of them. They almost feel like cheating!
  - garyclarke27 9y ago
    I agree Lateral Joins are amazing, Ive started using them instead of CTEs a lot recently. Set returning functions are automatically lateral joined and make the logic much easier to follow than sub queries. Performance is great because Postgres happily inlines most SQL functions - unlike my experience with SQL Server (several years ago may have changed) where UDFs where munch slower than sub queries.

dizzystar 9y ago

Definitely a huge fan of window functions. I once read a comment that there are two eras of SQL: Before Windowing and After Windowing, and I have to agree with that. Not mentioned, but pg_x and information schema is a probably my favorite somewhat-hidden feature. It really let's you dive into analysis and can help get out of little binds very quickly. It's not something I use every day, but I'm always glad it's there.

Klathmon 9y ago
My favorite usage of the information schema stuff was with unit testing. We have a VERY small ORM-ish library called PG-Promise that we use in a node.js application. It's basically just a nice interface to the database, but it does include some small functionality where you can define the database layout in javascript and it will auto-convert most of your data from JSON to the correct columns and in many cases types. We wrote tests that use information schema to compare those objects and ensure that there aren't any missing or extra on either side, and to make sure that the javascript types match the database types.

MightySCollins 9y ago

Disappointed that there is not a Postgres hidden game which is what I thought when reading the title.