Show filters Hide filters

Refine your search

Publication Year
Publisher
Person found in the video
1-36 out of 105 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
54:51 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2012

Index support for regular expression search

Regular expressions (regex) are powerful tool for text processing. When dealing with large string collections it's important to search fast on that collections (i.e. search using index). Indexing for regex search is a quite hard task. This talk presents novel technique (and WIP patch for PostgreSQL implementing it) for regex search using trigram indexes. Proposed technique provides more comprehensive trigram extraction than analogues, i.e. higher performance. There are two existed approaches for index-based regex search. The FREE indexing engine is based on extractions continued text fractions from regex and perform substring search. Google Code Search approach present more sophisticated recursive analysis of regex with extraction of various regex attributes. This talk presents novel technique of regex analysis which is based on automata transformation rather than original regex analysis. Superiority of proposed technique will be proved by examples and tests. The talk would be organized as following: Introduction. Regular expressions Finite automata pg trgm contrib module Existing techniques for index-based regular expression search FREE indexing engine Google Code Search Proposed technique Description Examples Comparison with analogues Limitations Performance results.
  • Published: 2012
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
46:02 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2012

Monitoring Ozone Levels with Postgresql

Monitoring Ozone Levels with Postgresql Database Streaming Replication and Monitoring Postgres is used to manage data from the Ozone Monitoring Instrument aboard NASA's Aura spacecraft. The database implementation must handle large volumes of complex data transmitted continually from the satellite and generated by processing-intensive analyses performed by a team of atmospheric scientists. This talk will describe the architecture and some of the challenges faced. Focus will be given to our replication efforts, software developed for monitoring, and ongoing work to create a decentralized network of services commnicating through a RESTful interface. NASA and its international partners operate several Earth observing satellites that closely follow one after another along the same orbital track. This coordinated group of satellites, is called the Afternoon Constellation, or "A-Train", for short. Four satellites currently fly in the A-Train: Aqua, CloudSat, CALIPSO, and Aura. Each satellite has one or more observational instruments that are used together in the construction of high-definition three-dimensional images of the Earth's atmosphere and to monitor changes over time. Aura's instruments include the Ozone Monitoring Instrument (OMI). Data management and processing services for data harvested by OMI are provided by the OMI Science Support Team headquartered at Goddard Space Flight Center. Raw OMI data is received and initially processed at a ground station in Finland, then ingested into the system, where it is analyzed by scientists who submit processing jobs. Earth Science Data Types (ESDTs) are the products of these jobs, and one of the principal types of data managed in the database. Complex and abstract, ESDTs represent the interface between the raw science data and the data management system, and more than 900 are currently defined. Our current database implementation includes 10 clusters, each running Postgres 9.0.4, and divided into three production levels: development, testing, and operations. The central operations cluster handles on average about 200 commit statements per second, contains tables as large as 160 million rows, and is configured for streaming replication. New data is continually being added to the system, and the total quantitiy is increasing at a rate of about 60% per year. This influx of data, in addition to scientific analyses, can cause the load on the database to vary suddenly, and monitoring software has been developed to provide early warning of potential problems. The latest implementation of our software architecture uses decentralized services communicating through a RESTful interface. Databases are bundled together with their software component, and schema changes are managed using patch files. A utility has been created to apply the patches, and ensure schema consistency as the databases are amended. Perl's Rose-DB is used as an object-relational mapper, and database queries, via HTTP requests, are supported by encoding the query information into JSON. The new platform uses a different data model, making it necessary to sync between the two representations, and causing some difficulty with data duplication.
  • Published: 2012
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
1:03:31 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2012

PostgreSQL on Amazon

EC2 with somewhat reduced tears Amazon Web Services (AWS) has become a very popular platform for deploying PostgreSQL-backed applications. But it's not a standard hosting platform. We'll talk about how to get PostgreSQL to run efficiently and safely on AWS. Among the topics covered will be: -- Selecting an EC2 instance size, and configuring it for PostgreSQL. -- Dealing with ephemeral instance storage: What is it good for? How much do you need? -- Elastic Block Store: How much do you need? How do you configure it for best performance? -- AWS characteristics and quirks. -- Why replication is not optional on AWS. -- Backups and disaster recovery.
  • Published: 2012
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
1:00:47 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2012

