PGConf.Russia 2018

• Spatial and advanced spatial analysis with pure PostGIS (including cutting edge PostGIS functions available)
• How could we mix and tied efficiently PostgreSQL and Python data types (as NumPy ndarray, and Pandas DataFrames)
• Tools to improve our data manipulation environment (Jupyter tricks, easy dataviz...)
• How to go further throught GeoDataScience, with Python libs and framework tied with PostgreSQL/PostGIS (including Machine and DeepLearning)

In this talk, I will tell you the story of how a bunch of sysadmins got sick of having to resuscitate their petabyte-sized Elasticsearch cluster and decided to replace it with some tried technologies: PostgreSQL, Kafka, a bit of Redis, lots of glue, and the typical sysadmin stubbornness. The result is Bagger: the sysadmin answer to Big Data. A fast, fairly reliable, fault-tolerant store, used mostly for logging timestamped events for some amount of time. Bagger is named the Bagger series of bucket-wheel excavators, feats of German engineering and some of the largest land vehicles ever produced by man. Just like the excavators that dig through tons of material, our Bagger digs through tons data.

Rambler & Co is a lot of publications, services and projects. Appear new and grow existing. This environment requires a reliable, fault-tolerant, scalable, automated system.

I'll tell you about the structure of our PostgreSQL SaaS, what tools and technologies we use. Quorum of 3 Data Centers. A single entry point for clients based on dynamic routing. Emergency switching of the primary server. Transparent scaling for reading. Create a replica without load on the cluster. Transparent transfer of PostgreSQL cluster to other servers. Update dev environment from prod for development. Backup with compression and the use of multiple CPUs on the side of the database, the restoration of one database from basebackup. Monitoring of sql queries.

One of advantages of document-oriented databases like MongoDB or Couchbase over RDBMSs is an ability to change the data scheme easily, fast and often. The traditional approach in RDBMS world involves doing an expensive ALTER TABLE operation, slow upgrade of an existing data, and stuff like this. This approach is often slow and inconvenient for application developers.

To solve this issue PostgreSQL provides JSON and JSONB datatypes. Also there are extensions like zson and pg_protobuf. From this talk you will learn how to work with these datatypes and extensions, their pros and cons and also related future work in this area.