81–90 of 91 posts
"Do not perform table alter commands. Table alter commands introduce locks and downtimes. Instead, use live migrations." You know what avoids alter? Going schemaless.
Consider, that in this case, the json text field is schemaless. How is that different from other schemaless databases?
If anything, you should state that if you wanna avoid alters, use CQRS, not just shcemaless
that mapping from routes to sites seems also great to be stored in a Memcached machine, as it is probably set once and then stays the same for months if not years. Memcached and MySQL is always a great combo
As this title demonstrates, NoSQL is no longer a useful term. (It was useful for a period of time when SQL-based RDBMs systems were quite predominant, so it could be used as a gross differentiator). Now the best way to think about it is that there are database platforms with varying features, one of which is support for SQL. When you evaluate which platform to use, you should have a list of business-derived criteria,…
This is right and wrong in the same time. Not sure which more. Storing JSON as TEXT is great, but you really only query the data based on the mySQL index. What if you need to query based on the site_data? This is really not NoSQL this is just a key value store with a JSON object that is not even native type to the database, you will still need to parse back/forth. What about updating the data? You need to get the obj…
"What about updating the data? You need to get the object and then you need to parse it, change it and send it back marshalled to TEXT that MySQL can understand."
I implemented something similar, with db columns extracted from the json data to be used in querying. I have a "schema_version" column and property in the API, and a migration class which checks the versions of the stored objects, and if the current schema version is greater then runs the needed migrations (a "migration" in this case is extracting some new columns from the data).
Having a schema version probably handles your other case also.
Yes, running the migration can of course take some time depending on your data, but if you update one row at a time then you don't even have to have service interruptions (the old schema version rows just don't eg. get found in queries using the new fields until updated).
I did an extensive survey of NoSQL databases recently and for my particular requirements Postgres ended up being the best choice. I examined MySQL too in this particular survey. Can't remember why but its JSON handling didn't meet my needs. Every time I consider alternative databases -and I've done so several times in the past ten years - I do a thorough examination and the answer always seems to come back to Postgre…
I've experienced this time and time again. Postgres is an outstanding database for most use cases. I also love all the enhancements it has been getting over the last twenty four months. If you haven't considered Postgres before, definitely check the project out.
But the general consensus from blogs I've read say that NoSQL should really be used more as a quick cache. Redius seems to replace memcache more than it acts as a real data-store. People who try to go the full NoSQL route tend to have to use several systems just to get what they had with a regular relational db.
What bothers me more than anything is that it seems like PostgreSQL is it. MySQL/MariaDB/Percona show the fragmented former MySQL landscape.
I've heard some people talk about Firebird, which I really need to check out. But is that really it? In the OSS world, are we down to PostgreSQL and Firebird (will MySQL really continue to grow under Oracle's control? Or is it on a dead end path?)
Earlier quoted context omitted.
Author seems to be confused on how databases operate (especially since they really should just be using a KV store like Redis), because you're exactly right, the query would be the same as: select sites.* from sites join routes using (site_id) where route_id = ? Assuming sites.site_id and routes.route_id are both primary keys, this query is going to perform identically using either syntax. It'll read 1 row from each…
You are right that this join query should be equivalent. However, with InnoDB there will not be much benefit from your suggested index. In InnoDB primary indexes are clustered. Hence, any column may be retrieved from the B-tree of the primary index.
Earlier quoted context omitted.
Author seems to be confused on how databases operate (especially since they really should just be using a KV store like Redis), because you're exactly right, the query would be the same as: select sites.* from sites join routes using (site_id) where route_id = ? Assuming sites.site_id and routes.route_id are both primary keys, this query is going to perform identically using either syntax. It'll read 1 row from each…
You are right that this join query should be equivalent. However, with InnoDB there will not be much benefit from your suggested index. In InnoDB primary indexes are clustered. Hence, any column may be retrieved from the B-tree of the primary index.
Earlier quoted context omitted.
This comparison is a dangerous one without some kind of qualification. This performance difference is not inherent to the design of these systems like you seem to imply. What use case and hardware are your hypothetical relational/non-relational database under where you get your 600 times speed-ups? I can run a benchmark of a few hundred fast machines with sharded sqlite databases doing key-value and operating in RAM…
I brought the performance argument (among others) because the article does. When I read 100k I thought it was rps, not rpm. It would have been an achievement. The achievable performance of dbs is linked to their horizontal scalability, and SQL does not scale horizontally because of the relational model. Subsets of SQL can be made to scale horizontally, like in Cassandra, without locks, transactions, joins, aggregatio…
Whether a DBMS allows relations or not or uses SQL or not is an independent property of whether it has built in dynamic schema/graph/replication/failover/query distribution/sharding/rebalancing/distributed consistency solutions. And whether those solutions are built-in has little to do with whether solving them is possible.
If it's just that we are disappointed that the traditional database systems (which happen to be relational and SQL because these are elements of that tradition) feel like they don't need to solve these problems, then I absolutely agree.
The performance benefits of flattening your data model, avoiding indexing things you don't query against, sharding, using a distributed map reduce, etc. can all be had with SQL and an RDBMS, so speed is a poor argument for straying from the tradition, IMO.
If it's really about wanting a new generation of comprehensive platforms for solving distributed data management (all those features above) that's a great argument, but somehow it's always about speed. Of course distributing asynchronous writes across a cluster of cheap cloud VMs starved for disk IO is faster than single-point synchronous writes on one of those VMs, but is the operations overhead of orchestrating and monitoring that cluster really cheaper than provisioning the hardware it'd take to do the same throughput with a simpler traditional system? Not as often as I'd be had to believe.
Quick question - I'm confused about the comment "The nested query syntax ensures that we are doing only one round-trip to the database to run both SQL queries". Why is the nested syntax better than a regular join in this case? Seems like a join would allow the query planner to decide how to carry out the query.
We have tried a few different alternatives, including the join, and found that this sub-query option actually works better.
A sub query forces the database to execute the first query on routes using the index, then use the result for the second query on sites. It leave nothing for the interpretation of the optimizer which may decide to so some silly thing like full scan on the sites table before performing the join.
Quick question - I'm confused about the comment "The nested query syntax ensures that we are doing only one round-trip to the database to run both SQL queries". Why is the nested syntax better than a regular join in this case? Seems like a join would allow the query planner to decide how to carry out the query.
Your question is valid, assuming you trust the query planner to make the right decision. However, the query planner in most databases is not perfect and can miss. We have tried a few different alternatives, including the join, and found that this sub-query option actually works better. A sub query forces the database to execute the first query on routes using the index, then use the result for the second query on sit…
I use postgres these days and my system characteristics are such that I don't have to worry about query performance so much. Though I have found that I've needed similar tactics sometimes with postgres too.