Hacker News Reader

Comment by dhd415 | Hacker News Reader

I think there are several useful points here:

1) It's rare to have enough insight into the internals of a particular datastore to accurately predict how it will perform on a particular workload. Whenever possible, early testing on production-scale workloads is essential for planning and proofs of concept.

2) Database capabilities are a moving target. E.g., the performance improvements to pgsql's LISTEN/NOTIFY are essential to its ability to handle this particular workload. In previous jobs, I've had coworkers cite failed experiences with 15-20 year-old databases as reasons for not considering them for new projects. Database tech has come a long way in that time.

3) Carefully-tuned RDBMSs are more capable than many tend to admit.

View on HN

> Carefully-tuned RDBMSs are more capable than many tend to admit.

I'd generalize that further: RDBMSs are more capable than many tend to admit.

When it comes to a persistent data store, you've got to go out of your way to justify using something that isn't statically typed with firm ACID guarantees. I'm not saying those use cases don't exist, I'm saying most people don't have them.

"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.

After a long time with RDBMSs I used nosql at one point for my product in anger and I found the work to keep the data consistent just too stressful.

I'm happy to be back on Postgres again (and have been for a couple of years). It protects me from my own human inadequacies.

"Those who refuse to study history are condemned to repeat it"

People like Jim Gray, Mike Stonebraker, et al. were/are not dummies.

"Those who ignore Codd are condemned to learn Codd, forcefully".- Codd's Law

Adapted to DBs.

Posts like this are annoying and completely stupid.

NoSQL databases encapsulates a wide variety of data stores including Key Value (Riak), BigTable (Cassandra), Document (MongoDB), Time Series (InfluxDB), In Memory Grid (Ignite) and dozens and dozens of others that blur the already grey lines. Many of which are strongly consistent (negating your consistency point). So your experience with one does not at all translate to other data stores.

The equivalent of what you're saying is "PHP sucks therefore all programming languages suck".

I think the point of the post is valid: it can be hard to work with a schemaless datastore. I think Cassandra is the only one you listed that supports type enforcements.

NoSQL != Schemaless.

That mantra hasn't been true for a number of years now. Especially since the big trend has been exposing SQL layers in front of NoSQL databases. For example Phoenix in front of HBase.

And SQL as well as all know requires data types to be usable.

Just to explain - I was going to write mongo originally but then decided to go more general. I evidentially went too general with my terminology. I've used mongo since the early days and I've also worked on projects that have used everything from neo4j and redis to oracle as the primary data store.

There are a bunch of things that made me nervous about mongo as the primary db. Data consistency was the main one and, like the author of the post - migrating away from mongo was hard almost entirely because of data inconsistency that had developed over the course of a couple of years.

Querying was also harder on an ad-hoc basis. With a relational db there are generally just a couple of recommended ways of modelling your data. But once you do that, it's easy to query after the fact. Or to add new stuff. With mongo I felt like I was constantly making a trade off that I would have to accept at querying time.

MongoDB has been able to do this (optionally) since v3.2.

Isn't it more like saying all loosely typed languages suck? Your analogy is what's really completely stupid.

RDBMs may not scale as well as let's say something like Cassandra, but as long as you don't need the scaling properties of Cassandra they make your life so much easier.

I have a feeling that premature scaling is a version of premature optimization. You probably don't have big data. And you probably don't need a massively scalable, distributed system. So, don't try to do it. Just don't.

The simple fact that I know the data I read from the datastore is consistent with my rules, is enough for me to stay on RDBMS. I've encountered enough bugs and failures to not trust a schema enforced in my data layer code.

> "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.

While I agree with the spirit of this, sometimes you'll never know what your schema is, because it's not under your control to begin with. That's why there are triple-stores and "document" databases and key-value stores now, and why there were deductive databases 30 years ago.

The world is wide, and there's room for more than one tool in the chest.

> I'm not saying those use cases don't exist, I'm saying most people don't have them.

Postgres is of course an excellent document store with JSONB.

