Guiding Principles of Relational Data Modeling

by

Since 1997 when I first wrote a set of documents to capture principles and checklists for good data modeling, I have carried them with me to every employer and client I have worked for. Every one of them needed this set of guides and standards. The software and data engineers have greatly benefited. Well, rather than keep them to myself, I’ve decided to share these with the public, in hopes they will help your data group to produce better data deliverables. Please comment below if you disagree and why, see text that needs improvement, or know a guiding principle that is missing.

Really Know Your Data

This is the most important principle, period. If you are curious about all aspects of the data you are working with, for which you are designing storage and retrieval mechanisms, you will become indispensable. You will be amazing at modeling, database development, queries and performance tuning.

A good engineer and architect is not content focusing on their little keyhole view of the system landscape. They want to know everything about the system they are a part of. And when it comes to the entity presently being designed, they want to know every nuance about every attribute. For example:

  • What kind of data will be captured? How much data will there be?
  • Is there a minimum or maximum length of the attribute?
  • What will be kept in every column? What values will be expected?
  • Will the users enter the data manually, or is the field fully controlled by the system?
  • Is the column value required or optional?
    • If it is required and the user doesn’t supply it, what is the default value?
  • How long will the data be kept?
  • Are there legal, financial or privacy concerns?
  • And so forth…

This thirst for knowledge about the data under your stewardship — and how it plays with the data in other systems where you are not the steward — is critical. See the Business Analysis doc (future post) for more hints and prompts about the sort of questions that are asked by a curious data designer. Knowing your data will help you make good design decisions now, and later, make you indispensable as a query wizard.

Model First. Model Cleanly. Model Thoroughly.

Model cleanly means to follow basic relational design principles and to not compromise or accept technical debt in this area. In essence, “do it right the first time.” It is just not worth it taking shortcuts on the system’s foundation for convenience or deadlines.

If the data foundation starts out compromised, it isn’t even suitable for the initial phases of the project, let alone enterprise-grade usage, expansion and growth of future phases. A poor data foundation will cause nothing but increasing pain and money as it ages.

It is far less costly to experiment, cut corners, and take risks with the exterior treatments, the user-friendly façade of a system. NEVER experiment or cut corners with the data foundation. Here are some structures where the foundation wasn’t properly prepared. The same WILL happen with software systems built hastily on a poor data layer.

On the other hand, if you start with a solid, clean data model, it is easy to build, re-build, extend and re-model on top of it later when needed.

The third level of normalization (3NF or BCNF) is usually sufficient for a clean model. Less than 3NF is lazy and reckless. More than that (4NF, 5NF, 6NF) runs into the law of diminishing returns, and results in models that are too complex to comprehend easily.

Once the model is clean and the database platform has been selected, proceed to the physical model, adding indexes, surrogate keys, constraints, historical tables and audit columns. These items support and optimize data queries, processing, and maintenance. Continue to avoid redundancy as much as possible.
After your base data model is solid, reviewed and tested, use it.

  • If the model is complex enough that it is difficult for developers to write joins, do the joins for them: write views that pre-join the harder parts of the model. Help them with their embedded and generated SQL
  • If the model is too slow for performance requirements, and other options have been exhausted (optimizing hardware, memory, database configuration, statistics, SQL tuning, etc.) THEN consider denormalization. The best denormalization options are those where the database automatically keeps the duplicates in sync for you, in real time, or periodic refreshes if real-time is not required. Oracle’s materialized views with mv logs and refresh on commit are a good example of auto-managed denormalizations. If you are going to duplicate data to improve performance, ensure that robust mechanisms are used to keep the duplicates immediately or eventually up-to-date.

Model thoroughly implies taking ownership and caring about doing a fantastic job. If you grok the current state of the business, and anticipate where it is going, your data model is much more likely to age gracefully and support the business well into the future.

If this is not done, you will produce a clean data model that is a dumpster fire for the business. A large part of modeling well is wrapped up in the first principle above, Really Know Your Data. Therefore:

  • Ask lots of the right questions to ensure that the business and data requirements are complete, and your model meets the needs of the system.
  • Capture and document all the entities, attributes, relationships, business rules and “tribal knowledge” mentioned in conversations, meetings, whiteboards, chats, emails and documents.
    • Collaboration spaces like OneNote, OneDrive, Teams and ADO can be used to store this documentation. However, with key players hiring on and leaving all the time, the documents will likely get stale quickly and fall into disrepair. The only place the documentation stands a slight chance of staying updated is in the object comments that are kept with the table, view, and column in the database. So ensure that every table, view and column has a full, well-written comment that is injected into the database with the COMMENT ON {table}.{column} IS ”; syntax.

Model first is a mindset that applies to database change management. It means new data structures and changed data structures all begin in the data modeling tool upon the versioned data model file, not manually altered in the database or in hand-crafted DDL scripts.

In an ideal world, all database changes would begin with the data architect for your project. These changes would be entered in the modeling tool, validated against existing standards, committed to the enterprise data model repository, and then forward engineered into DDL scripts for your Flyway folder. The DDL scripts can then be tested in development, then committed to the Flyway folder for release to higher environments.

When this model-first culture is achieved, we will be able to:

  • generate and validate data glossaries and data lineage
  • build and maintain slick diagrams and documentation from the modeling tool for publishing, reference and discussions
  • compare data models from prior releases to see changes over time
  • generate data model change release notes
  • maintain a much cleaner information model
  • re-use common entity, attribute, and domain definitions across models in the same enterprise
  • perform cross-system dependency analysis
  • drive a data structure change request from initial requirement to production-ready in 10 minutes (data management can be Agile too) and much, more more.

To some “model first” sounds like a fairy tail, just an unattainable fable. But I have built and lived in a model-first culture twice before, and it was awesome! It is do-able, and it is well worth the effort.

Duplication is a Plague

Period. It just plain curses you to unending headaches… unless you have very robust self-healing, self-reporting, automated mechanisms to keep the various copies in sync with the master record.

Each unique fact should not be found more than once in the database.

As I write this at home, there are three digital and analog clocks within view.

Every one of them is showing me a different time! Immediately I distrust all of them and have to check my phone, which talks to a more trusted network-connected master system. In essence the clocks in my home have become unreliable decorations, almost useless approximations of the truth.

When business users encounter inconsistency in the data you manage for them, the same thing happens. If not caught in time, account and business-ending decisions can be made. Goodwill and trust are then lost in you and your data. This trust is hard to regain.

The primary enemy of consistency and accuracy is duplication.

Duplicating a piece of data increases the points of failure. By failure I mean the very real possibility of data getting created, updated or removed in one place, but not in another. This likelihood increases as time passes, systems accumulate layers, and tribal memory is lost.

This topic deserves its own white paper. We could discuss scores of examples of well-meaning duplication that ended badly, examples I’ve witnessed in the last 25 years, and that was on fairly clean and well-designed systems!

Avoid duplicating entities and attributes at all costs…until you are forced to duplicate. If you must duplicate, do it well and ensure the copies are kept in synch with the master.

Name Things Well

Whenever I’ve spent time agonizing over the name of a entity or an attribute, I end up questioning the value of the time spent. How can something as trivial as naming something take so much time?

A well known quote from the late VP of Netscape, Phil Karlton, warns us “There are only two hard things in Computer Science: cache invalidation and naming things.” In the thick of it, giving something a good name seems trivial, but it is not.

Another famous quote by Donald Knuth helps explain why good names matter: “Programs are meant to be read by humans and only incidentally for computers to execute.”

A well-named object pays for itself many times over. A poor name might be unreadable, misleading, opaque, confusing, incomplete or even the opposite of what it actually contains. Once a database has a few handful of these, it starts to feel like you’re living in a shabby neighborhood with absentee landlords. Every time you try to explain the model to someone, you have to punctuate your explanation with caveats, warnings and apologies.

It IS worth it to name your tables and columns well, to accurately represent what they contain.
Good naming is more an art form, than a science. It requires creativity, practicality and a good handle on the English language.

I’d like to share a little tip that has helped me: Read the table/column comments out loud to yourself. Obviously if there is no documentation for the object you are naming, that needs to be obtained first. Once the item has been fully understood and documented, then a good name organically materializes. About once a year I’ve had to rename a column which I named poorly during the initial design. Literally every time this has happened, I found the better name hiding in the column comments. If I had simply paid better attention to the very documentation I had gathered, it would have been named right the first time around.

See the Naming Guide (future post) for a better treatment on this subject. It has a single-page naming syntax cheat sheet which can be printed and referenced until it is memorized and part of you.

Keep it Simple

“Simplicity is the most difficult thing to secure in this world; it is the last limit of experience and the last effort of genius.” – George Sand
“Simplicity is prerequisite for reliability.” – Edsger Dijkstra
“If you can’t explain it to a six-year-old, you don’t understand it yourself.” – Albert Einstein

A data model diagram and the content it depicts should be fairly easy to grasp, even by non-technical people. If you are having a hard time understanding the model, much less explaining it, or writing queries against it, the design is not done yet. Difficulty or complexity usually signifies the requirements were insufficient, the model was over-engineered, or there are modeling mistakes. With enough experience, you’ll keep refining a model until it “feels right.” See the principle of modeling cleanly above. When a model is not clean, it looks and feels bloated, complex, dirty. Viewing the model in a diagram is an enormous help because it can quickly show you where foreign keys or unique constraints were missed, where columns should have been NOT NULL, where circular relationships are found, etc.

Revisit and refine the model until it is simple. Over time I have found that this state is not reached until at least the fifth iteration. It takes time to do it right. If you have to invest some extra time and dollars, do it here; invest in the application’s foundation for short and long-term paybacks.

Keeping things simple and avoiding duplication are related to the “DRY” principle of software engineering, which states “Every piece of knowledge must have a single, unambiguous, authoritative representation within a system.” (Hunt and Thomas)

