Show filters Hide filters

Refine your search

Publication Year
Person found in the video
1-36 out of 59 results
Change view
  • Sort by:
54:10 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2015

Shootout at the PAAS Corral

Shootout at the PAAS Corral head-to-head for PostgreSQL cloud platforms Where should you run your PostgreSQL in the cloud? Join us for a comparison of features, pricing and performance between various cloud options, including most or all of EC2, Amazon RDS, Heroku, OpenShift, Google Compute, and the Rackspace Cloud. To determine which cloud is the fastest, cheapest and best, over the next few months Josh Berkus and others will be running a series of performance benchmarks against several of the many cloud hosting options available for PostgreSQL. This will include most or all of EC2, Amazon RDS, Heroku, OpenShift, Google Compute, and the Rackspace Cloud. The results will be presented to you in this talk, including: Benchmarking methdology Cost comparison for each configuration Feature differences Performance scores
  • Published: 2015
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
50:48 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2015

Transacting with foreign servers

Managing transactions involving multiple foreign servers. PostgreSQL has Foreign Data Wrappers and they are writable too! Upcoming features like partitioning, foreign table inheritance and join-push down for foreign tables pave the path for sharding. One missing piece in the puzzle is the distributed transaction manager required to maintain the atomicity and consistency of transactions involving foreign servers. The presentation talks about the current status of such transactions and discusses the path forward towards distributed transaction manager. Support for writable foreign tables was added in PostgreSQL 9.3. As of now, atomicity and consistency is guaranteed, when a transaction makes changes to at most a single foreign server. It fails to do so when changes are made to multiple foreign servers. In order to achieve atomicity and consistency of transactions involving multiple foreign servers, PostgreSQL needs to take up the role of a distributed transaction manager. The talk covers the current status of distributed transactions. It further explores protocol to drive distributed transactions and infrastructure necessary to overcome various hardware, software and network failures during a distributed transaction. It also covers the use cases like data federation and sharding.
  • Published: 2015
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
50:57 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2015

Monitor more of PostgreSQL

pg statsinfo comes with new features There are many information vanishes as the server operates. pgstatsinfo/pgstatsreporter is a monitoring tool which records such various status and statistics of PostgreSQL server and lets you see them in graphical and interactive way. It is very usable not only for DBAs to check the health of the server daily, but for technical support to find out what happened in the past on the remote site. It is widely adopted among our systems using PostgreSQL and the new pgstatsinfo 3.1 has new features to support them more. The new pg statsinfo 3.1 has the following new features in comparison to 2.5. This talk will introduce these features with demo and dig inside some of them. Collecting plan statistics. Plan statistics is based on an original pgstatstatements-like extension named pgstoreplans, which is a similar tool to 2ndQuadrant's pgstatplans but it still differs in some points to fit to pg stastinfo. Statistics of autovacuum/analyze including cancellation stats. Cancellation stats would be in some situations. Storing server logs into the repositiry database. Stored logs are examined using filtering feature of pgstatsreporter. Storing alerts previously only emitted into server log. Alerts gets more valuable with pgstatsreporter.
  • Published: 2015
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
39:51 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2015

Scalable MVCC solution for Many core machines

In Current MVCC solution of PG, ProcArrayLock is the major bottleneck on many core machine 120+ and can scale up to 30 connections in TPCC test. Done experiment with lock free MVCC solution, and it can Scale up to 120 cores. In Current MVCC solution of PG, ProcArrayLock is the major bottleneck on many core machine 120+ and can scale up to 30 connections in TPCC test. Done experiment with lock free MVCC solution, and it can Scale up to 120 cores. We have taken the CSN based solution proposed in PG community, and implemented a lock free version of the same. By considering the High Memory and other resources in many core machines, locks are avoided in all the performance patch and only in some rare paths locks are used.
  • Published: 2015
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
49:21 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2015

If you can't Beat 'em, Join 'em!

Why, when and how you can integrate documents and key-value pairs into your relational model There is a pitched battle going on between the relational, document-based, key-value and other data models. PostgreSQL is uniquely capable of leveraging many of the strengths of multiple data models with JSON(b), HSTORE, XML, ltree data types, arrays and related functions. This presentation outlines the use-cases, benefits and limitations of document-based, key-value and hierarchical data models. It then presents practical advice and code snippets for incorporating them into PostgreSQL's relational framework. The presentation ends with SQL examples and code snippets for loading, accessing and modifying (where possible) JSON, HSTORE, XML, ltree and array data types. This presentation begins with a very quick review of the rationale, benefits and implications of the relational data model. It then does the same for document-based models and hierarchical models. The balance of the presentation works with three publicly available data sets, world-wide airports, Wikipedia Inbox key-value pairs and Google address JSON objects, showing how they can be be incorporated into a simple relational model. The presentation also includes snippets of code for loading the files and accessing elements. The full SQL, and shell code will be available on the web site.
  • Published: 2015
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
49:46 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2015

