Logging SQL Commands of Selected Users in PostgreSQL

Recently looked a bit into logging PostgreSQL for one of our production setups. The reason was that we wanted to find a secure way to log the SQL commands run by ordinary users but ignore the commands run by the server users. This was for security and auditing purposes. We tried logging all SQL commands once - was enough to bring the disks to a total halt due to the traffic.

After some investigation, turns out the trick is using the ROLES settings to turn on logging for the users you are interested in. As long as the user is not a superuser, this works fine. Even with superusers, you should at least see the command that turns off logging. A snippet of the configuration:

CREATE ROLE viewer;
CREATE ROLE updater;
CREATE ROLE server;

CREATE ROLE admin CREATEDB CREATEROLE;
ALTER ROLE viewer, updater, admin SET log_statement TO 'all';

CREATE ROLE ken LOGIN INHERIT;
GRANT updater TO ken;

This means that all users with viewer, updater or admin role will have all their statements logged. Of course, you still have to set logging to a secure location.

Burnt Out Light Bulbs vs Burnt Out Managers

Encountered a great way to describe the management mindset during a weekly chat recently. Say there's a burnt out lightbulb in the office. You're from the maintenance department. What you perceive as the problem and how you go about resolving it is very different depending on whether you're normal staff or manager/team leader:

• Staff: Problem - burnt out lightbulb. Solution - Replace bulb.
• Manager/Team Leader: Problem - team has not replaced lightbulb. Solution - examine team and processes to improve them and ensure that all burnt out bulbs will be dealt with in a timely fashion in the future.

This highlights the major shift in thinking anytime someone begins moving up the management ranks. Too often, I've seen recently promoted team leaders/managers try to take all the burden of work onto themselves. Anytime they see something wrong, they try to fix it themselves. That is not their job and in the long run, it only burns them out. It is a short term solution that merely masks the underlying problems.

Management isn't about doing the task. It's about training, enabling and motivating your team to do the tasks. The focus is no longer on what you as a person can do but about what you can enable your team to do. You must clean out the obstacles in their way, provide missing training and organize resources so that the team functions at peak productivity.

Another example is watching a team that is struggling because of insufficient teammates. Because there aren't enough people around to do the job, the manager often rolls up his/her sleeves and jumps into the tasks, trying to add 1 more person's productivity to the team. That is fine if and only if the manager has already done everything in his/her power to increase team size. Has every avenue to expand the team been thoroughly explored? Are there no other resources the manager can beg/borrow/steal? In many cases, it is better to let team productivity suffer in the short term while management resources are devoted to building for the future.

Being a manager isn't about what you can do. It is all about what your team can do. It is a very different way of thinking.

Slave or Free Citizen?

My attitudes towards work has changed a lot over the years. At graduation, I had the usual, "Get into big corporation and work hard" mentality. Over the years, that has changed and mellowed a lot as I have worked with and in several large and dysfunctional organizations. I have come to figure out what I wanted out of life and what type of organization would best suite my needs. I have also learned to laugh at the misconceptions that I had at graduation.

That's why articles like this annoy me. Entitled "4 Things You Should Never Say to the CEO", it gives readers the impression that they must work in fear of the single mistake that will destroy their careers and ruin their lives forever. It suggests that CEOs/bosses are like Greek gods, capricious and unpredictable. Employees, even Vice-Presidents, must tremble in fear.

In the current recession fearing climate, this article and many others like it give the impression that the employee is little more than a slave. They live in fear for their jobs, work only because they have no other choice and are at the mercy of their bosses. Unfortunately enough, too many believe this. There are too many people out there working under miserable conditions and accepting abuse that they should not. The term wage slave exists for a reason. Nothing could be further from the truth.

We are all free beings and we should act like it. In my opinion, there are a few things that distinguish a free being from a wage slave:

• Attitude
• Financial Independence
• Ability

ATTITUDE

This is the most critical part of being free - you must believe that you are. No matter what you encounter, you always have a choice and you _choose_ to be where you are. No matter what, there is always another option. It might not be any more pleasant than where you currently are at, but you chose to take this option rather than one of the many alternatives

This is liberating in a few ways. Firstly, since the choice is yours to make, you are free to unmake your choice or to choose something better if things change. If your boss or co-worker is an unbearable pain, then do something about it! You have many options, including switching jobs. You are never truly trapped.

