31–40 of 52 posts
There's no easy answer i don't think. it depends. If you decide it makes sense to switch to an rdbms, you should probably consider postgres rather than MySQL.
Why not MySQL?
TL;DR: you don't have to use one or another, use both! Your use case, running a restaurant is certainly a mixed use case. You'll certainly want to query the latest orders if this is going from the waiter to the kitchen. But yesterday's orders aren't that much important. As others have mentioned, this data is exactly the kind of data you would store in a relational database. Just be sure to not query for the last 10 o…
If you have an index on the date, then selecting the last 10 orders will be very cheap, and there's no reason not to do that in a relational database - it should be near-instantaneous. The trade-off here is that every index you add makes insertion a little bit slower, so you don't normally want to just add an index for every query.
Earlier quoted context omitted.
If you have an index on the date, then selecting the last 10 orders will be very cheap, and there's no reason not to do that in a relational database - it should be near-instantaneous. The trade-off here is that every index you add makes insertion a little bit slower, so you don't normally want to just add an index for every query.
Ok, thank you. Now you got me thinking, what did I do wrong with this Oracle 9 DB several years ago? :/
Of course, for that query, you could create an index on customer_id & timestamp, and then it would be near-instant again!
"Write-heavy" means your application processes thousands of records per minute. Three million orders on a monthly basis doesn't look like too big of a deal. Any database should be able to manage that easily. Redis would have been my last pick to do that. Redis I only use when to keep high-volatile non-essential data in memory, not much else.
Key in picking your database is how your data is being used. Are the most recent data records accessed most often and the rest almost never? MongoDB and an RDBMS will do quite nicely. Do you really expect _extremely_ heavy growth, a NoSQL datastore might be better. Are DBA's in short supply? Use a managed datastore.
As a big fan of NoSQL, I'd say; be cautious when using NoSQL datastores. Any SQL database will do the majority of workloads quite nicely and offer you with plenty of tools to do any type of query you might need to. NoSQL databases do analytical queries usually quite poorly; separating OLTP and OLAP is painful and costly for smaller apps, only for that reason it is best to avoid them in most circumstances.
Above all; use what you can run cheapest. The available skillset should play a role too, if most engineers are familiar with SQL Server, use SQL Server... I generally use MySQL or PostgreSQL on RDS (AWS) given DBA's are hard to find. I use DynamoDB when I have extremely high dataloads. I use MongoDB when developing a common app for NodeJS because NodeJS simply works very well with MongoDB… I consider other NoSQL datastores only when processing many millions of records per day. For all the rest (and majority of use cases); pick an RDBMS.
// In production I've used Oracle DB, MySQL, PostgreSQL, MongoDB, DynamoDB, Cassandra, Google Cloud Datastore, Redis, RedShift and Elasticsearch.
What I would consider though is keeping your Redis up front for accepting/processing the records in real time coming in and then batching them into your RDBMS. This provides you the ability to do reporting and other analytics through the RDBMS, but utilize Redis as a buffer for the writes. It also gives you the ability to massage the data into the specific schema that best fits the usage of the data in the RDBMS, but allowing the most efficient schema coming into Redis from the POS systems. It adds complexity though so that would have to be weighed.
>moving from redis Yes. It is a very poor choice for your needs. >to mysql No. It is a very poor choice in general. >What other databases Postgresql obviously. I'm honestly shocked that people are still considering mysql in 2014.
MySQL is a very good database. Especially if you use MariaDB.
There's no easy answer i don't think. it depends. If you decide it makes sense to switch to an rdbms, you should probably consider postgres rather than MySQL.
Why not MySQL?
no check constraints
views with aggregates are too slow to be used
no expression indexes
triggers don't fire on cascaded actions
no window functions
can't set default values to be the result of a function
no transactional DDL
doesn't have multiple databases, just schemas misnamed as databases
rollbacks are orders of magnitude slower than other RDBMS, and if it is interrupted it can corrupt the database
functions can't use prepare/execute, so no dynamic SQL in functions
subqueries are broken: can't modify and select from the same table
functions can't be called recursively (seriously? is this 1962?)
triggers can't alter the table they are firing against
stored procedures can't be invoked from prepare/execute
"slow query" log has a completely useless resolution of seconds
>moving from redis Yes. It is a very poor choice for your needs. >to mysql No. It is a very poor choice in general. >What other databases Postgresql obviously. I'm honestly shocked that people are still considering mysql in 2014.
Shocked for what? Mysql is a good choice for many cases. Please you should explain why mysql is better than postgres?
Name one. Mysql is horribly broken.