A satirical article about duplication not being “all that bad” referred to the new NoSQL world of spreading data everywhere redundantly as the “WET principle”, which stands for “Write Everything Thrice”, “We Enjoy Typing” or “Waste Everyone’s Time.”

  • Everything should have a home.
  • Put things where they belong.
  • Reduce and re-use.
  • Ensure each entity and attribute and function does one thing, and one thing well.

Bonus Principles

These additional principles have served me well over the last two decades of data architecture and design for large and small companies:

Protect Your Data

One of the primary reasons that databases exist is to protect the integrity and accuracy of critical data. Ignoring integrity constraints is like building a supercar without dashboard, steering or brakes, leaving you with just the engine and a pretty shell. It’ll go, but be hard to steer, brake and protect the passenger.

  • Use foreign key constraints, ensuring only valid values are allowed in a column.
  • Use primary key constraints for the surrogate keys, and unique constraints on the natural keys.
  • Use check constraints, default values, and NOT NULL constraints.
  • Define the columns with the right data type and constrain the maximum length (if applicable).
  • Secure the data (this could be a book unto itself, so I’ll just leave this here for further expansion).

Unleash Your Database

Too many treat their database like a hole in the ground, only useful for dumping data. This is akin to purchasing a fine sports car, but never leaving 1st gear. Use the full capabilities of your database. Let your database do what it does best. There is no need for an application to write its own mechanisms to store, join, sort, cache, search, validate, or protect the data. This is the job of the database. It has been coded and optimized by hundreds of software engineers. There is no need to attempt to replicate their efforts on your own.

Of course, this is not carte blanche to use every new feature the database releases. Put new features through appropriate testing before adopting them.

Design to End Goals from Day One

Determine the security, performance, functionality, flexibility and data quality goals of your system before beginning. These things cannot be stapled onto the data model right before release. They must be the driving forces behind your design sessions from day one. Automated, round-trip, and agile development tools and processes are making late-stage changes easier, but it will still cost less to design properly the first time around.

If you had to choose one phase of your project on which to expend extra time, it should be during data model design. Infuse the model with features that meet system goals from the start, not after trouble comes along in production.

Job Loss, Anxiety & Sleeplessness

by

My whole life I’ve enjoyed a sense of who I am, what I wanted to accomplish, and how to get there…until Oct 7th, 2014. On that day I went through my first and only panic attack. It was related to losing half my account in the foreign exchange market. I had “lost” half of my account many times before in the two years of practicing before going live with real money. And I had lost half of my real account twice before this day. But this day was different. Something broke inside me. The panic attack was so far outside of my normal, I was sure I was having a heart attack and went to the hospital to get an EKG and examination. Scary stuff.

On that day, I lost my mojo. That wind beneath my wings, compelling sense of purpose, vision and clear direction…all gone in an instant. It is still a mystery to me, why that would all disappear with the only panic attack I’ve ever experienced, but it did.

This was followed by ever-present anxiety that slowly ebbed as the days passed. That week I tried everything to deal with the heavy foreboding, racing heart and sleeplessness that followed. It was rough. I did what I could with the tools I had, things that had brought me peace, joy or contentment in the past:

  • Listened to “Desires of the Heart” by Chris Spheeris and other quieting, peaceful, instrumental music
  • Cleaned and organized things around the house (small daily victories)
  • Took walks and frequently engaged in deep breathing
  • Dug around in my garden’s dirt, cleaning things up after fall harvest
  • Took bike rides, and did sets of pull ups and push ups (more small, daily victories)
  • Asked for hugs, and gave more hugs with my wife and children
  • Meditated, prayed and talked to my Creator/Father in Heaven
  • Even had a cry which I found to be surprisingly relieving
  • Talked to my mother, my siblings, my wife and a few male friends
  • Started keeping a gratitude journal, reaching deep in my memory to see the blessings

With persistent application of some additional behaviors and practices I learned about, as well as patience and time, over the next three months it gradually lessened to mild anxiety, and then finally dwindled back to almost nothing. What a relief!

Since then anxiety and sleeplessness only rear their heads when yet another great employer decides I’m too expensive in a cost-cutting effort and lays me off. This has happened far too often recently, but each time I feel more resilient.

Here’s where this blog post makes sense on a site about data and databases: After the second job loss in 2020 , I was hit hard mentally and emotionally. Luckily I learned how to use LinkedIn far better at that time, and my prior CIO referred me to my next CTO, which meant I only spent three weeks job hunting. What a blessing that was! I chose to take a “step down and sideways” to learn AWS services, Cassandra, Python, Athena, Kafka, Spark, dimensional modeling and data lakehousing/data warehousing, an area of my career that had been missing. The new employer had good people, a great future, fantastic nearby office, view of the mountains, and fully-paid benefits. Best of all, I was allowed to come into the office and have human interaction again, something I had really missed since Covid struck. I was pretty happy and excited to be there. The brilliant data architect, who wrote the entire data aggregation and analytics infrastructure from scratch, was training me. It would take at least a year, maybe two, to learn everything about this business, its data nuances, and all the code the architect had written. Then he quit 3 months into my new job, leaving me with a small team to hold it all together. It was as if a Boeing 747 pilot had decided to parachute out the plane and left one of the passengers in charge of the cockpit’s instruments, tasked with continuing the flight as-is and getting everyone home safely.

Anxiety hit me heavily for many weeks after he quit. It was very difficult to sleep with so much dread and worry on my plate, with no way or time to figure everything out he had built, on technologies and programming languages I hadn’t yet trained in. My manager, Jed Reisner, was very understanding and had some additional suggestions to deal with the anxiety and lack of sleep:

  • Binaural beats music
  • Guided meditation
  • Counseling
  • Hobbies

The music was very pleasant, and useful, and helped me sleep well the first night I tried it. The second night I tried the meditation from Berkeley and fell asleep right away. The counseling and hobbies would come a couple months later as I was recovering from knee surgery at the time and wasn’t ready for either of those. With Jed’s suggestions that worked immediately, my tool belt now felt complete. And I have been dipping into those tools each time low-grade anxeity has come sniffing around my tent again, or with each new job search and life change.

These tools and practices helped me a great deal, so I would like to share my findings and tips with you, especially if you are dealing with feeling lost, confused, dissatisfied, depressed, aimless, sleepless, or not at peace due to anxiety, worry, fears, general dissatisfaction, job loss, illness or life changes. It is my hope one or more of these will help improve things for you.

The human organism is so complex, and our circumstances so different, I can’t guarantee that what has worked for me, will work for you. Despite that, It would be selfish if I did not share what I’ve learned.

Some Things to Try

The following sections are what worked for me as I tried things to lessen anxiety, fear. and sleeplessness during times of self-doubt after losing employment.

2023 Feb 06 Update: Netflix recently released a documentary, a conversation between actor/director Jonah Hill and this therapist, Phil Stutz. The Observer published a fantastic article, chock full of studies and references, agreeing with Dr. Stutz, that the first line of treatment for those with anxiety and depression should be lifestyle changes. They cite new international guidelines that establish nine pillars for “whole-of-person” care, all of which are included below (the only one I don’t cover is to cease alchohol and smoking, which I totally agree with, but don’t have personal experience in doing).

Start Small

A word of caution: for someone dealing with anxiety, or in desperate need of a quick fix, a long article or list like this can induce even more anxiety. I mentioned small wins and victories in the introduction above. This is very important. Focus on trying just one thing for today, or for the next week. Make a simple chart on a piece of paper and tape it to your bathroom mirror to remember this one goal. If you forget and miss a few opportunities, that’s OK; these are often called “practices” for a reason. You are practicing, and it’s OK to make mistakes.

When you add the new behavior to your routine and “tool belt”, celebrate! Especially if it made a difference. Be grateful. Pat yourself on the back. Get yourself a comforting snack or treat. Tell a loved one about what you learned and did. Then make a new chart and keep it going for a few weeks to make it a habit, perhaps adding another behavior change if you’re feeling ready.

Do try at least one of the practices below. If you’re up to trying and succeeding at several of them, you will probably see a noticeable increase in your well-being. Once you manage to incorporate five or six of them, you may very well find yourself content, at peace, calmed, able to tackle whatever comes your way!

Breathe

Deep and relaxed breathing is one of the quickest and most accessible ways to instantly let go of a lot of pent-up tension, stress and worry. Everyone can do it, at any time, and in any place. You are flooding your system with oxygen, the fuel you live on and need for energy. You are taking control, over something you can control, which is a win for those with anxiety. Try it! It’s free and easy.

While you can do it while walking, standing, working, shopping or driving, ideally you will find a comfortable place to sit in a quiet environment. Gently close your lips, then breathe slowly in through your nose, dropping your diaphragm to give more room for your lungs as they fill. If this is unfamiliar to you, there are web pages and videos that can instruct. If you find diaphragm breathing complicates things, that’s OK; let it go for now and breathe in as you normally do, but do so slowly.

As you are breathing in, you could close your eyes and imagine a wispy, sparkling river of light and love and peace flowing into you. Or not, it’s up to you. You could choose to just be in this moment, seeing, perceiving and focusing on just this at this time, your breathing. Our brain, our mind, is an amazing tool. What we imagine, or “see” in our mind’s eye, is how we begin the achievement of anything we have become. Those who belong to my belief system call this faith, or exercising faith. It is a good use of your creative power that starts with imagining, believing in, and hoping for the joy and goodness in your life that you desire.

When your respiratory tank is full, gently hold it for two to eight seconds. You may only feel comfortable with two seconds to begin with. That’s OK. You can progress over time as you practice holding it for a little longer. Then part your lips and use your diaphragm to slowly exhale. You can choose to imagine that with your breath out, you are also letting go of toxins like grief, worry, fear, grudges, envy, stress, etc. I have found that it takes me twice as long to exhale slowly, as it does to inhale slowly. I’m no expert at this, but I believe the reason for counting while you breathe is to give your mind something to focus on, keeping you too busy to think about negative things.

