Hacker News Reader

Comment by tracker1 | Hacker News Reader

Structured Query Language ... that's the biggest thing you get. Also database resolved relational data.

It also allows you to present said information from other stores through a well defined interface, such as via stored proceedures that can be locked down via login/identity/application and other features typically in SQL, while being able to act as a gateway for more optimized data stores for specific types of data. Not to mention that JSON data in pgsql has gotten pretty good... I think with plv8 it gets event better. Where I think pg falls is in terms of replica sets, hot failover and automated recovery options which many nosql servers give you in the box. With PG you pretty much have to pay for a decent HA solution.

That said, I honestly really like document stores, and RethinkDB as an example offers some of the relational features of a typical SQL database while being mainly available as a document store.

View on HN

You can get SQL over Cassandra or almost everything other database (even MongoDB) via Spark. And CQLSH is SQL-like at least.

And personally I have always thought putting business logic in stored procedures is a truly dumb idea. You forever lock yourself to that database and there is always a point that you can exceed its capabilities and need to implement functionality in the application layer anyway.

There is thins annoying thing called security with such things as trust boundaries etc. So if you are creating a mildly secure web app the only permission your web apps db user has is to execute a subset of stored procedures and that is it.

I guess you better let everyone know that their web platform isn't secure unless they are using a SQL database with stored procedures. Which would make PSN, Steam, iCloud, Office365, Gmail, Facebook, Twitter all insecure right ?

Generally if you have exposed your db credentials it's because something has gone very wrong i.e. your entire host has been compromised in which case stored procedures aren't going to save you.

I think it's the extent of SQL coverage. As an example, PostgreSQL covers all join types (inner, outer, anti, lateral) that I can think of.

Postgres has been building its SQL coverage for over a decade, and compares favorably to others databases: https://news.ycombinator.com/item?id=9018129

Well, of course it does, but "join" in a distributed database is notoriously different (and often not present at all) from "join" in a non-distributed scenario, so this doesn't make it a strong argument for Postgres.

The problem with joins and reason why most distributed databases don't support them is primarily that the local machinery is complicated to implement.

Given local join machinery, a join between 2 distributed tables can be achieved by joining the partitions of the tables locally after ensuring that tables are co-partitioned and partitions are co-located. That is, if performing A JOIN B ON (A.X = B.Y), ensure that A is partitioned by X and B is partitioned by Y with the same partitioning scheme such that every partition in A has a corresponding partition in B with which it can be joined. A distributed join can then be performed (in parallel) by joining each pair of partitions and concatenating the results.

Ideally, tables are co-partitioned and partitions are co-located in advance. The article describes scenarios in which the tables need to be repartitioned during the query, which can cause a lot of network traffic. However, the amount of traffic is limited in practice since filters and projections can be applied before shuffling any data.

The great thing about implementing a distributed database on top of PostgreSQL is that the local machinery already exists, and it is very good.

Reply on news.ycombinator.com