pg shard: Shard and Scale Out PostgreSQL

PostgreSQL extension to scale out real-time reads and writes pg shard is an open source sharding extension for PostgreSQL. It shards PostgreSQL tables for horizontal scale, and replicates them for high availability. The extension also seamlessly distributes SQL statements, without requiring any changes to the application layer. pg shard addresses many NoSQL use-cases, and becomes more powerful with the new JSONB data type. Further, the extension leverages the rich analytic capabilities in PostgreSQL, and enables real-time analytics for big data sets. In this talk, we first summarize challenges in distributed systems associated with scaling out databases. We then describe "logical sharding", and discuss how it helps overcome these challenges. Next, we show how pg shard uses hook APIs, such as the planner and executor hooks, to make PostgreSQL a powerful distributed database. We then cover example customer use-cases, and conclude with a futuristic demo: a distributed table with JSONB fields, backed by a dynamically changing row and columnar store. pg shard is an open source sharding extension for PostgreSQL. It shards PostgreSQL tables for horizontal scale, and replicates them for high availability. The extension also seamlessly distributes SQL statements, without requiring any changes to the application layer. pg shard addresses many NoSQL use-cases, and becomes more powerful with the new JSONB data type. Further, the extension leverages the rich analytic capabilities in PostgreSQL, and enables real-time analytics for big data sets. In this talk, we first summarize challenges in distributed systems: dynamically scaling a cluster when new machines are added or old ones fail, and distributed consistency semantics in the face of failures. We then describe "logical sharding", and show how it helps overcome these challenges. We also discuss this idea's application to Postgres. Next, we show how pg shard uses hook APIs, such as the planner and executor hooks, to make PostgreSQL a powerful distributed database. We then cover example customer use-cases, and conclude with a futuristic demo: a distributed table with JSONB fields, backed by a dynamically changing row and columnar store.
  • Published: 2015
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
40:00 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2015

SchemaVersus

1v1 Schemaverse Battles A tournament of 1v1 schemaverse battles, each round taking only about 10 minutes. No prepared scripts allowed! The Schemaverse is a space-based strategy game implemented entirely within a PostgreSQL database. Compete against other players using raw SQL commands to command your fleet. Or, if your PL/pgSQL-foo is strong, wield it to write AI and have your fleet command itself! This year, rather than the classic large space battle, the rounds will be 1v1 and only take 10 minutes each. There will also be NO pre-created scripts allowed. Matches will be broadcast live for all to see and a ladder updated after each round.
  • Published: 2015
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
47:13 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2014

Gaia Variability Studies: case for Postgres-XC.

Mapping a billion stars throughout our Galaxy and beyond Gaia is a cornerstone European Space Agency mission that will create an extraordinarily precise three-dimensional map of more than a billion stars throughout our Galaxy and beyond, mapping their motions, luminosity, temperature and composition. This huge stellar census will provide the data needed to tackle an enormous range of important problems related to the origin, structure and evolutionary history of our Galaxy. I will describe how Variability Studies of light sources observed by Gaia pose Big Data problem that we are trying to solve with the help of Postgres-XC.
  • Published: 2014
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
50:30 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2014

9.4 On The Floor

How 9.4 will shake your world Join us for this first look at new and upcoming features in the 9.4 release. We'll discuss some of the what and why of the new features, and help you start planning your upgrades. The Postgres development should be mostly through feature freeze by the time PGCon rolls around, so this talk should be able to give you a good idea of what will be in the next release of Postgres. Improvements in JSON, replication, updateable views, materialized views, and more. Hope to see you there!
  • Published: 2014
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
49:17 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2014

In-Memory Columnar Store for PostgreSQL