On snakes and elephants

Using Python with and in PostgreSQL Python is one of the most popular application programming languages and there's a plethora of PostgreSQL libraries and utilities for Python. This talk will try to give an overview of the contemporary Python-PostgreSQL landscape in a way that's useful both for Python programmers starting on a PostgreSQL project and DBAs dealing with what those programmers wrote. We'll try cover a slightly opinionated selection of libraries, frameworks and technologies and give some recommendations. The richeness of the environment is sometimes confusing. Python people starting with PostgreSQL often don't know which driver or ORM library should they be using. Sometimes they're not aware of all the things PostgreSQL can offer to a Python programmer and the tools available. On the other hand, DBAs sometimes need to debug Python programs (mis)using their database and PostgreSQL-savvy people join or consult on projects written in Python and need to have at least a basic understanding of how Python works, particularily on the database connection front. We'll try to make both of these groups a bit more comfortable when dealing with the other. The talk will cover available drivers, focusing especially on psycopg2 and some of its lesser-known features and ORM libraries, focusing mainly on SQLAlchemy. We'll also discuss PL/PythonU, the possibilities it opens, along with some best practices and caveats.
  • Published: 2012
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
1:10:25 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2012

Temporal Data Management in PostgreSQL: Past, Present, and Future

Range Types and Temporal: Past, Present, and Future Range Types didn't exist before, why do we need them now? How do they work? Why is "Temporal" important if we already have timestamps? How do we apply these concepts before deploying PostgreSQL 9.2? What's left to be done, and what solutions are in the works? I'll be asking the audience these questions, so -- Err... I mean: I will be answering these questions during the talk. Extensions, changes to core postgresql, and future ideas will be described in the context of solving a simple use case from 2006. These ideas build up to the larger point that powerful types are important, and database systems should do more to support them.
  • Published: 2012
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
58:14 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2012

Performance and Scalability Enhancements in PostgreSQL 9.2

Bigger servers, bigger problems The upcoming PostgreSQL 9.2 release features a large number of performance enhancements by many different authors, including heavyweight lock manager improvements, reduced lock hold times in key hot spots, better group commit, index-only scans, better write-ahead log parallelism, sorting improvements, and a userspace AVC for sepgsql. In this talk I'll give an overview of what was changed, how it helped, lessons learned, and the challenges that remain.
  • Published: 2012
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
51:29 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2012

Making your own maps

An introduction in using free Geospatial data PostGIS is an extension to PostgreSQL that turns PostgreSQL into a superb spatial database. Storing spatial data in PostgreSQL is a great way too use up the space on your SSD's however using the data to make maps is much more fun. This talk is aimed at people with limited GIS experience and will talk about how to use OpenStreetMap data for map making. We will tell you how you can get free geo-spatial data from OpenStreetMap and how it can be loaded into a PostGIS database. Common methods of using and accessing your data will be discussed including: Open Source desktop GIS software Generating custom map tiles for use on your website Making pretty paper maps. This talk will introduce common tools and techniques used to with PostGIS when working with OpenStreetMap data. This is a user focused talk suitable for people who have next to no GIS background.
  • Published: 2012
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
54:18 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2012

Which is best? SQL Server vs Postgres

Falling in love with the free spirit of Postgres Using the StackOverFlow datasets, we'll ditch all the drama of a Microsoft stack and convert from SQL Server to Postgres on Windows. Once we do that, we'll migrate our entire DB and Web App from Microsoft to Linux using Postgres and Mono with as few code changes as possible. Having the StackOverFlow dataset loaded into SQL Server and a mock StackOverFlow app in ASP.NET MVC3, we are going to show various ways to ETL into Postgres from SQL Server on Windows. Once that is done, we'll go over some basics of going from Postgres on Windows to Postgres on Linux as we attempt to migrate our app. Once we get our back-end moved, we'll show just how easily you can wire up ASP.NET MVC3 to Postgres and then move our entire stack to Linux using Nginx and Mono. Since I am a SQL Server DBA, I will also be adding lots of opinion on where Postgres really shines compared to SQL Server and where it doesn't. This session will be informative, entertaining and incredibly nerdy.
  • Published: 2012
  • 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
