Did anybody ever ask you: what are Postgres’ best practices regarding this? ” Even if you are not a consultant (because if you are, it’s your job to answer questions like this) but a humble human working with Postgres, chances are you’ve been asked this question more than once.
This question may cover various topics, from high availability and backup tools to the optimal number of indexes on a table or correct schema usage.
When I am asked such questions, I rarely know what to answer. Who defines, what are “Postgres’ best practices?” Is there any Postges Wiki page that lists “best practices”? (Actually, there are a couple of such pages in Postgres Wiki, but for a limited number of topics)
There are many aspects of PostgreSQL where I have a strong opinion on how things should be done. Most times, these opinions come from both knowledge of internals and practice, but often from practice only. If I have no practical experience with certain things, I ask people who have done it before. Also, there were cases when people told me what I was doing was wrong because it was “against Postgres’ best practices.” To that, I usually reply that if I am doing it, it’s Postgres’ best practice :). At the same time, when I read somebodies recommendations that categorically disagree, I rarely pick up the fight since I rarely can offer any argument except for “I had a different experience.”
What do “Postgres’ best practices” mean to you? Who or what would you consider the authority to dispense “best practices”? What are some best practices which you follow? Did you ever have to go against best practices? Do you ever judge other people’s technical solutions based on that criteria?
Please share your thoughts!
The World of Data 
Catholic theologians distinguish two modes of revealed truth, scripture (organized over the centuries into the biblical canon) and tradition (transmitted teachings that contextualize, reinforce, and build on scripture and are held to have equal weight with it). Without getting needlessly sacrilegious I think the parallels are obvious 🙂
I think best practices are constructed by users, administrators, designers, and contributors in a comparably unsystematic way. Unlike Catholic tradition, ours has no central authority founded on the apostles, jokes about “so help me Codd” aside; there is a central group at work, but they can’t tell you anything ex cathedra about how much RAM you need or whether you should use a custom type for that.
Postgres’ best practices have an ultimate source in the code, docs, standards, and environmental considerations of running DBMSs, but the feedback loops involved have long since achieved liftoff and become a self-sustaining system of knowledge that now both informs and is informed by Postgres development.
Someone entering the space now would pick up ideas like “use TEXT unless you really need a length constraint” or “default work_mem assumes you’re deploying on a toaster” that _could_ be synthesized with complete understanding of those ultimate sources. Nobody has the brainspace for that, though, so instead tradition develops through individual experience and study then just kind of floats around the noosphere. Occasionally someone will pin a bit of it down to answer a question, explain something they saw, propose a useful way of doing something, or even to argue against it under new or different circumstances — continuing the process.
Sometimes also tradition falls out of date! Adding a non-nullable column with a default value hasn’t required a full table rewrite since Postgres 11, but I still occasionally act & see others act like it’s a live wire. I think it’ll be a while yet before that one filters all the way out.
Dian, i LOVE it!!!! As always, I want your answer to be framed😀❤️. Do I have your permission to repost it as a separate post???
sure, go for it!
Well my answer is obvious, since I maintain a list on my website and my twitter profile 😉
Thanks! For how long, do you think you maintain this list? Did you ever feel a need to modify it?
Pingback: A Follow-up to my “best practices” post. | The World of Data
I have come to believe that “best practice” is a business speak buzzword.
People who ask for best practices really mean: “I don’t want to understand this, so please don’t explain it. Instead, give me a cookbook I can follow without thinking.”
I prefer to call that “rule of thumb”, and I keep encouraging the people I teach not to follow them blindly.
Some rules of thumb I follow:
– shared_buffers = min(8GB, available RAM / 4)
– shared_buffers + max_connections * work_mem * 2 <= available RAM
Some rules of thumb are just myths, like the one that you should put tables and indexes on different disks.
Thank you so much for you response! I think you are right to “translate” the “best practices” question as “do not make me think.” Also, you are right about myths. My husband coined the following definition for “myths” in IT: Myths represent facts which were true or could be true under certain circumstances in the past and not true any more or under different circumstances. I think that your example fits into this definition perfectly :).
Here is one more question I have. What do you think about going “against best practices”? When somebody suggests doing something which is generally “not recommended”? What would be your criteria for deciding whether it’s worth trying or not?
Since I don’t hold best practices in high regard, I see no problem with not following them. If somebody has good technical reasons to do something a certain way, that is much better than following what most other people do. After all, no rule of thumb is appropriate in all cases.
What I find more reliable in practice are certain meta-rules, like “keep it simple”, “don’t use software for something that it is not intended for” or “if something is difficult, you may be able to move the difficulty from one part of the system to another, but you will never get rid of it”.
I love that last one!