As you practice deep breathing, do your best to focus on just this. If dark, difficult or worrisome thoughts knock on your door demanding to be addressed, acknowledge them, tell yourself you’ll give those thoughts an audience later; but for now, your only job is to focus on breathing slowly, calmly. Breathe in, and allow in, all that is good and right and brings you joy. Breathe out all that troubles you. Try not to visualize your problems at this time. Just collectively let them all go. For those who belong to my faith, let go of your troubles to Yeshua ha Mashiach (Jesus Christ) who lives and longs to heal, help and guide you. For others, you can just let them go, or give them away “to the universe” to work on or dissipate. Focusing on just this breathing will strengthen your ability to deal with problems as you go about your day. It will help you in becoming “mindful.”

One deep breath provides immediate relief. If you choose to discipline your mind to stick with it for two to five minutes, you’ll improve your sense of well-being, your ability to relax and stay relaxed by several factors.

Touch the Earth

Although I didn’t know it at the time, when I was getting in the garden, working the earth with my bare hands, I was doing something called “grounding”. Some call it “earthing.” By coming in contact with the earth, your body takes up some of its negative charge in the form of free electrons, which act like an anti-oxidant in the body, providing all sorts of positive effects.

Sounds woo-woo, tinfoil hat ridiculous, but it is real and it works. The benefits of connecting with the earth were innately known for thousands of years. It is only in the last century that we have been sealing ourselves off from touching the earth, in our synthetic, carpeted, plastic, rubberized, concrete and metal jungles. Unlike many home remedies and “alternative” medicine, this is backed up by at least a dozen peer-reviewed articles and studies, like those collected here, here and here. One of the metastudies concludes that grounding can improve

“…inflammation, pain, stiffness, circulation, blood pressure, blood viscosity, HRV, vagal tone, cortisol, stress, depression, anxiety, tiredness, fatigue, energy, mood, blood glucose, immunity, sleep, thyroid function, metabolism, serum electrolytes, wound healing, athletic performance and recovery, and more”

It makes sense that we were meant to be in contact with the earth, and is probably the reason many cultures refer to the earth as our nurturing “mother.” We are 55% to 70% water. We are conductive. Our body’s systems are bioelectrical. The earth is also electrical, a conductor, like a giant liquid battery with a metal core; it is an infinite source and sink of electrons, negatively charged at the surface. We are made up of elements that came from the earth. We are organisms that can and should live symbiotically upon the earth. When we touch the earth, with our skin, we are allowing nature to touch us, heal us and bring our bodily systems back into balance.

Although there are desk mats and bed mats you can purchase to direct negatively charged electrons into you and improve your sleep, the most simple way of grounding is to get outside!

Unfortunately our soled shoes insulate us from the positive effects of the most common way in which we touch the earth: walking upon it. So try to go barefoot in the grass, the sand, the soil, or the water. You can also touch the earth with your hands. Small-scale home gardeners know that working the earth with their bare hands reduces anxiety, stress and depression. And a recent meta-analysis of 22 case studies concluded the same, including reduction in BMI, and increase in life satisfaction, quality of life, and sense of community. Ever heard the term “tree-hugger?” I actually do that. Well, when I’m hiking I like to touch, pat and appreciate the magnificent trees and rock formations that line the sides of the trail. Touching the earth with your hands is something you can do easily in winter time as well.

Although there are detractors and skeptics trying to debunk the science, why bother listening to these scrooges when this is a very simple, free and easy activity that feels wonderful to most? Take off your shoes and wiggle your toes in the sand, dip your feet in the stream, or go walk through your lawn or garden. You could also lay down in the grass during warm and dry conditions. For a double-whammy of goodness, hike barefoot on the beach or through a forest. Try to touch the earth at least once a day for 5 to 15 minutes. What have you got to lose?

Connect

A Harvard study on Adult Development is just astounding in its breadth, depth and rigor. It gathered data for 80 years on everything about 724 Boston males and their offspring (268 from the 1930’s and 456 from the 1970’s). Robert Waldinger, professor of pyschiatry at the Harvard Medical School gave a viral TED talk on the findings of the study. As it turns out, the key to long-life, good health and happiness was good quality relationships. To summarize the summaries on this study: loneliness kills; substitute screen time with people time. Reach out. Call someone. Write an email or letter. Ask to get together, go for a walk, play a game, or have lunch. Talk and share. Be interested in the other person; ask questions about their life and what you can do to help.

This is the one I struggle with the most. I’ve long felt that I was an independent introvert, that I needed no one to complete me or help me. Unfortunately, escaping into social media, memes, video games and entertainment has convinced most of the current generation that they don’t need anyone either. As I’ve matured — and especially during the pandemic where I was forced to work at home away from daily interactions with people — it has become clear that I do need the company and complex interractions with others, especially friends and family.

I’ve come to cherish and treasure family dinners and holiday gatherings. I’ve tried to keep friendships alive by using text, email and facebook to send occasional messages, and invite friends on bike rides, hikes and lunches with me. And they have reciprocated. We set up a weekly Zoom call with my siblings, one of whom is in Rhode Island, and another in Germany. This has been such a blessing and critical for me, and particularly for my mother. And finally my wife and I resumed a habit of trying to have a different neighbor over for dinner once a month. I can attest that being with friends and family, and reaching out to make new friends, is essential to our mental well-being, contentment and fulfillment in life.

Hopefully as you consider this principle of happiness your inner voice will guide you to the things you can try to increase your connection to the good people around you and the quality of your relationships. I wish you luck as this one requires more effort than most, but is the most rewarding.

Sleep

In an unpublished video created for the iFIT platform, Dr. Moran Cerf, neuroscientist and business professor at Northwestern University, shared the five pillars of happiness, each pillar additively increasing one’s happiness. Unfortunately he did not cite the source, or study methodology, but said the pillars are the result of a multi-national study which concluded that the practices which actually increase happiness are:

  1. Sleep (good quality sleep increased happiness by 2 points)
  2. Social interactions/connection (1 point)
  3. Spirtuality, or believing in a higher purpose (1 point)
  4. Exercise (1.1 point)
  5. Volunteering and serving others (1 point)

Although this post inadvertently covers all five of these, when Dr. Cerf mentioned that sleep affected one’s happiness more than any other factor, that intrigued me. I have had a fairy tale relationship with sleep ever since college. Most nights I’m asleep within one minute of my head hitting the pillow. I’m then fully rested and my body wakes up without an alarm just 5 hours later. But every once in a while, usually after another layoff, or right before an epic physical challenge with friends, I can’t sleep a wink. My brain just won’t drift off like it normally does, no matter what I try. An hour later when I start to worry I’ll never get to sleep, it becomes a self-fueled perpetual motion machine that keeps me up all night. I have yet to do any athletic endurance event on anything more than 2 hours sleep, sometimes zero. Urg.

There is so much information around the science of sleep, I won’t even try to unfold it here. Most experts agree you need between 7 and 9 hours of quality sleep. What they don’t agree on, is how to obtain it. Like the science of nutrition, mankind is uncovering more knowledge about the brain and human body every year; and everyone is different, so you will need to experiment and find what works for you.

Here are some things to try that worked for me.

  • Avoid long or late naps, especially within an hour or so of bedtime. If you are feeling drowsy, stay awake until bedtime. Otherwise you’ll be up ’til 2 or 3am, probably engaging in some other poor habit to pass the time until you feel exhausted enough to try sleep again. “In a study of 440 college students, the poorest nighttime sleep quality was observed in those who reported taking three or more naps per week, those who napped for more than 2 hours, and those who napped late (between 6 p.m. and 9 p.m).”
  • Sleep cool. You will heat up at night. It is best to keep your room, and perhaps your bed, around 65 degrees Farenheit.
  • Ensure your mattress and pillow are good quality and right for your sleeping posture, straight spine, neck curvature, etc. Everyone is different. This one can be expensive and difficult to get just right.
  • Avoid screens, blue and bright light within the 2 hours before bed. Instead get ready for bed, then journal, read to your kids, or read a book by the light of a soft white (2700K) bulb. Allow your mind to quiet down, instead of being entertained right up to the edge of bedtime.
  • Eat dinner around 12 hours before you plan on breakfast, and try not to eat food, especially ice cream and desserts later in the evening close to bedtime. Bad idea. Your body will convert those carbs to fat if they aren’t utilized; and they won’t be, because you’re about to go into hibernation for the evening.
  • Empty your bladder and only take a few sips of water before bedtime.
  • Darken your sleep chamber. Cover any LED lights from electronice devices that can’t be put in another room. Use room-darkening curtains if your windows or blinds let in too much light.
  • If you do wake up during the night, ensure you ignore the time on the clock. Don’t worry about it. Assume it is still early in the evening, and you still have the whole night ahead of you for some good rest. You’ll get back to sleep shortly.
  • Listen to relaxing, calming, meditative, instrumental music (more on this in the Listen to Beauty section below)
  • Write it down. If you have a nagging worry, or are going through tomorrow’s to-do’s and schedule, keep a notebook nearby, or write it down in your phone’s calendar or to-do list app. Put a date and time reminder on it, so you know it will be addressed in time. Doing this physical act gives yourself the permission to address that worry later, but not now while sleep is calling.
  • Meditate. There are many guided meditations you can find on the web. Try them out. In college I learned from somewhere to let go of every thought after closing my eyes. Imagine a tiny dark speck in the distance of the blank space in your mind’s eye. The dark speck of sleepiness is slowly, slowly drawing closer, enveloping more of the bright space that is your alert mind, putting things to bed as it nears, much like the evening unfolds as the sun sets. As it nears you, it will blanket you in velvety, comforting softness and welcome you into sleepfulness. When I was a teen I lay awake for three hours every night, planning every detail of the next day. Crazy. When I first learned this technique during my freshman year at college, I practiced this every night while listening to a calming cassette tape on repeat (Desires of the Heart, by Chris Spheeris). When I first started, the cassette would play in its entirety, and reverse, three times before I finally went to sleep. By the end of the second semester, I was falling asleep before the end of the first song. My point is that this, and any of these practices, could take a while to make a difference. Be patient. It will be worth the effort.

