Hacker News Reader

Knowing what indexes you need and why is far from being some kind of esoteric specialized knowledge of database internals. That and some basic relational modeling techniques are the very minimum prerequisites to being able to leverage an RDBMS competently. Learning these things is not as difficult as trying to use an RDBMS without learning them.

I can't say I get the comparison to machine code. SQL is probably the highest level language there is.

I agree.

>>Learning these things is not as difficult as trying to use an RDBMS without learning them.

Very aptly put. But I guess mastering SQL is a rather difficult task as SQL is a very high level language (may be even higher than Haskell minus the great type system of Haskell). It may be because of such a high level SQL seems difficult for many people. No wonder, the ORM crap sells so much, they sell almost impossible dreams (read snake oil) to naive people who don't understand much about SQL, data modeling, database system and OS.

Also the relational modeling requires a lot of deep thinking to get yourself an effective schema. The magic art of getting indexes right to a large extent, relies on your understanding of the particular domain and the basics of relational modeling, the database internals don't come in picture too much. Of course, getting enough performance gains and squeezing time/space here and there requires some knowledge of database internals but when you reach to those levels of optimizations, NoSQL based solutions may as well require you to know enough details of their implementations.

Take home lesson I learnt: these things (very high performance, efficiency, scaling) don't come for free.

> SQL is a very high level language (may be even higher than Haskell minus the great type system of Haskell).

I have a solution for that :)

https://github.com/tomjaguarpaw/haskell-opaleye/

Looks great, will try it out.

Yep you can learn the basics of decent SQL in a week if you cant even hack that well get a job at starbucks - working in tech is not for you.

The database should already know what indexes you need just by looking at how you access the data. Explicitly choosing indexes is explicitly making a choice off time-space tradeoffs, and you have to do it at a very low level: which columns do you build indexes on? How many do you build?

It really isn't that far off from choosing the layout of a struct in C and having to know how it's packed and aligned.

>Knowing what indexes you need and why is far from being some kind of esoteric specialized knowledge of database internals.

I'll concede that, but the issue still exists. Does your database have DROP TABLE IF EXISTS or not? Some do, some don't. If it doesn't, how do you achieve the same functionality? A given database offers thousands of operations, and the syntax, performance, and semantics of each varies by quite a bit.

My point is, you have to learn the specifics of the database you're using to do general purpose data storage stuff.

Do you know what tables in your DB need indexes and why? Do you know how to optimally set up your partitions? Do you know why your one query runs fast for a week and then suddenly runs slow, and what you can do about it?

Yes. And I'm absolutely not an expert.

But I think you are missing an important goal of RDBs: data integrity. This is why people like to use this storage method over a NoSQL method.

Need to store subscriptions or news items? Then NoSQL might be fine. But when you need to store data relations, well thats why it's called Relational Data Base.

I advice you never to stop learning even when stuff seems difficult.

hopefully this won't come across as an ad hominem, but

you basically said: This difficult thing is difficult.

why are restaurants so difficult. the menu is too low level for most people's comfort zone. most people just want to reduce their hunger level and obtain flavors.

Everything you highlight is basic level knowledge to work with databases. If you don't know that, you should either learn or not go near them.

However I will agree that "scalable" is starting to get a little complicated with all the various scale up/out options and strategies out there.

I also would guess that for 95% of databases it would be enough to just run a query/performance analyzer and follow the advice it gives you.

You can buy, off the shelf from any number of vendors now, a single box with 512G or more main memory, 48 or more processor cores, and several teras of storage, with the option of SSDs. Like, click click click and a courier delivers it a couple days later, that easy. Running Postgres say, the workload these things can support is insane. Far, far more than 99% of people worrying about "scalability" will ever actually need. This is a solved problem.

True, most corporations are under 1TB of data (per project) so not a big deal.

However scalability is about more than just a big box though. Scale up has always been easy, and should be the first thing to do, but there's still a new class of companies/projects that need HA for 100% uptime and scale-out so they can have relational access across PBs of data.

This is usually done with Hadoop or other "big data" tech but there's no reason regular relational databases can't scale up to this and offer all the benefits and tooling they come with. Closing that gap is still very tough these days without 3rd party vendors or custom extensions.

Everyone even contemplating Hadoop should read this first http://aadrake.com/command-line-tools-can-be-235x-faster-tha...

That's a pointless article. When you have a few GB of data, you can use anything. Command line tools or SQLite or anything in between would've worked fine.

Realistically, anyone contemplating Hadoop or anything bigger than traditional relational databases is dealing with the hundreds of TBs to PBs range which is not going to work with some unix tools.