IMCS is In-Memory Columnar Store for PostgreSQL. Vertical data model is more efficient for analytic queries performing operations on entire column. IMCS provides 10-100 times improvement in performance comparing with standard SQL queries because of: data skipping: fetching only data needed for query execution parallel execution: using multiple threads to execute query vector operations: minimizing interpretation overhead and allowing SIMD instructions reduced locking overhead: simple array level locking no disk IO: all data is in memory IMCS is implemented as standard PostgreSQL extension. It provides set of function and operators for manipulations with timeseries. Some of them are analog of standard SQL operators (arithmetic, comparisons, sorting, aggregation...). But there are also complex analytic operators like calculation of ranks, percentiles, cross points and extended set of aggregates for financial application like split-adjusted price, volume-weighted average price, moving average... Columnar store manager stores data tables as sections of columns of data rather than as rows of data.In comparison, most relational DBMSs store data in rows. Such approach allows to load the whole record using one read operation which leads to better performance for OLTP queries. But OLAP queries are mostly performing operations on entire columns, for example calculating sum or average of some column. In this case vertical data representation is more efficient. Columnar store or vertical representation of data allows to achieve better performance in comparison with horizontal representation due to three factors: * Data skipping. Only columns involved in query are accessed. * Vector operations. Applying an operator to set of values minimize interpretation cost. Also SIMD instructions of modern processors accelerate execution of vector operations. Compression of data. For example such simple compression algorithm like RLE allows not only to reduce used space, but also minimize number of performed operations. IMCS is first of all oriented on work with timeseries. Timeseries is sequence of usually small fixed size elements ordered by some timestamp. Operations with timeseries rarely access some particular timeseries element, instead of it them operate either with whole timeseries either with some time interval. Such specific of timeseries operation requires special index for timeseries, which is different from traditional database indexes. Such index should not provide efficient way of locating arbitrary timeseries element. Instead of it this index should be able to efficiently extract range of timeseries elements. Advantages of IMCS approach: Fast execution based on vector operations Parallel execution of query No changes in PostgreSQL core (just standard extension) No MVCC overhead (MURSIW isolation level) No disk IO (in-memory store) Optimized for timeseries (massive operations with time slices)
  • Published: 2014
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
55:23 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2014

HowTo DR planning for the worst

planning for the worst There's a lot more to disaster recovery than making backups. Most of DR, in fact, is planning instead of code: knowing what you need to do when disaster strikes, how to do it, and who does it. Further complicating things, management and admins are fond of preparing for unlikely events while failing to prepare for probable outages at all. There's a lot more to disaster recovery than making backups. Most of DR, in fact, is planning instead of code: knowing what you need to do when disaster strikes, how to do it, and who does it. Further complicating things, management and admins are fond of preparing for unlikely events while failing to prepare for probable outages at all. This talk will outline how to make a disaster recovery plan, and some basic dos and don'ts of DR. Included: The three most common downtime causes Determining acceptable losses (and getting management to agree) Backup vs. Replication Planning for the unexpected Against Improvising (always have a Plan B) Public Cloud DR Other Dos and Don'ts When disaster strikes, it's too late to start planning. Do it now.
  • Published: 2014
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
38:59 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2014

SQLite: Protégé of PostgreSQL

The Keynote for PGCon 2014 by D. Richard Hipp SQLite has become the most widely deployed SQL database engine in the world, with over two billion installations providing SQL database services for cellphones, web-browsers, cameras, and countless other gadgets and applications. But SQLite is not in competition with PostgreSQL. Rather, PostgreSQL and SQLite complement each other, with each engine targeting a different class of problems. SQLite can be thought of as a derivative of PostgreSQL. SQLite was originally written from PostgreSQL 6.5 documentation, and the SQLite developers still use PostgreSQL as a reference platform to verify that SQLite is working correctly.
  • Published: 2014
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
49:02 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2014

Balancing Database Storage Latency And Throughput

by Greg Smith The easiest type of benchmark to run on a database is checking its throughput: the total transactions processed during some period of time. But throughput isn't what application users care about. They want low latency. Latency and throughput have a complex dependency on each other, and you'll need a whole new type of test to balance them well. Recent advances in benchmarking tools, like the rate limit in pgbench 9.4, make it possible to analyze latency in a way that highlights this difficult to see area. The graphics and reports of the pgbench-tools package make it possible to see how tuning changes impact both latency and throughput. That that lets you test the "lore" for how best to tune your PostgreSQL server to find out what really works. Using these new tools, this talk will look at the tricky correlation between throughput and latency and how to measure each usefully in common situations. We'll look at three different storage stacks with very different latency profiles: regular disk, disk with battery-backed write cache, and SSD. We'll then look at exactly how checkpoint spikes play out on each and what you can do about them. You'll never trust a simple transactions per second count again!
  • Published: 2014
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
41:55 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2014