Second, by realizing that you chose this option out of many others, you keep your eyes open for other potentially better choices that come by. Too many people fail to pay attention and miss the many other better opportunities out there.

Third, since it was a choice, you know your reasons for staying in this job. You know what benefits and drawbacks of this choice as opposed to the others you could have chosen. This reminds you of the positive reasons why you are here, to balance any potentially negative reasons.

Finally, knowing that its your choice makes it your responsibility. For many, this means that instead of passively accepting, they are constantly looking for ways to improve their situation.

FINANCIAL INDEPENDENCE

Many people say that they must stay at their jobs because they feel they are dependent on the pay check. They cannot quit because the loss of income would be disastrous to them.

Financial independence is the solution to that. It's unfortunately far too large a subject to be properly covered here. However, you do not need full independence to be free. You merely need a sufficient buffer to cover you until you have secured another stream of revenue. Often a reserve cash buffer of six months salary is more than enough for this.

Building this reserve takes some effort of course but for most, it is quite achievable. It requires discipline, sacrifice and living within one's means. However, the feeling of liberation and freedom when you have achieved it is worth it. The idea that you can quit the next time someone attempts to mistreat you at your job gives an immense feeling of satisfaction and confidence.

ABILITY

Finally, being really good at what you do opens up a lot more options. Even during a recession, all the competent people I knew were in demand and able to find employment with ease. The market for good people is always tight, no matter how tough it may be for others.

Once again, volumes can be written about the need for constantly improving one's skills and ensuring they are relevant. Just do it. Keep improving and your ability to earn will increase too.

This has a side benefit of reducing your need for financial independence. The better you are, the less financial reserve you need. It is also easier for you to earn money and save it.

FINAL THOUGHTS

Everyone's starting point in life is different. Not everyone has the same array of choices and some are more fortunate than others. That said, it is still our choice and still in our power to make a difference in our lives. We do not have to take the abuse that dysfunctional organizations dish out.

This blog post started as a rant against the poor way many organizations/managers treat their people - as slaves, not as free humans. However, this condition will last for as long as we allow it. As long as enough humans accept abuse and slave-like treatment from their employers and managers, this situation will not change.

If you are unhappy with the way you are being treated, you have to demand better treatment for yourself. However, you can only do this after making sure you are in a position to make these demands. Attitude, financial independence and ability. Get to work collecting them :)

Getting promoted to the level you are performing at ...

Some of the stuff I write about aren't my own thoughts but thoughts from fellow front-line managers. In particular, today's thought comes from Cindy:

You do not get promoted because you are doing your job well. You get promoted because you have been performing at the next level long enough for people to notice and reward you.

This came out of a discussion about how some people (fortunately not from Exoweb) were thinking that just because they had just started doing well at their jobs, they were due a promotion/raise. Coming from the other side of the trenches, this doesn't seem really realistic to me.

First - you never promote lightly. For the most part, you cannot demote someone. You end up having to terminate that person if you make a mistake. So until you are absolutely sure this person can perform at the higher level, you take your time with the promotion.

Second - humans have highly variable performance. Someone who is highly motivated today may be very demotivated tomorrow for no apparent reason. If what you are seeing is one of those peaks, promoting the person would be a mistake. One week later, the person could very be performing at a much lower level again. You need proven, sustained performance above the minimum level for the position to be promoted to.

Finally, a person doing well for the current position does not merit a promotion. Not unless you want the Peter Principle throughout the organization (people getting promoted to their level of incompetence.) A person doing well in their current position may get raises and bonuses, but never a promotion. Proof that one does well at the current position is rarely proof that the person would do better at a higher level. The skills are often different. The skills required for a software developer are different from that of an architect, project manager, etc.

Year End Thoughts ...

The year draws to a close and it is time for the yearly retrospective again. Time really does pass quickly:

2007 RESOLUTIONS

So how did I do? From the 2006 year end retrospective, I had the following two goals:

• Make myself (almost) redundant
• Teach, not do

