PostgreSQL is the first relational database which recognized the need of non-atomic data types to support developers of applications from science to Web. Jsonb in Postgres is the attractive feature for modern application developers, who want to work with json documents without sacrificing a strong consistency and ability to use all the power of proven relational technology. Finally, SQL world has recognized the NoSQL and released the new SQL-2016 standard, which includes specification of SQL/JSON data model and path language, as well as SQL commands for storing, publishing and querying JSON data. I will present the implementation of jsonpath, the …

This talk will cover a case study in migration from Oracle to PostgreSQL. Attention will be paid to the multitude of business and technical challenges to migrating the main product database from an "established and accepted" system to one that at the time was not nearly as accepted (nor was open source in general) as it is today, particularly in the financial market.

Applications nowadays are written with the help of many programming languages. When the backend should implement user oriented workflows, it may rely on a RDBMS component to take care of the system’s integrity. PostgreSQL is the world’s most advanced open source relational database, and is very good at taking care of your system’s integrity. PostgreSQL also comes with a ton of data processing power, and in many cases a simple enough SQL statement may replace hundreds of lines of code written in Python, Java, PHP, Ruby, Javascript, you name it. In this talk, we learn advanced SQL techniques and how …

The tissues in our bodies are complex systems composed of diverse celltypes that often interact in highly structured repeating anatomical units. Some of the major challenges in research today include understanding disease progression and classifying diseases based on molecular markers. Obtaining quantitative information about gene-expression changes within cells in their native environment is laborious and challenging. How would you quantify the entire transcriptomes of cells while accounting for their spatial coordinates? However, methods that combine traditional histology with transcriptome data are already available or under development—some can capture the full transcriptome in a standard-size tissue section, while others focus on … Spatial transcriptomics is a technology used to spatially resolve RNA-seq data, and thereby all mRNAs, in individual tissue sections. I'll demonstrate how we use POSTGIS to store and retrieve rna-seq data for our in-house analysis and pipelines.

PostgreSQL is really powerful when dealing with structured data, but sometimes you can’t avoid having to work with data that is unstructured. That is the case at Geoblink, where we have to deal with data coming from several different providers, forcing us to combine the strengths of relational databases and NoSQL. Fortunately for us, a great deal of work has gone into the last versions to develop the JSONB format, which allows proper indexing and fast result retrieval. However, JSONBs in PostgreSQL are notoriously hard to operate with, for example, to merge two jsons and add up values for properties … In this talk we will share how we developed two PostgreSQL extensions in C to work with JSONBs and some lessons we learnt on the way. These extensions have allowed us to avoid the need of using NoSQL databases to persist unstructured data, storing it in PostgreSQL with the rest of our application data. We are now able to execute complex operations at retrieval time directly in PostgreSQL, decoupling this process from our back end services. This have resulted not only in a simpler infrastructure but also a boost in performance. We will introduce the techniques to work with JSON from native code. In particular we will explain how to use JsonbIterators and JsonbValues for performant access, and how to build algorithms on top to execute standard JSON operations like merging fields or schema validation.

Abstract: A look back at the last ten years, how Postgres has developed, technology-wise and adoption-wise. Some of Postgres's early design decisions have withstood the test of time and helped Postgres weather several generations of fashionable trends. The steady design directions of the core developers and focus on quality have given Postgres the reputation it has today, as the defacto enterprise open-source database. Many historical aspects of the original Postgres project give hints to its future, and we will discuss some roadmap goals that will take Postgres forward into the next decade and beyond. Speaker: Mehboob Alam is a community … Mehboob recently joined Nutanix as a Sr. Solutions Architect to build the next-generation Postgres platform for data-center and private cloud customers.1. 1. list text here

I would like to share the experience of implementing Patroni in Gitlab. Basically, I would like to talk about the reasons why we decided to change the previous solution, how we planned, the cooperation with Ongres to execute the plan, testing, and execution. Mentioning the advantages and pitfalls that we found during the whole project. The whole scope of the project was on the infrastructure online for gitlab.com, and our scenario is based on version 9 of Postgresql and we have our cluster hosted in a cloud solution. Also, we would like to talk about our next steps using Patroni …

Despite spatial data is really popular and it is one of the most actively developed data formats there is no good tool for working with it. When we were looking for the easy way to manage spatial data with various SRID we found out that most of tools are old, heavy or complicated for usage. The list of our needs was not huge. It included: - Show objects on map - Work with various projections (SRIDs) - Render 3D-objects - Export data in different notations - Export pictures as files - Get and show GIS data from queries - Migrate … Because of that, we decided to create a new open-source extension for DBeaver that would cover all these topics. We analyzed various existing libraries and built our plugin. Now, we are ready to share our results, development experience, most interesting challenges and solving ways. If you also need to operate with GIS data regularly, keep it in different databases, import spatial data to PostgreSQL and you prefer to work in GUI, you are more than welcome to listen and discuss this talk.

Kubernetes has enabled a new model to deploy applications abstracting away the infrastructure, enabling multi-cloud and on-premise seamless deployments. However, **containers are not lightweight virtual machines**, and the packing of software paradigms that work on VMs are not valid on containers/Kubernetes. This talk will cover, among other topics: how to generate PostgreSQL minimal containers; how to package and interact with sidecar containers; integration and extension of Kubernetes. Join this journey describing **how to prepare PostgreSQL to run natively on Kubernetes**, and how to build a full PostgreSQL stack (PostgreSQL plus all the components it requires for a full-featured system with …

PostgreSQL implements MVCC in a slightly different way when compared to databases like Oracle, MySQL and others. In PostgreSQL, you see a different method for managing UNDO which requires a cleanup concept called vacuum. Over a period of time, PostgreSQL implemented autovacuum that has been enhanced with every release. It is now important for PostgreSQL admins/DevOPS to understand the internals of autovacuum and the MVCC implementation. We shall discuss these topics in detail during this talk.

1. MVCC in PostgreSQL.
2. Transaction ID Wraparound
3. Vacuum and Autovacuum Internals.
4. Parameters you should know to understand how autovacuum works.
5. Problems you may see in a busy OLTP database with untuned autovacuum settings.
6. Methods to tune the autovacuum parameters in PostgreSQL.