The internals of PostgreSQL
interdb.jp
interdb.jp
From what I can tell, PostgreSQL is sensibly structured. At least, it seems better than MySQL. The hackiest thing I have heard so far about MySQL is that if you run the exact same SQL text more than once, it will fetch the result from cache. If you run a differently worded query, it will skip the cache, even if the query were to bring back the exact same data.
This is different than what I remember about PostgreSQL (correct me if I'm wrong). I remember reading in some book that PostgreSQL just let its rows become memory pages, and if a query resulted in data already in memory, then it got it from memory, otherwise it got it from disk. No need to make sure you didn't insert a space or something into your query the second time.
Suppose you have a query that is doing a group by on a low cardinality field (eg. state) on a hundred million row table. Large amount of data in, small amount out. postgres has to actually look at all the rows to re-run the query. The mysql query cache only has to pull the tiny number of results out of the cache for that specific query.
This is a dangerous feature because there is a super slippery slope as traffic increases... any change to any of the tables invalidates the cache when you need caching most, and hits all queries against those tables at the same time. But if used well in an environment with many copies of the data (replicated or otherwise) where you could manage when updates happened, it can be a lifesaver.
InnoDB also has its own buffer pool that caches rows and is in general terms similar to the postgres buffer cache.
It is fair to say that mysql has a lot more features like this that are "sometimes life-saving hacks that can bite you hard if you don't understand them" and postgres is much more restrained in ensuring the features are a little more general and thought through.
Seems sensible, each has features to support the base design.
Fits with my general feeling of "if you want to optimize for a super specific use case, sometimes mysql can provide some cool tricks for that use case that can be amazing" but if you want a more general solution if you need more flexibility or don't have the knowledge or don't know your future needs, postgres shines.
When they added this optimization, they issued a warning about it, because identical queries without "order by" clauses will return the same results, but without a guarantee of how they're sorted.
I am sorry, but I rather not have a feature than having a feature that is questionably implemented.
It compiles queries and stores them in a cache. In many cases it can parameterise simple ones so even if the exact text changes it will pull back the matching plan (and it stores all this data so you can analyse later what plans were compiled, what was reused, which ones were similar, which were recompiled later - though hardly anyone deep dives into it because it's a tar pit of info and why bother instead of just focusing on worst cases).
Also when the query pulls data into memory it stays there in the buffer. If the rows change the buffers aren't discarded, they're flushed to disk (along with the log records). So if your query pulls back the exact same data then it's from memory sure but it's not stale or anything.
I don't consider any of these problems so I'm surprised how they could be interpreted as bad in other products. I don't understand enough about them to say but it's fun to discus.
The mysql query cache actually caches the results of the query, see https://dev.mysql.com/doc/refman/8.0/en/query-cache.html so it doesn't even need to look at the rows at all.
SQL Server may have a similar thing now, I'm not up to date on it, but wanted to call out the distinction based on how you described it.
No question, SQL Server has a lot of awesome qualities for the right use case and has come a long long way since the Sybase days.
As you start running into scaling issues and need to search for solutions that are faster to implement you'll reach for Enterprise and then for more cores and before you know it you're stuck with a $16,000 / core scaling cost that you can't avoid because you can't get your data out to offload things easily.
Dealing with SQL Server on a customer facing system is the stuff of nightmares.
Enterprise per core: $14,256
With SQL Server your options are variations of polling the database. We even tried posing the question of utilizing Elastic Search to developers at the PASS Summit recently and were widely met with groans of "Yea...that's a pain to do with SQL Server. You're basically out of luck."
For an internal standalone system it's great. For moving data into it do do analytics, great. For anything close to a real time web facing system with a growth pattern...absolute nightmare.
Not true https://msdn.microsoft.com/en-us/library/t9x04ed2(v=vs.110)....
What do you mean by "get data out"? I've not had any issues getting rows out of SQL Server to be indexed externally.
It works well at cheating benchmarks, and masking un-optimized queries. It is not something that I would recommend.
The only thing, I am missing are incremental materialized views.
PipelineDB will be a standard PostgreSQL extension[0] by release 1.0.0. Currently we're about to release 0.9.7, and each release after that will incrementally factor out PipelineDB into a completely independent PostgreSQL extension.
That's the plan, anyways :) With https://www.stride.io being rolled out under heavy demand, we've got a lot on our plate!
Just checked - stride.io , is it pipelineDB admin / user front end as a service ?
I am assuming there is a lot more going on behind the scenes ? Can you talk about the architecture.
These processing nodes can be queried and joined on (with SQL) at any time to easily power realtime dashboards and other analytical applications. Check out the original announcement [0] and the Stride docs [1] for more detail.
And to answer your question, no, Stride is not simply a frontend for PipelineDB. While it does use PipelineDB and PipelineDB Cluster extensively, it also uses other systems to provide unique capabilities, and all of this is ultimately synergized behind a dead-simple HTTP API.
We've found that a hosted database isn't actually all that interesting for users nowadays, and near impossible to build a business around because they've essentially become a cheap commodity. So we aim to deliver maximum value to users by nailing one use case (realtime analytics) with a highly focused API that eliminates most of the complexity and decision points you'd encounter when trying to do the same with a generic database.
[0] https://www.pipelinedb.com/blog/announcing-stride-a-realtime...
There are workarounds for emulating it with triggers, though. I have found the following discussion to have helped in a problem I had:
https://hashrocket.com/blog/posts/materialized-view-strategi...
[1] Wiki - https://en.wikibooks.org/wiki/PostgreSQL/Architecture
Here's the Oracle article about materialized view refresh, there are further details about the different schemes for incremental refresh within. Briefly, it's a method for refreshing by looking at the deltas since last refresh.
https://docs.oracle.com/database/121/DWHSG/refresh.htm#DWHSG...
http://web.archive.org/web/20170126103158/http://www.interdb...
Also a reminder to everybody to give them a few $CURRENCY if you can. Archive.org offers a valuable service and are very underfunded.
Guessing they're experiencing too much traffic or something, so this is their way to throttle it. ;)
I always see these things posted while they're still being written and then I never go back to see them when they're completed. And nobody posts when it's completed.
I'd prefer the posting not be done until it's finished, or, that there be an "email me when it's finished" button.
A tip on how to use IFTTT quickly and easily for this particular task would probably be a more constructive response. I for one don't know how to use IFTTT to reliably detect changes in any webpage, I thougt it was mostly for different api services and the like, as those are the only use-cases I've heard about.
https://martinfowler.com/bliki/EvolvingPublication.html
So the RSS feed is not per article, but per article part.