13 ms·

Important database news from the last few months

123 points by MarkusWinand 8y ago

thanatos_dem 8y ago
It would be interesting to see a "new and noteworthy" section in these types of news recaps. There's always a lot of hype around new databases, and it'd be nice to see an unbiased perspective from someone with such deep knowledge of database systems, similar to Aphyr's deep dives into consistency and durability. This seems focused on only SQL systems, and only the largest ones, which is okay, but I think there's a lot more to the story than that :)
- jchanimal 8y ago
  If you are into global consistency but also want NoSQL flexibility, my employer FaunaDB has ACID transactions, joins, indexes, and object level access control.
  - atombender 8y ago
    I'm sure you guys are doing good business, but the only way we'd use FaunaDB is if it's open-sourced. I suspect you'll have a hard time competing against Google Spanner (or even Google Cloud Datastore).
Scarbutt 8y ago
Finally, an open source SQL database with history for data builtin: https://mariadb.com/kb/en/library/system-versioned-tables/ https://mariadb.com/kb/en/library/system-versioned-tables/ Always wondered why this feature has been ignored for so long.
- bottled_poe 8y ago
  No features come for free. How do you justify the universal value of this feature?
- Latteland 8y ago
  Thanks for pointing that out! They appear to only support one of the two time models proposed by the research community. They support transaction time (when you write it), but not the interesting and useful 'valid time'. The difference can be explained as you are going to be paid a salary for working say from July 1, 2018 to June 30, 2019 (this is valid time), but we write the information into the db at a different time, say we write it on June 15 (this is transaction time), then we realize a mistake and update your salary record on June 20th. But your employment starts July 1.
  - MarkusWinand 8y ago
    Just FYI: The SQL standard supports both types. The other one (valid time) is called "application versioning" in SQL. Read this paper if you'd like to learn more about it: http://cs.ulb.ac.be/public/_media/teaching/infoh415/tempfeaturessql2011.pdf http://cs.ulb.ac.be/public/_media/teaching/infoh415/tempfeat...
    - Latteland 8y ago
      Cool I hadn't heard about that.
  - ldng 8y ago
    Hum, you don't have the syntaxic sugar but I'm pretty sure you can do bitemporal tables for sometimes in PostgreSQL.
olavk 8y ago
Is this the final nail in the coffin for the NoSQL hype? Although I'm not sure the road back to sanity is to add SQL interfaces to non-relational database systems. SQL and the relational model are often conflated, both in the NoSQL hype and apparently also in the backlash. I like things like LINQ in .net which allows you to work with a relational model and use relational algebra without having to use the SQL language
- cyberpunk0 8y ago
  Nosql dead because ine company uses SQL? Oh man are there shome sheepish people here.
  - olavk 8y ago
    NoSQL is not going to die, there are many places where specialized non-relational databases are appropriate. But the hype wave may be coming to an end.
- kimi 8y ago
  I am personally playing a bit with Apache Calcite - SQL without the data storage - and it sounds very interesting. For most reporting jobs, a couple of SQL queries are all that s needed and are easy to read and easy to write, even if you are actually querying some in-memory structures.
peatmoss 8y ago
I recently bought the author’s book to brush some cobwebs off parts of my SQLing. Over the years I’ve seen Markus’s blog posts and guides posted here on HN. I’m always impressed when I see an individual focus on a topic and build an identity / business like this. Thanks for all the blog posts, Markus!
rm999 8y ago
SQL is by no means perfect. For one, t̶h̶e̶r̶e̶'̶s̶ ̶n̶o̶ ̶o̶f̶f̶i̶c̶i̶a̶l̶ ̶s̶p̶e̶c̶i̶f̶i̶c̶a̶t̶i̶o̶n̶: some dialects are meaningfully different than others, and even similar ones are often full of implementation details about the underlying database. It has a ton of quirks, and isn't as powerful as I'd always want it to be. And sometimes it can be really hard to read. But I still haven't found a better universal "language" to talk about extracting and manipulating data. The core set of functionality of SQL (selects, joins, aggregations) is usually adequate to express powerful ideas. Most importantly, a lot of people who work with data know it, and I've found semi-technical people (including non-programmers) can learn it quickly. Anyone who has been paying attention to trends in the data world in the last 5 years knows SQL is here to stay for the long-term.
- deleted 8y ago
  [deleted]
- MarkusWinand 8y ago
  > For one, there's no official specification I'd take ISO/IEC 9075 as an official specification: https://webstore.iec.ch/publication/59685 https://webstore.iec.ch/publication/59685 > and isn't as powerful as I'd always want it to be Do you have examples? There is a lot in SQL that is not commonly known—if you let me know what you are thinking of, I might be able to show you an adequate SQL feature.
  - rm999 8y ago
    >I'd take ISO/IEC 9075 as an official specification Fair enough, but I'd argue it's not a de facto standard because the vast majority of implementers don't follow it. Case in point: SQL is rarely portable across databases, even if you're e.g. moving from postgres to a postgres-like system like bigquery or redshift. >Do you have examples? For me (as a machine learning + data engineer) it often comes up around aggregating with window functions. Things that would take a couple lines in R or Python or Scala can take dozen of extra lines with superfluous CTEs.
    - MarkusWinand 8y ago
