Notes from Prague PostgreSQL Developers Day 2017

Thursday in the middle of February means that there was another year of p2d2 conference. This year, it's already 10th p2d2 meet-up of PostgreSQL users. Although it might seem interesting, it's nothing special, 16th will be better opportunity to celebrate.

Beating 1 billion rows per second

Billion rows in any database is nice portion of data, even more interesting it is in relation database, but as Hans-Jürgen Schönig from Cybertec explained, it is possible do it in 1s. It all began when Oracle published an article saying they can do it, Hans decided he can beat it with PostgreSQL as well.

From the real beginning, it was clear that it must be done by parallelism. Traditional one-core execution could help in parallelism when running more queries at a time, while pl/proxy could handle parallelism using sharding on procedures level. Parallelism from scratch is simplu not something feasible these days.

Hans began with a simple scenario with 3 columns, but this can be scaled to real data using more memory and more processors (more machines), theoretically to any level, so we can take this simplifying as acceptable. So, what can one server offer?

With 9.6 parallelism, which is not complete yet, optimizer determines number of cores that will be processing a query, small tables are processed by one core only. Option parallel_workers can be set to overcome guessing and make number of workers higher.

Benchmark must be done in memory only, to not do disc benchmark instead. It turned out, that one server can scale linearly up to 16 cores, even when running on 32 core box, because that box is actually 32 thread box, hyper-threading eventually degrades it to 16 core, though.

So, at this point we have 40M rows. Next thing is partition tables. And we can do this to needed extend. In the end, 32 billion rows were processed in less than 3s, which means the goal was reached, only few patching was used on top 9.6, many of them will be already in 10.

PostgreSQL for OLAP alias how the parallelism works in PostgreSQL

Tomas continued with an overview around processes and connections. Traditionally no threads and no forks are done in PostgreSQL when processing a query. That is ideal for OLTP applications, where apps do many small chunk of work jobs. Sharing processes via connection pooling is one thing how to utilize one process more, but it is not interesting for making query processing more effective.

OLAP applications use usually less connections, so some real multi-processing is more needed there. Gather process can run more processes in parallel, gather data from them and every process can do something on their own. Quite a lot of restrictions so far, like shared locks don't allow to run data modifying queries. There is also only one level of parallelism, but for selects it might cover pretty nice chunk of use cases.

In version 10 there will be some improvements in bitmap scan, hash joins or aggregations. Users will need to set flag whether the PL function is parallel safe though. Using TPC-H, standardized benchmark, many tests were faster at least twice, comparing to single process queries.

Effectiveness of parallelism (difference from theoretical limit) depends on core number, we saw it goes from 100% (1 core) to 80% for 32 cores.

Currently, refreshing materialized views is not parallelized, but it would make great sense. Same for parallel sort, although gather sort might be done in 10.

David Turoň from LinuxBox began with a bit of joke, explaining how miners in Ostrava region start to program instead of mining.. Anyway, he talked about partman_to_cstore, which is a plugin living in gtihub currently, so user needs to build it first. It might be good for: table with rotations (timestamp/timestamptz), senzor data, log of events; generally big tables that does not change. It's nice that in most cases partman_to_cstore might be used without changes in application. Cron may run function and store data to cstore.

Roman Fišer from nangu.tv continued with sharing their problems when delivering a lot of multimedia content to many users. From the tools they use, let's name Consul, Patroni, or HAProxy. As for the approaches they chose, they need to be tolerant for latences between nodes, etc. For data collection they use collectd, store logs to RRD files, Collection3/Cacti as a frontend, InfluxDB/Grafana for back-end, and they think about Prometheus. New PgSQL version integration takes them approx. 6 months.

Containers everywhere

My talk then focused on containers in distributions, of course giving all of the examples from PostgreSQL world. Describing mostly basic stuff I wanted to bring containers a bit closer to users that typically don't like them much. Mentioning OpenShift, Flatpack, or systemd-nspawn, except traditional Docker, people could start trying it on their own.

After presentation there were quite a few questions:

• Network - could IP tables (firewall) inside the container?
• Data in volumes shared in COW mode, so they can share the common part on disc - could volume-mounted data be on zfs?
• Several questions about user namespaces, what they are good for and whether it works as people expect.
• Sharing libraries in memory was an interesting question we couldn't answer for 100%, but from further look-up, any memory sharing would be against containers' concept of isolating processes, so nobody will probably implement it.

Machine learning using PostgreSQL

Lightning talk by Hans-Jurgen about Machine Learning focused on detecting fraudulent logins on a machine. Support Vector Machine is used to separate good users from bad, it's basically about creating model that is used for decision. PL/Python function to load data and use history data for learning itself. In the end the decision is also done in SQL and that all of course in PostgreSQL..

Pavel Stehule talked about \g, \gset and \gexec commands in the second and also last lightning talk. Those commands were added recently in psql, all help in faster work in psql console.

What does it mean to hack PostgreSQL?

Pavel Stehule closed the conference with a talk about experiences with implementing XMLTABLE for parsing XML, which was missing in PostgreSQL. It was possible to use the XML functions before, but with XMLTABLE it is much more obvious, which is the reason why SQL looks like it looks. It was interesting excursion to the world of PostgreSQL hacker.

When somebody reads ANIS SQL, it does not need to be really nice reading, details are missing and existing implementations are slightly different from standard, so what to do? In PostgreSQL, which implements the XMLTABLE later than DB2 and Oracle, author needs to take the functionality that is already existing. Generally, when implementing some new functionality, we might either consider patching the code, or writing an extension. If the feature can be done using extension, it's much easier, it can live anywhere and people are happy and prosper. In case patching of PostgreSQL is needed, it takes time and developer will spend a lot of energy. Especially tricky it is when using some library like libxml2, which, same as PostgreSQL, uses own memory management, uses different types of data and a conversion needs to be done.

In case of XMLTABLE implementation, some code change needs to happen in parser and executor. Here it comes handy to know well how the execution pipeline works in PostgreSQL -- parser, rewriter, planner, optimizer, executor. Also, SQL needs to be translated to some other language, so changing behaviour is basically changing of translator. On the other hand, this SQL translation won't be the bottle neck. Patching of parser (bison) needs to be clean, no conflict warnings will be acceptable during the review. Implementation is either an SRF function or executor patch.

