Hacker News Reader

>So why not start with a relational database?

I asked the same question. The biggest reason I've heard is RDBMS's don't horizontally scale well, meaning you can't easily have 50 replicated nodes across the globe and expect it to perform well, or setup easily, because it's fairly complicated with an RDBMS. There's things like Oracle's grid or SQL's high availability clusters, but they get complicated fast, particularly when you need a bunch of nodes.

Obviously it was a solution for a very large amount of unstructured or semi-structured data that needed to be redundant across networks. With NoSQL, the key/values are a lot simpler to replicate apparantly.

It's a Google/Twitter size problem that most industries wouldn't have, but since it's the new shiny thing, you know how that goes.

> It's a Google/Twitter size problem that most industries wouldn't have, but since it's the new shiny thing, you know how that goes.

You nailed it on both points. Very few organizations are going to need that kind of scale, but many want to think that they will!

Even when you get to horizontal scaling, you can simply replicate what needs to be replicated. Key-value is really just a subset of relational data, right? If so, I bet we can query and push only that data out across the nodes.

It's not to say non-relational models don't have a place, but they're the exception as far as I'm concerned. I prefer to start with relational and only move off of that if I can justify it. As I said upthread, though, I work in insurance and you might imagine relational works well for us.

Also, the unstructured data is another thing. For example, lets say you need to store a bunch of contracts (as in your industry) in PDF/TIFF or whatever. The filesystem isn't a really good place to store it because you have a loose coupling between the index in the table and the file on the filesystem. That can get messy and unless security is tight, some developer or admin could muck something up on the filesystem. This is particularly dangerous when you need to keep contracts over a period of time (say 7 years). If you have a really large amount of files, you'll either need a huge array, or figure out a way to distribute them across several NAS's or whatever, then that gets complicated.

The RDBMS is a little better at storing this type of data, but it has it's own problems. Backups quickly become huge and a massive undertaking. Development databases now have a bunch of missing data (because who wants to replicate that down to lower levels), you still have the same problem when you have a large amount of data that you need to shard across NAS/arrays, etc.

The NoSQL seems like a third option that may well be worth looking into. Again this is for unstructured data that you just need to reference and not necessarily query against. (like stored contracts).

> That can get messy and unless security is tight, some developer or admin could muck something up on the filesystem

This isn't a great reason not to use a filesystem--it's a reason not to let staff have at the filesystem like cowboys, and not to avoid replicating and backing up the keys and values on the filesystem as if they are really important.

That said, there isn't really a solid replacement for a database transaction involving both fields + blob, but most situations are okay with, say, stowing a blob in S3, and later making a half-hearted attempt to delete it if the associated database transaction fails.

Contractual information can quite often be quite relational in nature. I have worked for organisations that had custom contracts and custom sales documentation (different industries) that were based on templates that could be filled in from a database.

In some cases, the companies were successful in storing the relevant information in a database and use that to generate the documents required based on document templates. This is not hard, but it can be tedious to set up. The companies also have to have a will to walk down that path and many do not see a benefit to this kind of process.

It takes a very insightful designer to manage and lead this process as well as a client who has an incentive to move in this direction. One of the considerations is that the approved contract must be in some unalterable form that can be tested against the contract generation process from the information within the database. So that there is always a means of ensuring that any information in the database cannot be corrupted (in a legal sense). That is a matter of safeguards not database information storage capabilities and there are many different ways to do this kind of security.

The problem of sharing of data and replication is not often thought through properly. Oft times, I have seen replication being performed wholesale instead of piecemeal. Similarly for data sharing and the consequences have been less than efficient. Oft times, the actual requirements for sharing and repliation are very small and it is a matter of understanding what is actually required and what is desired for the business.

Finally, very little data is unstructured if you look at it correctly. Many times, I have seen people talk about unstructured data (including documents of all kinds) and when you look at the data, it is highly structured. It becomes a matter of seeing what that structure is and working out what is variable and what is constant data within the structure.

This is a perfect example of not having to use the same hammer for everything. The "standard" solution to this problem is to store the files themselves in cheap yet durable object storage (S3, NFS, etc) with a reference to the unique ID or URL in your primary database. The record locator is small (which is good for the RDBMS) and can be transactionally updated in the DB with any changes that involve that object (ex: update contract associated with a client).