No, what's pointless is the typical Hadoop workload. Sure there are some people who need it but I'll wager they're not even 1% of the people using it.

Ok, but this is a random tangent. People will always use things they shouldnt, that's their problem.

The point of the thread is that there's still no easy scale-out solution that has come along for relational databases to provide for data that typically gets put into proprietary data warehouse or hadoop installations. Citus data and memsql might get close but the whole industry is still far behind where it should be for this.

I'm not sure I would consider DML and DDL statements complex programming APIs... To tune a database, you need to be able to look at what the query is trying to retrieve, know a bit about what you are storing, and know how to look at an execution plan.

You can usually add an index, but it's often good to know that if you have a correlated subselect that uses an equality match in the where clause, then it's possible that you can influence the database engine by using an EXISTS clause instead - and it's probably more appropriate anyway because that's what you are attempting to find at any rate.

The issue I take with your comment though is that "just store some damn bits" is not really what you are doing. You are adding in actual data into a system that needs to be retrieved effectively.

The truth is, knowing the basics of a relational database should come easily to a developer, or at least one that has paid a bit of attention to set theory and relations.

It's sort of funny in that I don't know if those who struggle with database concepts would have much more luck with a NoSQL engine like MongoDB. Each storage technology has it's own unique challenges, and eventually you are going to need to get to grips with somethat that seems "complex".

I've had no trouble learning the basics of a relational databases. The problem is that the basics don't really transfer between implementations all that conveniently.

It's like how I know the basics of processor architectures. There's registers and caches and interrupts. But now ask me to apply that to a specific machine's code? Now I've got to read the whole manual for that machine to figure out how the 'basics' apply to this particular machine.

Everyone has their stumbling blocks, so I'm not judging you - only that I think it's not that hard for myself and an essential skill for mostly every app developer except in niche industries.

But I guess I'm curious what your stumbling block is. Is the database servers themselves, or is it something like SQL that gets you? SQL was odd to me when I first looked at it due to its declarative nature, and having to think in sets. But it was quite intuitive once I used it for a little bit. Indexes... I knew about them from when I was learning about C++ data structures, so it should be a transferrable skill.

SQL is probably only 1/50th of what a modern database does.

What is the proper method for returning a result set from a stored procedure? Is that a basic DB skill or not? Because that's the kind of thing I end up needing to know on day 2 of the (conceptually simple) queries that I need to implement. "Oh, this query didn't work well? I've done a pseudo-close?"

The problem is that databases solve one very constrained subset of data storage needs elegantly, and then throw on a mountain of hacks to get the rest of the way there. Basic database knowledge gets you as far toward modeling real data in the same manner that basic arithmetic gets you an understanding of modern physics.

You can use a filing cabinet to quickly solve your paper storage problems, but it knows nothing of the type of papers your are stuffing in them.

When it comes time to retrieve your papers, you need to have had them organized in a fashion that makes it easy to look them up.

You're the only one that knows your data and how you need to look them up, and it's up to you to decide what goes in a file folder, how to label them and sort them.

You kind of need to know ahead of time how you are going to look your files up, otherwise you are going to have a hard time finding them.

RDBMSs are like a toolset of best archiving practices implemented digitally and comes with data integrity and a query language built in.

Some different types of storage may look sexier, but come with trade offs. In the end you have to live with your tool choices.

I'm not complaining that you have to build a schema, just that, once you've done so, it should be relatively hands-off, and it often isn't. But maybe I'm biased because I'm subject to a constant stream of customers' issues.

Is any of that really less the case with NoSQL?

Yes. The need for indices is easier to identify on NoSQL: if you want to grab a key in O(1) then you need an index.

However this simplicity comes not from eschewing SQL but because the queries you typically demand are simpler.

I'm not sure I follow that line of argument. If plain SQL is too low-level for you there's a wealth of ORMs that abstract away all the nitty-gritty details.

Up front: I'm thinking about leaving NoSQL because I hate its memory model.

SQL requires joins to get a "document" back. In order to properly optimize that process, you'll want an index. Most DBs don't do that (if I understand them correctly). See 1 for details about PG.

NoSQLs like Mongo lack joins. As a result you pack most of your joinable data into a document. When you need some manner of linked relation, you can store either the key to the collection, or a URI that goes through your API to that collection. In either case, you're pegging against an indexed key (probably the PK).

http://dba.stackexchange.com/questions/53809/need-for-indexe...

https://docs.mongodb.org/manual/reference/operator/aggregati...