Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Things to know about databases

architecturenotes.co

Post reply on HN
Print view View original

141–150 of 247 posts

Re: Things to know about databases

#141

Post by funcDropShadow »

bakuninsbart wrote: post #112
brightball wrote: post #91

Earlier quoted context omitted.

I've honestly never understood why people have such a distaste for SQL. SQL and Linux/Unix have been the biggest constants of my entire programming career to this point (20ish years). I always know I can count on them.

SQL is great at what it is designed to do, and absolutely horrible for anything else. Sometimes I have had to use tools that only allow (a subset of) SQL for querying data. (Especially BI) Doing regex date validation in a json derived from a string in an sql dialect without function-definitions is horrendous. These kinds of "why the f do i have to use sql for this "-moments happened surprisingly often to me working a…

The problem you are describing is probably rooted in the specific SQL dialect you had to use. Selecting from inside JSON strings and matching regular expressions should be a no brainer. And it is straight forward in PostgreSQL, e.g.

Re: Things to know about databases

#142

Post by omginternets »

oarabbus_ wrote: post #133
brightball wrote: post #91

Earlier quoted context omitted.

I've honestly never understood why people have such a distaste for SQL. SQL and Linux/Unix have been the biggest constants of my entire programming career to this point (20ish years). I always know I can count on them.

Honestly, I think it's because a lot of folks go their entire careers avoiding set-based thinking. Many of these folks are talented programmers so I would assume they'd become quite decent at SQL if they devoted time to it. I'm speaking more on the DML side than the DDL side here. In my experience The rest range from "SELECT * is as far as I'll go; where's the ORM" to "why the hell are you using cursors to aggregate"…

Where can I learn about the relationship between set-theory and SQL?

Re: Things to know about databases

#143

Post by YetAnotherNick »

SulphurSmell wrote: post #61

This article is informative. I have found that databases in general tend to be less sexy than the front-end apps...especially with the recent cohort of devs. As an old bastard, I would pass on one thing: Realize that any reasonably used database will likely outlast the applications leveraging it. This is especially true the bigger it gets, and the longer it stays in production. That said, if you are influencing the d…

> I have found that databases in general tend to be less sexy than the front-end apps

I don't know if there is a single soul who believes this. If you are designing a database, it is much more cooler than front end apps.

Re: Things to know about databases

#144

Post by funcDropShadow »

jstarfish wrote: post #127
brightball wrote: post #91

Earlier quoted context omitted.

I've honestly never understood why people have such a distaste for SQL. SQL and Linux/Unix have been the biggest constants of my entire programming career to this point (20ish years). I always know I can count on them.

I wholly agree with you, but I'll say this: When it comes to prototyping, I'm not going to fuck with something like Java-- I'm going to reach for Python. If I don't know what I'm doing to begin with, I don't need something telling me all the ways in which I'm doing it wrong. Same goes for SQL/NoSQL. I loosely know what I need to model and may revise it arbitrarily. SQL does not lend itself to this. NoSQL was designed…

In the case of SQL/NoSQL I think it is the other way around. In order to use a NoSQL database to its strength, you have to know all access path up front [1]. Because you are designing your table structure with all the queries in mind. In SQL on the other hand, you can always go for a normalized structure and you are pretty safe to be prepared for most requirements that are coming in the future.

Additionally, I think drafting a DDL schema is a great way to learn about a new application domain. It forces me to ask hard questions. And that improves my understanding of the domain. I guess that is similar to some people who like to prototype using Haskell type signatures.

[1] Unless you mean specifically a document store without schemas.

Re: Things to know about databases

#145

Post by funcDropShadow »

spmurrayzzz wrote: post #69
happimess wrote: post #65

Earlier quoted context omitted.

> There is need to iterate and adapt frequently, sometimes as part of your go-to-market strategy, in order to fully understand the problem space. If you're pivoting so hard that your SQL schema breaks, how is a schemaless system going to help you? You'll still have to either throw out your old data (easy in both cases) or figure out a way to map old records onto new semantics (hard in both cases). I agree with GP tha…

>If you're pivoting so hard that your SQL schema breaks, how is a schemaless system going to help you? You'll still have to either throw out your old data (easy in both cases) or figure out a way to map old records onto new semantics (hard in both cases). I agree with your comment that it's a central problem to solve and that both options, throwing out data or map old records onto new semantics, is an endemic choice…

