Hacker News Reader

I have a LOT fewer requests than you, but this one is just so irritating:

>- Fucking SQL. I don't want to learn your stupid DSL. I want to give coworkers a SQL client a say "go! You already know how to use this!". If I want a new feature, then dammit, build on top of SQL the way PostgreSQL has. Odds are, regardless if its some JSON API or SQL, my language will have a client for it that will be superior than writing raw queries anyway.

It pisses me off to no end these developers have to out-think SQL and re-invent the whole damn new wheel for "efficiency's" sake. It seems that no one takes into account friction costs and instead just waggles their dick around saying "Look how smart this new language is or how I co-opted [erlang,perl,JS,etc] into being the query language!" Just stop it. Some people making less than $150k/year are going to have to use this and they are good at DB analysis but not bullshit esoteric language writing.

Good lord this makes me so mad. I feel your pain.

Almost blasted my screen with yerba mate after the dick waggling remark, kudos.

This 100x this!

I'm sick and tired of this elitist new feature for new feature sake as well. Some of us have to work for a living and would like to go home after the workday instead of catching up on the new flavor of the week.

This! Yes, this is why I like Redis and Cassandra if I'm not going with a standard SQL DB. The first acts like a KV store on steroids and Cassandra has CQL which is almost exactly like SQL except without joins (ok, slightly more than that.) If Cassandra could get it right, why can't everyone else?

That's an amusing (and incorrect) assumption that the smartest people are making >$150k.

That's also not the intent of the sentence, but thanks for putting words in my mouth.

Fair enough. What was the intent? That's certainly what it sounded like.

> Fucking SQL. I don't want to learn your stupid DSL.

I really like one change in LINQ syntax (and probably various other SQL-inspired DSLs), which is to invert the basic query structure, enabling better IDE support:

from thing in things ... (yay, now my editor knows what a "thing" is and can provide better code completion and type checking for the remainder of this deeply-nested behemoth of a query that probably should be expressed as hundreds of simple statements instead of cramming half of the day's work into a single "statement" with no obvious rules for indentation because, after all, run-on sentences are notoriously annoying to read and generally should be avoided).

That alone is worth investing a few minutes to learn something new.

Thank you. Doing a join on an SSD isn't as big a deal as on a spinning platter. Find the N regions of storage and pull 'em in. (replace tricky disk scheduling algos for simple FIFO or priority queue of requests)

Many of these denormalized "document" storage systems are likely to look like real legacy cluster-bombs in a few years.

Well, so long as I'm spitballing my dreamDB's "continuous views", then why not have a concept of "foreign documents"? Let's define "foreign documents" as a type of field that is an exact copy of a document in a separate table. This field gets updated when the original document is updated.

This could apply to denormalization really well! For example: let's say you have a table called "items", a table called "customers" and a table called "sales". A sales document is just a item foreign document + a customer foreign document + a date.

The item foreign document is literally an exact a copy of a document in your items table that gets updated whenever the original gets updated. So if you change the customer information, the customer information in the corresponding "sales" documents get updated.

This is a terrible example because it's not a useful/practical use case, but maybe it could be useful if you have enough data where this join is unreasonable.

You can use the same architecture to update your continuous views as you do for your foreign documents. And add the same syntax with throttling and whatnot.

Something like this is available in postgres, mssql, etc and is called a "materialized view" [0]. In the postgres case, you have to update the materialized view manually, but in the mssql case, the updates are done automatically when the origin data is updated.

[0] - http://en.wikipedia.org/wiki/Materialized_view

Interesting. I knew about PostgreSQL's mviews, but not MSSQL.

Have you looked at rethink DB? http://www.rethinkdb.com

I think it addresses most of your issues, except the SQL one, which I admit is a big one.

>> "I want to make a materialized view that's the result of a query, but that gets updated as new rows get inserted or as the rows it uses gets updated."

RavenDB[0] gets a lot of this right for the .NET stack.

RavenDB creates something very much like your continuous view -- called indexes[1] in RavenDB -- for every query you've run against it. Any successive queries aren't really queries at all, but are results from a pre-computed set.

When new data is inserted, the index gets updated asynchronously, leaving you with eventual consistency for your queries.

Raven creates these indexes automatically when you query it. It then maintains these indexes: long periods without querying an index will relegate the index to idle (updates on low priority), and eventually abandoned (no longer updated). This way, your app hot path -- the queries most often used -- remain the fasts, alleviating a lot of performance issues.

[0]: http://ravendb.net [1]: http://ravendb.net/docs/article-page/3.0/Csharp/indexes/what...

GigaSpaces XAP (http://www.gigaspaces.com) gives you a big fraction of that. You can create an in-memory distributed grid that acts as both an object store and processing platform, that is both resilient (self-healing) and scalable. The CTO of Elasticsearch used to work for GigaSpaces.

Disclaimer: I work for GigaSpaces.

Check out python blaze: http://blaze.pydata.org/docs/v_0_6_5/index.html

http://continuum.io/blog/blaze-datasets

The continuous view concept would be amazing. It's possible to get get most of the way there with triggers, but it always feels like there's a possibility that I might break something that I would prefer to defer to defer to database experts.

These would be great. One thing I had need for at a previous job was a combined search / compute engine.

We basically had a search cluster that would return ids that would get joined into the actual data in another system, then we'd do heavy post-processing and push it off to another system for slicing and dicing.

Not sure if it's any use to anyone else, but a system that allowed people to push search queries into a cluster, do some user defined post-processing and then have the engine pipe that to an endpoint would have been interesting for that use case.

I believe there's some Hadoop eco-system bits that do a bit of this now (Impala comes to mind).

>Fucking SQL. I don't want to learn your stupid DSL... If I want a new feature, then dammit, build on top of SQL

What I hear is "I don't know anything better than the incredibly cut-rate solution I've been taught how to abuse, so I'm opposed to any improvements."

SQL is a human interface language. I absolutely don't understand the desire that people have to use it for IPC. The tremendous set of SQL injection vulnerabilities would never have existed in the first place if people used proper IPC systems for IPC.

It isn't what I am willing or able to learn; I am a good software developer that can pick up your new DSL or whatever crap you want to invent.

But guess what? The guys making $70k/year doing DB analysis and reporting who have little to no software development background can't. And they are the ones that live in the hell you've created.

>And they are the ones that live in the hell you've created.

I didn't create SQL ;)

It's not even that. Everytime I have to write an aggregation in Elasticsearch, I cringe. JSON is a terrible human format, and their annoying nested trees to do the simplest thing really suck to type out. I understand SQL doesn't cover everything a DB may expose, but then allow it for what it can do, or come up with nice syntax instead. Don't make me deal with your AST.

If you make a system that's designed to replace SQL, doing everything SQL does and more, built by people with a lot of SQL experience, great!

But you get a lot of data stores making custom APIs without a comprehensive plan. That's what causes frustration. APIs that aren't generic. APIs that don't handle relations well or at all, sometimes with it bolted on too late.

re: materialized views -- you can kind of fake them in PostgreSQL by creating a view, populating it, and then defining triggers on insert, delete, and updates to your source tables.

(I'm coming from the perspective of having to use Oracle at work - and I'm happy to be corrected if my experiences do not match your wishlist)

> Easy sharding, a la Elasticsearch

Oracle RAC lets you add nodes to the cluster. Helps with CPU - doesn't really help with disk contention

> Fucking SQL. If I want a new feature, then dammit, build on top of SQL the way PostgreSQL has.

There is no good story here for Oracle. Postgres is amazing (and has the potential for more amazing) here. I'm surprised the legions of NoSQL developers aren't contributing to psql.

> For example, I want to make a materialized view that's the result of a query, but that gets updated as new rows get inserted or as the rows it uses gets updated. Let's call it a continuous view or something. Eventual consistency is fine.

This is how (certain) [1] Oracle Materialised Views already work (.. with some effort..) unless I've misunderstood you.

But you have to:

1. Define "logs" on all the included tables (observables)

2. Only define certain types of queries, otherwise they won't dynamically update

3. If not 2, then schedule the updates on a timer/job (eventually consistent)

4. Write your joins with the old syntax (WHERE A.fieldd = (+) B.field)

> I want to be able to choose if a table/db is always in memory or not. I don't care about individual rows - that sounds like someone else's problem

Oracle's In memory feature[0] supposedly offers this though I haven't had a chance to get at it yet.

> While I'm at it, I want a pony, too.

If you've got the money to pay for all the Oracle gear above, then you have more than enough money for a few Ponies ;)

[0] http://www.oracle.com/us/corporate/features/database-in-memo...

[1] https://docs.oracle.com/cd/B28359_01/server.111/b28286/state...

I come from a PSQL background where materialized views are manually refreshed :)

After skimming Oracle's docs, I'd settle for something as simple as:

CREATE MATERIALIZED VIEW my_mview

REFRESH FAST ON COMMIT

THROTTLE 10s

AS ( ... )

Does Oracle use deltas to refresh these mviews? It'd super cool if it did! For example, if the mview is a sum of a bunch of rows, then it could use the logged changes on those rows and its existing results to make the new mview.

Oracle supported both full refresh and incremental refresh as of at least 10 years ago (maybe longer).

Fast refresh requires creating "MATERIALIZED VIEW LOGS" on the source table(s) and covers most (but not all) aggregations/groupings in the mview SQL. Once it's setup, DML to the source tables gets logged to the mview logs and a "fast" refresh of the mview uses it to incrementally update the mview's data.

I'm actually trying to plan out some experiments towards building something towards this sort of vision, and it seems that I may soon be in a fortunate situation where I'll be able to do production large scale experiments towards this very vision, and perhaps be able to open source pieces over time.

Point being, you're articulating a need thats definitely there.

Quick question about the pony. I mean, about Cstore. What do you think is the best option for doing that nowadays? (By that I mean columnar stores that are hopefully optimized with the usual tricks like inlined compression. I know Vertica works but if are there cheaper (free?) alternatives?

Vertica is great, but expensive. I think it's free until 1 TB or something?

Postgres + cstore_fdw looks promising.

Great, I'll check it out! S

I don't know if this is the kind of thing you had in mind but you could try monetdb. I ran some tests a while ago and it performed very well.

It's impractical to write maintainable code in the forms of SQL I'm familiar with. But maybe someone here knows better.

You should be able to abstract logic out to blocks for the duration of the query. Along these lines:

    cset means select id, venue_id from customer

    select venue_name from venue where id = cset.venue_id

cset would be scoped to your connection.

Instead of pages of copy-and-paste queries where the levels are all mixed up with one another. You can get something similar by polluting the namespace (e.g. views, temporary tables) but that's hacky and not the same as good.

Well that's why (in my opinion) you should _generate_ the queries, not _write_ them. Use an ORM or whatever flavor of basically a SQL generator you prefer. Or even easier, use any programming language to concatenate around some strings and build your SQL.

The power of using SQL comes from the fact that, no matter what, it's always there. You can always write/buy/find another client for your DB in another language, and as long as it generates SQL, it's all good.

I mean, you can also use CTEs for that but it is of course limited.

For all of the flaws of managing SQL, it's when I get handed giant swathes of dynamic SQL that I die a little inside.

I've just been reading up on CTEs - thanks. That's the kind of thing I thought would be out there.

I'm definitely in the same camp.

I don't use Redis, but one thing I like about it is that it's a fairly generic storage layer that you can adapt into various data models; you can implement higher-level tools using its lower-level ones. I would love to be able to build a whole database machinery out of specific primitives.

For example, a "column" is basically a primitive you should be able to instantiate using some kind of storage strategy: keep it in RAM, keep it partitioned into chunks, append-only, compressed, sorted, remote etc.

The main thing that I want is to separate data from queries. It's always baffled me that traditional RDBMSes choose to commingle tables and indexes. Why does my commit have to wait for the indexes to catch up? Why do indexes have to be involved in the slow, complicated system of updating tuples that involve transactions, locking, paging and so on?

A better approach is to separate the two entirely:

1. The careful, slower ACID core that journals, fsyncs, keeps my data safe and provides a rigorous data model.

2. The super fast read-only, distributed indexes that organize the data in the most efficient way for querying (mostly through RAM and vectorization).

Now, I don't really need my write node to be a super-complicated distributed, replicated, gossip-based hash-ring-sharded, quorum-coordinated eventual consistency monstrosity. Single master is fine as long as you can keep standby replicas that are easy to fail over to.

What I want is for there to be a whole gaggle of read-only slave indexes that get my data reasonably quickly and are super fast to query. Indexes can be eventually consistent; but they don't need to be safe. Indexes can be reconstructed from the master data, after all. All we need to do is for indexes to ingest a continuous stream of data changes from the master.

Again, the master can be a bit slow: As slow as Postgres, at least. But the indexes, since they don't need to deal with transactions or locking or anything of the sort, can be really fast. Splitting the two up means they can each worry about different things, and apply different tolerances and constraints to what they do.

Today, we accomplish something similar by using ElasticSearch with Postgres. It's not good enough in the long run; they have completely different query mechanisms and data models, for one. It's also difficult to keep ES in perfect sync, and ES is generally heavy-weight; ES indexes are beasts, not very mobile. For example, the schema is mostly static, and changes require cloning the index (which I find weird). Still too fulltext-oriented; it's just not quite as good at non-text stuff. GIS support is lacking. And so on. A uniform query/database system is definitely needed.