There's no need to store the full details as a BLOB in your DB. That has nothing to do with SQL, NoSQL, distributed, or centralized. It's just a vanilla separation of immutable object data (the raw bytes of the PDFs) and application state (the record locator).

It's proprietary and therefore not an option for everyone, but SQL Server's "Filestream" feature [1] was designed for the "large blob of unstructured data" use case (small blobs of unstructured data generally work fine in regular tables). It stores large blob data efficiently in individual files directly on disk that are still managed by the database engine and written/read in the same transaction as table data. It's well-integrated with their other standard features such as backups, HA clusters, etc. It's a pretty impressive feature.

[1] https://technet.microsoft.com/en-us/library/bb933993(v=sql.1...

Postgres has the BLOB functionality, but also has the JSONB data type that not only stores structured JSON data efficiently, but also allows indexing on the contents of that data. So you can use BLOBs and explicitly store in the table what you need to search on, or else you can avoid that and take the pseudo-NoSQL route and store indexed JSON.

> For example, lets say you need to store a bunch of contracts (as in your industry) in PDF/TIFF or whatever.

Contracts are not as unstructured as you would think. At least in my part of the insurance world, most of them are produced by a contract writing system of some kind. Think of it as assembling as a series of contract provisions, each having a valid set of choices to select. Use your auto policy as an example -

Liability - $100k Comprehensive - $10k / $1000 Deductible Collision - $50k / $1000 Deductible

Yes there are other attributes captured, but you see the pattern. It's provisions and boilerplate. The use cases needing the imaged executed contract where the attribute/provision soup is not sufficient is uncommon.

The operational terms of the contract exist in some other system (and database) where it's consumable in some structured way.

    Very few organizations are going to need that kind of
    scale, but many want to think that they will!

Further to that point, many software engineers know that NoSQL is a bad fit for their organisation but use it anyway because they wish to gain the experience required to work with Big Data in the future.

Nothing helps you learn new tech quite like fighting to the death with it!

> The biggest reason I've heard is RDBMS's don't horizontally scale well

Not true anymore with "NewSQL" databases (Google Spanner, CockroachDB, ...)

Both are laudable. But, Spanner is closed source and hosted on a high egress cost cloud. So use cases are limited. And Cockroachdb is promising, but in it's 1.0 release. Follow the current bug list and performance numbers...you wouldn't use it just yet for something important.

While I agree that there are situations where "network egress" can become a large part of one's bill, I encourage you to think of the technical impact of having something like Spanner and something like the Google network.

A large part of what Spanner does to sustain its high cross-regional availability at strong consistency relies on Google's powerful network [1]. Spanner isn't just source code - it's vertically integrated custom hardware + software + Google network + Google SRE - you would have very limited use from Spanner if you had just source code.

Example: Recently Google introduced "hot potato" network tiers that closer match AWS and Azure offerings (aka degraded Google network)[0]

(work at G)

[0] https://cloudplatform.googleblog.com/2017/08/introducing-Net...

[1] https://cloudplatform.googleblog.com/2017/02/inside-Cloud-Sp...

Sure. I wasn't trying to make it sound like a conspiracy. Just that the constraints of running it only on the Google cloud invalidate some typical use cases. For many companies, you would have to migrate quite a high percentage of your business data out there to avoid huge amounts of egress charges. Spanner also doesn't support INSERT, UPDATE, etc, which may also be limiting.

I think your point is a microcosm of the popular "cloud TCO" debate - benefits of higher levels of abstraction. Can you get Google's network elsewhere? No. Does it cost more? Sometimes. Is it useful? For a lot of folks running products and services very much so.

Higher levels of automation and abstraction (Spanner) may cost more on paper, but when you start calculating the cost of dealing with shoddy network, cost of maintaining your own service, cost of equivalent to what Google SRE gives you, cost of upgrades/maintenance, sharding/replicating your MySQL database and so on, your TCO math may look a little bit different.

I'll opine that network egress is just one of the many factors that you should think about when evaluating a technology like Spanner. Kind of like price-performance. I encourage you to look at cost-value, not just cost.

(work at G)

None of that is my particular issue.

This is specific to moving a largish companies data to the cloud. Typically, nobody wants to move it all at once. They want to move one thing first. Think like a trucking company. They have databases of customers, drivers, equipment, orders, invoices, quotes, etc, etc. With the current model where egress traffic is priced so high, it's difficult to move one of those subject areas to the cloud. The reason is because none of them act like an island. They are used together. So unless you move it all at once, huge amounts of data go back and forth all day long.