The first part is pretty much complete and in some ways through no real effort on my part. What happened in 2007 was that I stood aside and let my team leaders take on more and more responsibility. They all rose to the challenge and the team functions pretty well without me. These days, my role is mostly HR management with a small bit of project management on the side. The PM work isn't even because it is really necessary, but more because it is much faster for me to do it due to language and experience issues. My team leaders are a bit overworked at times so I jump in here and there to balance the load.

The second part turned out to be quite a bit different from what I expected. Rather than having long lectures and training sessions on how to do the work, it turned out to be a lot more effective to just let people do the work and give feedback where necessary. My team members are good enough that, for the most part, you can just throw them in and yell, "have fun swimming!" Now and then, feedback from gathered from peers or my personal experiences helped several people to improve what they were doing even better. However, it would be quite laughable to claim that I really taught them anything. For the most part, they figured out how to do things themselves and often came up with new ways that were far superior.

So in conclusion, both goals were achieved but in ways quite different from what I envisioned. I suppose this is one of the benefits of working with really good people :)

2007 LESSONS

The following are some of my major lessons for 2007. All these topics will probably be covered in more detail in later blogs:

• Timely feedback
• Groups also grow and mature
• Really learning from your team mates

If 2007 is anything to go by, the greatest value a manager in Exoweb provides is in giving timely feedback - both positive and negative. As mentioned earlier, I found that I did not really have to teach much. However, I did have to tell people how far along they were towards achieving the goals set for them. Without clear feedback, people are either demoralized by the uncertainty of their performance or wrongly assume they are doing well/badly. In retrospect, some of the people that did not work out in 2004-2006 may well have done a lot better given the correct feedback and corrective actions early. Things have gone a lot smoother in 2007 as the management team as a whole has gotten better at this.

Another realization was that it is not just people that grow and mature. Groups somehow do too. They take a life and culture of their own, often picking up best practices that make the group far superior than merely adding the individual members together. One clear example was when we threw a group of new people into a project. Although everyone individually was quite competent, they were all new and unfamiliar with best practices the rest of Exoweb has gained through bitter experience. As a result, the team repeated all the same mistakes and was on the verge of turning dysfunctional. Ultimately we disbanded the project and team, merged everyone into mature teams and things work out much better after that.

2007 was also the year I learned the most from my teammates. Partly because they were getting comfortable and confident in their teams/roles, partly because there was much more communication this year. The many subtleties of working with people, what works, what doesn't - all these I have learned from talking to my colleagues. Some give me feedback on what they have seen me do, giving me a chance to tweak methods better. Others try new things that have obviously good results. These I adapt into my own daily routine. Overall, 2007 was the year I learned the most about managing and working with people - even more than my days in evil MBA school.

There of course is a lot more I have learned over the course of the year. In many ways, working in Exoweb is like being in school - you are always learning new things every day. May this always continue.

2008 RESOLUTIONS

Strangely enough, I cannot think of anything that I would make as a new year's resolution. Sure, I can see many areas that need improvement/fixing, but these are things that will naturally fix themselves over the course of time. Maybe I'm getting complacent, but I honestly think that Exoweb has built itself a self-sustaining culture that will continuously improve itself as time goes on.

If anything, my resolution for 2008 is to ensure that 2008 is even better than 2007. 2007 was a really good year, but one should always try to strive for more. I would like to make things even better next year, from all aspects. What specifically, we'll figure it out as we go. However, Exoweb in 2008 should be an even greater place to work in than the years before ...

Language learning on FOSS vs OSX

My primary desktop is OSX and for the most part, it suits my purpose well. 99% of the time, everything just works, there are few hassles and things behave the way you expect them to. The Ubuntu releases are close but not quite there yet for me.

As great a desktop as OSX is though, it only shines in the most commonly used functions. In many niche areas, FOSS can provide superior solutions by virtue of the huge variety of applications, developers and freedom to develop on it. One such area that comes to mind these days is language learning.

I'm barely able to read in Chinese and trying to work my way through a website or document is pretty much impossible without a dictionary. I particularly need a dictionary that can immediately translate words I highlight on screen. On OSX, the options are pretty limited. Most solutions are shareware or paid software. Right now I'm using TranslateIt! which works fairly well. I highlight the word(s) I want translated, hit a keyboard combination and TranslateIt! pops up, hopefully with the translations. It is one extra step though. The paid version for TranslateIt! includes the functionality to get immediate translation after I highlight it.