I know greater-than-or-equal-to when I see it!

A tour of the operator class facility A PostgreSQL operator class tells the database server how to sort, match, and index values of a particular data type, and an operator family ties related data types together for the purpose of these operations. They constitute agreements between data type authors and data type-independent code, with most data types defining at least one operator class. This talk will explore the theory behind operator classes and families, including the assumptions data type-independent code is and is not entitled to make based on available operator classes. We will walk through the creation of new operator classes, some practical and others deliberately perverse, and examine some exceptional operator classes already present in PostgreSQL. Writing a query expression such as "val greater than = 1" without knowing the data type of "val" is almost always a mistake. It will work for the vast majority of data types, but it will malfunction in rare cases. We will examine the use of operator classes to adapt such an expression to each data type. This talk caters to writers of code intended to deal with unforeseen data types. Application code such as replication systems and client interfaces are affected, as are backend features such as integrity constraints and join algorithms. As an author of such software, you will leave the talk equipped to audit for mistakes and substitute code that harnesses operator classes and families to process values of arbitrary type. The talk will also help prospective authors of new data types.
  • Published: 2014
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
45:55 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2014

When PostgreSQL Can't, You Can

After using PostgreSQL for a while, you realize that there are missing features that would make it significantly easier to use in large production environments. Thankfully, it's extremely easy to make add-ons to enable some of those features right now without even knowing a single line of C! After using PostgreSQL for a while, you realize that there are missing features that would make it significantly easier to use in large production environments. Thankfully, it's extremely easy to make add-ons to enable some of those features right now. And you don't even have to know a single line of C code! Over the past view years I've been developing tools that have made PostgreSQL administration for our clients much easier. Table partitioning is one of the best methods for providing query performance improvements on large tables in PostgreSQL. While the documentation gives instructions on how this can be set up, it's still a very manual process. For time/serial based partitioning, the maintenance of creating new partitions is huge part of that process. PG Partition Manager is an a extension that aims to provide easy setup & maintenance for the most common partitioning types. It also provides advanced features for taking advantage of constraint exclusion and retention to drop unneeded tables. Another area where we ran into limitations was when one of our clients needed an easy method to keep the database schema checked into version control. The pg extractor tool grew from this and has become a popular method of finely tuning the extraction of database objects from PostgreSQL into individual files. Autonomous transactions is another one of the more popular features in other RDMS systems that is missing from PostgreSQL. Begin able to reliably log a function's run status requires that function failure not roll back the entire transaction and erase entries made to the log table. PG Job Monitor takes advantage the dblink contrib module to give the behavior of an autonomous transaction, providing logging & monitoring for mission critical functions. PostgreSQL's built in replication is all or nothing, but sometimes you just need to replicate specific pieces of your database. There are several 3rd-party systems capable of accomplishing this, but their setup can been daunting to most new users and overkill if you just need to grab a few tables. Mimeo is a logical replication tool that requires minimal permissions and setup and provides several specialized means of replicating from one or many databases to a single destination. This talk will discuss these project and hopefully bring insight on how easy it is to contribute to the PostgreSQL community.
  • Published: 2014
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
50:53 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2014

Static analysis, test coverage, and other 12+ letter words

It is hard to maintain complex C code free of bugs. Numerous tools have been developed to help, and we have thrown many of them at the PostgreSQL source code. We will take a look at tools such as Coverity Clang scan-build AddressSanitizer Valgrind Gcov How do they work? How should their results be interpreted? What have they and have they not delivered for us in practice? How can we integrate them into our workflow going forward? How can we structure our code better to work with tools like these?
  • Published: 2014
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
52:42 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2014

Why UPSERT is weird