Not every customer with concerns is just someone that needs to be educated about how awesome Google is :) I do like Spanner. I'm just pointing out a barrier to adoption.

RDBMS are such a production kill.

imagine you just received two TB data sets, and you'd like to join them and run a process. In hive, you create the schema (pretty simple) and join them. queries may take a few minutes but you get to doing the work you intended all along.

In RDBMS, you have to fus with its schema, "you said 125 characters for that string field and you just received 130, FAIL". Its a STRING JUST DEAL WITH IT. you have to create indexes and fus with all sorts of relational nonsense when it has little to do with the work you are trying to accomplish.

take your billion row database, and now you need to add a new field. its going to read the entire table into memory and re-write it back to disk, meanwhile you aren't doing the work you were trying to accomplish.

relational databases are great for small static data, but when you deal with new large messy data every day, they sap all your time.

> "you said 125 characters for that string field and you just received 130, FAIL"

Most (all?) RDBMS's have something like a varchar(max) which accepts reasonably large strings.

> you have to create indexes and fus with all sorts of relational nonsense

No, you don't.

> its going to read the entire table into memory and re-write it back to disk

Probably not, if you're adding a nullable column.

you obviously have not used databases very much if you think you can join 2 billion row tables on a key that is not indexed in a reasonable amount of time.

So? It's not like a nosql db without indexes will be any faster at it.

yes, its absolutely faster. try it. Granted a nosql db like hive is 'cheating' by taking advantage of lots of disks/hardware on lots of machines, but i routinely join billion row tables with million row tables and wait only a minute.

> Granted a nosql db like hive is 'cheating' by taking advantage of lots of disks/hardware on lots of machines

That's not "cheating", it's literally the only relevant factor.

Who would win in a fight: a superintelligent ninja cat with laser nunchucks and body armor, or a dog?

My cat beats my dog in fights all the time, and he doesn't have any of that training or gear.

Creating an index in one command is a bonus of using SQL databases. Messing around creating yet another database seems to be the equivalent on NoSQL.

> In RDBMS, you have to fus with its schema, "you said 125 characters for that string field and you just received 130, FAIL". Its a STRING JUST DEAL WITH IT.

While standard SQL may have this problem, most concrete RDBMS do not, because they support a simple length-unspecified string type (or, in the case of SQLite, treat column types as documentation and mild hints rather than actual rules.)

> you have to create indexes

Absolutely not. You can, for efficiency, but (ironically, unlike some noSQL systems that require explicit indexes to support queries) you can query and join tables of relational databases without defining explicit indexes. This may, for some queries, be extraordinarily inefficient (it may not, if you've defined the schema well, because many RDBMSs implicitly create indexes based on schema features like PKs and unique constraints), but you can do it.

> With NoSQL, the key/values are a lot simpler to replicate apparently.

If all you need is a key/value store, I can see how that simplifies replication. Eg, if you have the key "foobar" and a 12-node cluster, you convert it to a number using a hash function, do "module 12", and that tells you which node to store the value on. If you want to store it on 2 nodes, do this again with a second hash function.

That's lovely - as long as you don't need to join records, and never will. It's a risky prediction.

>I think the bigger issue is that you can use an RDBMS to do NoSQL-like things, such as key-value stores, with the flexibility to structure your data if you need that later. So why not start with a relational database?

I've tried that in the past and failed miserably.

1) values in a key-value table will endup needing to hold nested data structures, such as a JS object/hash. Ie. mykey={...}

2) turning values to JSON (or some other serialization) makes it impossible to concurrently update or search subkeys.

3) so you convert your complex (sets, hashes) key-value data into several rows that hold one subkey per row, so now you have updatable rows but still no indexable solution and a serious performance problem.

4) so you create a multi column type-segregated table (one column for each DB type) for indexing your key-values and making them searcheable. That also requires a metadata table to go with it so that you know in which column your key or subkey is indexed.

5) say you successfully implemented your key-value store with your RDBMS. You still don't have usable joins (you don't have relational data) or a real migration path out of your key-values.

Trust me, don't just put keys and values in a relational DB. Start with the right tool for the job, either make your schema relational from the beginning or use a proper KV or document store.