My colleagues on FOSS desktops don't need to pay for this. StarDict comes with most distributions and does the highlight/translate thing right out of the box. It's invaluable in a mixed Chinese/English environment like Exoweb. Besides the software, the power of FOSS shows up in StarDict's dictionaries, which are varied and extremely useful. So much so that TranslateIt! actually uses StarDict dictionaries and all the translations I am using are StarDict's. Without StarDict, TranslateIt! would actually be useless to me.

My favorite StarDict dictionaries include (all zh_CN -> en):

• cedict-gb dictionary (has pinyin and tone marks. Must have)
• langdao-ce-gb (a much larger vocabulary but translations sometimes not precise. No pinyin)
• Chinese idiom dictionary (a dictionary translating chinese idioms. Unfortunately the translations are chinese->chinese and some colleagues have said its translations are suspect)

Two are under the GPL while the cedict library is under its own license similar to cc-by-nc.

While I'm sticking to TranslateIt! until such time StarDict works natively under OSX, I would simply be unable to read anything at all in Chinese without StarDict's dictionaries.

We Make It Up As We Go

Every now and then I find myself repeating the same thing in weekly chats, so I try to note them down in a blog post so the Exowebers that actually read the Exoweb Planet have a chance to see it. This particular one has to do with how we developed our work processes and best practices. There is no mystical method, no profound MBA insights or deep pondering. Quite simply, we make it up as we go.

That's not to say all our current processes are arbitrary. Every process or practice we have, we evolved to overcome a perceived problem. We experiment with different processes and those that work, we kept. Those that failed we learned from and moved on. The entire organization is a work in process, continuously trying to improve itself.

There are a few core values we do have, which very much reflect those of agile methodology: People over process, working code over documentation, etc. We also believe very much in making this a great place to work. Since you spend a huge portion of your waking hours at work, why wouldn't you want to make it as pleasant, as fun, as challenging a place as possible?

From these core values we simply figure things out as we go. For example, our current process of code reviews was triggered by the realization that our code quality wasn't good enough, that too much bad code and bugs were leaking into production systems. We tried the NASA style group code reviews but found that much too heavy. We then tried having a couple of core code reviewers doing all the work, but found that it did not scale and that the benefits of code review actually were disproportionately accumulating with the core reviewers. Our current method is a lightweight team review process that seems to combine the benefits of code review while reducing the cost. We are likely to make more tweaks and changes in the future, depending on future needs and ideas.

What does this mean for Exowebers? Most important of all, it means that it is the past ideas of all of us that have created the great environment we have. It means it is your future ideas that will make us an even better place to work. You need to pay attention to your environment, be willing to question our processes and methodologies, and contribute new ideas when they come to mind. No process is sacred. Given good enough reason, visible enough benefits, anything can be changed.

You can contribute ideas openly, by floating RFCs by email, or you can quietly suggest them to your team leaders in weekly chats. Whatever method you choose, it is important that the ideas are communicated and considered. Only then can we as a company improve. Only then can we make this place an even better place to work.

Yes, this includes higher salaries too. If you have ideas on how to make us more profitable, we can all share in the profits in the form of higher salaries :)

What Happens When You Turn Fsync Off On Postgres

We use the PostgreSQL database extensively to handle a fairly large amount of data. Our largest single database is over 25G in size, with a fair amount of transactions going through it daily. As such, we've had to do a lot of optimization over time. One of our experiments was turning fsync off on one of our non-critical databases. In retrospect, this probably was not that great an idea ...

This database was a non-critical but fairly write intensive database. It logged a lot of information, largely in the form of inserts. Inserts in postgres can be a bit slow sometimes since a insertions tends to lock the same section of the index until the insert is complete, forcing all inserts to go in sequence. Updates are usually a lot faster if you're updating different rows since they don't all rely on the same section of the index and can often be done simultaneously.

The fsync option slows this down even further, since postgres then waits for the data to be flushed to disk successfully before continuing on with the next operation. Not a problem for low traffic databases but if you attempt to insert hundreds of transactions a second, the milliseconds spent waiting for the disk to write the data completely really hurts. fsync ensures data integrity but at the price of speed, especially in the case of unexpected power failure.