Facing a night without rest happens about twice a year, and when I’m newly laid off. When I’ve tried all of the above, and yet it’s been hours waiting for sleep to come, and my brain just refuses to get out of high alert, I’ve had to utilize a histamine blocker, like 25mg of diphenhydramine hydrochloride (Benadryl) to help me feel drowsy. This usually puts me to sleep within 30 minutes, but I always wake up exactly 4 hours later. Benadryl as a last-resort does not give me a full night’s rest, but it’s better than nothing.

Find Your Higher Purpose

Dr. Moran Cerf warned that this pillar of happiness can’t be faked. You actually have to be spiritual, not feign spirituality. Those who are happier believe they are here for a reason; they find meaning in their life, usually due to a belief in an afterlife. They believe they are created for a purpose and given a mission to learn, create, work, serve, love, have children, be hospitable and generous, leave the world a little better than you found it, etc.

I was raised Christian, but I was very cynical. I loved, leaned into and trusted science and logic. And yet, I had a hard time accepting that evolutionary uniformitarianism was the only explanation for life as it is, that I am an organic meat bag, a happy accident of billions of years of random mutations, and that consciousness ends at death. I spent three years exploring other faiths and reaching out, yearning and begging the God I had been taught about to let me know if he was there. It is a much longer story best saved for a different blog, but I was blessed with two profound spiritual experiences where I experienced my God and his overwhelming love for, acceptance, and intimate awareness of me. These experiences, one in 1988 and one in 2003, gave me hope and meaning, and changed the course of my life for the better.

It is fine if you disagree or think I have allowed my mind to manufacture these experiences. I know what I experienced and it gave me purpose. And this is the point of this section. Belief in a higher purpose and meaning for this mortal existence improve one’s happiness, especially those who feel lost or aimless, who ask “Who am I?” or “Why am I here?” If you don’t yet know what you believe, why don’t you set aside a little time every day to explore belief systems, meditate, listen to the inner light or voice within you, or pray to know what is true and listen patiently for guidance? Finding “your Why” could make all the difference.

Listen to Beauty

Audible beauty can take the form of getting outside and just listening to the sounds of nature (see Touch the Earth and Get Moving sections), or listening to your young children imagine and play, or just listening to music. The sounds or music that soothe your soul is very personal and can be different for everyone. Count it a blessing if you already know which music calms and relaxes you. If you do not yet know, you are in for a rich experience as you try out some of the suggestions below. With Pandora, Spotify, Amazon Prime and other music services, it is very easy these days to sample different tastes in music.

Music is so powerful. It is vibration and frequency in its purest form. It can induce various feelings, motivate, inspire, relax, and energize. Like several of these practices, you will need to experiment and find what works for you. In my moments of disquiet, worry and fleeing peace, I have found the following types of music and specific artists have improved my mood, quieted my mind, slowed my breathing, and helped lessen worry, stress and anxiety. I will mark with an asterisk those that actually induced restful sleep on nights when my mind would not be quiet. This list exposes how shallow my taste in music is, but so be it. If this helps someone in need, I’m OK with that.

You can search for the recommendations below on YouTube or Amazon. I’m not including links on purpose because I’m not trying to sell anything. There are literally thousands of artists to explore in each of the categories highlighted below. I hope you enjoy exploring all the options! If you have some great recommendations, please drop them in a comment on this page.

New Age

  • Chris Spheeris, in particular album Desires of the Heart*
  • David Lanz
  • 2002*
  • Enya
  • Michael Gettel
  • Windham Hill artists
  • Suzanne Ciani

Piano

  • Jim Brickman
  • John Schmidt
  • George Winston
  • Kurt Bestor

Guitar

  • Michael Dowdle
  • Govi

Saxophone

  • Kenny G

Relaxation, Meditation, Massage, Yoga

  • Dream Music: For Sleep and Relaxation
  • Soothing Relaxation*, by Peder Helland
  • Native American flute music
    • In particular the 2004 Best of R. Carlos Nakai album
    • Native Moods: Nature Music and Flute Spa Music

Tibetan Bowl

  • Liquid Blue

Binaural Beats, Sleep Music

  • SleepTube, in particular “The Blue Forest”*

Eat Well and Hydrate

I was tempted to not even include this because how I ate did not change as I learned to deal with these new difficulties in my life. I was already eating fairly well, and continue to do so. I am absolutely convinced that good nutrition plays a huge role in our mental well-being. Making changes in your well-established patterns of eating can be very hard, time-consuming and expensive, which is the reason I encouraged small steps at the start of this article. If you’re ready to tackle this one, here’s what I recommend, one step at a time, not all at once:

  • Eat mostly from the produce, dairy and butcher aisle, as well as canned and frozen fruit and vegetables, hummus, salsa, beans, grains and nuts. It is really easy to make your own hummus and salsa. One tip, substitute olive oil for tahini. Try to obtain meat and eggs from pasture-raised chickens, milk and beef from grass-fed cattle, wild-caught fish, and organic pesticide-free plant life. Yes this kind of nutrition is expensive, unless you are running a little farm.
  • Try to avoid processed food, especially man-made ingredients meant to make food last longer on the shelf, like preservatives, artificial colors and flavors, MSG, and hydrogenated oils (trans-fats).
  • Cut out all artificial sweeteners and sodas of all kinds, especially diet. If you do imbibe in a sweet drink, limit to fruit juice or real sugar (like homemade lemonade) once in a while. If you are diabetic, see if a low-glycemic natural sweetener like monkfruit or stevia works for you. I cut out soda in 1994 and feel it has been one of the best decisions I’ve ever made. I am human though; I do still enjoy an orange juice, glass of homemade grape juice, or root beer with pizza here and there.
  • Try to learn and make your own tortillas and bread, especially from sourdough starter and home-ground wheat. After I was doing this for years I was fascinated when I would eat a slice of store-bought bread elsewhere, and found that I could now smell and taste the retarded spoilage in what everyone else thought was good bread.
  • Hydrate well. Get your water from a spring, filter it, or be lucky enough to live near the foothills of the Rocky Mountains. Publications have been giving rules of thumb for years on how much to drink, like half your body weight in ounces. That’s ridiculous, especially if you’re obese. A Stanford professor who is one of the few experts in this area, but who I forget the name of, said that the trends have no basis in any published science; she said to do what humans have done for thousands of years: drink when you feel thirsty. This article agrees. Unfortunately, that approach doesn’t work for me, because I never feel thirsty. But it will work for most.
  • Determine the vitamins in which you are deficient and supplement. Most people are heavily deficient in magnesium and Vitamin D. And it just so happens that magnesium plays a huge role in feelings of calmness and muscle relaxation. Magnesium is best absorbed through the skin, but many find that messy and uncomfortable, so resort to taking internally. The best way to get Vitamin D is by exposing 80% of your skin to the sun for 15 minutes each day; but unless you have a private place to get naked, that’s not happening and the best most of us can manage is exposing 5% of our skin 15 minutes a day. If you do supplement with Vitamin D, ensure it is the D3 form. Much vitamin D can be obtained while getting your “vitamin N” (Nature) in shorts and a tank or T-shirt, talked about in Touch the Earth (above) and Get Moving (below). After that period of absorbing the sun’s rays, then go ahead and apply sunblock.

Get Moving

Of all the things that helped me, this one is my favorite. Exercise and getting outside to enjoy nature, — all while getting in a good workout — is my drug. I simply can’t start my day without a good workout. And I look forward to hiking or biking outside like a kid itching to go to the candy store. Moving, stretching and exercise is good for you in the short and long therm, will extend your lifespan, allow you to do things other peers cannot, and releases endorphins and rewards you with dopamine. It is worth every effort to incorporate into your daily routine. Do remember to take a break, and have days at light intensity, so that your body can recover and build muscle.

Various studies have shown that exercise is just as effective as anti-depressants. See this short little article from the Harvard Medical Journal, although I prefer this article from the NIH, and this one from the British Journal of Sports Medicine. If you would like relief from anxiety and mild to moderate depression, look no further than finding some form of movement, stretching or exercise that is easy to implement and enjoyable for you. It needs to be something you can get to readily; having to drive 30 minutes to the gym, or driving an hour to the trail head isn’t going to work for a daily routine. It must be something you enjoy, or the new habit will fall apart quickly. Walking/jogging, biking, and mobile fitness apps, or YouTube strength and yoga videos fit the bill nicely.

Here’s my story with exercise and what worked for me:

