C
Craig Kerstiens
craigkerstiens.com · Personal blog · on HN since 2011
Craig Kerstiens, a Postgres and Heroku veteran, writes about Postgres features, SQL tips, and startup practice.
What HN saysCommenters call his Postgres explanations concise and easy to follow, and cite his posts on CTEs, psql, and PR for startups.
101HN people shared it
17linked it in comments
203stories submitted
9.0Kstory points
2 years agolast shared
2011people per year2026
Most-shared posts
- 1Why Use Postgres5 people · 367 points · first shared 2012 · 144 comments on HN
- 2The best Postgres feature you're not using – CTEs aka WITH clauses5 people · 223 points · first shared 2013 · 77 comments on HN
- 3Redis in my Postgres4 people · 103 points · first shared 2012 · 22 comments on HN
- 4How Heroku Works - Maker's Day4 people · 57 points · first shared 2011 · 5 comments on HN
- 5Getting more out of psql (The PostgreSQL CLI)4 people · 13 points · first shared 2013 · 1 comments on HN
- 6Using Email as an Effective Tool4 people · 13 points · first shared 2019 · discussion on HN
- 7Give Me Back My Monolith3 people · 1.2K points · first shared 2019 · 411 comments on HN
- 8Why Use Postgres?3 people · 568 points · first shared 2017 · 225 comments on HN
- 9Dear Postgres3 people · 329 points · first shared 2017 · 59 comments on HN
- 10Better Database Migrations in Postgres3 people · 319 points · first shared 2017 · 85 comments on HN
- 11More on Postgres Performance3 people · 233 points · first shared 2013 · 42 comments on HN
- 12Unfinished Business with Postgres3 people · 165 points · first shared 2022 · 36 comments on HN
- 13How I work with Postgres – psql, My PostgreSQL Admin3 people · 149 points · first shared 2013 · 56 comments on HN
- 14A Guide to PR for Startups3 people · 135 points · first shared 2015 · 20 comments on HN
- 15Documenting your PostgreSQL database3 people · 113 points · first shared 2013 · 30 comments on HN
What HN people wrote when they linked it
neovintage · 2017-01-17
I would start with the Mode Analytics SQL tutorial. It's fairly robust and gets into advanced topics:
https://community.modeanalytics.com/sql/tutorial/introduction-to-sql/
That will get you pretty far but the advanced SQL topics will require the study of what the underlying database provides and how it works. Every database is different in terms of how it implements advanced features, if at all. For example, MySQL doesn't have window functions. For postgres related topics, the documentation is excellent, postgresguide.com gives a high level overview, or you can follow craig's blog (http://www.craigkerstiens.com/) that provides a gentle introduction to many of these topics as well.
rented_mule · 2025-01-25
Common Table Expressions enable a lot of composability. Using them can look like you're asking the DB to repeat a lot of work, but decent query optimizers eliminate much of that.
https://www.craigkerstiens.com/2013/11/18/best-postgres-feature-youre-…
craigkerstiens · 2020-11-16
This is a really handy list, but misses a few of my favorites.
The first is \e to open up your default $EDITOR for the query. In the past I've actually setup sublime text to open up my last run query, be able to edit it, close and save and it'll execute. I'd imagine you could do the same with vscode, though I'm currently just configured for vim personally.
The second isn't really psql commands, but that you can setup a psqlrc - https://www.craigkerstiens.com/2013/02/13/how-i-work-with-postgres/
kornish · 2017-06-13
Craig Kerstiens has a phenomenal set of blog posts about a few different Postgres topics to get you thinking: http://www.craigkerstiens.com/content/
It's really worth checking out all his stuff - his explanations are concise and easy to follow: http://www.craigkerstiens.com/categories/postgresql/
ozgune · 2016-09-22
Good read! I also found the following post useful and practical for getting press for startups: http://www.craigkerstiens.com/2015/07/21/An-intro-PR-guide-for-startup…
phunge · 2015-10-25
I had the same sentiment. I eventually ditched the gui and went back to psql (the CLI) based on this advice: http://www.craigkerstiens.com/2013/02/13/How-I-Work-With-Postgres/
The CLI is really really good for a CLI. Wagon is also good, my coworkers have been using it to pull data and like it a lot.
Overall love postgres, it's been 6 months (since starting a new job) and I'll never go back.
fphilipe · 2013-08-19
Craig Kerstiens from Heroku periodically posts great articles about PostreSQL [1]. They're all really worth a read, especially [2] which is in the nature of the original link.
[1] http://www.craigkerstiens.com/content/
[2] http://www.craigkerstiens.com/2013/02/13/How-I-Work-With-Postgres/
csears · 2013-01-18
Peter van Hardenberg (@pvh) and Craig Kerstiens (@craigkerstiens) have done many Postgres presentations over the last few years. I didn't see any specifically on lessons learned, but their blogs/quora/tweets have lots of good info based on their experience.
http://www.craigkerstiens.com/
http://www.quora.com/Peter-van-Hardenberg
buss · 2012-01-05
That is definitely an underlying problem. Interviews are too short and too stressful to accurately show how good a candidate is.
I'm not particularly good in interviews because I get nervous and stumble over my words. It is not at all like a real working environment, so I think any conclusions you draw from one are necessarily flawed. I recently read about Heroku's interview process, and I think it's great that they can invest so much in evaluating their candidates. They give each new candidate a project to work on and make a hiring decision after it's been completed. (See http://www.craigkerstiens.com/2011/12/02/how-heroku-works-hiring/). I also think it's unsustainable as their company grows but I would love to be proven wrong.
arthurcolle · 2022-08-25
A quick Google of this dude's handle/name and the first link is his blog with a reflective article about his experience at and subsequent departure from Heroku:
https://www.craigkerstiens.com/2022/05/18/unfinished-business-with-pos…
chishaku · 2022-02-09
What other examples are there of "single dependency stacks"?
This article is really about the versatility and reliability of postgres.
And I'm all in agreement.
Reminiscent of:
https://www.craigkerstiens.com/2017/04/30/why-postgres-five-years-late…
https://webapp.io/blog/postgres-is-the-answer/
http://rachbelaid.com/postgres-full-text-search-is-good-enough/
http://boringtechnology.club/
As much as HN could lead you astray with the hype of this and that tech, articles like the above are some of the most consistently upvoted on this website.
sbuttgereit · 2021-04-09
You can use the .psqlrc for this purpose, though I suppose you could also real that in via a file on demand, too. You make entries something like:
\set show_slow_queries
'SELECT
(total_time / 1000 / 60) as total_minutes,
(total_time/calls) as average_time, query
FROM pg_stat_statements
ORDER BY 1 DESC
LIMIT 100;'
(got from https://www.craigkerstiens.com/2013/02/21/more-out-of-psql/)
and then later at the command prompt you access it as; ":show_slow_queries".
I have a few system queries like this in my .psqlrc file, but I don't do much which this feature often truth be told. I have a pretty strong knowledge of the database schema I work with, I do a lot of work on systems I don't regularly control so its not dependably available, and readline history suffices for more immediate needs.
pjungwir · 2019-11-12
The Biggest Mistake Postgres Ever Made: http://www.craigkerstiens.com/2018/10/30/postgres-biggest-mistake/
theandrewbailey · 2019-04-02
It might be useful to include a link so people from here can spam it. Like this: http://www.craigkerstiens.com/2019/03/29/okrs-arent-going-to-fix-your-…
Zikes · 2015-07-30
Foreign Data Wrappers make that even easier: http://www.craigkerstiens.com/2012/10/18/connecting_to_redis_from_post…
bjt · 2014-12-04
At work we're in the midst of rolling out a sharded Postgres platform based on http://www.craigkerstiens.com/2012/11/30/sharding-your-database/, with the sharding implemented at the application level. The biggest piece of complexity in that post is around designing the sharding in such a way that you can gracefully add more shards later.
Having read the pg_shard readme, it's not clear to me how it addresses that issue. I'd need to have a really clear idea how to handle scaling my cluster before committing to a sharding solution.
ibotty · 2012-09-09
here is the link to part two:
http://www.craigkerstiens.com/2012/05/07/why-postgres-part-2