Since this was a non-critical database and losing data wasn't really a problem (we could either recreate it or live without it), we turned fsync off on this database. All went well for months, until we actually did suffer a power failure. During the busiest period possible. Good old Murphy.

At any rate, once we brought everything back up, things seemed to work as usual ... for about 30 minutes. Then we realized our servers were frequently losing connection to this particular database. Investigations revealed that the postgres processes were terminating themselves with messages like "Error: out of memory" or complaints about data inconsistency. Yep, we got our first corrupted Postgres database. The first one I've encountered in over 7 years of using this database.

I have to admit, I had very little clue on how to recover a corrupted database and each database was corrupted slightly differently. Initially it appeared only the indexes were damaged and a reindex removed most of the problems. Later we found that there was some damage to the tables themselves (took a long time to find that) and we attempted to restore through a backup. The Write Ahead Log (WAL) backup proved to be useless. Those were corrupted or inconsistent. Strangely enough, the database could still do a pg_dump, so we just dumped out all the data and reloaded it back in the database. This ultimately fixed everything.

Morale of story - don't turn fsync off unless you really know what you're doing, including how to detect database corruption and fix it. Our biggest problem was that postgres, unlike MySQL, does not scream "Table/database corruption!" immediately. It took us a while to determine what the problem was. Then again, unless you turn off fsync, it is probably something that almost never happens on postgres. I've had tons of corrupted MySQL databases. This is my first corrupted postgres database.

Three Questions ...

This is the English version of Cindy's "3 Questions" blog post, written from a different viewpoint. As a bit of background, Cindy and I are both managers in Exoweb and one of our responsibilities is to carry out weekly chats with the people in our teams. Both of us have been asked these questions before and we figured it would be of interest to Exoweb people in general. I'm writing this without having actually read her post, so it should be interesting to see how our viewpoints differ.

The questions are:

• Does Exoweb give team members a chance to switch career paths?
• Are personnel evaluations transparent?
• If an employee makes a mistake, how will the company handle things?

Does Exoweb Give Team Members a Chance to Switch Career Paths?

Yes. We definitely prefer to use existing, proven and trusted team members to fill open positions, rather than taking a risk with hiring an unknown person from the outside. As long as the person appears to be competent and capable of taking the task, we are quite willing to take a chance.

We have had a few examples of these career changes so far, mostly people switching to project management positions. Two of our admin staff have switched over to project management roles in our software development teams. The admin team handles everything in Exoweb but actual software development, so is a lot more challenging than the name suggests. After battling with unreliable suppliers, organizing events for 40-50+ people and solving the problems that crop up in daily office life, they have proven that they are more than capable of getting the job done. That actually makes them very good project managers as they have the "get things done" attitude.

We had a software developer also try his hand at this a while back. He did a pretty good job at it, but after 6 months, found that it was really not something that he wanted to do and switched back to pure technology. This is also a learning experience that we are quite happy to allow our people to have within Exoweb - the chance to try new things out and discover if a certain path is for them. If it doesn't work out, such is life. You never know until you try and the experience typically makes you appreciate your chosen role more.

Are Personnel Evaluations Transparent?

Depends. We do our best to make obvious to the team member how they are perceived, both by team leaders and fellow team members. Depending on the team lead's management style and workload, we try to schedule chats between once a week to once every couple of months. During these chats, past accomplishments, challenges and issues are discussed, along with feedback on how the team member is doing. These chats are mostly informational and problem solving - for both sides to quickly identify and resolve issues that crop up. They also usually give team members a fairly clear idea of how they are doing.

Of course, not every team lead does things in the same way and there are large variances between each team. As a general rule we try to provide feedback, both positive and negative, as soon as possible and resolve problems before the grow too big.

However, we try to keep these evaluations as private discussions between team member and team lead. In this sense, personnel evaluations are very opaque. We usually do not make it obvious to others when someone is underperforming, even if the under performer knows very clearly that he/she is in trouble. Making this kind of information public while trying to resolve the problem is rarely helpful. We do share evaluations between team leads though, both for redundancy (in case one team lead has to take over for another) and also as knowledge sharing (e.g. how to handle situation x).

If An Employee Makes a Mistake, How Will the Company Handle Things?