Its really powerful being able to create relational views backed onto data as json and vice versa.

Exactly. The arrival of JSONB in Postgres killed most of my interest in other nosql stores. It's the best of both worlds.

This assumes that JSON is what you want to store and query. In my experience, about 1 year ago, I couldn't get basic things out from the Puppet database.

Even those use cases can sometimes be met by an RDBMS. Postgres can outperform Mongo as a key value, or json doc store. But Mongo is what many people think of for something like that.

And for those who may not know, but appreciate irony, Mongo now uses ... wait for it ... a somewhat stripped down, key-value-ish, embedded ... RDBMS as its underlying data store. Yes friends, "it's a dessert topping AND a floor wax"

should also point out that Mongo is more than a jsonb store, and postgres is, at least currently, less than a distributed, "scale-out" database. Mongo supports problems that postgres cannot currently address. period. (and I'm a huge postgres fanboi)

Could you share some of those problems? (genuinely interested)

Horizontal scalability. Outgrowing a single master database (writes being bottlenecked, not reads) is really painful. You can engineer around it, but spinning up another master in a multimaster DB is a really quick and easy fix (once you have tooling built out it's a matter of minutes). Sharding your Postgres DB is not a quick and easy fix. It will likely take about a month to engineer around and perform the data migration, if not longer.

I've heard good things about Citus for horizontally scaling Postgres.

I've pretty much only heard about it in the context of OLAP. If your data model requires transactions, you're gonna have a hard time when you outgrow a database.

PostgreSQL outperforms MongoDB how ?

Because as someone who has tried to update JSON documents (1M/s) in both PostgreSQL and MongoDB I would strongly disagree. MongoDB with WiredTiger was at least 10x faster per node and in terms of pure updates one of the fastest databases I've ever seen. And before the trolls yes I was fsyncing to disk.

Postgres doesn't fancy many small and frequent updates (like rapid counter increments).

It is workable, though, via combination of batching, ensuyring indexes on hot fields have appropriate fillfactor, and making sure Postgres can use heap-only tuples.

Here's my old SO question on this and a well-comprehensive answer that's still actual and could be helpful for some cases: http://stackoverflow.com/a/1663434/116546

fwiw https://www.enterprisedb.com/postgres-plus-edb-blog/marc-lin...

Not worth much. MongoDB is a completely different beast after version 3.0 with WiredTiger being the default.

But I am sure that there are use cases for which PostgreSQL is going to be much faster in particular single field lookups on unindexed fields across all records.

My point is that it's never black & white to say Database A is faster than Database B. It's always use case dependent.

well there is also https://www.techempower.com/benchmarks/#section=data-r13&hw=...

Point being when someone says something is faster without providing verifiable numbers... it should not really be taken seriously

I think it depends on the workload. PostgreSQL does not not support updating documents in place but is really fast at writing and reading. Never done any benchmarking myself, this is just based on my general knowledge of what data structures and algorithms both databases implement.

This is pretty amazing. Now, would you mind showing me a situation when the raw insert performance is the single dimension when we are making a technical decision?

Hint: you probably can't, the reason why you store data is that you can read it (query it) later. MongoDB could be 10000x faster with inserts, still not a considerable solution for us because it fails with many of the other aspect of providing a great data access layer.

Raw insert performance is the main driver for tick databases persisting high frequency market data for HFT systems. The TimesTen DB, later bought by Oracle, was built for this scenario. Arthur Whitney's KDB too.

Not to mention the sub second latecy for querying 1B rows.

Not the single dimension but an important one. Any real time application processing events data (IoT, heartbeats, prices logs) would have a similar requirement. That's one reason why people uses queues, to try to workaround this. And as for the reads, it will drilldown to your query needs. I have done systems where the read requirement was a retrieval from a key and the writes request came in thousands per second.

Absolutely, I have run data ingestion pipelines before for event data processing, perfectly suitable for the use cases you mentioned too. I just dont see mongodb as a good fit here.

> "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.

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.

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.

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.

But there are document databases that have schemas, like couchDB.

So then it boils down to the cap theorem, and ease of ad-hoc querying vs ease of replication/scaling out. Which is more interesting.

Or do people try and push "not being able to validate your data" as some kind of feature?

I've been using couchdb to sync local and remote copies of a single database for a medium sized product and it is really convenient for that use case.

Also, Postgres Devs reignited the benefit of "schemaless" dbs, which supports JSON as a first class deeply queriable type.

I'd agree entirely. Unless you have an exceptional reason, in which case you probably know what you're doing already, build a version of your system which uses either a relational database (with a sensible schema, not a single-table EAV thing) or a filesystem as a backing store first.

(When it comes to a primary data store, its first job is integrity, so denormalization is premature optimization.)

Similarly; don't distribute any computation you don't have to distribute. Just getting a bigger machine is remarkably likely to be cheaper, more operable and more reliable.

> ... or a filesystem as a backing store first.

File systems are far harder to use correctly compared to pretty much any database; suggestion: only use them for storing large blobs, and use collision-free names (eg. random UUIDv4). Many applications don't need that though.

Sometimes you just have large blobs, though! (I'm thinking, for instance, of machine-learned models; big matrices are exactly the kind of thing we have HDF5 and friends for.)

I think it's important to distinguish two cases. If mainly one app is the storage client, then you usually have the schema in the app, no problem with schemaless (most early phase startups).

If you have many different server apps accessing the database, then everyone has a different view on the schema which leads to high coordination overhead.

I will say this can go the other way in the hands of someone who doesn't understand what they are doing.

I recently started work at a startup where the codebase is running PHP/MYSQL which in itself is ok, but the database was horribly designed.

To deal with the tracking of "events" they constantly receive from devices that vary a lot from device to device it uses a linker table for every field that can be stored aside from ID. Considering the data never changes once inserted, it's really seems like overkill.

Any meaningful query that goes beyond getting a single datapoint involves dozens of joins which just kills the database. If someone just made him use mongo for the events storage we would probably be in a better position to fix the issues we are dealing with.

Postgres hstore fills the key value gap quite well too.

Though I agree with you there is the problem that RDBMSs can't handle hierarchical data very well. This comes up in enterprise master data quite often, eg. in employee relationships or organizational structures. I'm no SQL wiz but I have yet to see a readable and well-performing SQL which can select the managerial line of an employee.

> I have yet to see a readable and well-performing SQL which can select the managerial line of an employee.

This is not a simple problem - I'm not even sure how to map it mathematically to set theory. SQL is based on set theory, and does so very well. What you describe is a graph problem, which simply falls outside of what SQL was designed for. Either a RDBMS needs to be extented to handle data in graph form and operate on them acording to graph theory, or you should use a DB suited for this problem.

Point is, ideally you want both. Especially master data is closely entangled with data which you have in tables (and rightly so). In the example above consider an order header table which will have a reference to an employee (the one working on the order or the one who created the order). The schema for orders is rightly relational but I'd need a join to the employee which therefore is stored in a relational table as well.

I don't see how that scenario is anything but relational, even for queries, or are you talking in extension of the previous post about the managerial line? If so I agree, but instead of "poluting" the RDBMS with graph related constructs, I think the option here would be save the data in the RDBMS, and either build a graph on demand, or maintain an in memory graph, for queries. "Everything but the kitchen sink" are rarely a good idea for tools, it has a tendency to make the cost of switching some module unacceptable.

A PostgreSQL (recursive) WITH query [1] can deal with that:

  CREATE TABLE employee (
         id int primary key,
         name text,
         manager int references employee(id)
  );

  INSERT INTO employee(id, name, manager)
  VALUES (1, 'jane', null),
         (2, 'john', 1),
         (3, 'jake', 1),
         (4, 'jeff', null),
         (5, 'jessica', 3);

  WITH RECURSIVE t(manager, managed) AS (
       -- Direct managers
       SELECT manager, id
       FROM employee
       WHERE manager IS NOT NULL
       UNION ALL
       -- Indirect managers
       SELECT employee.manager, t.managed
       FROM t, employee
       WHERE t.manager = employee.id
         AND employee.manager IS NOT NULL)
  SELECT
          (SELECT name FROM employee WHERE id = t.managed),
          array_agg(employee.name) AS chain_of_command
  FROM t, employee
  WHERE t.manager = employee.id
  GROUP BY managed;

Results of the query:

    name   | chain_of_command
  ---------+------------------
   jessica | {jake,jane}
   jake    | {jane}
   john    | {jane}
  (3 rows)

You can argue about the readability (syntax highlighting would help), but to my eyes it isn't that bad (if you know how WITH queries work). It's also more concise than the corresponding query would be in many a procedural language.

The efficiency should be pretty acceptable too (instead of having direct pointers O(1) to follow the manager, you follow them indirectly through an index O(log N)).

[1] https://www.postgresql.org/docs/9.6/static/queries-with.html

Recursive CTE can be used for this kind of queries. Or you can use nested sets / intervals depending on your workload and the tradeoffs you're ready to make.

How about this one: http://stackoverflow.com/questions/12231322/find-all-the-sup...

> 3) Carefully-tuned RDBMSs are more capable than many tend to admit.

So important. I have, in the past, worked with many people who simply thought NoSQL was new and RDMS was old therefore ALWAYS use new. Trying to get someone to pick an RDMS solution in said environment was impossible. The times I've had to take a document store and make it store relational data are more than I'd like to admit.

I've never worked with postgres before my current project and I'm so impressed with how easy it is to get stuff done. I used Cassandra for a prototype (bad choice for my workload, not Cassandra's fault) and documentation, ease of tuning, tooling maintenance all seem to be so much easier. Of course, Cassandra has a host of problems to solve that postgres doesn't -- being meant to run on clusters and all. But you know what, I don't care. I also was heavily leaning new stuff before. But what can I say, if an RDBMS is what I need -- I probably should use one.

I have a pet theory that any given system has a maximum cleverness budget. Make sure to spend it on the things which actually matter in your problem domain.

Interesting data products often have to spend a lot of that cleverness budget in the algorithmic layer – machine learning and statistics. So when I work on that kind of thing, I've learned to have a real appreciation for boring data storage. I'll even happily use MySQL; the ways in which MySQL can suck are very well-understood and that's a hugely underappreciated virtue.

This 'cleverness budget' is a fantastic term. Thank you!

And yet when I see data engineering positions, particularly around here, it's all about Hadoop/HDFS, Spark, Cassandra, Mongo. I'm not saying there aren't use cases for those, just that it seems like they've become the default basket of options rather than starting with an RDBMS and figuring out why you shouldn't use that.

I feel like a dinosaur working with Teradata all these years, but it hasn't failed me. Sure we spend some extra time modeling, and then some extra when we need changes, but other than the cost, it's really been solid.

> but other than the cost

Why do you think people are moving to Hadoop ?

That cost that you glossed over is massive. Teradata is very, very expensive. It's great. It works. But it's expensive. Especially if you want to store large amounts of data e.g. networks or web traffic.

Fair enough. I've been in large companies for too long, it seems. We'll pinch pennies over things like coffee and continuing education, but we're still paying those Teradata and SAS bills.

RDBMS are more capable in multiple aspects; may it be speed, may it be functionality.

I often see developers doing all kinds of things in the application layer; enforcing constraints, manipulating data, even sorting and filtering. It's like some folks are feared of writing SQL or don't trust their database.

Maybe they got spoiled by ORM mappers that anything beyond standard CRUD may feel cumbersome to write.

> > I often see developers doing all kinds of things in the application layer; enforcing constraints, manipulating data, even sorting and filtering. It's like some folks are feared of writing SQL or don't trust their database.

> Maybe they got spoiled by ORM mappers that anything beyond standard CRUD may feel cumbersome to write.

A decent ORM[0] will use the constraints expressed in the application to reflect those as constraints in the DB schema as well.

Why the duplication, you ask? For the same reason that many of the same constraints are imposed in the front-end code: Responsiveness.

Validation that happens in the front-end avoids a request/response to the server just to give the user feedback that their password isn't long enough.

Relationships and constraints expressed in the application can avoid querying the database just to get the same kind of feedback.

This duplication may seem useless, pointless, repetitive, and redundant, but not all accesses to the web server will be via a JS-enabled client, for example anyone accessing the application via an API.

[0] eg. SQLAlchemy: http://docs.sqlalchemy.org/en/latest/core/constraints.html

I think you're right about number 3. I have a friend who likes to do as much as possible with pgsql and I'm often surprised at how well it performs for the things he uses it for.

And yet i hear day after day, don't put business logic in the db, it does not scale ... it's like people are stuck in 2005

I'm not sure if it's fair to read your parent as putting business logic in the db: it may just use Postgres whenever a datastore is required and Postgres is not a bad fit.

Deciding where business/application logic should reside is an important decision, and treating PostgreSQL as an application server in addition to a database does require a different way of approaching the tool. Of course, this is no different than any of the other application architecture choices that need to be made.

I am not sure i understand your comment also :) My point was that people (read web devs) treat databases (postgres) as only data stores, put in data, get data out, and they are so much more. Sure not anything goes into the db, but if it's a bit of logic that relates only to the data (who can access it, who can change it and in what way), most likely it belongs in the database. PS: but let's not derail this thread with debates on where business logic should go :)

I think I agree with you that there are some things that belong in the database. For example, anything I can do in the database to ensure data integrity (without unduly affecting performance), I'll put there (data types, constraints, triggers, functions which encapsulate transactions). Similarly with access privileges.

My initial comment was in response to these statements:

I have a friend who likes to do as much as possible with pgsql and I'm often surprised at how well it performs for the things he uses it for.

I understood you to read this as putting business logic in the database (indicated by the And yet) in your comment:

And yet i hear day after day, don't put business logic in the db

I read the initial comment by 'fapjacks as meaning using Postgres whenever appropriate, as opposed to doing everything one could with Postgres in Postgres.

Not a big deal at all. I saw a potential misunderstanding and attempted to clarify.

PS: Of course, business logic should go where I put it, right? ;)