Counterintuitive lessons learned from the implementation effort Talk that examines implementation process on the INSERT...ON DUPLICATE KEY LOCK FOR UPDATE feature proposed for PostgreSQL. "UPSERT" can loosely be described as a DBMS feature that allows a DML statement's author to atomically either insert a row, or on the basis of the row already existing, update that existing row instead, while safely giving little to no further thought to concurrency. One of those two outcomes must be guaranteed, regardless of concurrent activity, which is the essential property of UPSERT. Examples include MySQL's INSERT...ON DUPLICATE KEY UPDATE, or the SQL standard's MERGE statement (as implemented in a number of proprietary systems). The absence of this feature from Postgres has been a long-standing complaint from Postgres users, and an effort to implement an extension to the Postgres INSERT statement, that implements something broadly in line with user expectations in this area was undertaken in 2013. This talk considers the experience of working on that implementation, and examines how the proposed INSERT...ON DUPLICATE KEY LOCK FOR UPDATE feature deals with concurrency issues, visibility issues, the interplay of value locking and row locking, and the general avoidance of unprincipled deadlocking. In order to come up with an implementation that satisfied user expectations, while still preserving and respecting long standing abstractions and conceptual precepts, some interesting and counterintuitive choices were made to resolve the fundamental tension in tying value locking (as always required on a limited scale for unique index enforcement) to row locking (to LOCK FOR UPDATE). Finally, the talk examines the strategic significance of UPSERT, and further use-cases enabled by the feature in the area of multi-master replication conflict resolution.
  • Published: 2014
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
53:00 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2014

XML, HSTORE, JSON, JSONB - OH MY!

Use the unstructured data type that's right for you. PostgreSQL 9.4 adds a new unstructured data type, JSONB, a variant of JSON optimized for storing objects. JSONB complements the existing XML and JSON document objects, as well as HSTORE. Which one is right for you? We'll take a tour of the features of each, and their advantages and disadvantages for various use cases, all illustrated with real-world examples. There has been a lot of work on the representation of unstructured data in PostgreSQL, culminating in the addition of the JSONB type in the forthcoming 9.4 release. JSONB complements the existing HSTORE, XML, and JSON types, not to mention arrays. With so many options, which do you use? As usual it depends on your use case. In this presentation, we'll review the unstructured data types in PostgreSQL, and look at their advantages and disadvantages for: Document storage Configuration management A "schemaless database" Object serialization Entity/Attribute/Value models Path queries
  • Published: 2014
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
48:57 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2014

The Next Five Years for PostgreSQL Advocacy

or "How We Can Overtake Every Other Database" According to Jeff Barr, the Chief Evangelist for Amazon Web Services, "Over the past few years, PostgreSQL has become the preferred open source relational database for many enterprise developers and start-ups, powering leading geospatial and mobile applications." Through the hard work from the major contributors of our community and certain market events in the data world, it is indeed true that many new projects, both open and closed source, are defaulting to PostgreSQL as their data storage choice. So what is preventing PostgreSQL from becoming the default database for everyone? We will explore the PostgreSQL Advocacy efforts to date which has brought us to this crossroads, and how through a combination of our ongoing product development and a bit of community marketing prowess, we as a community can make "PostgreSQL" as household technology name over the next five years. According to Jeff Barr, the Chief Evangelist for Amazon Web Services, "Over the past few years, PostgreSQL has become the preferred open source relational database for many enterprise developers and start-ups, powering leading geospatial and mobile applications." Through the hard work from the major contributors of our community and certain market events in the data world, it is indeed true that many new projects, both open and closed source, are defaulting to PostgreSQL as their data storage choice. We know that PostgreSQL is an amazing product, and those who know the realm of technology agree. So what is preventing PostgreSQL from becoming the default database for everyone? In order to solve this problem, we will first explore the PostgreSQL advocacy efforts to date. We will take a look at what features have helped propel PostgreSQL into its "newfound" limelight and what the community has done to create a "buzz" around PostgreSQL to capture more users. We will then arrive at the present day crossroads, and look at different strategies to more effectively market ourselves as a product and a community, including how to: Actively make more people aware of PostgreSQL and what it can offer Make it easier for new people to adopt PostgreSQL Determine the pain points of our current users and figure out how to fix them Measure the success of our advocacy efforts Through these combined efforts on the development and advocacy fronts, we as a community can make PostgreSQL as household technology name over the next five years.
  • Published: 2014
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
54:08 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2015

All the Dirt on VACUUM

The use of Multi-Version Concurrency Control (MVCC) is perhaps one of the most powerful features PostgreSQL has to offer, but it can be a source of confusion for new and experienced users alike. In this talk we will provide an in-depth walkthrough of why Postgres needs to vacuum and what vacuum does. Topics: - MVCC details - HOT overview - Identifying tuples to be vacuumed/frozen - VACUUM and indexes - Vacuuming heap pages
  • Published: 2015
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
45:25 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2015

9.5 Coming to You Live