mmaunder 8y ago
Whew. Those years of enduring "SQL is dead" posts were not easy. Now if we can just rebrand "cloud" to "someone else's computer", much will be right with the world.
- peterwwillis 8y ago
  Really it should be rebranded "shared hosting".
  - aklemm 8y ago
    Novel. I like it.
- dboreham 8y ago
  Web. Scale.
- ebikelaw 8y ago
  The conflation of SQL with consistency in this blog post is dumb. Yes Google offers SQL on Spanner but the query execution is built atop lower level Spanner primitives that are also available to developers and are similar to Bigtable concepts. Also Bigtable is still much, much larger and more important within Google than is Spanner/F1, so the death of Bigtable is much overhyped. Point is there are strong and weak consistency models with and without SQL execution engines, and they’re all useful.
  - derefr 8y ago
    The SQL standard specifies explicit consistency semantics for statements and transactions, though. So something claiming to adhere to e.g. SQL:2011, is claiming that executing certain commands will have certain consistency properties (and if you've set up your DBMS to have weaker consistency, then it should be blaring a loud alarm that you've effectively put it in a non-SQL-standard-conformant mode.) Other generic DBMS query protocols could specify their own consistency semantics, but I'm not aware of any that do. (I guess, if you consider REST a "DBMS query protocol", then CouchDB is an example of a DBMS "conforming" to the consistency semantics specified in the REST protocol.)
Annatar 8y ago
"According to the “shoot the messenger” principle, PostgreSQL has been heavily criticized for fsyncgate. Indeed, PostgreSQL suffers from this problem more than other databases as it doesn't offer direct IO." Not a problem on any illumos based operating system, as fsync(2) and ZFS do what they're supposed to do and do not lie about data being on stable storage when it isn't. PostgreSQL is a good database, and if you want it to be bullet proof, run it on an illumos based operating system.
- peatmoss 8y ago
  This comment is currently fading into downvotitude. Can someone explain why? Is it factually incorrect? Are people upset at the implication that other systems do lie about data being on stable storage when it isn’t? I haven’t spent much time with Illumos, but if Postgres is not impacted on Illumos, then why the downvotes?
  - tialaramex 8y ago
    Ultimately the Operating System doesn't (and can't) know whether the storage is actually going to retain data you've written to it. Even if your OS goes out of its way to use all six kinds of double secret synchronization primitive, the cheap eBay clone product didn't bother implementing any of them and replies "Yup, definitely 100% synchronised" while doing nothing, then the user is disappointed because your OS promised that the data was safe and it isn't. This isn't really just about hard disks either. Over the years lots of manufacturers have sold "smart" network cards that corrupt data if they're left to their own devices to "speed up" networking by cutting corners, miscalculating checksums, copying things wrongly from RAM, and so on. But it's even possible to build a card where the most trivial scenario - where the OS lays out the entire packet and then explicitly does IO operations to write it to the card - still sometimes randomly corrupts data. Your OS can promise a "guarantee" that the network data is exactly what you sent... and then you find 0.1% of packets are corrupted before they even reach the switch, oops. Ultimately all bets are off when running on real hardware. Many years ago a friend of mine had a PC where randomly sometimes basic Unix features like "cat" would segfault or misbehave. Did disk checks, re-installed the entire OS, loads of hair-pulling. Eventually he discovered that the CPU fan failed, and overheating had damaged the on-die L2 cache...
amirouche 8y ago
How is realistic this article without a word about FoundationDB?
zzzcpan 8y ago
> The marketing term NoSQL, which was the hippest buzzword just a few years back, is slowly becoming a synonym for a defect. Without SQL, there is something missing. I guess RDBMS companies are too afraid of NoSQL to attack it with such silly propagandist statements. You can't win the market this way. To me it increasingly looks like SQL is losing in distributed systems. And NoSQL may never really gain SQL beyond niche usage after all.
- Joeri 8y ago
  To me it increasingly looks like SQL is losing in distributed systems. I’m seeing the reverse trend. Google went from nosql to sql with cloud spanner. The various flavors of SQL on hadoop are on the uptick. Cockroachdb is looking really interesting.
  - zzzcpan 8y ago
    But do you see Spanner, CockroachDB, TiDB actually getting any ground?
    - deleted 8y ago
      [deleted]