46:19 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2013

Using logging hooks for real-time log analysis

If you want to know right now what your server has been doing When problems arise, every DBA wants to know what the database has been up to. If your best idea so far has been to parse the log files or load the CSV logs into another database, you might want to learn about a better way. With logging hooks, you can attach data sinks directly to the PostgreSQL server log stream and analyze logging events as they happen. Many useful applications arise this way: put log data into another PostgreSQL database for ad hoc querying store log data in Hadoop for analytics throw log data into a graphing system generate alerts directly from logging events I'll show you how to put these things together and use them in practice.
  • Published: 2013
  • 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
58:06 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2013

On the road to in-core logical replication!? Part 1: Architecture of Changeset Extraction

Logical Replication in the context of postgres to this date consists out of several independent out-of-core solutions. While some of these solutions are great, the existance of many of those also causes problems like code duplication, lack of trust, features, reliability and peformance. As part of a proposal to include one logical replication solution into core postgresql we submitted the changeset generation/extraction part as a core infrastructure to postgres. In a way its usable by all the existing replication solutions and for lots of other usecases. This talk is about: the architecture of the committed/proposed changeset generation mechanism (2/3) An overview over further proposed patches (9.4+) to get a whole logical replication into core postgres (1/3)
  • Published: 2013
  • 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
40:03 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2013

Visualizing Postgres in realtime

Postgres comes with several introspection tools out of the box. Some are easier to understand than others, but they are all useful. Recent improvements in 9.2's pgstatstatements make it even easier gain insights into the performance of your application. This talk will explore these built in tools, and what it takes to combine them to provide real-time visualizations of your database. Other topics will incldue - What metrics are the most valuable and how to use them - A deep dive into example application for realtime postgres visibility - Storage of postgres statistics - Tools for time series visualization - Collecting metrics at scale - What works and, just as importantly, what does not work
  • Published: 2013
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
1:00:36 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2013

Hacking PostgreSQL

We'll cover how to write your first patch to PG, submit it for review, and profit! Ever wished PostgreSQL had a particular capability or feature? Know a bit of C? This talk will walk you through writing a patch for PG, what needs to be modified to add an option to an existing command (grammar, execution, etc) and the major components of PG (parser, commands, memory management, etc). We'll also cover the PG style guidelines, a crash-course on using git, how to submit your patch, and the review process.
  • Published: 2013
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
55:23 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2013

Autoscaling PostgreSQL: a Case Study

Managing Your Thundering Herd Amazon Web Services provides tremendous tools and techniques for scaling services up and down in response to planned or experienced load. However, too many systems are configured to use AWS as an equipment-rental facility, which wastes money and does not take advantage of AWS' unique properties. We'll talk about how to build systems that flex-scale using AWS tools. Among the topics we'll cover are: -- Designing your application and database for sharding and scaling. -- Planning for load spikes. -- Detecting load fluctuations. -- Scripting your scale-up/scale-down functionality. -- Scaling the database vs scaling the application front-end. -- Monitoring and fault-recovery. The demonstrations will be specifically about AWS, but the techniques can also be applied to other cloud environments.
  • Published: 2013
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
57:44 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2013

PostgreSQL Backup Strategies

Rumor has it all these other users and companies do this thing called "backup" on their database. And people keep saying it's a good idea. But what does it actually mean? In this talk we'll go through the different options we have for PostgreSQL backups, how to use them and some good tips and best practices for setting up a backup strategy that actually works when disaster strikes.
  • Published: 2013
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
1:01:19 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2013

Estimating query progress