I was born with agility, flexibility, endurance and enjoyed playing sports. Frankly my brain didn’t grasp the dynamics or social nature of team sports; I did far better with individual sports. Unfortunately, my genetics gives me poor joints. I had to stop playing soccer at seventeen because 30 minutes of running caused me such pain in my knees I couldn’t stand up. Luckily I discovered cycling at 19 and never looked back, biking 40 to 100 miles per week, racing mountain bikes in college, and still cycling today. Hiking in the woods has also been a life-long love affair, and I took up mixed martial arts in 2009. I tore my left meniscus for the first time in 2010, which mostly healed. Then my right meniscus started tearing in 2016. At that time the doctor told me to cut the martial arts and hiking out entirely, which I did. Luckily I was allowed to keep cycling as part of my physical therapy. Since snow sports were out and I tended to gain 10 lbs every winter from lack of exercise, I picked up a ProForm iFIT bike from Costco in January of 2020. I immediately fell in love. On the bike’s screen, I was able to follow and ride with trainers in locations all over the world. It was like a mini-vacation every day! The same platform also offers strength, HIIT and yoga/stretching classes. Then in the summer of 2020 I tore my right meniscus for the fourth time. The orthopedic surgeon, who had agreed to my desire for natural healing for years since my first tear, told me it was time for surgery. Recovery went well; too well. I thought I was all healed just a month after surgery! I overdid it with two intense trail rides back to back, and set myself back six months. The iFIT bike continued to be a great way to recover and eventually I was back at full strength. I ended up purchasing a NordicTrack X22i treadmill and upgraded the bike to a NordicTrack S27i, both with big iFIT tablets embedded. And now my wife was hooked on the treadmill! She could run all over the world; walk on beaches in Tahiti, Fiji, Hawaii, and Thailand; hike in Japan, Norway, Scotland, or the Swiss, French and Italian alps; take walking tours of Petra, Egyptian pyramids, Constantinople, and so, so much more. I succumbed as well, and now do three workouts (strength, hiking and biking) almost every day. Highly, highly recommended. The subscription membership for the devices is worth every penny. It — is — so — much — fun!

And that is the point; even though I couldn’t do much, it was enough. I sought and found something easy, accessible and fun. Thanks to these machines, and the grace of God, the muscles around my knees, and my heart, are as strong as they were in my thirties. This has allowed me to return to hiking, gettiing in 150 hikes of 2 to 10 miles each last year, and oh what joy this has brought to my soul to be out in the red rocks, slot canyons, and mountains again!

Be Grateful, Mindful, Prayerful

This is a very broad topic. There are entire books, websites, and authors who have dedicated their careers to teaching mindfulness and meditation. I’m not that guy, nor will I pretend to be. This article was meant to share what worked for me, and what worked for me was continuing a habit of prayer and journaling, and starting two new journals. Yep, you read that right: three journals.

In my primary journal that I started when I was seven years old. I record events, family visits and dinners, trips, accomplishments and concerts of my children, thoughts and feelings, hopes and dreams, etc. I only write about once a week, but during my times of joblessness and worry, I started writing daily, and it helped to offload my thoughts onto digital “paper.”

I also found an old spiral notebook and began hand-writing at least four new things each day that I was grateful for. I kept this going for a month to try and form a new habit. What I found valuable about this exercise was the introspection required, the mental exercise it took to look inward, find things in my life that I cherished, and see aspects of my life from a new perspective. Finding and seeing things in your life through a lens of gratitude is frankly one of the keys of happiness. During a time when I was really blue and wondering “Why me?” this practice dispelled much of the dark clouds of negativity, lifted my spirits and helped me see more clearly.

The second journal I actually started in 2017, long before the first job loss in 2018 began. This journal is solely intended for “hand of God” moments. When I experienced an answer to prayer, a divine protection from harm, a miracle so dripping with coincidence that it could be nothing other than God manifesting in my life, I wrote it down. Even if you don’t agree with my source attribution, these amazing events were just too good to be lost by my leaky memory. This small book is now about 40 pages long, and it has become precious. I hope to pass it on to my children so they can learn and know how my Father in heaven was quite often, strongly, obviously there for me. During time of worry and fear about the future, re-reading this journal, and writing the miracles that took place during the times of joblessness, were of great help and kept my perspective in a better place.

One of the things I have come to be grateful for is that pivotal panic attack in 2014, and the struggles with anxiety with each loss of employment. I still miss much of the old, super-confident me, but I’m grateful I went through these experiences for at least two reasons:

  • Empathy: Two of my daughters were about to manifest chronic anxiety, one in 2015 and the other in 2017. Prior to 2014 I just didn’t understand folks with mental health challenges. Figured they should just “pull it together” or do A, B and C to “get over it.” I’d like to think I have now learned some empathy and compassion towards those who struggle, and I feel more capable and well-rounded for it.
  • Perspective, Patience and Resilience: I had enjoyed a relatively stable career for a long time. I thought I was pretty smart and sharp, completely unaware that I was weak in certain areas because everything had always gone so well. Starting Dec 27, 2018 I began a period of being let go from employment, over and over. Each new layoff, intense job hunt, insurance change, and disruption strengthened me. These experiences became easier and easier to accept and adjust, having been through it before.

What have your struggles taught you? Have previous difficulties led to any learning, growth, maturing, unexpected opportunities or blessings in your life? Reflecting on your past like this might be a good start to your own gratitude journal. Perhaps put it by your bedside where you can reflect on your day and write a little something each night before bedtime. Or put it by your chair where you read or pray in the morning. Make it part of your daily routine to journal, meditate, pray, or all three!

Help Someone

This is one of, if not THE surest, quickest way to dispel darkness and let light, joy and happiness back in:
Think of others and then do something for one of them. I tend to be self-centered; when people wish me a good day, it doesn’t even occur to me that they might want or be expecting to have the same well-wishing reciprocated. But membership in my church, and opportunities put in front of me “by the universe” (God) have allowed me to participate in lots of service. And I can vouch that there is a pure joy you feel when you serve someone in need because they too are a child of God, and deserving of your help, time, love and attention.

If you’re having trouble finding motivation to do anything at the moment, do it anyway. Anyone who selflessly thinks of others and finds someone or something to love, serve and care for in their moment of need will find their reservoirs of happiness refilling. This is part of the Connection, Get Moving, and Finding a Purpose practices as well, as you are usually getting out of the house and being with the person you think well enough of to care for.

If you don’t know where to start, or have never really participated in volunteerism, you could ask your local city office, pastor, imam or rabbi for suggestions. Your workplace might have some partnerships and volunteer days that you’ve generally ignored in the past. Or simply search the web for “food pantries near me” or “rescue missions near me” or “domestic abuse centers near me.” You could make a big difference giving of your time to the less fortunate. One really great resource is a website created by my church: https://justserve.org which is designed for folks who want to start volunteering but don’t know where to begin. Give it a look.

You Do You

One of the practices that can help calm a harried mind is re-direction, focusing on something that genuinely brings you wholesome pleasure and joy. I strongly believe that the closer we get to living the way we believe — or know deep down to be good, healthy and wholesome — the closer we get to true peace. For me this was following Christ’s teachings, hiking, biking, gardening and being on the mountain and in the forest. But you do you! What used to make you happy, feel freedom, or feel “in the zone” when you were younger? Bikes, fishing, legos, baking, fashion design, horses, caring for a dog? Has it been a while? What’s stopping you from picking it up again? If you genuinely cannot, what was it about that activity that you loved? Can that same quality or reward be found in other hobbies or activities? It might be time to try new things, some requiring effort to set up, purchase gear, or learn. Some will be free and easily accessible. Here’s a handful of ideas:

  • Reading
  • Yoga
  • Running
  • Painting
  • Writing poetry or short stories
  • Organizing and cleaning
  • Making gifts
  • Making jewelry
  • Woodcarving and whittling
  • Playing games or solving puzzles
  • Going to a ballgame with your Dad
  • Riding bikes with a buddy
  • Visiting the beach
  • Lifting heavy things and putting them down (gym)

Whatever it is, you do you. Find or create the time to allow a little fun and joy back into your life. Let your creativity soar!

Get Help

Let’s say that little by little, week by week, you add some of these practices to your daily routine. You cut out sodas, most sugar, and eat more living, unprocessed food and clean water. You exercise 30 minutes and take a walk most days. You stay off the mobile screen and TV at night, reading a little and journaling a little before bed. You reconnect with friends and co-workers and start texting and calling family members more. By this time, you should be noticing your contentment, purpose, and calm genuinely improving. In the last decade several of these practices have been shown to have an equivalent or better effect than anti-depressants.

However, if these aren’t helping at all, or if you feel so broken or depressed that you can’t even bring yourself to try one of them (if you haven’t already done so) it is time to seek professional help. Finding a good counselor is tricky business, because it can cost upwards of $250/hr for each one you “try on for size.” There is a shortage of trained counselors at the moment; it can take weeks or months to get an appointment. It would be great if each counselor would let you interview them, much like home repair services are willing to come meet you, learn of your needs, sell you on why you should go with their company, and give you a bid. At the moment, though, that is not how things work. You might talk with friends to see if they know of anyone who has truly helped their clients, or talk to your ecclesiastical leader who might be connected to some good ones. Betterhelp.com advertises itself as matching you to great therapists, but when we test-drove their website for a loved one, they just seemed to be a broker, much like the many insurance websites that send leads to agents for a small fee.

Best of luck in this endeavor to find someone who “gets you” and can really help! Sometimes all you need is a listening ear. Sometimes you may need a little more intense approach, like cognitive behavorial therapy or dialectic behavioral therapy. Be willing and open to try new things and give their suggestions a good try. They may prescribe medication to help with your seratonin uptake or epinephrine/dopamine levels. In deciding whether to take medication or not, go with your “gut” here. I call it listening to the Spirit or Light within you, which often knows exactly what you need, and when finally presented with a missing puzzle piece, you will just know or “feel” that this is right. These medications can take weeks to realize their full effect, and it may not work for you. That’s OK. You learned something about your body in that experiment and can move on to try another. Personally I haven’t needed this option, or had any prescribed, but I recently finished reading Radical Love, by Zachary Levi, and even after intense therapy had brought major breakthroughs to his mental health, it wasn’t until he fell apart again (after thinking he was finally healed), that he found medication was the last piece of the puzzle that stablized everything and brought him the peace that had eluded him his whole life.

Wrapping Up