spraak 8y ago
Maybe the NOSQL hype is dying down, but it still seems like for caching for fast reads it can be helpful. What do you think?
- emperorcezar 8y ago
  NoSQL is a catch all term. There is a place for key/value, document, graph, etc databases. People got all excited cause they discovered that these exist. They've been around for a long time. Relational DBs are a good default, but there are plenty of cases in which you'll use another type of DB. Engineers need to stop trying to find a silver bullet.
  - mmt 8y ago
    They also exist as features within an (otherwise) relational DBMS. Perhaps the trouble is that it's mostly been in the realm of the commercial ones and only relatively recently has Postgres started adopting enough of them to make the notion popular.
noncoml 8y ago
From a developer’s point of view, I absolutely hate SQL. Converting my data from and to row format is a pain. Transactions don’t really tie well with the rest of my application logic. And of course embedding multiline SQL statements in my code looks like monstrosity.
- tzahola 8y ago
  Why do you put SQL statements in the “core”? Just put them behind and interface and call it a day. SQL and relational data is the only option, unless someone comes up with an equally powerful alternative with similar theoretical underpinnings.
  - noncoml 8y ago
    Sorry, it was a autocorrect shenanigans. s/core/code
geekuillaume 8y ago
On the database subject, I'm working on a new project called DBacked, basically a simple and encrypted database backups as a service. I'm not ready yet for a Show HN (more things to improve on the presentation website) but happy to get your advices ;)
- mxschumacher 8y ago
  easier if you show us the code
esaym 8y ago
Anyone know a really good SQL book or tutorial? Every book I've ever bought is usually way too simple. I'll flip to the "advanced" section thinking I'll finally learn some tricks only to see the "advance" section just introduces the join syntax... I'd like a good book that goes over queries common in reporting, you know a 3 page sql query that joins to sub queries that themselves are joining to sub queries. You know, the good stuff!
- ProfTeggy 8y ago
  Feel free to browse/download/peruse the many PDF slides of my course "Advanced SQL" (in which joins are considered basic). https://db.inf.uni-tuebingen.de/teaching/AdvancedSQLSS2017.html https://db.inf.uni-tuebingen.de/teaching/AdvancedSQLSS2017.h...
- laichzeit0 8y ago
  E. F. Codd - The Relational Model for Database Management - Version 2.
- reitanqild 8y ago
  Did you try SQL Cookbook? I think that was the one I used to enjoy. Lots of receipts. The first are quite simple but the later, more advanced ones should take you way beyond simple joins and would also have variations for the most common databases. From the table of contents: ... Chapter 5 Metadata Queries Listing Tables in a Schema Listing a Table's Columns Listing Indexed Columns for a Table Listing Constraints on a Table Listing Foreign Keys Without Corresponding Indexes Using SQL to Generate SQL Describing the Data Dictionary Views in an Oracle Database Chapter 6 Working with Strings Walking a String Embedding Quotes Within String Literals Counting the Occurrences of a Character in a String Removing Unwanted Characters from a String Separating Numeric and Character Data Determining Whether a String Is Alphanumeric Extracting Initials from a Name Ordering by Parts of a String Ordering by a Number in a String Creating a Delimited List from Table Rows Converting Delimited Data into a Multi-Valued IN-List Alphabetizing a String Identifying Strings That Can Be Treated as Numbers Extracting the nth Delimited Substring Parsing an IP Address Chapter 7 Working with Numbers Computing an Average Finding the Min/Max Value in a Column Summing the Values in a Column Counting Rows in a Table Counting Values in a Column Generating a Running Total Generating a Running Product Calculating a Running Difference Calculating a Mode Calculating a Median Determining the Percentage of a Total Aggregating Nullable Columns Computing Averages Without High and Low Values Converting Alphanumeric Strings into Numbers Changing Values in a Running Total Chapter 8 Date Arithmetic Adding and Subtracting Days, Months, and Years Determining the Number of Days Between Two Dates Determining the Number of Business Days Between Two Dates Determining the Number of Months or Years Between Two Dates Determining the Number of Seconds, Minutes, or Hours Between Two Dates Counting the Occurrences of Weekdays in a Year Determining the Date Difference Between the Current Record and the Next Record Chapter 9 Date Manipulation Determining if a Year Is a Leap Year Determining the Number of Days in a Year Extracting Units of Time from a Date Determining the First and Last Day of a Month Determining All Dates for a Particular Weekday Throughout a Year Determining the Date of the First and Last Occurrence of a Specific Weekday in a Month Creating a Calendar Listing Quarter Start and End Dates for the Year Determining Quarter Start and End Dates for a Given Quarter Filling in Missing Dates Searching on Specific Units of Time Comparing Records Using Specific Parts of a Date Identifying Overlapping Date Ranges Chapter 10 Working with Ranges Locating a Range of Consecutive Values Finding Differences Between Rows in the Same Group or Partition Locating the Beginning and End of a Range of Consecutive Values Filling in Missing Values in a Range of Values Generating Consecutive Numeric Values Chapter 11 Advanced Searching Paginating Through a Result Set Skipping n Rows from a Table Incorporating OR Logic when Using Outer Joins Determining Which Rows Are Reciprocals Selecting the Top n Records Finding Records with the Highest and Lowest Values Investigating Future Rows Shifting Row Values Ranking Results Suppressing Duplicates Finding Knight Values Generating Simple Forecasts Chapter 12 Reporting and Warehousing Pivoting a Result Set into One Row Pivoting a Result Set into Multiple Rows Reverse Pivoting a Result Set Reverse Pivoting a Result Set into One Column Suppressing Repeating Values from a Result Set Pivoting a Result Set to Facilitate Inter-Row Calculations Creating Buckets of Data, of a Fixed Size Creating a Predefined Number of Buckets Creating Horizontal Histograms Creating Vertical Histograms Returning Non-GROUP BY Columns Calculating Simple Subtotals Calculating Subtotals for All Possible Expression Combinations Identifying Rows That Are Not Subtotals Using Case Expressions to Flag Rows Creating a Sparse Matrix Grouping Rows by Units of Time Performing Aggregations over Different Groups/Partitions Simultaneously Performing Aggregations over a Moving Range of Values Pivoting a Result Set with Subtotals Chapter 13 Hierarchical Queries Expressing a Parent-Child Relationship Expressing a Child-Parent-Grandparent Relationship Creating a Hierarchical View of a Table Finding All Child Rows for a Given Parent Row Determining Which Rows Are Leaf, Branch, or Root Nodes Chapter 14 Odds 'n' Ends Creating Cross-Tab Reports Using SQL Server's PIVOT Operator Unpivoting a Cross-Tab Report Using SQL Server's UNPIVOT Operator Transposing a Result Set Using Oracle's MODEL Clause Extracting Elements of a String from Unfixed Locations Finding the Number of Days in a Year (an Alternate Solution for Oracle) Searching for Mixed Alphanumeric Strings Converting Whole Numbers to Binary Using Oracle Pivoting a Ranked Result Set Adding a Column Header into a Double Pivoted Result Set Converting a Scalar Subquery to a Composite Subquery in Oracle Parsing Serialized Data into Rows Calculating Percent Relative to Total Creating CSV Output from Oracle Finding Text Not Matching a Pattern (Oracle) Transforming Data with an Inline View Testing for Existence of a Value Within a Group Appendix A Window Function Refresher Grouping Windowing Appendix B Rozenshtein Revisited Rozenshtein's Example Tables Answering Questions Involving Negation Answering Questions Involving "at Most" Answering Questions Involving "at Least" Answering Questions Involving "Exactly" Answering Questions Involving "Any" or "All"