Theory and practice of query progress indication Your query has been running for 70 hours. Should you kill it now or ignore angry calls for a few more hours and hope it returns the result? A question many a DBA have asked themselves. This talk will try to cover some of the techniques the database system could use in order to make decisions like that easier. We'll describe an approach based on existing research papers and report on the attempt of implementing it in a useful way inside PostgreSQL. There is ample scientific literature about reporting query progress in relational database systems. Some of the papers published even mention implementations in PostgreSQL. The practicalities, however, are often skimmed over. The talk will start by describing a method for calculating a progress indicator for running queries proposed by Surajit Chaudhuri, Vivek Narasayya, and Ravi Ramamurthy in their 2004 SIGMOD paper. We'll try to see how the terms used in the paper translate to modern PostgreSQL and what practical challenges lie before a hopeful implementer. We'll continue with a demonstration of a module that could be grown into a useful progress indicator solution. The topic will also be an excuse for a little excursion through the PostgreSQL executor and its specific behaviour that needs to be accounted for when calculating query progress. We'll try to give the listeners a basic understanding of how the executor works and familiarize them with nomenclature used in that subsystem.
  • Published: 2013
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
51:58 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2013

How Postgres Got Its Groove Back

Why a 25-year old database is the next big thing.
  • Published: 2013
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
54:01 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2013

Implementing High Availability with PostgreSQL

Availability, Durability, Architecture, Replication... explained How to implement PostgreSQL in a demanding project, what are the different technical offerings good for? All you wanted to know about replication and never dared to ask. PostgreSQL includes several High Availability solution, some replication solutions, and some external Open Source projects complement the offering. When to use which project and what for? This talk will present the usual needs you want to address in a medium size project and how to use several replication solutions to implement them.
  • Published: 2013
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
57:35 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2013

Introducing PostgreSQL Enterprise Consortium activities

PostgreSQL Enterprise Consortium (PGEcons) is a non-profit organization aiming for promoting PostgreSQL in production use, especially in mission critical area. It is formed by leading IT companies in Japan last year. Currently PGECons has 36 company members. In the first fiscal year, PGECons performed two major technical activities: PostgreSQL scale up/scale out evaluation (by Working Group 1: WG1) and establishing migration process from commercial DBMSs to PostgreSQL (by Working Group 2: WG2). These objectives were determined based on requests from PostgreSQL users and PGECons members and PGECons believes that sharing the experience is a big benefit for PostgreSQL community. In this talk we present current achievements of WG1 and WG2. PostgreSQL Enterprise Consortium (PGEcons) is a non-profit organization aiming for promoting PostgreSQL in production use, especially in mission critical area. PGECons performed two major technical activities conducted by two working groups (WG1 and WG2). In this talk we present current achievements of WG1 and WG2. The tests conducted by WG1 fall into two categories: scale up test and scale out test. For scale up test the target was a 80 physical core server to check how PostgreSQL 9.2 behaves against concurrent transactions. We will report the detailed results including the fact that PostgreSQL scales up to 80 concurrent users with read-only load. For the scale out tests, we tested three configurations: PostgreSQL's cascading replication with a master, a cascading standby, and four standbys, pgpool-II running in native replication mode with 4 nodes and Postgres-XC with 4 data nodes. Each configuration showed different characteristics. For example, pgpool-II performed well with read-only load, while Postgres-XC was strong in write load. We will present them with detailed data. To promote adoptions of PostgreSQL in enterprise domain, WG2, Operation and Design working group in official, examined several related issues on the start time and determined to focus on DBMS migration. According to the survey of attendees at PGECons opening seminar, users of Oracle, DB2, SQL Server, Sybase and MySQL are in total 2.5 times more than those of PostgreSQL, even in such a PostgreSQL event. Because many of those utilizations are predicted to be enterprise ones, dealing with them is indispensable for our purpose. In the first fiscal year, WG2, participated by 11 software businesses, carefully studied the DBMS migration process, divided it into 14 subtasks, and started to tackle to 11 of them by dedicated sub-teams. Selective topics from these activities as well as the big picture of WG2 will be presented in this talk.
  • Published: 2013
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
58:51 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2013

One step forward true json data type.