I sincerely hope you find success and better well-being by trying some of these good habits. Feel free to drop a comment below if you have suggestions, find typo’s, or have feedback on how things are going. This Bluehost blog editor mysteriously doesn’t offer the Subscribe widget. If you’d like to stay in touch, you can connect with me on LinkedIn or Facebook.

Other Resources

Dr. Martin Seligman’s 5 Pillars of Happiness

Happyness Hub

PostgreSQL Tuning – pg_stat_statements

by

Although I’ve dabbled in postgres since 2002, I’ve only been working with it heavily since 2019. My favorite challenge was tuning a 37 TB AWS RDS instance that ran a flagship feature for a previous employer. It was a very busy system, much like an IOT system enduring millions of reads/writes and billions of updates and deletes every day. It was so busy I was only able to investigate and tune from 9pm to 2am. At any other time, even the simplest queries wouldn’t even return.

When you embark on a big tuning effort like the one just described, and one is tasked with figuring out where the sluggishness is coming from, it is invaluable to peer into all the unique SQL statements the system has had to process over time, and where inefficiencies are lurking. The pg_stat_statements extension is crucial to such an effort. First added in version 8.4, performance monitoring continues to improve with every release.

It is a good idea to learn how to use it to your advantage.

Getting Started

Assuming the user/role you are using to connect has the appropriate level of privileges, query your statement monitoring cache:

> select * from pg_stat_statements;

If this errors out, then either your don’t have the right privileges, or it still needs to be installed by your DBA (which might be you). First check that it is included in the preloaded libraries. Open your postgres.conf file and look for this line:

shared_preload_libraries = ‘pg_stat_statements’

There might be other libraries in that listed of pre-loads. If you have to add pg_stat_statements to that parameter, restart Postgres. Once that is done, then enable the feature:

> CREATE EXTENSION pg_stat_statements;

This will create the pg_stat_statements view (and also pg_stat_statements_info on v 14.X). A number of columns were renamed in version 13. The examples below were written for version 14. If the statement does not run on your system, your version is 12 or below; just remove the word “exec” from total_exec_time, min_exec_time, max_exec_time, mean_exec_time and stddev_exec_time. I won’t spend any time explaining each column. Most are self-explanatory. You can read the official Postgres doc linked above to get a better understanding of each column.

There are a handful of postgres.conf parameters that pertain to statement tracking. I like to reveal their values using this:

select name, setting, unit, min_val, max_val, source, vartype, enumvals, boot_val
  from pg_settings
 where name like 'pg_stat%' or name in ('track_io_timing','track_activity_query_size');
pg_stat_statements gathering parameters

The default settings are typically good enough. However, if your queries are quite large, you might want to set track_activity_query_size to 2048 or 4096. Multi-page queries might require even more bytes to accommodate the whole thing. Also, if you find that blk_read_time and blk_write_time columns are blank for your statements, this means that the track_io_timing parameter is off. You can turn it on for the entire server in the postgres.conf file, or just for the current database, like this:

> ALTER DATABASE mydb SET track_io_timing = on;

It does consume some resources to track I/O. You may wish to turn it back off once your heaviest physical disk-touching loads have been identified and corrected.

I’d like to introduce you to a handful of queries, that I picked up here and there over years of tuning, which I find handy for quickly identifying sore spots that need attention.

Top SQL Consumers: Time

Below you will see my favorite query for working with tracked statements. It is multi-purpose. You can add WHERE predicates if you wish, or partial query strings to filter the results to just a query or two you are looking for, or switch out the column(s) being ordered by to change the focus of the query. But this one is the one I use the most, bubbling to the top the biggest consumers of time (and therefore resources) on the system.

with stmts as (
   select query
        , round((total_exec_time/1000/60/60)::numeric, 2) as total_hours
        , round((100 * total_exec_time / sum(total_exec_time::numeric) OVER ())::numeric, 2) AS pct_of_total_hours
        , calls
        , rows
        , (round(blk_read_time::numeric,0))/1000 blk_read_seconds
        , (round(blk_write_time::numeric,0))/1000 as blk_write_seconds
        , (blk_read_time+blk_write_time)/calls as physical_io_per_call_ms
        , round(min_exec_time::numeric, 2) as min_call_ms
        , round(max_exec_time::numeric, 2) as max_call_ms
        , round(mean_exec_time::numeric, 2) as mean_call_ms
        , temp_blks_read
        , temp_blks_written
     from pg_stat_statements
    where dbid = (select oid from pg_database where datname = 'postgres')
)
select *
  from stmts
 where 1=1
   --and query like '%:partial_query_string%'
order by total_hours desc, calls desc;

Be sure to replace the name of your database of interest in line 16 if yours is using a name other than the default of “postgres”.

If I already know the statement I’m looking for, or a table name or keyword found in that statement, I can comment in the filter query like '%:partial_query_string%' and re-run to find only those statements matching my string. You can get fancier with regular expressions too.

You may find yourself working on a system where each of the thousands of unique statements only differs by a few characters. In order to reduce the noise, you could truncate the query down to 40 or 50 of the first characters and group the measurements by that. This aggregation may show the primary culprit, occuping 40, 50 or 70% of the total time for all statements. From there you can jump back to using the query above to zero in on the individual statements (using the partial_query_string version to find all statements sharing the same signature as found in the grouped version below), which are causing the majority of the load on the database.

-- bucket statements by simple truncation
with stmts as (
   select query
        , round((total_exec_time/1000/60/60)::numeric, 2) as total_hours
        , round((100 * total_exec_time / sum(total_exec_time::numeric) OVER ())::numeric, 2) AS pct_of_total_hours
        , calls
        , rows
        , (round(blk_read_time::numeric,0))/1000 blk_read_seconds
        , (round(blk_write_time::numeric,0))/1000 as blk_write_seconds
        , (blk_read_time+blk_write_time)/calls as physical_io_per_call_ms
        , round(mean_exec_time::numeric, 2) as mean_call_ms
        , temp_blks_read
        , temp_blks_written
     from pg_stat_statements
    where dbid = (select oid from pg_database where datname = 'postgres')
)
select substring(query,1,50) as query_first_50chars, sum(total_hours) as hours, sum(pct_of_total_hours) as pct
     , sum(calls) as calls, sum(rows) as rows, round(avg(mean_call_ms),2) as mean_call_ms
     , sum(temp_blks_read) as temp_blks_read, sum(temp_blks_written) temp_blks_written
  from stmts
 group by query_first_50chars
order by hours desc, calls desc;

There are times in your tuning efforts, where one query was so horrendous it was consuming most of the resources, or took so much time to complete, they skew the results. This will usually stand out like a beacon, seen by a gigantic difference between min and max execution time, and a large standard deviation. In the example below, ordered by total time, the queries behind rows 3 and 4 have really huge stddev. Their minimum execution time is .02ms, and average is 19s and 8s respectively, but look at the maximum! 105 hours and 51 hours to execute!

Example output from trimmed version of pg_stat_statements query

If those queries took days to run every time, that would be the first thing you’d fix. But they don’t. Here’s where you dig a little deeper and exercise judgement. Pursuing queries with large max time or stddev could be a waste of your time. There might have been one anomalous incident where resources were locked, preventing the statements from completing. Steps should definitely be taken to log and alert when queries take this long in the future (subject of future blog post), instead of hanging indefinitely. But if this only happened once or twice, this is a distraction. In fact queries 3 and 4 aren’t executed much at all compared to queries 1 and 2, and might have even stopped being used. Perhaps they were only used during initial migration. The mean tells us queries 3 and 4 are not too bad. Indeed these queries that I can’t show to protect my client, are part of a backend data pipeline system that does large load and deletion operations, so 8s to 19s is OK. It is for reasons like these, that I’ll glance over stddev, but I get more value by focusing on total time taken.

I don’t have an example to show at the moment, but imagine that query 1 in the screenshot above took a mean time of 1 second to return. That seems pretty good if this were a query supporting a UI screen. But it is not. It was called over six million times in the week-long period where these stats were gathered. A one second response time for a query called millions of times could break the business or bring the system to its knees. Luckily the top two statements both take less than 100ms and aren’t a concern (the second query is a stored procedure, and postgres doesn’t appear to log rows affected by stored routines, otherwise the rows column can be informative).

Top SQL Consumers: Temp

One indication of a wasteful query, or a work_mem setting that is too small, is a large use of temp space because there wasn’t enough work_mem in the session to handle the sort operations. Use this to identify those types of statements:

with stmts as (
   select query
        , round((total_exec_time/1000/60/60)::numeric, 2) as total_hours
        , round((100 * total_exec_time / sum(total_exec_time::numeric) OVER ())::numeric, 2) AS pct_of_total_hours
        , calls
        , rows
        , (round(blk_read_time::numeric,0))/1000 blk_read_seconds
        , (round(blk_write_time::numeric,0))/1000 as blk_write_seconds
        , (blk_read_time+blk_write_time)/calls as physical_io_per_call_ms
        , round(min_exec_time::numeric, 2) as min_call_ms
        , round(max_exec_time::numeric, 2) as max_call_ms
        , round(mean_exec_time::numeric, 2) as mean_call_ms
        , round(stddev_exec_time::numeric, 2) as stddev_call_ms
        --, shared_blks_hit, shared_blks_read, shared_blks_dirtied, shared_blks_written
        , temp_blks_read
        , temp_blks_written
     from pg_stat_statements
    where dbid = (select oid from pg_database where datname = 'postgres')
)
select *
  from stmts
order by (temp_blks_read + temp_blks_written) desc, calls desc;

Having identified some big uses of temp space, now you need to know the business context behind these queries. Many top users of temp space will be large INSERT, CREATE INDEX, DELETE and other bulk-type operations that don’t happen very often. These require lots of in-memory work that spill over to temp and probably don’t need tuning. If you find a big, bloated query though, that is taking 34 minutes to run every execution and is inefficiently spilling over into disk access and temp space (and nobody knew because it was running at night and not affecting end users), then it probably requires attention and tuning.