New features by demo This all-demo, no-slide talk will show off 9.5's new features. With every new Postgres release comes new features and improvements to make your life easier. Come see some of the new 9.5 features in action and learn how this next release will make your life better.
  • Published: 2015
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
24:23 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2015

GSoC2014 - Sharing Code and Experience

This presentation is about my experience in the FOSS world and contributing to PostgreSQL as a Google Summer of Code 2014 student. In this presentation I'll talk about all my involvement with the FOSS world and how it change my life and career in many ways. I'll explain how Google Summer of Code works and the importance of this program to the open-source communities. Some points covered: - who can apply - how to apply - how you can help the PostgreSQL community - principal events
  • Published: 2015
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
53:03 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2015

Modern SQL in PostgreSQL

A lot has changed since SQL:92 SQL has gone out of fashion lately --- partly due to the NoSQL movement, but mostly because SQL is often still used like 20 years ago. As a matter of fact, the SQL standard continued to evolve during the past decades resulting in the current release of 2011. In this session, we will go through the most important additions since the widely known SQL-92, explain how they work and how PostgreSQL extends them. We will cover common table expressions and window functions in detail and have a very short look at the temporal features of SQL:2011 and the related features of PostgreSQL.
  • Published: 2015
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
49:29 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2015

Rethinking JSONB

PostgreSQL 9.4 has introduced JSONB, a structured format for storing JSON, which provides many users with the new opportunity: an effective storing and querying JSON documents inside ACID relational database. While users have notice a great jsonb performance, their feedback also reveals some hidden problems with current jsonb implementation. We want to discuss different approaches to resolve aforementioned problems and present several proof-of-conceps, so we could rethink jsonb for 9.6.
  • Published: 2015
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
50:07 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2015

Row Level Security

In this talk we'll review Row-Level Security (RLS), provide examples and use-cases, discuss the work which has been done on adding Row Level Security to PostgreSQL and the current state of that effort. PostgreSQL has long had a complex and interesting set of permissions available through the GRANT system. There is another system which exists in many other RDBMS's known as row-level security (RLS), where the rows returned is filtered based on a policy implemented on the table.
  • Published: 2015
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
57:02 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2015

Parallel Sequential Scan

Unleashing a heard of elephants Parallel query is close to becoming a reality in PostgreSQL! A year ago, much of the low-level infrastructure needed for parallelism, such as dynamic shared memory and dynamic background workers, had been completed, but no user-visible facilities made use of this infrastructure. Major work on error handling, transaction control, and state sharing has been completed, and further patches, including a patch for parallel sequential scan, are pending. In this talk, we will talk about parallel sequential scan itself, including performance considerations, the work allocation strategy, and the cost model; and we will also discuss the infrastructure that supports parallel sequential scan, including state sharing for GUCs, transaction state, snapshots, and combo CIDs; error handling and transaction management; and the handling of heavyweight locking. Finally, we'll discuss the future of parallelism in PostgreSQL now that the basic infrastructure is (mostly) complete.
  • Published: 2015
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
40:44 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2015

Scalability and Performance Improvements in PostgreSQL 9.5

This paper will main talk about the scalability and performance improvements done in PostgreSQL 9.5 and will discuss about the improvements that can be done to improve the scalability for both Write and Read operations. The paper will focus on pain points of Buffer Management in PostgreSQL and the improvements done in 9.5 to improve the situation along with performance data. It will also describe in brief the performance improvements done in 9.5. It will also discuss the locking bottlenecks due to various locks (lightweight locks and spinlocks) taken during Read operation and what could be done to further scale the Read operation. The other part of the paper focusses on improving the Write-workload in PostgreSQL. In this part we will discuss about the frequency of writes done by backend operations (along with data) due to limitations of current bgwriter algorithm and some ideas to improve the performance by reducing writes done by backend. It will also discuss about the concurrency bottlenecks in write operation and some ideas to mitigate the same.
  • Published: 2015
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
49:12 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2015

Multi-Tenancy in PostgreSQL