gfodor 8y ago
"NoSQL" took the idea of a rejection of syntax (SQL) but also, in practice, burned in with it a rejection of vast swathes of database theory that resulted in the modern RDBMSes. It was a meme that was generally easy to ignore and identify the "bad ideas" within if you understood the history of databases. SQL obviously is just an incidental syntactic detail on top of a fairly sound, generalized model for databases -- relational algebra combined with a clear semantic model for transactions (ACID, etc.) These two things I believe will stand the test of time. Everything else, I think, is ultimately incidental complexity to meet operational goals (like indexing, or caching methods, or sharding, or replication, or denormalization) or conceptually useful models and interfaces (like graphs, or literate APIs, or query langauges, or ORMs, etc) on top of these basic facilities that need to be provided by a database system. The trend of NoSQL databases I think can be summarized as people building systems that tilted towards letting these bits of incidental complexity dictate the design of the system as a whole. The overriding concern seemed to be "scalability and performance" with "easy APIs" a close second and so that resulted in specific data structure and API needs or access patterns dictating everything about how the database system itself worked, and how data was modeled in that system. For example, MongoDB was hearalded as finally letting you access a hierarchical data structure without SQL JOINs, but did so not by just providing a nice abstraction on top of a sound relational schema that allowed you to "re-project" your thinking and access patterns to a document-oriented one when it was the right model contextually, but instead by burning that denormalized, document-oriented data structure into the entire conceptual and technical stack for the whole system! The cost, of course, is self-evident since choosing to reject the relational model also chooses to reject the benefits that were explicitly recognized as a reason for the relational model to be a good choice for database systems: normalized data allows a much more rich set of transformations and projections on that data to be unambiguously expressed and fulfilled by the system. In other words, it's much more future proofed since you now have the flexibility to mix-and-match and analyze it in arbitrary ways, decoupled from your choice of schema. So the effect of choosing MongoDB meant that you had minimal future-proofing if you got your document structure wrong and suddenly needed to re-project your data in a new way. The supported path, of course, was to "just run map reduce on it" -- ie, lets force every application developer to do the job the database is supposed to do :( It's nice to see things are moving forward on this again, hopefully the trend doesn't just reverse now and we have a flood of "SQL access" APIs to poorly grounded database systems.
deleted 8y ago
[deleted]

