Hacker News Reader

Comment by stouset | Hacker News Reader

> "Saving" 15 minutes of dev time because you don't know what your schema is going to look like is going to cost you orders of magnitude more time down the road asking yourself that same question.

Yeah. In my experience, "schemaless" doesn't mean you have no schema. You still have a schema, it's just implicit and you don't have any tools available to actually operate on that schema.

View on HN

This is pretty similar to the argument for static typing. Better to let the type system (help) prove correctness ahead of time, rather than wait until runtime to debug.

It's generally easier to fix an incorrect program than corrupt data.

You're not wrong, but the magnitude doesn't compare.

Yep. Everyone winds up with some war story about cleansing some multi-petabyte data store – but the better data engineers I know try very hard to avoid having two of them.

Yeah. I'm a huge fan of dynamically (yet strongly) typed languages like Ruby. They absolutely have their place. But weakly-typed data persistence or data interchange are absolutely terrible.

It's not just about type safety, though. A good RDBMS enforce things like referential integrity (FOREIGN KEY constraints) and allow you to express further constraints on your data (e.g. order.amount must be a positive number, a certain combination of columns must be unique across the table, etc.)

And most (all?) RDBMS that have been around for a while have been tuned and optimized to support this efficiently. I remember reading that at some point (1980s-1990s-ish) DBMS vendors were buying compiler developers like crazy to help them with query optimization and such.

Seriously, eventually the data have to be structure some how so you can make use of it.

Hence you end up writing map reduce down the road.

Mind as well do it upfront with Relational DB.

In almost all cases I've seen the schema is simply in the application layer as opposed to the database layer.

The argument for schemas within databases was when you had dozens of users and applications all connected to a database. In the last few years we've seen micro services, Kafka queues and REST APIs replace the database as the primary integration points.

What sort of tooling does rethinkdb have for verifying the schema of existing data and inserts?

Soon, write hooks. Not the greatest solution though.

Your schema might sit in code, but you've got to reinvent the wheel on all sorts of validations and guarantees your database would provide. Whether you have queues and APIs is irrelevant, your persistent store should be a strong one.

Worse is when the schema is written into the code. It turns tje codebase into a horrible mess.