This talk is about the need of multi-tenancy in PostgreSQL, and the way to achieve multi-tenancy in PostgreSQL. What is a multi-tenant cluster? Why multi-tenant cluster is needed? PostgreSQL Provides multi-tenancy with the following - Shared Database, Shared Namespace - Separate Databases - Shared Database, Separate Namespace However multi-tenancy means more than this. - Issues with a multi-tenant cluster - What can be done and what can we do to make it easier. This talk will propose a multi tenanted architecture for PostgreSQL, to make it the database of choice in a cloud environment. Multi tenanted architecture is one of the key requirements for any software to be efficiently deployed in the cloud. As more and more databases are made available 'as-a-Service' in cloud offerings, it is necessary to take stock of the features in PostgreSQL to analyse how cloud friendly they are, especially for a multi-tenanted infrastructure. This talk will mainly focus on what functionalities are needed in PostgreSQL to make it truly cloud friendly. PostgreSQL needs to have the functionalities that will make it the database of choice for service providers in the cloud. This can be achieved within the current architecture of PostgreSQL by developing new features that will satisfy these requirements.
  • Published: 2015
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
46:49 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2015

...Lag

Most of the time, a streaming replication slave in the same data center is so close to the master that lag can be measured in milliseconds. However when it's not, that lag can be baffling at best, and catastrophic at worst. We will look at all things lag; strategies of monitoring, configuration options to fit application needs, diagnosing common issues and real cases of 'what went wrong'. If you google from "postgres streaming replication lag" (go ahead, I'll wait...) your result set will include much information on set up and monitoring, but very little on diagnosing and even less on correcting. This talk is an attempt to fill that gap. We will start with the basics of monitoring and trending over time, look at configuration options and 'gotchas' for making your slaves trusted read sources, diagnose hardware and system factors, and finally share the pain of elusive lag patterns that took days, if not weeks to figure out. This talk takes a broad look at system health. Many factors contribute to making a database cluster run perfectly; disk speed, network latency, user query patterns, etc., etc. It can be easy to over look, or take for granted things that may strongly effect how close a slave follows the master. In fall of 2014 iParadigms converted 8 server clusters across two data centers to streaming replication, allowing us to find and document many such issues.
  • Published: 2015
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
45:43 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2014

SELECT * FROM changes; - Part 2

9.4 saw a great deal of development around a feature (now) called changeset extraction. This talk will explain what the feature does, which areas of use we see, what the state of the feature in 9.4 is and which additional features around it we want to see in future releases of postgres. Usecases for the changeset extraction feature are: Replication Solutions Auditing Cache Invalidation Federation ... Changeset extraction is the ability to extract a consistent stream of changes in the order they happened - which is very useful for replication, auditing among other things. But since that's a fairly abstract explanation, how about a short example? -- create a new changestream postgres=# SELECT * FROM create decoding replication slot('slot', 'test decoding'); slotname | xlog position ----------+--------------- slot | 0/477D2398 (1 row) -- perform some DML postgres=# INSERT INTO replication example(data) VALUES('somedata'); INSERT 0 1 -- and now, display all the changes postgres=# SELECT * FROM decoding slot get changes('slot', 'now', 'include-timestamp', 'yes'); location | xid | data ------------+---------+--------------------------------------------------------------------- 0/477D2510 | 1484040 | BEGIN 1484040 0/477D2628 | 1484040 | table "replication example": INSERT: id[int4]:1 data[text]:somedata 0/477D2628 | 1484040 | COMMIT 1484040 (at 2014-01-20 01:18:49.901553+01) (3 rows) All this works with a low overhead and a configurable output format.
  • Published: 2014
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
44:01 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2014

Postgres in Amazon RDS

A compelling option for startups In recent years, Amazon's cloud service is key for deploying and scaling IT infrastructure tiers, and this is not different with Postgres as well. Amazon's relational database service for Postgres RDS - is a new but compelling option for the startups, when the resources are scarce and administrators are hard to find! RDS is a great option for people to achieve in terms of rapid deployment of replicas, ease of failover, and the ability to easily redeploy hosts when failures occur, rather than spending extensive time trying to repair. In the talk, I'm planning to topics related to managing Postgres RDS : Introduction of RDS Benefits of using RDS Challenges with RDS Choosing EC2 vs RDS Security Tuning Scaling Monitoring Backups Cost Generic Recommendations
  • Published: 2014
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
48:47 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2014

PostgreSQL 9.4 and Json

We'll cover the new Json generation functions, as well as jsonb, and when to use json, jsonb or hstore. We'll end by looking into the future to see what other json functionality might be coming, such as json mutation functions. The talk will present a survey of the current and coming state of json features, as well as a speculative look at what which be in the future beyond 9.4 Overview: * the new json generation functions, such as jsonbuildobject and jsonbuildarray and how they can be used to build up arbitrarily complex and non-rectangular json. * json typeof() * jsonb, and its relation to json and nested hstore. * how jsonb is different from json, (e.g. object field ordering, treatment of duplicate field names and whitespace) * performance differences between json and jsonb * what's missing from json/jsonb? What should we work on for 9.5? Mutation functions have been suggested, Are there others?
  • Published: 2014
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
34:34 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2014