Nested hstore with arrays support. We present a prototype of nested hstore data type with arrays support. We consider the new hstore as a step forward true json data type. Recently, PostgreSQL got json data type, which basically is a string storage with validity checking for stored values and some related functions. To be a real data type, it has to have a binary representation, development of which could be a big project if started from scratch. Hstore is a popular data type, we developed years ago to facilitate working with semi-structured data in PostgreSQL. It is mature and widely used data type with indexing support. Our idea is to extend hstore to be nested (value can be also hstore) data type and add support of arrays, so its binary representation can be shared with json. We present a working prototype of a new hstore data type and discuss some design and implementation issues.
  • Published: 2013
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
43:15 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2012

Improving foreign key concurrency

To lock and not to block Row locking is a mechanism that lets Postgres maintain strict consistency in certain database constraints, such as foreign keys. However, Postgres has historically only provided share and exclusive row locking, which I'll show to have significant drawbacks for concurrency. To solve the concurrency problem, two new row lock types are being introduced in release 9.2: SELECT FOR KEY SHARE and SELECT FOR KEY UPDATE. In this talk I'll explain how this new locking came to be, how it works, and how it helps significantly improve concurrency in applications.
  • Published: 2012
  • Publisher: PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross
  • Language: English
1:00:58 PGCon - PostgreSQL Conference for Users and Developers, Andrea Ross English 2013

Billion Tables Project (BTP)

Aka how long a "\dt" takes on a 1B tables database Usually "large" databases are considered as such for the high number of records they hold, reaching billions or even more than that. But what about creating a billion... tables? Sometime ago, this apparently crazy question was found in a database soup. It may not be your day-to-day task, but the task of creating them exposes some topics about PostgreSQL internals, performance and large databases that may be really worth for your day-to-day. Join us for this talk, where we'll be discussing topics such as catalogue structure and storage requirements, table speed creation, differences between PostgreSQL versions and durability vs. table creation speed tradeoffs, among others. And, of course, how long a "\dt" takes on a 1B tables database :) This talk will explore all the steps taken to achieve such a result, raising questions on topics such as: The catalogue structure and its storage requirements. Table creation speed. Durability tradeoffs to achieve the desired goal. Strategy to be able to create the 1B tables. Scripts / programs used. How the database behaves under such a high table count. Differences in table creation speed and other shortcuts between different PostgreSQL versions. How the storage media and database memory affects the table creation speed and the feasibility of the task. If it makes sense to have such a database. It is intended to be a funny, open talk, for a beginner to medium level audience, interested in large databases, performance and PostgreSQL internals.
  • Published: 2013
  • 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
out of 3 pages
Loading...
Feedback

Timings

  335 ms - page object
  259 ms - search
   11 ms - highlighting
    1 ms - highlighting/19075
    2 ms - highlighting/19021
    3 ms - highlighting/19053
    2 ms - highlighting/19078
    2 ms - highlighting/19074
    2 ms - highlighting/19047
    3 ms - highlighting/19084
    3 ms - highlighting/19067
    2 ms - highlighting/19066
    2 ms - highlighting/19042
    3 ms - highlighting/19123
    2 ms - highlighting/19081
    4 ms - highlighting/19077
    3 ms - highlighting/19146
    2 ms - highlighting/19144
    1 ms - highlighting/19064
    2 ms - highlighting/19139
    2 ms - highlighting/19062
    2 ms - highlighting/19071
    2 ms - highlighting/19051
    1 ms - highlighting/19023
    3 ms - highlighting/19085
    2 ms - highlighting/19149
    3 ms - highlighting/19028
    2 ms - highlighting/19024
    3 ms - highlighting/19032
    2 ms - highlighting/19033
    3 ms - highlighting/19135
    2 ms - highlighting/19143
    2 ms - highlighting/19026
    3 ms - highlighting/19029
    2 ms - highlighting/19027
    4 ms - highlighting/19048
    4 ms - highlighting/19050
    2 ms - highlighting/19046
    2 ms - highlighting/19055

Version

AV-Portal 3.8.0 (dec2fe8b0ce2e718d55d6f23ab68f0b2424a1f3f)