1. Postgres has a json and jsonb datatype specifically to be your values.

2. It has built in query and modification tools that can manage the nested objects and array and whatnot.

3. It let's you build indexes to help with all this, including reverse indexes for asking something like "is this string in the array inside this object".

4. You don't have to specify your schema in sone horrid way, you can just throw what you want at it and specify later, and get performance numbers that are competitive and even exceed some popular NoSql stores.

5. You can mix and match freely with all the niceties of a proper SQL database with transactions, joins, constraints, etc, as you see fit to evolve.

So yeah, don't just throw your keys and values into some text table somewhere; but use instead one of the best proper kv stores, that just happens to also be one of the best sql databases, and figure out how you want your schema you need it.

Same can be said of MySQL. Not comparing to Postgres, but I've been amazed at how many people do not know that either of these as a powerful JSON type.

Yes, too many devs I know haven't even heard about generated columns and virtual indexes in mysql.

Postgres JSON(B) falls nearly in the same category. Do not use Postgres as a key value store, use a KV store for that.

Postgres JSON is great for storing the original JSON data coming from external APIs and Webhooks, such as Stripe payments. But it's not a silver bullet and really not a first class citizen. SQL query syntax for JSON data is awful, non-standard and requires casting types, which defeats the purpose of schemaless. I believe Postgres JSON types solve a different use case: an app based on a relational schema that needs to store JSON structures eventually.

I don't recommend starting off your app basing it on Postgres JSON. Use the full relational Postgres instead and do it right from the start.

Modern SQL servers implement this

I did this too. The issue with using an RDMS for a KV Store is that the data you are storing often has to drop to lowest common form, or the database structure gets wildly complicated. So we initially had:

subject (varchar), key (varchar), value (varchar)

But that gets very messy very quickly. So we added a field to specify the value's type. That worked, but then there's an overhead reading and writing the data - because there's a constant conversion. So we then added the most common types as extra value fields. That was the best option. But then the problem is that it gets fiendishly complicated to extract the data. I mean, this was the simplest version that never made it to production. In the end, reporting killed the idea. Because using SQL Server Reporting Services and needing to pivot the data to create any reports became a massive performance issue. Around that time EF Code first was coming in the .Net world, so we went that route instead. And really, I feel like it was the right solution.

>> Pretty sure that SELECT, FROM, WHERE will work just about anywhere

Cassandra & CQL is a prime example. CQL become the de facto interface for getting data out of Cassandra, even without the benefits of JOIN's & sets.

CQL is not SQL. CQL does not even have SQL SELECT. There is no support for arbitrary WHERE clauses, for example. You need to either slice an ordered column, or select a row by primary key -- and nothing else! They have an ALLOW FILTERING option which lets you "do it anyway", but this reduces down to paging the whole dataset without the WHERE applied, and then doing client-side filtering.

I always felt that CQL did more harm than good for Cassandra. It's not like Redshift -- which implemented a full mostly-Postgres compatibility layer. Cassandra's CQL "looks like" SQL in the same way JavaScript "looks like" Java. Similarity by superficial appearance only; under the hood, totally different.

It's not just a subset, but there are also incompatibilities like limit limits the number of rows scanned not the number of rows returned.

I think the opposite, and that CQL is the only reason people still adopt Cassandra today. Remember Thrift, which started by using terms like columns and rows for completely different concepts and went downhill from there? I felt it was a very interesting choice to have CQL only capable of expressing queries that Cassandra could perform quickly. It forced people to adjust their data models immediately, rather than defer the problem until the performance issues got too deep to ignore. Its a different mindset to relational databases, for different sorts of problems. If only they had used the same sort of restraint on other features attempting to make Cassandra do things it is unsuitable for, such as 'transactions' and counters, which are just an embarrassment of problems.

You missed the point - both SQL & CQL are declarative languages.

>> CQL does not even have SQL SELECT.

https://docs.datastax.com/en/cql/3.1/cql/cql_reference/selec...

This sure looks a lot like SQL SELECT. It returns a projection of data. Beyond that it's just pedantry.

Not supporting WHERE, GROUP BY, all aggregate functions, almost all other "standard" functions, most forms of DISTINCT, ORDER BY (except one order defined at table create time), all forms of sub-select/CTEs, etc. is pedantry?

OK. :)

