PgConf.Russia 2019

JSONB in PostgreSQL has interesting properties for design and development of business systems with heavy domain, helping to fight against complexity and variability. We will discuss the trade-offs of the JSONB approach. Using an open source project for Health IT - fhirbase we will:

• load synthetic medical data into PostgreSQL
• learn how to search and index this data (gin, jsquery, json-knife)
• use JSON aggregation to build complex queries (GraphQL)
• see how this data can be modified and validated
• discuss architecture consequences of JSONB usage

Technical requirements:

• docker
• docker-compose

Detailed prerequisites

In this talk we will have a look at the details of autovacuum's implementation and see what kind of practical implications they have. The talk will also provide a short overview of patches for autovacuum that are currently being considered by the developer community and that may be included in newer versions of PostgreSQL.

The dark age for PostgreSQL started at 2GIS after transitioning to the microservice architecture. Every team tried to cook database on their own — by installing instances, juggling versions, trying to code deployments with numerous tools or using manual operations. It was the right time to develop a “silver bullet” — a common set of tools to solve all the problems at once. We created our own cluster solution based on well-known PostgreSQL, repmgr, pgbouncer and Barman. Despite of the complexity of our final solution, we developed a repeatable flexible deployment to accelerate postgresql cluster deployment and management. Also we deployed the our own cluster to consolidate all databases. It helped to eliminate team efforts for database management and focus on their main goals. Failover works, we tried it :-)

Our company has developed a software product Live Universal Interface (LUI) is a tool to quickly create and modify standardized on-screen forms to WEB browsers without compiling code, know just enough SQL. LUI is aimed at B2B, B2G, G2C and B2C segments and intended for use in billing systems, financial management, accounting and control of production, where you must decide first and foremost functional tasks, rather than demonstrate unnecessary graphics elements.

Collective elaboration is provided by storing all elements of program code in the total database, which can be located on the corporate server or in the "cloud" on a third-party site.