You are right in both cases, yes i read that comment like you described and yes that is probably not what fapjacks meant.

As for the beginning of your comment, i am 100% with you. The problem is that the cases you describe (integrity,access,reports on data) are most of the time what the entire application does (we are all just doing CRUD and all that ...) and instead seeing that for what it is (let's call it DataLogic) people are all to often calling it Business logic, and the moment they put that label on it their brain automatically (after years of conditioning) does not even begin to consider the database as the appropriate place for that type of logic.

My problem for putting business logic in the db is not so much about performance but readability, custom sql scripts are very hard to maintain...

If you do not like pgsql syntax i get that, you can always use python or something but why would you say custom sql scripts are hard to maintain?

Isn't it harder to create well designed abstractions for business logic in SQL compared to a full-fledged backend application server?

I know SQL has stored procedures and the like, but is it correct that SQL is limited in how it can express domain logic, compared to languages that backend application servers are generally written in?

The point i was making was more in the direction of logic in the database, not specifically in pure SQL, it can be PgSql, Python ... does not matter. For the tasks that database side code should be used for (data consistency/access rights/reports on data) i would say they can express the domain logic for those tasks quite nicely, even better then in other languages. For example i can say "grant select on invoices to accountant" or i can add a constraint on a table like "CHECK (price > discounted_price)" or "CHECK (price > 0)". I do that in one place and i no longer have to worry about checking this (simple example of) domain logic anywhere else.

Most of the time this is what people call domain logic (consistency/access/reports) although i would think a better name for it would be "data logic", heard that somewhere and in a lot of the cases this is almost everything the application does

> I know SQL has stored procedures and the like, but is it correct that SQL is limited in how it can express domain logic, compared to languages that backend application servers are generally written in?

Procedural extensions aren't, really. I mean you can literally just use Python (pg/python) or Javascript (plv8) to write postgres functions.

At one dev position I had, the DBA wrote stored procedures for everything I could possibly want to do with the database, and that was the only way I interacted with it period. I wrote a small wrapper around these stored procedures and the records they returned, and used it exclusively.

It was a much more pleasant experience than having no stored procedures and a full blown ORM, which IMO inevitably leads to sillyness like doing inner joins at the UI level.

This sounds a lot like what Stephen Feuerstein and Tom Kyte advocate for Oracle environments.

> and a full blown ORM, which IMO inevitably leads to sillyness like doing inner joins at the UI level.

What ORM was this that made you do inner joins in the UI layer?

We store settings based on a hierarchy in the database using JSONB. Basically:

global -> country -> account -> user

PostgreSQL doesn't support deep merging of json docs, so I just wrote an aggregate function using plv8 to merge them. (the result from the query always results in ~10 records which is then grouped and merged so perf is always fast)

https://gist.github.com/phillip-haydon/02e1cda346b4900a3e009...

This doesn't include the javascript used for merging cos I just wanted to show the stub.

This means we can just pass the args to postgresql and return the settings for any given user.

We used to attempt to run 6 queries and merge in C# code when we did this in SQL Server.

I also had to write JSONB deep merging as part of this RethinkDB --> PostgreSQL rewrite. Here's the code I wrote https://github.com/sagemathinc/smc/blob/ce594ff0574ce781bf78... That code constructs a single complicated query; I wonder whether I should write it as a stored function in the database instead. (I'm the author of the blog post.)

