Hacker News Reader

Comment by dragansah | Hacker News Reader

What extra knowledge would you advice that one should combine with postgres. Web development vs OS knowledge for example?

View on HN

Heck, you don't need anything else. I've worked with SQL consultants who are paid big bucks to come in and optimize a company's "enterprise analytic reporting" (i.e. HUGE queries run on non-production databases). All these guys do is optimize SQL queries. They're valuable because they can reduce the runtime of a naively-written report from 100 hours to 1 hour.

Are you talking about SQL Server analytic environments like MS Business Intelligence suite? Yes that's a lot more tooling than postgres, probably 10x of times bigger than all the postgres tools out there. Most of postgres tools I've used are command line tools like pgtop or pgbadger.

Mmm, the company I met them at was using Business Objects (Clarity/Crystal, etc.) which was where the inefficiency arose. But the consultants would come in and write optimized queries against the underlying SQL database because the Business Objects reporting was so inefficient. Basically they dealt with optimizing some very long, complex queries.

And yes, I think typically orgs. today are using SQL Server or Oracle for these databases.

How does "big bucks" compare to the "big bucks" in other computer specialties, like security?

There's this thing I wish existed but AFAIK doesn't: a guide that shows computer people the relative salaries of a bunch of different specialties. So people who have one specialty but would enjoy something else can see what the salaries are like.

Glassdoor is a very rough start on this.

I have no idea. All I meant to imply there was that they are highly valued by the companies that contract them, because they typically are called in as a last resort when the organization's reporting situation has degenerated to being basically unusable.

From my experience tackling a postgres server that is under-specced, a good knowledge of file systems (block devices, NFS, SANs, etc.), OS caching and kernel settings for maximum memory and such (check the postgres docs). Ofcourse, this only becomes an issue once you have a decent amount of data that does not fit into memory - before that, postgres will keep everything running relatively smooth without much tinkering.

For postgres itself, make sure you know what all the settings in the configuration means, why they make sense 90% of the time and definitely do not make sense in 10% of the time (such as a low memory server with super-fast SSD disk arrays). And in general ofcourse a good knowledge of SQL, index usage/performance. Postgres extensions (arrays, JSON, etc.) are decent, but in my experience it's something you can get into relatively fast if you are solid on the rest.

Reply on news.ycombinator.com