The thing about the NoSQL trend is that it's still very valid in some areas. Even big and slow companies have some project or supplier that uses a software with a NoSQL service in it.

You just rarely see those bundled redis or elasticsearch servers that are crucial for some app to do its session management and search engine.

The most disturbing part of the NoSQL trend is that people are treating it like a battle between two competing systems.

I have at least one major system under my own belt that uses relational SQL for backend data, ES for search engine and cassandra for TSDB.

I need and trust all those services to work as one unit.

> The thing about the NoSQL trend is that it's still very valid in some areas. Even big and slow companies have some project or supplier that uses a software with a NoSQL service in it.

This is a good point and I didn't do a good job of acknowledging it in my prior comment. There are use cases for NoSQL, but to me it's a tool for a narrower set of circumstances. My default is relational, unless I have a clear idea of why that isn't the right fit.

The reason people don’t start with a relational DB has nothing to do with the data model or query language - people choose databases like Cassandra because scaling to 500 Postgres or MySQL instances holding a combined petabyte of data is horrific, but it’s dirt simple in things like Cassandra

The query language is a side effect of the underlying storage engine - you don’t choose it because you want a key value store, you choose it because you want horizontal scalability and cross wan HA

You make it sound like many people are using petabyte scale databases. This is not even close to true. ESPECIALLY when you're just starting out. Most new projects will start with almost no data at all and the vast majority of projects will never ever get to the petabyte scales.

Many people ARE using petabyte scale databases.

They don't start out that way, but they will be if they're successful, and if they're not they probably shouldn't be running their own databases anyway (go RDS it up or something).

Unless the amount of data you are collecting is increasing at a vastly unsustainable rate, you will be making megabucks and be able to afford a team of engineers to undertake a major redesign for version 2. But picking technologies based on scale at the onset is going to make getting to that point all the more unlikely.

What? No they aren't, I work with satellite imagery processing which is quite large in it raw data format, and after a decade we are not dealing with petabytes of active data, hundreds of gigs for a full earth coverage.

Before that I have held positions in finance, dealing with realtime transaction processing. We did not work in petabytes.

If you are working in petabytes you are storing crap in your production database, and 99% of that data is wasted.

> What? No they aren't, I work with satellite imagery processing which is quite large in it raw data format, and after a decade we are not dealing with petabytes of active data, hundreds of gigs for a full earth coverage.

This get large rather fast when processing time series from multiple sensors.

But I agree with you otherwise, and it's not like you're storing satellite products in a database.

Now I'm curious what NSA is using :).

https://accumulo.apache.org/

See (History): https://en.wikipedia.org/wiki/Apache_Accumulo

Thanks, I wasn't aware of it.

You could just check their Github? :)

https://github.com/nationalsecurityagency

Yeah right. We are using nosql in manufacturing. One second data for thousands of instruments create a lot of data very quickly. And that is per plant. Having a lot of plants at various sites in the world adds up.

Exactly this. Whenever you collect millions of things, even at low resolution, it adds up quickly

Collecting high resolution and filtering down to low resolution for lower importance is still difficult and can require petabytes for real world use cases

Postgres (as an example) has no upper limit for database size, and the upper limit for a single table was 64TB a while ago, perhaps higher now. And you can buy stock hardware with 6 TB RAM and 76TB SSD e.g. from Dell.

If you max out that single server, you could easily hire one database engineer to care about horizontal scaling.

Show me a single person who runs 64TB on a single table on a single node, and I'll show you someone who will cry because of a prod failure sooner than later.

It doesn't help when the nbr of queries and updates increases a lot. For many systems having TB of data also means having thousands or millions of users.

You also probably want backups and possibly slaves that are updated inside transactions to avoid data loss. These things can lower your throughput even more.

The vast majority of hipster NoSQL developers I have debated with are using a single machine.

Those people are objectively wrong.

You try and explain that to them, but whatever database they are using "is fast". Not that I have ever had a problem with relational databases being slow. Certain queries, yes, but a bit of rewriting them and proper indexing usually solves that.

Not familiar with teradata, but that looks like the equivalent of

select a, b, c from table where somedate between somebegin and someend and someotherdate = (select max(someotherdate) from table group by a, b)

which will work in all sqls. My point being, the advanced features make life nice, but are by no means necessary.

(would be more efficient as a join, but not sure if joining on subtables is always supported...)