I've deliberately avoided deep arrays for settings, so my jsonb settings merge is simply:

    SELECT COALESCE(companies.options, '{}'::jsonb) || COALESCE(groups.options, '{}'::jsonb) || COALESCE(users.options, '{}'::jsonb)
      FROM users
      JOIN companies ON users.company_id = companies.id
      LEFT JOIN groups ON users.group_id = groups.id
      WHERE users.id = ?

Where I need a hierarchy for key names I'm just prefixing.

I sit right on the knife edge of tossing all the jsonb options and replacing with an old-school normalized EAV model.

What are some resource one could use to learn 3.)? I know the very basics of postgres since I am a web dev, but I like to learn more about fine tunning rdbms for better performance.

The PostgreSQL manual [1] is an incredible resource. The first 100 or so pages give you a lay of the land. If you read those you will be lightyears ahead of the average web dev. After that you can dig into other chapters/topics that interest you.

[1] https://www.postgresql.org/docs/manuals/

Even though I've never sat down and read it, I can say with confidence that the Postgres manual is one of the most impressive manuals I've ever seen. They could have published the manual as 2 books, and I would have paid money to read them.

They published it as 3 books. Or, a third party did.

The more you know! It looks like they published the v9.0 reference manual in late 2010.

In case anyone is curious, you can order them here:

* Volume 1A - http://www.network-theory.co.uk/postgresql9/vol1a/

* Volume 1B - http://www.network-theory.co.uk/postgresql9/vol1b/

* Volume 2 - http://www.network-theory.co.uk/postgresql9/vol2/

* Volume 3 - http://www.network-theory.co.uk/postgresql9/vol3/

I highly recommend the Arch Wiki as well. It is the best of its kind, there is.

That too is 95% generic - https://wiki.archlinux.org/

I always appreciated that Postgresql documents their differences from standard SQL. When I want to know the standard way I often revisit their documents, in addition to those of the database I'm using.

Replying to save, this is very useful on first pass.

You can also click the "favorite" link to save a comment. Once "favorited", they're accessible via your profile page.

That's the spirit. Don't think about tuning the config, stock AWS RDS instances will give you all the things you need 99% of the time

If you're looking to learn a mental model of the internals, a coworker has pointed me at http://www.interdb.jp/pg/ and it's pretty good.

Reply on news.ycombinator.com