123 points by MarkusWinand 8y ago

It would be interesting to see a "new and noteworthy" section in these types of news recaps. There's always a lot of hype around new databases, and it'd be nice to see an unbiased perspective from someone with such deep knowledge of database systems, similar to Aphyr's deep dives into consistency and durability. This seems focused on only SQL systems, and only the largest ones, which is okay, but I think there's a lot more to the story than that :)

jchanimal 8y ago
If you are into global consistency but also want NoSQL flexibility, my employer FaunaDB has ACID transactions, joins, indexes, and object level access control.
- atombender 8y ago
  I'm sure you guys are doing good business, but the only way we'd use FaunaDB is if it's open-sourced. I suspect you'll have a hard time competing against Google Spanner (or even Google Cloud Datastore).

Scarbutt 8y ago

Finally, an open source SQL database with history for data builtin: https://mariadb.com/kb/en/library/system-versioned-tables/ https://mariadb.com/kb/en/library/system-versioned-tables/ Always wondered why this feature has been ignored for so long.

bottled_poe 8y ago
No features come for free. How do you justify the universal value of this feature?
Latteland 8y ago
Thanks for pointing that out! They appear to only support one of the two time models proposed by the research community. They support transaction time (when you write it), but not the interesting and useful 'valid time'. The difference can be explained as you are going to be paid a salary for working say from July 1, 2018 to June 30, 2019 (this is valid time), but we write the information into the db at a different time, say we write it on June 15 (this is transaction time), then we realize a mistake and update your salary record on June 20th. But your employment starts July 1.
- MarkusWinand 8y ago
  Just FYI: The SQL standard supports both types. The other one (valid time) is called "application versioning" in SQL. Read this paper if you'd like to learn more about it: http://cs.ulb.ac.be/public/_media/teaching/infoh415/tempfeaturessql2011.pdf http://cs.ulb.ac.be/public/_media/teaching/infoh415/tempfeat...
  - Latteland 8y ago
    Cool I hadn't heard about that.
- ldng 8y ago
  Hum, you don't have the syntaxic sugar but I'm pretty sure you can do bitemporal tables for sometimes in PostgreSQL.

olavk 8y ago

Is this the final nail in the coffin for the NoSQL hype? Although I'm not sure the road back to sanity is to add SQL interfaces to non-relational database systems. SQL and the relational model are often conflated, both in the NoSQL hype and apparently also in the backlash. I like things like LINQ in .net which allows you to work with a relational model and use relational algebra without having to use the SQL language

cyberpunk0 8y ago
Nosql dead because ine company uses SQL? Oh man are there shome sheepish people here.
- olavk 8y ago
  NoSQL is not going to die, there are many places where specialized non-relational databases are appropriate. But the hype wave may be coming to an end.
kimi 8y ago
I am personally playing a bit with Apache Calcite - SQL without the data storage - and it sounds very interesting. For most reporting jobs, a couple of SQL queries are all that s needed and are easy to read and easy to write, even if you are actually querying some in-memory structures.

peatmoss 8y ago

I recently bought the author’s book to brush some cobwebs off parts of my SQLing. Over the years I’ve seen Markus’s blog posts and guides posted here on HN. I’m always impressed when I see an individual focus on a topic and build an identity / business like this. Thanks for all the blog posts, Markus!

rm999 8y ago

SQL is by no means perfect. For one, t̶h̶e̶r̶e̶'̶s̶ ̶n̶o̶ ̶o̶f̶f̶i̶c̶i̶a̶l̶ ̶s̶p̶e̶c̶i̶f̶i̶c̶a̶t̶i̶o̶n̶: some dialects are meaningfully different than others, and even similar ones are often full of implementation details about the underlying database. It has a ton of quirks, and isn't as powerful as I'd always want it to be. And sometimes it can be really hard to read. But I still haven't found a better universal "language" to talk about extracting and manipulating data. The core set of functionality of SQL (selects, joins, aggregations) is usually adequate to express powerful ideas. Most importantly, a lot of people who work with data know it, and I've found semi-technical people (including non-programmers) can learn it quickly. Anyone who has been paying attention to trends in the data world in the last 5 years knows SQL is here to stay for the long-term.

deleted 8y ago
[deleted]
MarkusWinand 8y ago
> For one, there's no official specification I'd take ISO/IEC 9075 as an official specification: https://webstore.iec.ch/publication/59685 https://webstore.iec.ch/publication/59685 > and isn't as powerful as I'd always want it to be Do you have examples? There is a lot in SQL that is not commonly known—if you let me know what you are thinking of, I might be able to show you an adequate SQL feature.
- rm999 8y ago
  >I'd take ISO/IEC 9075 as an official specification Fair enough, but I'd argue it's not a de facto standard because the vast majority of implementers don't follow it. Case in point: SQL is rarely portable across databases, even if you're e.g. moving from postgres to a postgres-like system like bigquery or redshift. >Do you have examples? For me (as a machine learning + data engineer) it often comes up around aggregating with window functions. Things that would take a couple lines in R or Python or Scala can take dozen of extra lines with superfluous CTEs.
  - MarkusWinand 8y ago