I'd probably change this a bit by saying that it's not a problem to have the schema in the code (it's got to be somewhere, preferably version controlled), it's a problem when your schema reflects (or even worse, is) your business logic.

edit: s/you're/your/

Yes, exactly.

Schemaless design enables the organizations to collect data now and decide what to do later. Though analysts who work on these datasets should be able to write code to parse the records when needed.

> Though analysts who work on these datasets should be able to write code to parse the records when needed.

Right, so the data conforms to a known schema that they can program against. Schemaless design doesn't somehow make this possible, it actually makes it difficult. If you already know what data you're capturing, then you have a schema.

Schemaless imo is just kicking the can down the road.

You always know what kind of data it is anyway. You don't just dump random data, at least not the majority of the time. When you grab data you're grabbing a specific data group.

It's important to keep in mind that the "collect now and analyze later" is exactly that. You don't have to worry about constraints and integrity, you just capture what you think is of value, which is likely to be all the things unless you're working at really large scales. For all intents and purposes, storing everything as text files is just as good for this task.

> You don't have to worry about constraints and integrity

Sure, you don't have to specify or enforce constraints with a schema either. A schema just means the data has a well-defined structure, it doesn't mean you have to enforce constraints of any kind, though that often helps a lot. Data with no structure at all is very likely useless.

This seems to be a growing issue in the industry as we go more and more data driven.

I don't think most devs seem to realise that just throwing data into a data store, without constraints, integrity checks of any of that other boring stuff, actually makes the data useless.

Try doing analytics against data that can be anything. You can't. Mixing numbers with strings, duplicating fields or having them under varying naming strategies, all just mean you have lots of data that you cannot use.

One of the pet peeves I see data scientists having is lots of data, with no organisation, as they can't use it.

If you want to collect data now that's fine. Collect it in it's original format and label what that format is and where it came from. Then make a proper data store with a proper schema and work on importing the data from those sourcs when you have this foundation sorted out.

It's not always straight forward. A lot of protocols use delta encoding. For example "insert BUY GBPUSD, price=1.1702, volume=100000". But yes, it's worth coming up with a schema on write if at all possible.

But then you can't query schemaless data without sophisticated parsing or absurd wildcard/regex matching of some sort, which obviously is highly error-prone, thus making schemaless data at best only probabilistically useful.

For delta-encoded models, it still seems straightforward to define a general schema via (operation, objectId, value, transactionId) tuples. 'value' can be a string if you want maximum flexibility, but at least this way you can reliably query the operations, entities involved and which changes happened together as a group.

>But then [it sucks]

Absolutely. I agree with you, I'm just saying it's not trivial to turn a delta encoded format into a nice schema.

I mean, I would advise against storing deltas as tuples and considering that the schema. If it's at all possible, try to rectangularize your format. If you're using a column store a lot of the redundant data from the denormalisation can be compressed away.

If the rectangularisation is lossy then you may need to archive the raw data. This is the hurdle where a lot of people stop and choose schema-on-read since they can't afford the storage requirements for an archive of raw data and a nice analytic format for querying.

Offsetting database layout decisions is not having better flexibility, it is having less clue about database layout, unfortunately.

Collecting data in some form and re/de-normalizing it in some useful fashion isn't exactly huge problem if you're not NetFlix or Google (and isn't huge problem even then).

It just requires certain amount of education.

The problem with standard'ish SQL+RDBMS and their schemas is that types are not enforced well in SQL and most libraries and language integrations, thus we end up with the quagmire of static schemas that can not be reliably type checked when you use standard tooling.

Also lacking in many DB systems is integrated support for tables withh heterogenous schemas that is supported by page/row-level cersioning and/or on line schema alteration. Having to rewrite a huge table only because you want to narrow a couple field for future data, it gets old quickly.

I've never had such issues with postgres personally. I know mysql is awful for schema migrations.

> types are not enforced well in SQL

I'm not sure what you mean. Sane RDBMS do enforce types strictly.

> language integrations, thus we end up with the quagmire of static schemas that can not be reliably type checked when you use standard tooling

Languages that don't have static types to begin with maybe. > tables withh heterogenous schemas that is supported by page/row-level cersioning

Can you give an example of what you mean?

> on line schema alteration.

> Having to rewrite a huge table only because you want to narrow a couple field for future data, it gets old quickly.

Postgres doesn't need to lock the whole table for many types of alterations. It won't rewrite the table for null default fields.

> The problem with standard'ish SQL+RDBMS and their schemas is that types are not enforced well in SQL and most libraries and language integrations, thus we end up with the quagmire of static schemas that can not be reliably type checked when you use standard tooling.

This used to be an issue with MySQL (and not really any other major RDBMS implementation), but with STRICT_MODE and the default settings on other implementations, SQL is fantastic at representing and enforcing static types. Furthermore, ORMs and tools like Apache Spark have really upped the integration between types in the database and types in languages. Practically speaking, RDBMS is really the only way you can have sane static typing in most applications (esp. ones that are built on dynamic languages).

> Also lacking in many DB systems is integrated support for tables withh heterogenous schemas that is supported by page/row-level cersioning and/or on line schema alteration. Having to rewrite a huge table only because you want to narrow a couple field for future data, it gets old quickly.

This is just not true. All of the major commercial RDBMS systems support both online schema alteration AND flexible/semi-structured types (JSON, XML). Again, MySQL and Postgres are behind the curve on this (although they both now support JSON). Commercial systems like SQL Server, Oracle, and MemSQL all have both.

I'm not sure if I read your comment properly. But you said SQL Server has both? Both JSON and XML?

Yes SQL Server has json, but it's not real json support. It's an ntext column with some json functions... support for XML is great but the sql Server team refuses to support json properly because "we invested in xml and no one uses it"

PostgreSQL on the other hand has a real json type for a Long time with query and index support. As well as xml. In this regard, json support. PostgreSQL is actually ahead of all rdbms...

PostgreSQL may be behind in some other areas but not here. PostgreSQL has some of the best support for online schema changes (transactional DDL for almost everything, lots of work put into reducing lock level of schema changes) and was the first to implement support for JSON.

Please try to alter a schema of a live multibillion rows table before pretending that it works. Unless they changed how is implemented recently then such an operation can take hours. I love postgres for many reasons so i would like to be proven wrong.

Depends on the schema change. If you have a multibillion row table there are plenty of other things you need to pay close attention to as well, as you're probably all-too aware.

Some schema changes can take prohibitively long on large tables, so you do have to be careful. But there are ways of achieving the same results that are safe for production database. Braintree did a decent writeup on it. https://www.braintreepayments.com/blog/safe-operations-for-h...

Hours is okay, if it works.

Now, if you could just tell this to the people who depend on the software and tell me that hours of down time is not a possibility.

Hours of work <> Hours of downtime

The bigger the database the more complex it will be to implement schema change while keeping track of transactions that happen while your are transitioning. However I see no reason why it shouldn't be feasible given proper amount of preparation. And if short downtime is a goal I don't see why a proper amount of time developing a solution wouldn't make it either.

And on top of that PostgreSQL community is implementing new way to solve problem with each release. I'm not in a huge live-data use case so I might be wrong, but upcoming logical replication look promising for such use cases https://www.depesz.com/2017/02/07/waiting-for-postgresql-10-....

I have never worked with a database of that size but I have done major refactoring of tables with about half a billion rows. And while it can take hours for the schema changes to finish it is hours without any downtime (or just a couple of seconds of downtime during when the exclusive locks need to be acquired). It is tedious to have to wait that long (especially if one has 10 times the rows than what we did), but PostgreSQL can do almost any change online just fine.

What kind of schema changes do you think of that would require that much downtime? Even in complex cases that can be avoided with triggers or even rules.

When doing the rewrite, I was extremely surprised by how good PostgreSQL's JSON support is; there are a large number of functions for querying nested structures, and the indexing is very powerful. I mapped my data from RethinkDB documents to mostly relational data, but kept a couple of columns as JSONB for now, which saved time and massively simplified the rewrite. I remember trying to fully model similar data back in 2007, and it got overly complicated for no real benefit...

(I'm the author of the blog post.)

> online schema alteration AND flexible/semi-structured types (JSON, XML).

> Again, MySQL and Postgres are behind the curve on this

You may want to remove PostgreSQL from that list, especially in 2017.

PostgreSQL has supported XML for years (long before it supported JSON). And online schema changes are not only possible, but even transaction safe, which is especially nice for migrations.

This problem was definitely there for certain SQL servers when inserting incorrect values causes the server the silently cast the data to the field definition type. MySQL had something similar 10 years ago. I think in 2017 most of the SQL servers handle types properly. Adding support for more advanced types like UUID or custom types is a great thing and I see the use of it in production every day.

I dont really see the case for heterogenous schemas, I usually create a view of two very similar tables by creating a union with null fields. This works well enough.

Reply on news.ycombinator.com