When writing patch, first iteration in community is to write something dirty, to show that it is possible and conversation about benefits can begin. Next phase is complete patch, with documentation and regressions tests. The last part is commitfest patch, that is used for cleaning the code, documentation, extending tests, ...

The whole patch writing is very hard, one will learn assertiveness and respect in the first place. Some features take even 10 years from first patch to commit released. hstore for example helped Ruby on Rails and that helped PostgreSQL to grow. Without use case it is not very probable that change will be accepted.

So that was the 10th p2d2, I'm already looking for the next year!

Notes from Prague PostgreSQL Developer Day

What a surprise that I haven't heard about containers at all during whole conference.. Really a special thing today, database experts are probably quite reluctant to such cool hype technologies... Anyway, it was very interesting 1-day conference around PostgreSQL, so sharing my notes, since those might be interesting for others as starting point for further study. Most of the slides are here: http://p2d2.cz/rocnik-2016/prednasky

Hlavní novinky v PostgreSQL 9.5

First presentation by Pavel Stehule, last time replacement for Tomas Vondra, was about news in PostgreSQL 9.5 and even without slides it was very interesting summary with enough details at the same time. PostgreSQL 9.5 turns to be wanted by quite a lot of attendees.
Most of main PostgreSQL upstream developers do it as full time jobs, which means it is a professional development, even though it is still true open-source and always will be. On the other hand, as the project is more and more mature, a lot of features are in development for even 3 years.
As for version 9.5, many users are interested in UPSERT, performance is not that different from previous versions. UPSERT is kind of like MERGE from Ansi SQL, but MERGE was not implemented 100%, so it is called differently.
Grouping sets kind of allow to create multidimensional views on data.
Locks reworked a lot and arrays in PLPGSQL mean that for some complicated scenarios it may improve performance a lot. Usually, sort with C lang is much quicker than with non-C lang. Languages are simply sorted slowly, in 9.5 it is much better even for language sorting.
Bitmap indexes with a lot of duplicits do not exist like in Oracle. Something new was created though, originally called minmax, now BRIN index, which is much smaller than other indexes and is primarily used for append only data.
Extension pg_rewind avoids cloning master and we can make a slave from master that has even a bit old data during fail-over.

Two things where PostgreSQL is behind commercial DBMS are multiprocessors utilizing and partitioning. Today the HDD is not the bottleneck anymore, with RAM disks and many CPUs it is speed of on CPU. In 9.6 there is already couple of commits for parallel queries, which is so great that we might even call 9.6 to be 10.0. There are also plans to have logical or BDR replication.. some more patches for making guesses better, using correlations. Especially useful for users who do not understand query execution and cannot use EXPLAIN.
Another feature is RRS (row right security), especially interesting for banking sector for example.

IO in Postgres - Architecture, Tuning, Problems

Andres from CitusData talked about tuning, giving overview around memory architecture, explaining why there is shared memory and private per process memory. One cache handled by PostgreSQL, another hash is in the system when reading files. Clock sweep invalidates part of the buffer so we know what buffer part we can replaced -- that might be expensive in some cases.
Reason why WAL is efficient is because it is a sequential write. Checkpoint then write bunch of data at some point, that can be expensive. We can extend checkpoint timeout, for large data written, checkpoint_segments should be tuned up. To reduce starting up we can do checkpoint before shutdown.

Checkpoints explain why there is sometimes more load and sometimes less in benchmarks graphs. Andres also showed how the tuning of buffer size and checkpoint values influence the graphs.
Interesting that turning off caching in OS may do better job, although we need to count with consequences.
Tuning buffers is basically about understanding the workload, no advice can be good enough if it is done without that. Drop index and re-indexing can be better with lower shared_buffer settings.
Batch runs may use different settings. Slides available at anarazel.de/talks/pgdevday-prague-2016-02-18/io.pdf

SQL Tabs

Sasha from Shards.io was speaking about his cool project SQL tabs .

Sometimes psql console is not enough, but people love it. So he started a new SQL client with requirements to be nonblocking, black and white themed, having connections switching...

react.js, nodejs, and other technologies used for creating web-based system that allows browsing, datatype exploration, context advisor, functions listing, query history, ... It uses libpq in backend, so the output is as usual. It can also generate some simple graphs by adding some semantic in SQL comments and selecting proper type of chart and writing comments in markdown. It also allows to combine more queries and having a simple report with text, chars, table.
It understands some datatypes so time series are nicely formated as one would expect.
More info at www.sqltabs.com.

Jak jsme začali provozovat PostgreSQL (a co jsme se u toho naučili)

Ales Zeleny from Ceska Sporitelna sharing experiences with PostgreSQL. He advised:

• to try backup before going to production
• start with simple app for lunches
• not making oracle from PostgreSQL
• think about what to log because audit is missing in PostgreSQL
• creating schema for app instead of using public schema
• separating app modules to own schemas
• sometimes even separate databases
• Table spaces on own clusters to avoid influencing other databases when character of one app changes rapidly
• Monitoring is important but sometimes troublesome
• Use check_postgres.pl and autovacuum, configured well for the workload
• Default privileges help creating fine granulary permissions
• Logging can supplement audit
• Backup and recovery by barman, pgbackrest, pacemaker from rh for recovery, streaming replication with delay
• Testing data recovery scenery
• For monitoring they use elegraph + influx + grafana
• Visualization helps understand where is problem
• Configure autovacuum so that it runs often, then it does little things, so it is quickly

Open-source "clusterovací" řešení pro PostgreSQL