mmaunder 8y ago

Whew. Those years of enduring "SQL is dead" posts were not easy. Now if we can just rebrand "cloud" to "someone else's computer", much will be right with the world.

peterwwillis 8y ago
Really it should be rebranded "shared hosting".
- aklemm 8y ago
  Novel. I like it.
dboreham 8y ago
Web. Scale.
ebikelaw 8y ago
The conflation of SQL with consistency in this blog post is dumb. Yes Google offers SQL on Spanner but the query execution is built atop lower level Spanner primitives that are also available to developers and are similar to Bigtable concepts. Also Bigtable is still much, much larger and more important within Google than is Spanner/F1, so the death of Bigtable is much overhyped. Point is there are strong and weak consistency models with and without SQL execution engines, and they’re all useful.
- derefr 8y ago
  The SQL standard specifies explicit consistency semantics for statements and transactions, though. So something claiming to adhere to e.g. SQL:2011, is claiming that executing certain commands will have certain consistency properties (and if you've set up your DBMS to have weaker consistency, then it should be blaring a loud alarm that you've effectively put it in a non-SQL-standard-conformant mode.) Other generic DBMS query protocols could specify their own consistency semantics, but I'm not aware of any that do. (I guess, if you consider REST a "DBMS query protocol", then CouchDB is an example of a DBMS "conforming" to the consistency semantics specified in the REST protocol.)

Annatar 8y ago

"According to the “shoot the messenger” principle, PostgreSQL has been heavily criticized for fsyncgate. Indeed, PostgreSQL suffers from this problem more than other databases as it doesn't offer direct IO." Not a problem on any illumos based operating system, as fsync(2) and ZFS do what they're supposed to do and do not lie about data being on stable storage when it isn't. PostgreSQL is a good database, and if you want it to be bullet proof, run it on an illumos based operating system.

peatmoss 8y ago
This comment is currently fading into downvotitude. Can someone explain why? Is it factually incorrect? Are people upset at the implication that other systems do lie about data being on stable storage when it isn’t? I haven’t spent much time with Illumos, but if Postgres is not impacted on Illumos, then why the downvotes?
- tialaramex 8y ago
  Ultimately the Operating System doesn't (and can't) know whether the storage is actually going to retain data you've written to it. Even if your OS goes out of its way to use all six kinds of double secret synchronization primitive, the cheap eBay clone product didn't bother implementing any of them and replies "Yup, definitely 100% synchronised" while doing nothing, then the user is disappointed because your OS promised that the data was safe and it isn't. This isn't really just about hard disks either. Over the years lots of manufacturers have sold "smart" network cards that corrupt data if they're left to their own devices to "speed up" networking by cutting corners, miscalculating checksums, copying things wrongly from RAM, and so on. But it's even possible to build a card where the most trivial scenario - where the OS lays out the entire packet and then explicitly does IO operations to write it to the card - still sometimes randomly corrupts data. Your OS can promise a "guarantee" that the network data is exactly what you sent... and then you find 0.1% of packets are corrupted before they even reach the switch, oops. Ultimately all bets are off when running on real hardware. Many years ago a friend of mine had a PC where randomly sometimes basic Unix features like "cat" would segfault or misbehave. Did disk checks, re-installed the entire OS, loads of hair-pulling. Eventually he discovered that the CPU fan failed, and overheating had damaged the on-die L2 cache...

amirouche 8y ago

How is realistic this article without a word about FoundationDB?

zzzcpan 8y ago

> The marketing term NoSQL, which was the hippest buzzword just a few years back, is slowly becoming a synonym for a defect. Without SQL, there is something missing. I guess RDBMS companies are too afraid of NoSQL to attack it with such silly propagandist statements. You can't win the market this way. To me it increasingly looks like SQL is losing in distributed systems. And NoSQL may never really gain SQL beyond niche usage after all.

Joeri 8y ago
To me it increasingly looks like SQL is losing in distributed systems. I’m seeing the reverse trend. Google went from nosql to sql with cloud spanner. The various flavors of SQL on hadoop are on the uptick. Cockroachdb is looking really interesting.
- zzzcpan 8y ago
  But do you see Spanner, CockroachDB, TiDB actually getting any ground?
  - deleted 8y ago
    [deleted]

spraak 8y ago

Maybe the NOSQL hype is dying down, but it still seems like for caching for fast reads it can be helpful. What do you think?