Admittedly, my experience with MongoDB and Cassandra has gained some rust over the last decade, but what makes you say such nosql databases have expressive APIs? Compared to PostgreSQL they have miniscule query languages and it is very hard, if at all possible, to express constraints. And constraints, sometimes self-imposed sometimes not, are what makes projects successful, even startups. Many startups try to find this one little niche they can dominate. That is a self-imposed constraint. People tend to think freedom makes them creative, productive, and inventive, while in fact the opposite is the truth. With opposite of freedom I mean carefully selected constraints not oppression.

Re: Things to know about databases

#146

Post by oarabbus_ »

omginternets wrote: post #142
oarabbus_ wrote: post #133

Earlier quoted context omitted.

Honestly, I think it's because a lot of folks go their entire careers avoiding set-based thinking. Many of these folks are talented programmers so I would assume they'd become quite decent at SQL if they devoted time to it. I'm speaking more on the DML side than the DDL side here. In my experience The rest range from "SELECT * is as far as I'll go; where's the ORM" to "why the hell are you using cursors to aggregate"…

Where can I learn about the relationship between set-theory and SQL?

I'm not too well-versed in the academic/mathematical background of SQL, other than than to say it's closer to relational algebra than Set theory, and the resource to read is probably the 1970 Codd paper: https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf

SQL does implement the set operations of UNION, INTERSECT, and EXCEPT, but I meant "thinking in sets" more colloquially.

edit: this is more practical than theoretical but the author here actually does a nice job of discussing "set-based" thinking vs. procedural https://www.itprotoday.com/open-source-sql/fangraphs-tags-cl...

Re: Things to know about databases

#147

Post by hodgesrm »

jiggawatts wrote: post #136
Merad wrote: post #123

> a dirty read occurs when you perform a read, and another transaction updates the same row but doesn't commit the work, you perform another read, and you can access the uncommitted (dirty) value It's even worse than this with MS SQL Server. When using the READ UNCOMMITTED isolation level it's actually possible to read corrupted data, e.g. you might read a string while it's being updated, so the result row you get co…

I really wish SERIALIZABLE was the default transaction isolation level and anything lower was opt in… with warnings.

SERIALIZABLE is ridiculously slow if you have any level of concurrency in your app. READ COMMITTED is a reasonable default in general. The behavior GP is describing sounds like an out and out bug.

Dirty reads incidentally weren't supported for quite some time in the Sybase architecture (which forked to MS SQL Server in 1992). There was a Sybase effort to add dirty read support around 1995 or so. The project name was "Lolita."

Re: Things to know about databases

#148

Post by funcDropShadow »

spmurrayzzz wrote: post #119
marcosdumay wrote: post #96

Earlier quoted context omitted.

> This presumes that systems built on top of non-RDBMS are incapable of enforcing similar constraints. Are you kidding? They never can. The entire point of ditching the relational model is discarding data constraints and normalization.

Never? Many NoSQL stores are offering parity in many of the feature verticals that were historically the sole domain of RDBMS. Mongo has always had semantics to support normalized data modeling, has schema support, and has had distributed multi-document ACID transactions since 2019 [1]. You don't have to use those features as they're opt-in, but they're there. I know that full parity between the two isn't feasible, b…

What is the point of using MongoDB with multi-document ACID transactions? Enabling durability in MongoDB is usually costly enough that you can't find a performance benefit compared to Postgres. With JSONB support in PostgreSQL, I dare say, it can express anything that MongoDB can express with its data model and query language. That leaves scalability as the only possible advantage of MongoDB compared to PostgreSQL. And the scalability of MongoDB is rather restrictive, compared to e.g. Cassandra.

And I would never trust a database that has such a bad track record, regarding durability as MongoDB, although I admit that PostgreSQL had theoretical problems there as well in the past.

Re: Things to know about databases

#150

Post by funcDropShadow »

imachine1980_ wrote: post #94
jpdb wrote: post #89

Earlier quoted context omitted.

Hard disagree. The operational overhead of RDBMS and specifically their inherent reliance on a single primary node makes them, in my opinion, a bad place to start your architecture. I want to be able to treat the servers in my database tier as cattle instead of pets and RDBMSs don't fit this paradigm well. Either NoSQL or NewSQL databases are, in my opinion, a much better place to start. I feel like RDBMSs being the…

You can have multi tb postgree database, that are fast and usable whit limited number of cache layers for speed, but you probably don need it. mediums migrate from single postress in 2020.

I don't understand why this comment is down voted. I've been part of a project that uses a dozen different PostgreSQL databases from different services, one of those databases is multi TB with an OLAP usage patterns. And it beats performance-wise all the previous attempts to solve that specific problem.
Post reply on HN