This is the opening of the congress, a novelty that we want to introduce. It is uncommon in congresses alike, but we think of networking as a relevant part of PostgreSQL Ibiza. Having two days of knowledge and experience sharing ahead, we believe that this informal welcome event is the best ice breaker and it will ease later conversations with attendees as potential leads while building an easygoing environment for the congress itself.
Therefore, we have organized a welcome cocktail at the Hotel Sol Beach House, which is close to the venue as a matter of meters. This event will be held at the hotel garden, a turf area open to the sea that will be reserved to the attendees of PostgreSQL Ibiza and we are quite sure it will generate a valuable amount of social media posts by the attendees.
Sol Beach House Ibiza
Riding the second wave - open source for relational databases
Building on Top of PostgreSQL
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 …Link
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.Link
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.Link
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.Link
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 hereLink
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 …Link
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.Link
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 …Link
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
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.
This is the mid-congress social event of PostgreSQL Ibiza, it will secure the congress culture while being the optimal when & where to establish new business or community connections in a relaxed atmosphere.
Aguas de Ibiza
Grand Luxe Hotel
Traditionally PostgreSQL stores relations in a format called heap. This comes with advantages, but also drawbacks. One of the downsides is, that every PostgreSQL DBA at some point in time needs to deal with bloat. zheap promises to mostly eliminate bloat but in addition claims to be better for certain kinds of workloads. In various demos we'll go through the architecture of zheap, highlight the advantages and then do some tests for comparing heap and zheap. Although this is not a developer talk but more a DBA perspective on zheap you should be able to identify the correct workloads for …Link
Are you suffering from bad performance? Do you have the feeling that your database can do a lot better? Well, maybe it really can. This talk will provide you with a simple guideline to ensure superior database performance using a systematic approach to tuning and especially to performance analysis. You will learn to find missing indexes (which will help to cut performance problems in half), find out about important memory parameters and inspect storage at a professional level. Using some simple tricks you will be able to dramatically improve your database performance and make sure that your applications provide a …Link
Today BI platforms are typical instrument to make proper decisions on data in almost every size business. Market is full of cloud based as well as on-premises solutions, but sometimes companies are making decisions to build in-house solutions. I will talk about our BI application which we built on Postgres for largest Irish pharmacy supplier’s chain. We will review business requirements shortly. Later on I will describe specific of our architecture, why we chose snowflake scheme and how we are doing extract, transformation and load procedures. It will be mentioned about special Postgres tuning for OLAP and massive data bulk …Link
**HA of PostgreSQL with Kubernetes** PostgreSQL is there to keep our data, and if something terrible happen, it would be cool to have data safe. It would be even more cool if users, or applications that are using Postgres do not really know that something has happened. How failovers are handled in Kubernetes, and how do we handle syncronisation. **Scaling cluster** In order to obtain HA, and loadbalancing we need need multiple nodes, we can also add multiple replicas and scale our cluster in Kubernetes. **Upgrading strategies** It can be nightmare to upgrade server version and go from one to … I would also like to talk about upgrade strategies. **Storage is most important part of Postgresql. ** Kubernetes also handles storage in it's own way, and Postgresql is as we know all about storage, fast storage and there are few words to say about that.Link
Data security is an important issue and the introduction of GDPR has added even more requirements. - But how can one live in a demanding environment? - How can one secure data and make sure that the right people can see the right data? - Is GDRP a management problem a technical problem? - Are there services providing GDPR consultancy? Can we manage it on our own? - Encryption. Is it enough? What to encrypt? - Anonymization aka data masking. What is it? How it may help us? - Role permissions. How to get an overview of internal company role … Will touch these topics.Link
Using database connection pooling is a common take for addressing the problem of handling of a large set of requests, without affecting the overall throughput of a cluster or database. This talk will show the importance of its setup and some advanced techniques for handling active connections and pools in production. Particularly, vanilla Postgres architecture is robust, but non-persistent connections on scale do not tend to work at fully potential as other engines can handle. At some point, pools can save costs but sometimes can also not be suitable, which will be discussed on the talk. Some of the pooling …Link
Full-fledged health check of heavily loaded multi-terabyte production database usually takes many days of expert work. With postgres-checkup, it takes just a few hours. [postgres-checkup](https://gitlab.com/postgres-ai/postgres-checkup) is a new approach to survive in the era of clouds, microservices, and rapid transformation of DBA profession. Among its key features are: - close-to-zero footprint (risks of the ["observer effect"](https://en.wikipedia.org/wiki/Observer_effect_(information_technology)) are mitigated); - "zero install": nothing needs to be installed on Postgres servers; - reporting formats are both human- and machine-friendly (JSON, markdown, HTML, PDF). We will discuss how you can quickly perform health check without deep internals knowledge, diving into the following topics: … - indexes health; - deep query analysis.Link
SQL:2011 introduced the concept of a Period. These can be used for many things but the most well-known application is system versioned tables where all changes to a table are recorded and can be summoned via SQL. PostgreSQL is one of the last databases to be missing this feature (even MariaDB has them). In this presentation we will see the many different ways periods can be used, and talk about the current status of their implementation. We will also see a fully functional extension that simulates most of the features that the SQL standard provides for periods but obviously without …Link
This talk explores the new features coming in the next PostgreSQL release. This talk changes for every release.Link
PostgreSQL provides a way to communicate with external data sources. This could be another PostgreSQL instance or any other database. The other database might be a relational database such as Clickhouse, MySQL or Oracle; or any NoSQL database such as MongoDB or Hadoop. To achieve this, PostgreSQL implements ISO Standard call SQL-MED in the form of Foreign Data Wrappers (FDW). This presentation will explain in detail how PostgreSQL FDWs work. It will include a detailed explanation of simple features and will introduce more advanced features that were added in recent versions of PostgreSQL. Examples of these would be to show how aggregate pushdown and join pushdown work in PostgreSQL. The talk will include working examples of these advanced features and demonstrating their use with different databases. These examples show how data from different database flavors can be used by PostgreSQL, including those from heterogeneous relational databases, and showing NoSQL joins.Link