PGCon 2014: Lightning talks

Various speakers & subjects. Database Appliance based on PostgreSQL OHAI, My name is Chelnik! BDR - Bi Directional Replication What the XL?
  • Published: 2014
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
40:19 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2014

Scaling a Cloud Based Analytics Engine

Scaling a 100% Cloud Native Analytics Engine Your mission, should you chose to accept it, create a data storage system that can handle 200 Gigs of data per day on cloud servers with heavy analytics. GO! The architecture plan of a real time logging system built to handle 200g/day of data and hand it off from mid-term OLTP storage into and OLAP postgres data warehouse. It was built with heavy reliance on inheritance, dblink, and streaming replication.
  • Published: 2014
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
38:03 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2014

A Tale of Two Oracle Migrations

In this presentation I'll talk about two migrations from Oracle to PostgreSQL - the first back in 2001, which is how I got into PostgreSQL in the first place, and the second in 2013 involving the second largest stock exchange in Japan. One of the hotter PostgreSQL topics recently is the increasing trend towards enterprise-scale migrations from Oracle and other commercial databases to PostgreSQL - not only for cost savings but also for its rich and expanding feature set combined with a solid reputation for reliability. But it's hardly a new trend - that's how I first came across PostgreSQL back in 2001. I'll give a brief and hopefully humourous description of what I did back then (with some pictures of cute animals) and how it led to the Japanese stock market. Which is where I ended up in charge of the database underpinning the back office of Japan's second largest stock exchange, SBI Japannext. Since late last summer this has been powered by PostgreSQL and the changeover from Oracle has gone very smoothly. The main part of this presentation will be about how this migration was planned and implemented, and the foundations it has created for further adoption of PostgreSQL.
  • Published: 2014
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
25:11 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2014

PostgreSQL experience in Russian Parliament

In 2010 Russian State Duma (lower chamber of parliament) renewed its web site. That was the first step in modernization towards open data and e-goverment in further years. PostgreSQL took a central place in data integration between intranet systems and open web services. During recent years many new open data systems were launched powered by PostgreSQL. This talk covers the issues of PostgreSQL integration into State Duma intranet systems, which were mostly running Oracle Database as well as implementation of many open data systems. Data between intranet systems and PostgreSQL databases are mostly synchronized using ora2pg. Full synchronization was used for small databases as well as incremental synchronization for large databases. Open data systems implemented on PostgreSQL including: Law and draft law search systems Open vote system Verbatim system Using PostgreSQL database allows to save license const as well as achieve great performance and features.
  • Published: 2014
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
out of 2 pages
Loading...
Feedback

Timings

  214 ms - page object
  183 ms - search
    9 ms - highlighting
    2 ms - highlighting/19140
    1 ms - highlighting/19117
    2 ms - highlighting/19139
    3 ms - highlighting/19084
    3 ms - highlighting/19090
    4 ms - highlighting/19134
    2 ms - highlighting/19121
    4 ms - highlighting/19138
    5 ms - highlighting/19135
    4 ms - highlighting/19089
    4 ms - highlighting/19078
    4 ms - highlighting/19096
    3 ms - highlighting/19149
    2 ms - highlighting/19146
    2 ms - highlighting/19102
    1 ms - highlighting/19081
    4 ms - highlighting/19141
    2 ms - highlighting/19123
    1 ms - highlighting/19072
    2 ms - highlighting/19079
    2 ms - highlighting/19098
    1 ms - highlighting/19132
    3 ms - highlighting/19074
    1 ms - highlighting/19094
    3 ms - highlighting/19092
    2 ms - highlighting/19136
    2 ms - highlighting/19142
    3 ms - highlighting/19143
    1 ms - highlighting/19087
    4 ms - highlighting/19077
    3 ms - highlighting/19144
    2 ms - highlighting/19071
    3 ms - highlighting/19095
    2 ms - highlighting/19085
    1 ms - highlighting/19075
    2 ms - highlighting/19118

Version

AV-Portal 3.8.0 (dec2fe8b0ce2e718d55d6f23ab68f0b2424a1f3f)