Top SQL Consumers: I/O

In some circumstances, you may be more concerned about queries that have to spend inordinate effort reading from or writing to disk. To highlight these operations, just take the same query above, but order by physical_io_per_call_ms DESC.

In Closing…

It isn’t enough just to query pg_stat_statements and look for big time hogs. That’s a great first step and may very well net you some “low-hanging fruit” and quick wins. But you could also spin your wheels and tune the wrong thing.

Knowing the business, knowing the app or service context where the query is used, asking the right questions, figuring out what the query should be doing, how much data should be processed at each step in the query plan, and the order in which the data should be joining and sorting, this is all part of being a good performance tuner. “Know thy data” is the first principle I teach in my classes on data design and performance tuning. This helps you quickly zero in on true waste of resources, ignoring the fluff and noise, and is how you earn your living as a rock star data engineer.

Now that I’m writing again, I’d like to encourage you to subscribe to the blog for future posts. Feel free to contact me if you have questions or a desire to engage, or leave a comment if you have a clarifications or find a technical error.

Tips

  • Create a weekly job, perhaps orchestrated by cron, which saves a snapshot of everything in pg_stat_statements to a local table. Perhaps create a new schema in the database named monitor to hold this table and anything else you create to watch and learn about the database’s behavior over time.
  • If you intend to use the pg_stat_statements_reset() function to reset statement stats for the database, a user, or a query, save a copy of the pg_stat_statements data for that query, user or entire database BEFORE you make your change and reset and re-test to see if performance is better.
  • pg_stat_statements is only useful for successful, completed DML and DDL. To see queries currently in flight, typically useful when a query isn’t returning and you’d like to know what it is doing, query pg_stat_activity instead (subject of future blog post)

CREATE VIEW AS SELECT * does not dynamically adjust to the underlying object

by

It never ceases to amaze me how I can be humbled every day.

When you work with a technology for so long, teaching, writing, presenting, training and mentoring…you start to think you’re an expert.
You think by now you know all the basics, advanced aspects and nuances.
And yet, despite your experience, there it is: an aspect of Oracle that has been there all along, but somehow I missed it! 25 years doing this for a living, and I missed it!

That or I’m starting to go senile.

In every Oracle development environment in which I’ve worked, either I or an architect that preceded me has included in the SQL standard the common rule to always use explicit column lists when building SELECT or INSERT statements. The simplified version of the rule’s justification is that things tend to change and when they do, the generic SELECT * and INSERT with no column list will break your app.

With my current employer’s most recent project, for the first time I consciously chose to break that rule.

I thought my reason was sound, e.g. the remote table on another Oracle database was being altered frequently and I needed our local view of that table to adjust dynamically, rather than have to check in yet another script into our database build every time they added another column. Ask most DBAs what a view actually is, and they tend to agree with the Oracle documentation that says views are stored queries. They don’t contain the data. They contain the query definition to get at the data at runtime when the view is queried.

I figured that if my column list was the taboo “SELECT * FROM {remote table over db link}”, it would automatically see and include the new column(s) as it parsed and executed the SELECT * query.

As I understood it, upon feeding Oracle a view creation statement, it first strips out any SQL comments found at the top or bottom of the query, ensures the syntax is correct, and that it has the privileges it needed to function. After passing validations, it would then take the view’s defining query and store it as-is in the data dictionary. Then when a user queries the view, Oracle retrieves the view SQL out of the data dictionary, merges it with any predicates (WHERE clause criteria) in the user’s query, then handles it from there like any other SQL statement (optimizing, parsing, checking the SQL cache, executing, etc.)

The red text above is incorrect. My problem is found in my misunderstanding of how the view’s defining query is stored. If the defining query follows the common rule of using an explicit column list, it will store that SELECT statement as-is, preserving aliases, character case, comments, etc.  But if the defining query uses SELECT *, Oracle probes the data dictionary of the underlying objects to get every current column name. It then re-writes your “dynamic” SELECT statement to be static, replacing the “*” with a list of upper-cased, double-quoted column names as they stood at the time of view creation.

With a table defined like this:

DROP VIEW dyn_view;
DROP TABLE test_tab PURGE;

CREATE TABLE test_tab (
 ID  INTEGER NOT NULL
,NAME VARCHAR2(100)
,DESCRIPTION VARCHAR2(500)
)
/

INSERT INTO test_tab VALUES (1, 'First Row', 'I am the first row');
COMMIT;

And a “dynamic” adjustable view foolishly created like this:

CREATE VIEW dyn_view AS
SELECT * FROM test_tab;

We can look at how Oracle processed our view, whether it preserved the SELECT * or exploded it into an explicit, static column list:

SQL> SELECT text FROM user_views WHERE view_name = 'DYN_VIEW';
TEXT
----------------------------------------------
SELECT "ID","NAME","DESCRIPTION" FROM test_tab

As you can see above. The data dictionary now contains the current list of columns in test_tab, not the dynamic SELECT * as hoped for.

We can query the view just to see it in action before the table change:

SQL> SELECT * FROM dyn_view;
ID NAME      DESCRIPTION
-- --------- ------------------
 1 First Row I am the first row

Now let’s alter the table, as if we were the merciless department constantly changing the remote table we depend on:

ALTER TABLE test_tab
  ADD load_date DATE DEFAULT SYSDATE NOT NULL;

Did the content of the view happen to change because we changed the underlying object?

  
SQL> SELECT text FROM user_views WHERE view_name = 'DYN_VIEW';
TEXT
----------------------------------------------
SELECT "ID","NAME","DESCRIPTION" FROM test_tab

Nope, sadly as seen above, it is the same definition as it was before. So it is not possible to have a stored query that says “give me whatever you’ve got at the present time.” Instead, Oracle is going to force us to always store a static column list at the time of view creation.

Does the new column exist on the base table?

SQL> SELECT * FROM test_tab;
ID NAME      DESCRIPTION        LOAD_DATE
-- --------- ------------------ -----------
 1 First Row I am the first row 2017Aug17 16:09:39

Just to be doubly-certain, has our view adjusted to include it?

SQL> SELECT * FROM dyn_view;
ID NAME      DESCRIPTION
-- --------- ------------------
 1 First Row I am the first row

No, it has not. Well, that’s that.

So it turns out, the view has to be-recreated with an altered explicit column list to pick up the new column, or the original SELECT * creation script must be re-run:

CREATE OR REPLACE VIEW dyn_view AS
SELECT * FROM test_tab;

We can look at how Oracle processed our view, whether it preserved the SELECT * or exploded it into an explicit, static column list:

SQL> SELECT text FROM user_views WHERE view_name = 'DYN_VIEW';
TEXT
----------------------------------------------
SELECT "ID","NAME","DESCRIPTION","LOAD_DATE" FROM test_tab

And there it is. Now the view finally sees the new column.

Note: While researching the Oracle docs for this topic, I finally found that Oracle does spell this out clearly in the Database Administrator’s Guide, in the section on Managing Views, Sequences and Synonyms, under the heading Expansion of Defining Queries at View Creation Time. It says “When a view is created, Oracle Database expands any wildcard (*) in a top-level view query into a column list.” Whether it said this in the Oracle 7 manuals in 1995 when I first learned about views, is probably not worth pursuing.

It wasn’t until our application broke that I discovered my clever plan to have the view adjust dynamically was fundamentally flawed and would never work. It made me feel like it was my first week on the job with Oracle all over again. Because I had always followed the best practice of explicit column lists, I’d never run into this gap in my knowledge. Quite disappointing, but I learned something new, so it was still a good day.

BULK COLLECT and COUNT() do not raise NO_DATA_FOUND

by

I thought I knew bulk operations well.
Today while coding up a quick hunk of PL/SQL, the code did not behave as expected.
Basically I’d had a senior moment and forgotten something that I think my novice DBA and developer readers might benefit from.

When you try to select some rows from a data structure using a filter, for which none of the rows in the structure are a match, in a SQL client tool, you’ll simply be told that you received 0 rows. But if you are doing the same select statement in PL/SQL to get the output INTO some waiting variable(s), Oracle will raise the NO_DATA_FOUND exception.

DROP TABLE my_emp;

CREATE TABLE my_emp AS SELECT * FROM hr.employees;

SET SERVEROUTPUT ON
DECLARE
   l_email   my_emp.email%TYPE;
   l_last_nm my_emp.last_name%TYPE;
BEGIN
   l_last_nm := '&&last_name';
   SELECT email
     INTO l_email
     FROM my_emp
    WHERE last_name = l_last_nm;
   dbms_output.put_line('Email Handle: ' || l_email);
EXCEPTION
   WHEN no_data_found THEN
      dbms_output.put_line('Could not find employee given last name: ' || l_last_nm);
END;
/

Which produces this output if we give it a non-existent employee last name of “Bogus”

SQL>
Table dropped
Table created
Could not find employee given last name: Bogus
PL/SQL procedure successfully completed

This use of SELECT into a PL/SQL block is known as an explicit cursor and you must code for the NO_DATA_FOUND exception if you use it.
This is a good thing.

Unfortunately, once a new developer gets used to this NO_DATA_FOUND exception, they tend to get carried away with it and use it for basic UPDATE and DELETE DML, which is useless. Instead use cursor attributes SQL%FOUND or SQL%ROWCOUNT to determine if the UPDATE or DELETE worked.

I’ve also inherited lots of code in the past that had little functions and SQL that performed “existence checks” to ensure the data is present before attempted to do DML on the keyed data. This approach is entirely unnecessary and produces code that will never be exercised.