emperorcezar 8y ago
NoSQL is a catch all term. There is a place for key/value, document, graph, etc databases. People got all excited cause they discovered that these exist. They've been around for a long time. Relational DBs are a good default, but there are plenty of cases in which you'll use another type of DB. Engineers need to stop trying to find a silver bullet.
- mmt 8y ago
  They also exist as features within an (otherwise) relational DBMS. Perhaps the trouble is that it's mostly been in the realm of the commercial ones and only relatively recently has Postgres started adopting enough of them to make the notion popular.

noncoml 8y ago

From a developer’s point of view, I absolutely hate SQL. Converting my data from and to row format is a pain. Transactions don’t really tie well with the rest of my application logic. And of course embedding multiline SQL statements in my code looks like monstrosity.

tzahola 8y ago
Why do you put SQL statements in the “core”? Just put them behind and interface and call it a day. SQL and relational data is the only option, unless someone comes up with an equally powerful alternative with similar theoretical underpinnings.
- noncoml 8y ago
  Sorry, it was a autocorrect shenanigans. s/core/code

geekuillaume 8y ago

On the database subject, I'm working on a new project called DBacked, basically a simple and encrypted database backups as a service. I'm not ready yet for a Show HN (more things to improve on the presentation website) but happy to get your advices ;)

mxschumacher 8y ago
easier if you show us the code

esaym 8y ago

Anyone know a really good SQL book or tutorial? Every book I've ever bought is usually way too simple. I'll flip to the "advanced" section thinking I'll finally learn some tricks only to see the "advance" section just introduces the join syntax... I'd like a good book that goes over queries common in reporting, you know a 3 page sql query that joins to sub queries that themselves are joining to sub queries. You know, the good stuff!

ProfTeggy 8y ago
Feel free to browse/download/peruse the many PDF slides of my course "Advanced SQL" (in which joins are considered basic). https://db.inf.uni-tuebingen.de/teaching/AdvancedSQLSS2017.html https://db.inf.uni-tuebingen.de/teaching/AdvancedSQLSS2017.h...
laichzeit0 8y ago
E. F. Codd - The Relational Model for Database Management - Version 2.
reitanqild 8y ago
Did you try SQL Cookbook? I think that was the one I used to enjoy. Lots of receipts. The first are quite simple but the later, more advanced ones should take you way beyond simple joins and would also have variations for the most common databases. From the table of contents: ... Chapter 5 Metadata Queries Listing Tables in a Schema Listing a Table's Columns Listing Indexed Columns for a Table Listing Constraints on a Table Listing Foreign Keys Without Corresponding Indexes Using SQL to Generate SQL Describing the Data Dictionary Views in an Oracle Database Chapter 6 Working with Strings Walking a String Embedding Quotes Within String Literals Counting the Occurrences of a Character in a String Removing Unwanted Characters from a String Separating Numeric and Character Data Determining Whether a String Is Alphanumeric Extracting Initials from a Name Ordering by Parts of a String Ordering by a Number in a String Creating a Delimited List from Table Rows Converting Delimited Data into a Multi-Valued IN-List Alphabetizing a String Identifying Strings That Can Be Treated as Numbers Extracting the nth Delimited Substring Parsing an IP Address Chapter 7 Working with Numbers Computing an Average Finding the Min/Max Value in a Column Summing the Values in a Column Counting Rows in a Table Counting Values in a Column Generating a Running Total Generating a Running Product Calculating a Running Difference Calculating a Mode Calculating a Median Determining the Percentage of a Total Aggregating Nullable Columns Computing Averages Without High and Low Values Converting Alphanumeric Strings into Numbers Changing Values in a Running Total Chapter 8 Date Arithmetic Adding and Subtracting Days, Months, and Years Determining the Number of Days Between Two Dates Determining the Number of Business Days Between Two Dates Determining the Number of Months or Years Between Two Dates Determining the Number of Seconds, Minutes, or Hours Between Two Dates Counting the Occurrences of Weekdays in a Year Determining the Date Difference Between the Current Record and the Next Record Chapter 9 Date Manipulation Determining if a Year Is a Leap Year Determining the Number of Days in a Year Extracting Units of Time from a Date Determining the First and Last Day of a Month Determining All Dates for a Particular Weekday Throughout a Year Determining the Date of the First and Last Occurrence of a Specific Weekday in a Month Creating a Calendar Listing Quarter Start and End Dates for the Year Determining Quarter Start and End Dates for a Given Quarter Filling in Missing Dates Searching on Specific Units of Time Comparing Records Using Specific Parts of a Date Identifying Overlapping Date Ranges Chapter 10 Working with Ranges Locating a Range of Consecutive Values Finding Differences Between Rows in the Same Group or Partition Locating the Beginning and End of a Range of Consecutive Values Filling in Missing Values in a Range of Values Generating Consecutive Numeric Values Chapter 11 Advanced Searching Paginating Through a Result Set Skipping n Rows from a Table Incorporating OR Logic when Using Outer Joins Determining Which Rows Are Reciprocals Selecting the Top n Records Finding Records with the Highest and Lowest Values Investigating Future Rows Shifting Row Values Ranking Results Suppressing Duplicates Finding Knight Values Generating Simple Forecasts Chapter 12 Reporting and Warehousing Pivoting a Result Set into One Row Pivoting a Result Set into Multiple Rows Reverse Pivoting a Result Set Reverse Pivoting a Result Set into One Column Suppressing Repeating Values from a Result Set Pivoting a Result Set to Facilitate Inter-Row Calculations Creating Buckets of Data, of a Fixed Size Creating a Predefined Number of Buckets Creating Horizontal Histograms Creating Vertical Histograms Returning Non-GROUP BY Columns Calculating Simple Subtotals Calculating Subtotals for All Possible Expression Combinations Identifying Rows That Are Not Subtotals Using Case Expressions to Flag Rows Creating a Sparse Matrix Grouping Rows by Units of Time Performing Aggregations over Different Groups/Partitions Simultaneously Performing Aggregations over a Moving Range of Values Pivoting a Result Set with Subtotals Chapter 13 Hierarchical Queries Expressing a Parent-Child Relationship Expressing a Child-Parent-Grandparent Relationship Creating a Hierarchical View of a Table Finding All Child Rows for a Given Parent Row Determining Which Rows Are Leaf, Branch, or Root Nodes Chapter 14 Odds 'n' Ends Creating Cross-Tab Reports Using SQL Server's PIVOT Operator Unpivoting a Cross-Tab Report Using SQL Server's UNPIVOT Operator Transposing a Result Set Using Oracle's MODEL Clause Extracting Elements of a String from Unfixed Locations Finding the Number of Days in a Year (an Alternate Solution for Oracle) Searching for Mixed Alphanumeric Strings Converting Whole Numbers to Binary Using Oracle Pivoting a Ranked Result Set Adding a Column Header into a Double Pivoted Result Set Converting a Scalar Subquery to a Composite Subquery in Oracle Parsing Serialized Data into Rows Calculating Percent Relative to Total Creating CSV Output from Oracle Finding Text Not Matching a Pattern (Oracle) Transforming Data with an Inline View Testing for Existence of a Value Within a Group Appendix A Window Function Refresher Grouping Windowing Appendix B Rozenshtein Revisited Rozenshtein's Example Tables Answering Questions Involving Negation Answering Questions Involving "at Most" Answering Questions Involving "at Least" Answering Questions Involving "Exactly" Answering Questions Involving "Any" or "All"

