PostgreSQL provides a way to communicate with external data sources. This could be another PostgreSQL instance or any other database. The other database might be a relational database, such as MySQL or Oracle; or any NoSQL database such as MongoDB or Hadoop. To achieve this, PostgreSQL implements ISO Standard call SQL-MED in the form of Foreign Data Wrappers (FDW). This presentation will explain in detail how PostgreSQL FDWs work. It will include a detailed explanation of simple features and will introduce more advanced features that were added in recent versions of PostgreSQL. Examples of these would be to show how aggregate pushdown and join pushdown work in PostgreSQL.
The talk will include working examples of these advanced features and demonstrating their use with different databases. These examples show how data from different database flavors can be used by PostgreSQL, including those from heterogeneous relational databases, and showing NoSQL column store joins.
We all use and love relational databases... until we use them for purposes for which they are not a good fit: queues, caches, catalogs, unstructured data, counters, and many other use cases could be solved with relational databases, but are better solved with other alternatives.
In this talk, we'll review the goals, pros and cons, and good and bad use cases of these alternative paradigms by looking at some modern open source implementations.
By the end of this talk, the audience will have learned the basics of three database paradigms (document, key-value, and columnar store) and will know when it's appropriate to opt for one of these or when to favor relational databases and avoid falling into buzzword temptations.
How fast can you failover your databases? Do you trust it? Do you trust the process enough to let [almost] anyone do it, at any time? We do!
At Square, we manage thousands of MySQL and Redis database clusters. We recently rewrote all of our automation which fails over MySQL databases - making it even faster and more reliable. We brought the time from the user requesting the action, to database writes going to the new target - to generally under 2 seconds, with no real downtime or risk. This rewrite went so well for MySQL, that we decided to further abstract the process and apply the exact same set of tools to our Redis.
This talk describes the prerequisites, process, tooling, and lessons learned in safely cutting over database traffic and abstracting the process to apply to both MySQL and Redis.
Multi-source replication is an awesome way to aggregate data for BI/Analytics,
But how to get all those 42 database instances (Total 8T) into one instance when keeping weeks of binlog is not an option ?
Comparing performance, ease of use, and load on source system for mysqldump and Xtrabackup/TT.
Special cases :
* 5.6 and Partitioned tables.
* Tables in mixed formats (Partitions created over time)
A standard pattern in scaling relational data access is to use replicas for reads, but this compromises read-after-write consistency. Unfortunately, since many use-cases require stronger consistency guarantees, the pattern is of limited utility. At Box much of our relational data access requires strong consistency for the purposes of enforcing enterprise permissions, but we need to scale too! To address this challenge, we have introduced a novel design pattern and a framework implementing it. This framework, currently used at scale to serve production traffic at Box, utilizes real-time replication position analysis to safely direct traffic that requires strong read-after-write consistency to read-only replicas.