SET SERVEROUTPUT ON
DECLARE
   l_count   INTEGER := 0;
   l_email   my_emp.email%TYPE;
   l_last_nm my_emp.last_name%TYPE;
BEGIN
   l_last_nm := '&&last_name';
   SELECT COUNT(*)
     INTO l_count
     FROM my_emp
    WHERE last_name = l_last_nm;

   IF (l_count > 0) THEN
      SELECT email
        INTO l_email
        FROM my_emp
       WHERE last_name = l_last_nm;
      dbms_output.put_line('Email Handle: ' || l_email);
   END IF;

EXCEPTION
   WHEN no_data_found THEN
      dbms_output.put_line('Could not find employee given last name: ' || l_last_nm);
END;

Lines 21 to 23 will never be hit. Why? Because COUNT() does not raise the NO_DATA_FOUND exception. It returns 0 if no rows match the filter.

Well, my senior moment today was forgetting that BULK COLLECT behaves in a manner similar to COUNT().
The following anonymous block retrieves all attributes for employees who match the given last name.

SET SERVEROUTPUT ON
DECLARE
   l_count    INTEGER := 0;
   l_last_nm  my_emp.last_name%TYPE;
   TYPE       tr_emp_tab IS TABLE OF my_emp%ROWTYPE;
   lr_emp_tab tr_emp_tab;
BEGIN
   l_last_nm := '&&last_name';
   BEGIN
      SELECT *
        BULK COLLECT
        INTO lr_emp_tab
        FROM my_emp
       WHERE last_name = l_last_nm;

      IF (lr_emp_tab IS NOT NULL AND lr_emp_tab.count > 0) THEN
         dbms_output.put_line('Found employees:');
         FOR i IN lr_emp_tab.first .. lr_emp_tab.last LOOP
            dbms_output.put_line(lr_emp_tab(i).first_name || ' ' || lr_emp_tab(i).last_name);
         END LOOP;
      END IF;
   EXCEPTION
      WHEN no_data_found THEN
         dbms_output.put_line('Could not find any employees given last name: ' || l_last_nm);
   END;
END;
/

If we run it for “De Haan”, “King” and “Bogus”, we get the following output.

SQL>
Found employees:
Lex De Haan
PL/SQL procedure successfully completed

SQL>
Found employees:
Steven King
Janette King
PL/SQL procedure successfully completed

SQL>
PL/SQL procedure successfully completed

That last result surprised me. If there were no rows to put into the waiting collection, just like the simple explicit cursor INTO above, I was expecting it to raise the NO_DATA_FOUND exception and spit out the “Could not find…” sentence to the screen. What I had forgotten — and am sharing so you don’t have to learn the hard way — is that if the explicit cursor is using BULK COLLECT and no rows are found, it gives you an initialized empty collection, which we verify with the code listing below.

SET SERVEROUTPUT ON
DECLARE
   l_count    INTEGER := 0;
   l_last_nm  my_emp.last_name%TYPE;
   TYPE       tr_emp_tab IS TABLE OF my_emp%ROWTYPE;
   lr_emp_tab tr_emp_tab;
BEGIN
   l_last_nm := '&&last_name';
   SELECT *
    BULK COLLECT
    INTO lr_emp_tab
    FROM my_emp
   WHERE last_name = l_last_nm;

   IF (lr_emp_tab IS NULL) THEN
      dbms_output.put_line('The receiving collection is atomically NULL');
   ELSE
      IF (lr_emp_tab.COUNT = 0) THEN
         dbms_output.put_line('The receiving collection is empty');
      END IF;
   END IF;

   IF (lr_emp_tab IS NOT NULL AND lr_emp_tab.count > 0) THEN
      dbms_output.put_line('Found employees:');
      FOR i IN lr_emp_tab.first .. lr_emp_tab.last LOOP
         dbms_output.put_line(lr_emp_tab(i).first_name || ' ' || lr_emp_tab(i).last_name);
      END LOOP;
   END IF;
END;
/

If we run it and provide the last name of “Bogus”, it confirms that what BULK COLLECT does is initialize the collection and skip putting anything in it.

SQL>
Table dropped
Table created
The receiving collection is empty
PL/SQL procedure successfully completed

Knowing this, I was able to remove the exception section, and as a side effect made the code more simple and correct.

Live well!

Dropping a View Drops the Instead-of Triggers

by

On top of using this blog to speak on data and database design and development, I’ll also be using it as a notebook, documenting the moments of discovery and clarity that occur when dealing with problems. I wish I’d been taking notes this way since I began my career in 1995, instead of just hoping I’d remember the next time I ran into the same problem.

Today I had to learn the hard way (or re-learn; did I learn this before?) that when you replace a view that happens to have an instead-of trigger attached to it, the CREATE OR REPLACE will drop the instead-of trigger silently. CREATE OR REPLACE lulled me into a false sense of security. It seems to imply that the object and its dependencies will not be dropped, but left alone as the source code for the view is replaced in the data dictionary.

Not so.

This “discovery” was found due to things that broke in production. My tested script replaced the view, but silently and viciously removed the important instead-of trigger that belonged to the view.

Lets have a look to be sure I’m not hallucinating:

DROP VIEW my_emp_vw;
DROP TABLE my_emp;

CREATE TABLE my_emp AS SELECT * FROM hr.employees;

CREATE OR REPLACE VIEW my_emp_vw
AS
SELECT * FROM my_emp WHERE my_emp.department_id = 90;

CREATE OR REPLACE TRIGGER my_emp_vw_iot
  INSTEAD OF INSERT OR UPDATE OR DELETE
  ON my_emp_vw 
  FOR EACH ROW
DECLARE
BEGIN
  IF (inserting) THEN
     INSERT INTO my_emp
        (employee_id
        ,first_name
        ,last_name
        ,email
        ,phone_number
        ,hire_date
        ,job_id
        ,salary
        ,commission_pct
        ,manager_id
        ,department_id)
     VALUES
        (:new.employee_id
        ,:new.first_name
        ,:new.last_name
        ,:new.email
        ,:new.phone_number
        ,:new.hire_date
        ,:new.job_id
        ,:new.salary
        ,:new.commission_pct
        ,:new.manager_id
        ,:new.department_id);
     
  ELSIF (updating) THEN
     UPDATE my_emp
        SET first_name = :new.first_name,
            last_name = :new.last_name,
            email = :new.email,
            phone_number = :new.phone_number,
            hire_date = :new.hire_date,
            job_id = :new.job_id,
            salary = :new.salary,
            commission_pct = :new.commission_pct,
            manager_id = :new.manager_id,
            department_id = :new.department_id
      WHERE employee_id = :new.employee_id;
  
  ELSIF (deleting) THEN
     DELETE FROM my_emp
       WHERE employee_id = :old.employee_id;
  END IF;
END my_emp_vw_iot;
/

Now that the objects have been created, let’s look at the state and existence of our objects in the data dictionary.

SELECT object_name, object_type FROM user_objects WHERE object_name LIKE 'MY%';

OBJECT_NAME     OBJECT_TYPE
--------------- -----------------------
MY_EMP_VW_IOT   TRIGGER
MY_EMP_VW       VIEW
MY_EMP          TABLE

Now lets edit the view in place (in actuality it is dropping the view and re-creating it):

CREATE OR REPLACE VIEW my_emp_vw
AS
SELECT * FROM my_emp WHERE my_emp.department_id = 50;

And check whether the trigger was maintained after the CREATE OR REPLACE

SELECT object_name, object_type FROM user_objects WHERE object_name LIKE 'MY%';

OBJECT_NAME     OBJECT_TYPE
--------------- -----------------------
MY_EMP_VW       VIEW
MY_EMP          TABLE

Sure enough. The dependent trigger MY_EMP_VW_IOT was not maintained. It was silently dropped during the REPLACE part of the CREATE OR REPLACE VIEW statement. So be careful. Do better than I. Remember this every time you script a view replacement. The instead-of trigger will have to be re-created.

Live well.

Why DB Sherpa?

by

I’ve maintained the DB Artisans site for years, primarily as a place to ftp things and keep all my stuff. But as a website written from scratch in 2001 XHTML, it was not suitable as a blog.

So I decided to have a little fun and begin a blog. Named it DB Sherpa, partially due to:

  • my passion for mountains and the activities they afford
  • my hobby collecting Nepali khukuri knives
  • the rich metaphor of helping other database “climbers” successfully navigate the slopes of Oracle design and development
  • the domain being available.

I love sharing what I’ve learned in the course of of using Oracle for 20+ years. But that has mostly taken the form of Oracle user group presentations. In the early days, I’d get my PL/SQL and Oracle fix haunting the halls of RevealNet with Adrian Billington and William Robertson. There was another really active user, but I forget her name. Was it Barbara? And there was the phantom nitpicker (long story for another day). Then life intervened. Consulting 60+ hours per week, children, church service and a steady string of hobbies. I dropped off the radar on the web for some time and haven’t really been back since RevealNet died and Quest and Oracle forums took over. Frankly, I think we have enough gurus on the web. And some of the bloggers and writers do such a great job of research and answering questions, that I don’t feel a burning need to replicate or improve on their efforts. Tim Hall’s site, for example, is something I still turn to for short, concise explanations and examples. Steven Feuerstein’s long legacy of books, sites, libraries and videos are another treasure, as well as blogs from the aforementioned Billington, McDonald, McLaughlin and many more. All great.

Instead, I’ll use this blog to talk about best practices, templates, standards, and agile principles as applied to database design and development. These are subjects that normal frontend developers eat, sleep and breath. But for some strange reason, they are anathema to most database administrators and way too many database modelers and developers. Over the years I’ve found that “database people” love going it alone, starting from scratch and reinventing the wheel. It’s my mission to put a stop to that. It will be a notebook of sorts as well, documenting the problems I’ve encountered and (hopefully) their solutions.