gfodor 8y ago

"NoSQL" took the idea of a rejection of syntax (SQL) but also, in practice, burned in with it a rejection of vast swathes of database theory that resulted in the modern RDBMSes. It was a meme that was generally easy to ignore and identify the "bad ideas" within if you understood the history of databases. SQL obviously is just an incidental syntactic detail on top of a fairly sound, generalized model for databases -- relational algebra combined with a clear semantic model for transactions (ACID, etc.) These two things I believe will stand the test of time. Everything else, I think, is ultimately incidental complexity to meet operational goals (like indexing, or caching methods, or sharding, or replication, or denormalization) or conceptually useful models and interfaces (like graphs, or literate APIs, or query langauges, or ORMs, etc) on top of these basic facilities that need to be provided by a database system. The trend of NoSQL databases I think can be summarized as people building systems that tilted towards letting these bits of incidental complexity dictate the design of the system as a whole. The overriding concern seemed to be "scalability and performance" with "easy APIs" a close second and so that resulted in specific data structure and API needs or access patterns dictating everything about how the database system itself worked, and how data was modeled in that system. For example, MongoDB was hearalded as finally letting you access a hierarchical data structure without SQL JOINs, but did so not by just providing a nice abstraction on top of a sound relational schema that allowed you to "re-project" your thinking and access patterns to a document-oriented one when it was the right model contextually, but instead by burning that denormalized, document-oriented data structure into the entire conceptual and technical stack for the whole system! The cost, of course, is self-evident since choosing to reject the relational model also chooses to reject the benefits that were explicitly recognized as a reason for the relational model to be a good choice for database systems: normalized data allows a much more rich set of transformations and projections on that data to be unambiguously expressed and fulfilled by the system. In other words, it's much more future proofed since you now have the flexibility to mix-and-match and analyze it in arbitrary ways, decoupled from your choice of schema. So the effect of choosing MongoDB meant that you had minimal future-proofing if you got your document structure wrong and suddenly needed to re-project your data in a new way. The supported path, of course, was to "just run map reduce on it" -- ie, lets force every application developer to do the job the database is supposed to do :( It's nice to see things are moving forward on this again, hopefully the trend doesn't just reverse now and we have a flood of "SQL access" APIs to poorly grounded database systems.

deleted 8y ago

[deleted]