In one of the management books I read many years ago, I came across a great story about making mistakes. Unfortunately, I no longer recall where it comes from (if anyone recognizes, please let me know!), but it goes something like this (heavily paraphrased):

There was a young man who had just joined Mega Corp, a huge organization with many capable and talented people. Being very eager to prove himself, the young man throws himself into his tasks and pushes himself to the limits of his abilities. Unfortunately, his eagerness results in him making a huge mistake, one that ultimately ends up costing the company USD10 million dollars.

This young man then walks into his manager's office, confesses to his mistake and finally ends with, "I guess you want my resignation now?"

His manager looks at the young man with a incredulous expression and replies, "are you crazy? We just spent 10 million dollars training you! How can you leave now? Now get back out there and apply your newly gained knowledge to earn us our 10 million dollars back!"

Everyone makes mistakes. As long as one honestly did their best and learns from it, we recognize that this is only natural and move on. We work together with each other to try to minimize mistakes and compensate for each other. We spend less time figuring out who is to blame and more on how to ensure it will never happen again.

For the most part, almost all first-time mistakes are forgiven. The rare exceptions we've encountered are when someone makes a mistake so bad that trust is irrevocably broken. As a highly trust based organization, once someone proves themselves untrustworthy, it is simply not possible to continue working in Exoweb, no matter how much we like the person. Fortunately, it is very hard to make this kind of mistake. It has only happened once in my memory.

Things I Would Like to See More In My Teams

Overall, I'm working with a great bunch of people, so don't let the topic mislead you - I'm really happy with the people I work with. However, nothing's ever perfect and there's always room for improvement. The following are things that have recently made me stop and think, "I wish I saw more of that ..."

• Curiosity
• Sharing of information
• Dissatisfaction

Curiosity

There is an immense amount of information that we don't know. In the IT industry particularly, it is critically important to keep learning and improving ourselves. A little curiosity goes a long way on making this happen.

We all know that the technologies we learn today are often out of date within five years. Even within the same language, the change can be enormous over time. Java users five years ago would not recognize or be able to read some of the code being produced today. Constant learning is critical to a long term career in IT. For that matter, it's possible that the famous Chinese stereotype that people over 30 cannot code may just be because too many fail to learn and by age thirty, their skills are almost useless.

Keeping up with the information flood out there is of course a skill. There is no way to learn it all and a challenge to find those really worth our time. However, a little effort such as by keeping up with a few good magazines and blogs goes a long way.

Sharing of Information

Everyone has different strengths and experiences. We all have learned different things and it is much easier to learn from someone who has already learned something than to figure out things from scratch. Because of this, learning from each other is a quicker path to improving ourselves than struggling on our own.

Yet too few try to learn from each other. This comes in two different forms - some don't like to share. They do not talk about what they have learned, do not mention it to others, etc. They do not blog, nor do they present in ExoForums (Exoweb's weekly information sharing session) nor do they even mention it in conversation. Anything they learn stays with them.

The other problem is actually receiving this information. This is related to the curiosity issue mentioned earlier. Exoweb has a planet listing all the blogs of colleagues, yet there are a fair number of Exowebers who do not even bother to read this. If one will not learn from their colleagues, who's knowledge and experience is most directly related to their work, what chance is there that this person will learn from anywhere else?

Dissatisfaction

Believe it or not, being dissatisfied can be a good thing if channeled properly. Dissatisfaction indicates that there is something that can be improved. By seizing upon this dissatisfaction and making the effort to ensure that improvement happens, one makes one's environment and self better.

It is too easy to notice a problem and shrug it off, accepting that it is a problem that one has to live with. Quite often this is not the case. If we invest a little of our time each day in removing the most irritating, most unpleasant annoyances in our lives, we find that bit by bit, our lives get better and better. Time and resources are limited so we can never fix everything that bothers us. However, as long as we take the time and effort to fix things, each day gets more and more enjoyable. Ultimately life is short. Why make it miserable too?

Most cultures with a strong work ethic believe in enduring hard, boring work to succeed. However, there's nothing in that ethic that says we can't find ways of doing things better. Just because it is hard or boring doesn't mean it always has to be that way. Sometimes it is far too costly to change. Sometimes it is not. Until we have aggressively investigated the problem, we will never know for sure.