Petr Jelínek from 2ndQuadrant talked about general scaling concepts (vertical, horizontal), focusing on OLTP, OLAP, ...
For vertical scaling and OLTP we have couple of features already in 9.5 (sampling, BRIN indexes, sort optimizations and in 9.6 there will be some first implementation of parallel query execution.
Hot standby shorty introduced - integrated solution for scaling, it solves only read scaling, since slaves are read only.
PL/Proxy - now almost legacy, developed in Skype, no maintanance, map-reduce implementation in PostgreSQL.
Greenplum by Pivotal (fork of PostgreSQL), stable, open-source since Autumn 2015 (Apache) is kind of MPP database (multi parallel processing) and is quite a lot diverse from vanilla PostgreSQL. It has own tooling, tables optimized for inserts.
CitusData is gonna release CitusDB very soon as extension only, now it is a fork of PostgreSQL and extensions. And it will be open-source soon as well, now only pg_shard is open-sourced. It has logical servers, so it duplicates data in distributed tables.
Postgres-XL comming from Postgres-XC and StormDB, now Open-Source (MPL) taken by community after the company crashed. Soon on 9.5. It is MPP that supports both OLTP and OLAP, but is more complex for administration and installation.
BDR from 2ndQuadrant is also a fork of PostgreSQL (9.4), but it has goal to be integrated back once. It already pushed couple of features to vanilla PostgreSQL (background workers, event triggers, ...). It is asynchronous multi-master solution (all data on all servers) that uses optimistic detection and conflict solutions (in contradiction to MVCC in vanilla PostgreSQL). So, it makes the consistancy be true eventually, after conflict resolution (after commit). Primary focusses on latency optimization (avoid global writes across the universe).
Questions about eventual consistency and sequences; answer was that nobody can expect normal application will work the same as before without multi-master.

Monitoring PostgreSQL prakticky

Pavel Stehule talking about administration and monitoring, starting with interesting thought that moving from "It may work" to "I know it works" is quite a small step.. Architecture is always a key. Client server is for one server, cluster scales... Wrong chose in the beginning will affect all.
Already during user interface design to think about database -- so the database can return only limited set of lines. Important values are parameters of RAM, IO -- while we are in RAM, then there is performance degradation and it does not go linearly, it degradates by jumps.
Configuration of database and optimization of queries does not help when the architecture and app design is wrong.
Sometimes users add indexes without thinking. Sometimes users deploy replication and cluster when it is not necessary, ram may help much better, aggregated data in db is also not a bad idea.
Work_mem shared_buffers, effective_cache_size, max_connections are important - wm*mc*2 + sb + fs + os < ram
Database servers can never swap.
Shared buffers too high (over 20gb) could make finding free cache too slow, but it always depends on cache work characteristic
Effective chache size says how big portion of index size is actively used.
Strong negative feedback is about 80/20 rule, that 80% of workload is generated by 20% of queries. Sometimes even modules that are not used, eat 50% of workload.
People cannot work with memory today, it is better to read data once, work with them and write time to time to db, not connect to db for every piece.
Monitoring is important to seek for regressions.
Good queries should be verified that it is not an accident.
Disk in work has much different characteristic than on machine without load. Pg_bench is the other extreme, it does not simmulates any real workload.
Some overview at pg_stat table - Many Rollbacks are wrong, many tmp files are wrong. Changes in transactions count is suspicious, like firewall killing connections by mistake.
Kill very long running queries before they kill the server.
Especially in beginning of the app it is important to monitor queries because database is a living organism.
Queries around 50ms cannot be much faster, if we need more, we need cache and not use db at all..
Monitoring regressions in cpu utilization using profiling tools works good in linux, fix is usually to distribute the workload to more processors.
Autovacuum monitoring is also important.
And finally the bench-marking is needed and it should be done soon enough and ideally in production environment.
Finding whether something is effective is hard, takes long time. Finding whether something behaves the same as yesterday is simple and quick. Graphs are quick way from hope to know.
Too wide tables are usually wrong, in oltp we should respect normal forms.
How large is typical oltp today? Usually tens of gb. Today people often store bitmaps to db which makes it larger.
Ram > 1/10 db

pg_paxos: Table replication through distributed consensus

Pg_paxos was introduced by Marco Slot and we learned it is a framework for distributed problems. Servers sometimes need to agree on something, that is what paxos algorithm is about.
Two phases: nodes agree in participation, proposal asks for acceptance when majority must agree
If anything fails, we start over.
Paxos state machine (multi-paxos) helps to replicate log on multiple servers.
Paxos is extension with low throughput and high latency, so no alternative to streaming or logical replication, not even a magic distributed postgres, but it is a useful building block for distributed systems. It is somewhat experimental in pl/pgSQL. Available on github on citusdata organization.
Demo showed how to use three paxos servers in cloud to acquire a lock, then communication can begin normally and we know only one server works with the data.

That's all folks.

CentOS Dojo 2016 Notes after lunch

This is a second part of my personal notes from this year's CentOS Dojo before Fosdem, first part includes notes from talks until lunch. So, after lunch, we returned to our chairs and heard these interesting talks.

4. Quickstart. Contributing packages to a CentOS Special Interest Group

Brian talked about basics in centos build system, how to jojn SIGs and how to build a package in CBS for a particular SIG. Really a "must seen" for every newbie in centos SIG.

5. Path from Software Collections to Containers for OpenShift

My talk about experiences with creating Containers for Open Shift included two dozens of tips from various fields. We closely looked how to create a nice, Open Shift friendly container image (yes, it was about Docker) for PostgreSQL and Python. These two examples covered the most important information that one needs to create any similar database or application builder image. Later I went quickly through list of images that are already out there, made by Red Hat or CentOS and that are based on Software Collections packages. In the end I shortly introduced the concept of Nulecule and what this project is intended for.

6. Getting started with kubernetes

Kubernetes was described the same as other orchestration systems, even condor which development started already in 1987. What makes the technologies different from the PoV of potential developer folks is language chosen to be written in.
Mesos was secretly influenced by Borg, a group run by Google. It means guys creating k8s know what they do, because we may see k8s as a new version of Mesos.
Basics of Kubernetes explained clearly and on simple examples -- what pods, services, replication controller do.
Atomic was presented as the solution to use k8s on CentOS.
For learning k8s use gh.c/skippbox/...8s
Terraform plan for deploying k8s on AWS with atomic host and flannel.
A demo showed automatically created k8s nodes and let them scale in AWS.

7. Atomic Developer Bundle - Containerized development made easy

Atomic developer bundle, guys showed why there is something like ABD, stating problems devels face today during application development, all on real user stories. They showed Vagrant devel environment, running docker secured by TLS, user being able to connect from host machine by evaling 'vagrant adbinfo' output that defines devel environment on host.
Another example showed Eclipse running on the host, connecting to remote docker, which is a scenario that might work from any OS, even windows. Although the demo did not work and we could see the live-demo Murphy's law in practice again, we got the point and I'm sure it worked fine just before the talk.
ADB supports the OpenShift and other orchestrations technologies as well.
Why centos? Because of community, that might give the needed feedback. In the end the list of available links were mentioned and community was called to action.
The future is so bright, I gotta wear shades.
Architecture is still a thing to be changed, they plan to make vagrantfiles easier.
Landrush does not work and some help is needed..
In the end guys tried the demo again but with poor Internet connectivity and Murphy's law working better, we saw only one step further.

Description of the talks and hopefully soon also slides and recording available at: https://wiki.centos.org/Events/Dojo/Brussels2016

Thoughts from PGConf.eu, PostgreSQL Europe Conference 2015, Day 3

This is a continuation and the last piece of notes from PGConf.eu in Vienna this year. First day notes available here and second day notes available here.

I started the 3rd day with KaiGai Kohei, who talked about pb_strom in his presentation called GPGPU Accelerates PostgreSQL - Unlock the power of multi-thousand cores. Plugin pg_strom is a  combination of PostgreSQL and gpgpu, which is a project in early state that aims to online native GPU code generation (nvrtc run-time compilier, cached, so compilation is done only once). It uses 9.5 feature of custom scan/join interface, that allows to overwrite part of the execution plan. Benchmarking showed approx. 5x speedup for queries that included 100M rows; join was more important than aggregate. Another benchmark showed some anomalies where pg_strom was slower for some queries.
https://github.com/pg-strom/devel

Ruben Gaspar Aparicio from Cern provided some experiences from his work as DBaaS. Except DB related information, some Cern statistics were also unbelieveble -- 6000+ magnets in The large hadron collider, which makes it the largest machine in the world. It produces 0.5PB data in one second during proton collision, totally 100PB stored now, CPU with 250K cores, 2M jobs per day. 8000 physicists require real-time access to all that LHC data from 160 computer centres in 35 countries.

Except 36 PostgreSQL 9.2 and 9.4 they use 226 MySQL and 8 Oracle in DB on demand (DBoD), then RAC with ~100 Oracle DBs. They support SSO in system, overview of instances DB providers offer different things, own DBs on demand is how the physicists work. The DBaaS supports upgrades, monitoring, backup and HA. However, there is no DBA support nor app support nor Vendor support. Everybody gets own database and must solve issues on their own. System supports back-up, configuration changes, PiTR (point in time recovery). Statistics of queries can be intuitively seen by uesrs that are no DB experts. Logs overview helps to fix problems by users themselves.
I was smiling about Ruben's quote that "Deleting is manual, because there are people who don't realize that when they delete something it is no more there."

New machines are installed by puppet and they run on physical servers + OpenStack as infrastructure. They already plan to use containers for running the DBs in Cern. They use Coverity and apps are usually written in Perl 5.20. Travis-CI is then used for running unit-testing.

Correct mount options are very important for proper performance (slave laging) of PostgreSQL.
Tools using: pg_upgrade, pg_snashot,pgbadger, pgreplay
Future vision with Containers (LXC): limit CPU+memory, cli access to instances.
https://github.com/cerndb/

Locked Up: Advances in Postgres Data Encryption by Vibhor Kumar summarized possibilities of encryption in PostgreSQL -- we can either encrypt in applications, pgcrypto modul or use pgp encryption.

Gianni Ciolli talked about repmgr in talk called Automate High Availability using repmgr 3. The tool supports cascading replications, clusterware, uses barman, pgBouncer, pg_rewind.
https://github.com/2ndQuadrant/repmgr/blob/master/CONTRIBUTING.md

Talk The Elephants In The Room: Limitations of the PostgreSQL Core Technology by Robert Haas direct I/O was then about dirty kernel page buffers when writing to disc, where PostgreSQL was not able to do almost anything. A need for full featured logincal replication solution was mentioned as something we really need (not only features).
Database size is bigger and bigger -- horizontal scalability seems to be the answer, i.e. the focus is in better sharding.
Other areas to consider: better parallel queries, changes to storage format, direct I/O, built-in connection pooler.

Simon Riggs closed the event with a keynote where he mentioned pgconf.eu 2015 to be the worldwide biggest event ever.
Zero-downtime upgrade is something PostgreSQL upstream wants to achieve when upgrading from 9.4 to 9.5 -- using cross-version replication. BDR (bi-directional replicatoin) is heading into postgres, which may allow multi-master replication in 9.7.
Simon mentioned Stonebraker's Vision who said that there will be either data warehouse database or transactional databases, but nothing will do both. PostgreSQL is proof that Stonebraker was wrong, because PostgreSQL is both. It was also mentioned that patched-PG projects like PgSQL-XL, that focus on scalability and that will be based on 9.5 at 1Q2016 may be several times faster than 9.6.

Dave Page, Magnus Hagander had the final word that belonged to 2nd Quadrant, which is becoming sustainable open shource development, customer funded company that provides Highly Available Support and RemoteDBA.

I already wrote it was my first PGConf.eu, but since the three days were full of very interesting topics and hallway was full of very interesting fans of PostgreSQL, I'm sure it was not the last one.

Thoughts from PGConf.eu, PostgreSQL Europe Conference 2015, Day 2

This is a continuation of notes from PGConf.eu in Vienna this year. First day notes available here and third day notes available here.

I've started the second day with WAL, Standbys and Postgrs 9.5 by Michael Paquier, which was a morning talk about archiving and warm standby and how the postgresql 9.5 may help here.

Then there was my talk called Database containers in enterprise world. I mentioned there our experiences with containers preparation and how RH takes containers (not as VMs, but rather applications); that all was described rather in lower level of detail, but rather with bigger context. Not many people were presented (Robert Haas in the next room talking about Parallelism in postgresql was just too big concurence, just bad luck in timing), but those who were there, were already familiar with containers a bit, which turned into very nice discussion and many questions in the end. The questiones were mostly about differences between Nulecule and kubernetes templates, docker compose; further questions about Nulecule, that I also roughly presented.

VACUUM, Freezing & Avoiding Wraparound was a talk by Simon Riggs where he talked about high concurency, row visibility, deleting rows that make bloat. He explained the principle of vacuum, how long running queries influence it, how it works actually and why it is that important.

Duo Gabriele Bartolini and Marco Nenciarini talked about logging of huge PostgreSQL data and analysing them, which deserves proper tooling in the first place. Guys from 2nd quadrant were talking about experiences with elastic search + logstash + kibana (ELK stack) in a talk Integrating PostgreSQL with Logstash for real-time monitoring.

Linux tuning to improve PostgreSQL performance by Ilya Kosmodemiansky was a great overview around what everything in kernel we need to take into account when tuning postgresql performance. Most of it was about setting proper sizes of memory pages and buffers.

Tomas Vondra talked once again on PGConf.eu, this time about PostgreSQL Performance on EXT4, XFS, F2FS, BTRFS and ZFS after he did serveral benchmarks of different filesystems. Shortly, ZFS was quite poor in default settings, but it can be improved by fixing pages size. Otherwise, the results are quite comparable (XFS, EXT4 seemed the best though). BTRFS seemed quite bad in read-write bechmark, while XFS nad EXT4 were best again there.
http://www.slideshare.net/fuzzycz/postgresql-on-ext4-xfs-btrfs-and-zfs-54525451

Second day was closed by 90 minutes of lightning talks, that have already become tradition on PostgreSQL conference and strict 5 minute rule makes it really interesting.

Thoughts from PGConf.eu, PostgreSQL Europe Conference 2015, Day 1

It was my first PGConf.eu and it was awesome. My bad that I waited so long to share some of the thoughts, but fixing it now with summary. Links to presentations are located at https://wiki.postgresql.org/wiki/PostgreSQL_Conference_Europe_Talks_2015.

The keynote by Tamara Atanasoska called The bigger picture: More then just code mentioned that community project is more about people, code is second. It was also stressed how important it is to be open to new-comers and users. Great eye opener for enybody involved in open-source.

Upcoming PostgreSQL 9.5 Features talk by  Bruce Momjian involved these news: insert on update, aggregate functions enhancements, in-memory performance, JSON manipulation and operations and improvements in foreign data wrappers, which now can be part of sharding now. I really can't wait for PostgreSQL 9.5 GA.

Dockerizing a Larger PostgreSQL Installation: What could possibly go wrong? by Mladen Marinović was really something what I looked for, because containers is now the topic #1 for me.  Mladen demonstrated using containers mostly as VM, basics of Docker were also introduced, since only half of the room were familiar with it. Problems with using cache during `docker build` and locales inside containers were mentioned, but the solutions was rather hacky to me (using date to trick the daemon, instead of straightforward --nocache). Problem with sending signals to process was alse mentioned, which is something we were looking at also during creation of containers for Open Shift.

Mladen uses Ansible for building images and uses several containers for specific actions -- e.g. a separate container for tools (dump/load). They support replication -- host_standby, restart_command (run in separate container) and run on two physical servers. Backups (pg_basebackup + compress) according retention policy - another container.

It was also mentioned that taking back-up from slave is not that easy. Monitoring, that all containers are alive, is done by HA SW. Problem with OOM were also mentioned, that system may kill your container processes, but solution was to use max_connections. Problem with freezing server was then solved by using timeout for every command. Transparent huge pages were mentioned as something not to use -- use normal huge pages instead. Finaly, upgrading to new major versions were mentioned as tough point always.
See more at: https://bitbucket.org/marin/pgconfeu2015/src

DBA's toolbelt by Kaarel Moppel was more a list of possible tools to look at when you mean it seriously with administration of PostgreSQL. So, just repeating the list may be interesting for someone.:

• docs + pgconfig for configuring
• pgbench + pgbench-tools for bechmarking
• pg_activity (top-like) + pg_view + pgstats + pg_stat_statements + pgstattuple + plugins for monitoring systems (nagios) + postgresql-metricks (spitify) for monitoring
• pgBadger + pg_loggrep - log analyzing
• postgresql-toolkit - victorinox for postgresql dba
• pg-utils, pgx_scripts (https://github.com/pgexperts/pgx_scripts), acid-tools
• pg_locks, pg_stat_activity (locks)
• pgObserver, pgCluu, many others, based on what we need to do
• pgadmin4 on the way (backend and web frontend)
• developer: pgloader, pg_partman, pgpool-II, PL/Proxy

Managing PostgreSQL with Ansible by Gulcin Yildirim was more an introduction of Ansible.
https://github.com/gulcin/pgconfeu2015

Let’s turn your PostgreSQL into columnar store with cstore_fdw by Jan Holčapek introduced interesting plugin, that may convert clasic row-based storage PostgreSQL engine into column storage database by utilizing foreign data wrapper concept. In some cases this may help a lot for the performance, once ready.

Performance improvements in 9.5 and beyond by Tomas Vondra was not only an interesting insight to particular areas that PostgreSQL hackers look at, but also nice motivation to look at aupgrading to 9.5. For example sorting speed-up up to 3-4x in comparisson to 9.4 in some cases is something I wouldn't really expect. Another comparisson of BRIN vs. BTREE indexes showed that performance is quite similar, but BRIN is much much much smaller. Another set of graphs showed how parallel scan allows to speed-up selects up to half of the time.

Notes from second day are here and from third day are here.

Thoughts and notes from Prague PostgreSQL Developers Day 2015

The day started with news in PostgreSQL 9.4 presented by Tomáš Vondra, now working for 2ndQuadrant. He talked about improvements in replication, GIN indexes, numeric aggregates, refreshing materialized views, altering system variables directly from daemon, ...

Then, Pavel Stěhule from GoodData gave us a bit more deep review of storing non-structural data within PostgreSQL (from historic implementations to the newest great JSONB new in 9.4). It was quite interesting to see that even traditional relational DB users think seriously about NoSQL solutions like MongoDB. It seems even advocates of those traditional SQL solutions see some advantages of new way to store data (NoSQL concepts).

It was also mentioned that NoSQL is not only understood as not-sql, but more often like not-only-sql and that while relational databases implement features to get closer to NoSQL, NoSQL databases implement features to get a bit closer to traditional SQL solutions (assuming implementing better ACID, reliability, ...)

Vratislav Beneš from OptiSolutions had a talk about unstructured and sensor data, generally about big data challenges. He presented comparison between PostgreSQL 9.4 (with JSONB) and MongoDB for the same type of work with unstructured data. His testing showed there are no big differences in performance, PostgreSQL is however quite better in space utilization (~3 times less data on disk). He closed his talk with a thought that everybody should choose a database carefully, according to a specific use case (e.g. if we care about other NoSQL features like out-of-the-box sharding/replication or we're fine with a bit more heavy-footed, but more reliable SQL solutions).

Aliaksandr Aliashkevich gave quite basic overview about sharding solutions for PostgreSQL, just read his shards.io web for more information, the presentation had basically a similar content.
A similar overview, this time about other open-source tools (dumping, upgrading, partitioning, logical replications) was given by Keith Fiske, actual author of the tools. They are all available under his username on github, so just look there or chech his site keithf4.com for more information.

Marc Balmer from micro systems spoke about securing PostgreSQL application, where he emphasized the need to not limit access only on application level, but also on database level. He didn't omit to give a basic overview about main database vulnerabilities on some specific examples (like SQL injection) but most of the presentation was about ways to secure data within a database from being abused by users that shouldn't have access for them. I think nobody who was there and pays some attention about security will ever use one superuser role to access DB again. Hopefully the slides will be soon at p2d2 site.

Petr Jelínek from 2ndQuadrant talked about a feature, that is heading to PostgreSQL 9.next (not sure yet if 9.5 mades it) -- BDR -- Bi Directional Replication. This basically implements multi-master capabilities and it is already working solution, which is patch-by-patch heading to PostgreSQL upstream. We know upstream is careful about any new feature, so even in this case it goes rather slowly, because upstream needs to be sure it is good for everybody (not only for specific use cases).

Štěpán Bechynský showed Amazon's AWS in practice, especially what are the steps to get a PostgreSQL machine in the cloud (basically PaaS variant). It must have been interesting for anybody who haven't seen how provisioning in cloud works from users' point of view, but for me, as I already saw OpenStack in action, I didn't actually learned much new. I heard from other attendees as well that there were some specific experiences missing -- like performance experiences, what are some specific issues someone new to cloud world experiences, etc.

Since PostgreSQL now supports foreign data wrappers, couple of interesting wrappers are available already. Those add some new functionality to the daemon itself and reminds me MySQL's engine architecture a bit. Jan Holcapek introduced on of those wrappers -- cstore_fwd, which adds columnar storage capabilities to PostgreSQL.

It was interesting to see that even on some non-complicated queries the EXPLAIN command showed several times less IO operations, comparing to native PostgreSQL. For more complicated use cases that may be even better, since going through columns directly for aggregation is much more effective than reading all the rows with most of the data read unnecessarily for the specific query.

Even if this particular foreign data wrapper doesn't support insert/update/delete features, it seemed very promising. It was also interesting when Jan asked audience what column database they use. Since MonetDB was mentioned not only once, it seems to be a good candidate to be packaged into Fedora. Who is volunteering for this?

Fosdem notes alias what's happening in open-source world

I've had a great opportunity to be at Fosdem, enormous conference with several thousand of attendees that took place the last weekend in Brussels. The following are some notes from talks I attended.

Python & PostgreSQL, a Wonderful Wedding

This was more or less summary of various ways how python and PostgreSQL may interact. There were couple of python libraries introduced (psycopg2, SQLAlchemy, Alembic), then some PostgreSQL python-related extensions (like PL/Python, MultiCorn).
Especially MultiCorn is something which gives quite wide range of options to python developers above PostgreSQL, it basically allows to write foreign data wrapper in python, so whatever data you can get with python (be it a text file, embedded DB, XML document or another database), you can then access it within database using SQL.
Schedule entry here.

Nix, NixOS, NixOps

This seemed to be a bit chaotic demo about Nix* tools and OS. I expected to get some ideas about solving packaging-more-versions issue, but the talk was more focused on vagrant-like provisioning of virtual environments.
It seemed also a bit interesting that the speaker didn't know about environment modules existence, so obviously he was not able to tell what is the difference.
But hearing some guys from audience that environment modules are great to solve many issues for them was quite interesting for me, especially related to Software Collections.
Schedule entry here.

Upstream Downstream: The relationship between developer and package maintainer

Talk given by Oracle guy who is btw. responsible to maintain relationships with mysql package maintainers in other distros. He tried to show Oracle is not that evil and that they want to fix bugs if they know about them (which is not happening in some cases).
It was a bit less technical talk, where also some interesting numbers were mentioned -- that devel count on MySQL project has been doubled and QE count tripled recently.
Even after personal straight question what happens with MySQL in the future I got quite straight answer that I shouldn't be afraid, it won't get closed more.
We also talked together with MySQL/MariaDB debian maintainer Otto and MariaDB architect Colin about need to bring packages in various distros closer, so we unify users' experience.
Schedule entry here.

Modern SQL in PostgreSQL

This was half technical and half motivation talk which was supposed to convince developers to use latest features from SQL databases and also SQL standard. On few examples we saw like one keyword that is not known by majority of DB developers may help in readability or even performance.
It was interesting seeing when some of the latest SQL standard features are implemented in various databases. Usually IBM's DB2 or Oracle database supported the features even before or shortly after introducing it in the SQL standard, while PostgreSQL with MS Server usually implemented those features few years after.
And MySQL did not support those mentioned features almost ever, so it proofed that MySQL is better to simple use cases and less advanced (traditional) usage. On the other hand, PostgreSQL with MS Server follow Oracle DB's and DB2's development being few years behind.
Schedule entry here.

ProxySQL : High Availability and High Performance Proxy for MySQL

ProxySQL is a promising tool that may help setting up even complicated deployments with various replication schemes, since the proxy server communicates either with MySQL server or another ProxySQL daemon with pure MySQL protocol. Thanks that feature it allows to do various things according to regular expressions defined to behave differently on various queries.
It was almost the same talk as the last year, except there are probably some bugs fixed. Anyway, the tool seems worth packaging to Fedora and playing with it at some point.
Schedule entry here.

User-land and developer-land chat

Interesting talk about communication within open source project -- designers vs. devels vs. users. It was nice to see the opposite side of the relation, for example that users often don't understand why bug reports get closed and why some changes in design happens, which often leads to their frustration.
Some simple things like indicating briefly, when closing a bug, that devels still want to collaborate, they just don't have enough data. It may be that simple, so users don't feel like they are ignored.
Another example with Gnome 3 and it's firstly problematic and later quite large adoption showed that even if devels do rapid changes in GUI, users may feel good, but only after some needed plugins have been introduced, which brought some of their favorite features back.
Another issue was that even if upstream is open for collaboration, people still need some borders, so setting up some guidelines similar to Tango Icon Theme Guidelines may help producing consistent output and the collaboration works much better with then.
The last thought was dedicated to need non-technical people, since devels are often reluctant to write necessary documentation, maintain proper social networks, etc.
These all are few topics everyone involved in OSS may think of.
Schedule entry here.

SQL part of your work? Read SQL Performance Explained

Simply said this book by Markus Winand should not be missing in any shelf of any database engineer. It does not explain basic knowledge about SQL, which you might learn from many others SQL-oriented books or from the Internet, but it gives a great background on the inner database implementations.

Why is knowing this background so crucial for database programmers? A general rule sais, if somebody programmes in some language, he should know basics of what is happening in two layers down. For example, a Python programmer should know at least basics of programming in C and should also know something about assembly language. Moving this theory to databases world, a programmer who works with SQL, should know about inner design of DBMS and should also know at least basics of C language. And the book from the title gives you that knowledge of DBMS.

SQL Performance Explained does not prioritize any DBMS, but tries to cover all the most famous DBMS out there -- Oracle DB, MySQL, SQL Server and (my favourite) PostgreSQL. A reader learns the magic behind database indexing implementation in the beginning, since it is the key for good performance in the first place. Later, we learn how created indexes are actually used in WHERE or ORDER BY clauses and how to help DBMS to actually use them.

Since the book is quite fresh, it covers even some newer features that are becoming a part of DBMS in the latest versions, like index-ony scans. As an icing on the cake, readers will find useful some very concrete examples. One example for all, Markus explains how a bit more modern way of pagination on the web pages using "later" instead of classic numbered pagination can make quite big performance difference in some cases.

Well, the not even 200 pages book is not the cheapest one, which is usual for such a book for quite narrow set of readers though. But it is definitely worth reading and looking time to time.

Good work, Markus, and thanks for that!

Fosdem report with some thoughts from P2D2

(TL;DR version) Issues in cloud-based deployments totally dominated the database topics on both, Fosdem and p2d2 conferences. Whether it is high availability or scalability of the data stack, usually possible to handle with in-house features, people choose to implement those challenges more and more using external tools recently, which seems more accurate in some particular cases. Internal features simply include way too many manual steps, so they're not fine enough in environment, where full automation is necessary. Any such an extra effort distributions can eliminate counts.

A bit more verbose version follows.

This is my report from Fosdem conference from the last weekend and I also included some thoughts from Prague PostgreSQL Developers Day 2014 I attended on Thursday. As databases and general packaging are my concerns, I focused on databases lecture rooms on Fosdem, which means I alternated between rooms focused on MySQL/MariaDB, PostgreSQL, NoSQL and Distributions.

Generally speaking about databases direction, there were really a lot discussions about deploying databases in cloud-based environment.

The problems we face in that field can be summarized by several topics: replication for high availability, scaling for utilize more nodes effectively and automation to limit manpower in deployments where number of nodes has more than 3 digits. Quite a lot interesting smaller or larger projects, that would solve some of these issues, were introduced on the lectures and I'd like to mention the most interesting of them.

Some interesting tools for recent challenges

proxysql, currently a single-developer project, seemed like a generally usable light proxy server that can be configured to offer load balancing, partitioning or even some more complicated tasks like query rewrite or selecting proper server based on region priorities. The lack of community around that project is the biggest con right now.

For PostgreSQL, there is a similar project, Postgre-XC, that also allows to access multiple PostgreSQL instances through one single proxy for large cluster. PgBouncer then offers a solution by pooling the connections for deployments where number of client connections attacks thousands and thus reaches the limit.

Similar slight layer between client and server is community-developed Galera clustering project, which originated from Oracle's MySQL cluster solution. Even if it is considered standard for MariaDB now, the support in MariaDB itself is not merged yet and necessary patch kind of blocks wider adoption in distribution, since an extra MariaDB binary package has to be maintained.

Connect engine for MariaDB seems like really fine help in heterogeneous environment, since it allows to work with external data from MariaDB, the same as there was a native table. Similar feature is now included in the most recent stable version of PostgreSQL 9.2.

More about replication

Interesting comparison between an external HA solution Slony versus out-of-the box streaming replication, that new PostgreSQL offers and was evaluated as better, showed that PostgreSQL upstream does still very good. Also the number of new features focused on HA, scalability and NoSQL-like usage of this DBMS promise good future for PostgreSQL. For example new JSON document type has even better performance than pure-JSON MongoDB in the most recent PostgreSQL, according to one of the benchmarks out there; and as a bonus it doesn't have to give up the ACID features, like MongoDB does.

To sum it up - relational databases offer some basic features, but do not include good enough solutions for all distributed deployments using a simple way. This can change for PostgreSQL in the next releases, but since one size doesn't fit all, we would still need some little help of plug-ins, proxies, etc.

Where distributions and especially support companies can help is also offering some supported scenarios, because as a talker from Avast described, it was real PITA to choose the best-fit database system to store their big data, especially if the management can't give specific-enough requirements.

What is up in NoSQL world?

The situation is a bit different for NoSQL databases, that include some good enough automatic solutions out of the box or even by definition. The draw back side of NoSQL databases are rather in missing ACID transactions and that they require totally different approach to database design than relational databases.

However, really full NoSQL lecture rooms should be clear sign that NoSQL world will be very important in the next years' cloud-oriented market.

During the devops-related talks I've seen many desires to configure system using some automatic tool like puppet or chef, which is what companies use in the end and there is definitely some space to support these deployment tools better. So generally, no more admins' work can be manual any more, not in cloud environment. Automation then can't be done without versioning, logging, module testing, integration testing or continuous integration.

What is not happening any more

Interesting thing for me was kind of lack of single-server performance talks. It seems the database servers reached some point where improving performance is not so important for a single server, especially if we have so many challenges to improve things in scaling parallel deployments or increasing limits of data amount to be stored efficiently.

To sum it up, databases world might be quite boring the last years, but that is definitely changing now. Clouds are simply database-enabled.

The best database, PostgreSQL 9.3

Fedora 20 was released few days before Christmas with only slight delay and you can now enjoy many new bleeding-edge applications and one of them is the newest stable PostgreSQL 9.3. What can we find with this new shiny database engine is shortly summarized in this post.

Something on data definition field

One of the most significant news, materialized views, brings to users a possibility to create a real table instead of a virtual view and you can even create indexes on such view. However, after updating the view you need to call REFRESH explicitly, since this feature is not totally completed yet. Still worth using already, though.

Auto-updatable views might already be kind of hacked in previous versions, but now we have this cool feature made by default. Well, such views have to be quite simple, they need to have only one table in FROM clause, they can't modify relation using clauses like DISTINCT or UNION, but still cool, he? For more complicated views you can use INSTEAD OF triggers.

Event triggers allow you to define triggers as a reaction for some DDL query, i.e for queries like CREATE table. This can be used for some complicated settings of users, privileges and so on, where default settings doesn't work sufficiently.

New command COPY can be used for faster import/export data into/out of database, where there is a file or even application (using PIPE COPY) on the other end. The command is executed with rules that postgresql's superuser has and yes, only the superuser can do this, so nothing bad should happen. Another modification of COPY command is FREEZE, which means that constant records get frozen during copying, instead of running quite slow VACUUM FREEZE.

Performance is not without change

Since version 9.3 you can use GiST indexes for unlogged tables, which don't use transactional log and thus are not persistent during crash. Another new indexes, this time for regular expressions, will be surely beneficial for performance of many applications.

Performance improvements can be noticed also during numeric operations, parallel pg_dump, working with new range data type (which was introduced in 9.2), where upstream improved performance using histograms, and the same data type can be made faster using SP-Gist indexes, which was not possible before.

Other performance changes are introduced in shared memory, mmap is now used for implementation; large objects can be now 4TB large, while until now it was only 2GB. Locks can now use limited duration, which can be explicitely specified.

Checksums of data pages are not a performance improvement, rather the opposite, but some paranoid application can benefit from them. For such applications we can also use new style of warning messages, which now include easily parse-able items like SCHEMA NAME, TABLE NAME, CONSTRAINT NAME or LOCATION.

Something for plugin writers

Foreign Data Wrappers (FWD) bring new way how to work with other data than classic PostgreSQL data stack, while the data doesn't need to be only different PostgreSQL databases. We can get or even store data from/into almost any data storage, if technology allows it. New version of PostgreSQL brings also a new API which offers a way how to write new background processes, so called Background worker process. What this will mean in practice we'll see in the future, because there are no other plugins out there so far.

Another new way how to interract with PostgreSQL daemon is offered by pg_xlogdump, which basically prints transactional log in an easily parse-able format. What can we do with it? It seems it is limited only by your imagination.

News in SQL queries writing

I find it hard to explain what new LATERAL JOIN means, so I'll borrow quite easy description from [1] -- Put simply, a LATERAL JOIN enables a subquery in the FROM part of a clause to reference columns from preceding items in the FROM list. Deeper description is beyond this post, so feel free to use upstream's documentation [2].

First steps for supporting JSON data type were introduced in previous version, but now we have finally quite enough to work with it in real application. The thing is that the new version brings possibility to use agregation or we can parse JSON values now, which means we can use only some values from the JSON document. JSON can be also converted to record data type and also some basic values can be easily converted to JSON document. When working with arrays, we can use new functions array_remove and array_replace.

Some bonuses extra

Replication can be now set so that after a master server gets lost, new master can be set from one of the remaining slaves, while until now we had to set all slaves from scratch. PostgreSQL is therefore a bit closer to cloud-oriented database system, while automatic recovery is crucial in such applications.

Not only during systemd boot, where services get run in parallel if possible, we need to know that some service is ready. PostgreSQL now includes a new utility pg_isready, that can be easily used in these cases. It doesn't do nothing more than telling that a PostgreSQL daemon is ready to accept connections. But believe me, it'll help not only package maintainers a lot.

There are some news in the console psql as well -- back-slash commands \gset and \watch. Server now offers possibility to listen on more unix sockets and option unix_socket_directory is now renamed to unix_socket_directories. Well, this last feature is quite important for me personally, since I'm very proud to be recognized as an author of this patch. Well, I should admit that final edits were done by Tom Lane, the most active upstream committer. Anyway, great to see my name in such wonderful project.

And since the year 2014 is here for some time, I'd like to wish all guys involved in the PostgreSQL upstream to have wonderful peaceful time and get much energy for PostgreSQL 9.4. Thank you for all your work during the year 2013.

The article is based on the following articles:
[1] http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.3
[2] http://www.postgresql.org/docs/devel/static/queries-table-expressions.html#QUERIES-LATERAL
[3] http://www.postgresql.org/docs/devel/static/release-9-3.html
[4] http://www.root.cz/clanky/novinky-v-pripravovanem-postgresql-9-3/