15 Jun 2018 cbbrowne   » (Master)

PGCon 2018 Unconference

I attended the PGCon 2018 Developer Unconference, on May 30, 2018, which had, as always, a goodly mixture of discussions of things in progress.

Schema Deployment

I proposed a discussion of schema deployment methods; it fell just short of attracting enough interest to get a room. A few people asked me in passing about what I’d had in mind; I’ll point at the Github project, Mahout, which indicates the set of “agenda” that I have in the matter.

Mahout is a lightweight tool (e.g. – needs Bash, psql, and the most sophisticated shell thing needed is tsort), which, being PostgreSQL-specific, could readily be extended to support PG-specific tooling such as Slony.

In the documentation for Mahout, I describe my goals for it, and the things I hope it solves more satisfactorily than some of the other tools out there. I’d be particularly keen on getting any input as to useful purposes that point to changes in functionality.

JIT Compilation

– Andres Freund
– Biggest benefit comes in aggregate processing, that is where there is huge computational work
– planning is presently too simplistic
– no cacheing of compiled JIT code
– code is somewhat slow in some cases
– JIT compilation of COPY would probably be helpful
– COPY, cuts out a lot of presently kludgy C code
– Sorts
– hashing for hash aggregates and hash joins, there is already a prototype…
– interesting idea to capture generated code in a table (Jeff)
– either C or LLVM bitcode
– bitcode may be architecture-dependent
– want better EXPLAIN [ANALYZE] output (Teodor)
– better code generation for expression evaluation (Andres)
– Presently no Windows support
– Once you have cacheing, OLTP performance will improve, but we’re certainly not there now
– local cache, initially; eventually a global cache
– LRU cache
– If I generated much the same code last time, can reuse the compiled code
– would move some work from executor to the planner, but this is a pretty deep architectural change for now
– can definitely get degenerate optimization cases; gotta watch out for that
– generated code is way denser than the executor plans, so there are cases of significant improvements in memory usage
– Incremental JIT compilation (do it in background, after starting query, but before execution)
– impact of threading? Worker backends + data marshalling?

Connection pooler – Odyssey

– Multithreaded connection pooler and request router
– Open source release
Yandex/Odyssey @ GitHub
– Multithreaded via worker thread
– each thread arranges authentication and proxying of client-to-server and server-to-client requests
– worker threads share global server connection pools
– SSL/TLS supported
– tracks transaction state
– can emit CANCEL on connections and ROLLBACK of abandoned transactions before returning connection to pool
– Pools defined as pair of DB/User
– Each pool can authenticate separately and have different mode/limit settings
– UUID identifiers for each connection
– Log events and client error responses include the UUID


– Greg Stark
– Splunk with alerts based on log data
– Nice to have things actively exported by PostgreSQL
– Exposing
– Aggregating
– Reporting
– Error reporting
– Log files too chatty, lot of data all together
– Could different data be separated?
– But how about when it needs to be correlated?
– Sensitive data…
– Too much parsing needs to be done
– Loading into DB makes it structured, but recursive problems, and can overload the DB
– Metrics
– start with What You Want Measured…
– Rates of many things
– vacuum activity
– WAL generation
– error rates
– index usages
– violation statistics, rates of rollbacks and errors
– Nice to have…
– pg stats with numbers of violations and contentions
– let the stats collector collect a bit more data
– connection usage statistics
– Some tools
– Jaeger – JaegerTracing
Zipkin – distributed tracing system to find latency issues in microservice architectures
Opentracing – vender neutral APIs and instrumentation for distributed tracing
– Can there be a “pg stat user table” indicating bloat information?

Query Optimization with Partitioned Tables

– Planned improvements in PG11
– Partition wise pairs
– Partition wise aggregation
– Partition pruning
– Planning time
– Runtime

TDE – Transparent Data Encryption

– Inshung Moon
– Buffer level encryption/decryption
– Per table encryption
– Perhaps should be per-column???
– 2-tier encryption key management
– Working with external key management services (KMS)
– WAL encryption
– only doing encryption on parts other than header
– Nice to have it on LOB (large objects API) too, but no easy way…
– Log file data needs to be encrypted before submission to destinations


– Encryption of indexes is troublesome
– You lose the usefulness of ordering of disk
– Table added with a per-table/column private key
– What if some data seems to be exposed? Need to generate new key
and rewrite? This would be arbitrarily expensive…
– Changing master key is easy, as long as the function for
generating the private symmetric (per-table key) is symmetric

Threat model

– Translucent Databases
– Peter Wayner
– Order preserving encryption
– Agrawal encryption scheme
Order Preserving Encryption for Numeric Data, by Agrawal, Kernan, Srikant, Xu
IBM Almaden
SIGMOD paper

Syndicated 2018-06-11 16:47:54 from linuxdatabases.info

Latest blog entries     Older blog entries

New Advogato Features

New HTML Parser: The long-awaited libxml2 based HTML parser code is live. It needs further work but already handles most markup better than the original parser.

Keep up with the latest Advogato features by reading the Advogato status blog.

If you're a C programmer with some spare time, take a look at the mod_virgule project page and help us with one of the tasks on the ToDo list!