PgCon 2020 Developer Unconference

This is the PGCon 2020 Unconf Page
== Unconference schedule by Time and Room ==
Time 	Stream 1 	Stream 2 	Stream 3
Tue 11:15-12:15	On-disk Encryption 	Edge cases of synchronous replication in HA solutions Notes 	The title is "evolving data loading for data warehousing".
Tue 12:15-13:15	Lunch 	 	
Tue 13:15-14:15	Table AM Notes 	SQL standard stuff 	Which new illustrations do we need in documentation?
Tue 14:15-14:30	Coffee Break 	 	
Tue 14:30-15:30	Zedstore 	Competitive Benchmarking & Building a new open source benchmarking kit - Etherpad	Parallel grouping sets and refactoring nodeAgg.c
Tue 15:30-15:45	Break 	 	
Tue 15:45-16:45	Adding async / direct IO support to PG	Schema Management/Migration 	install and initial setup experience
== Notes/Minutes ==
=== Data Loading for Data Warehousing ===
- There was a brief presentation indicating areas where there have been issues
- issues emerge of reliable loading with ever increasing data volumes
==== Features NOT implemented yet ====
* parallel load, insert via multiple processes
* DATE cache
** cache to reduce cost of date conversion
* UNLOGGED - without WAL
* DIRECT
** format input data into Postgres blocks, write them directly
** no lock contention
** no search for available blocks
* BADFILE
** detect invalid input, constraint violations
** write data to a file
* ROWS
** execute a commit every /n/ rows during COPY to avoid all-or-nothing
** if error occurs, continue from last commit
* ERRORS
** various mechanisms/approaches
*** avoid (e.g. - validate before trying to load)
*** drop errors into a file (if you can validate this)
*** COMMIT once in a while so that parts of the data get in and we don't have lots of dead tuples
==== pg_bulkload ====
* DIRECT
* PARALLEL
* PARSE_ERRORS
* DUPLICATE_ERRORS
* on error stop
* on error resume next
* workers :: number of threads
* concurrency :: how many tasks to handle data
* retry by cutting input into 25K row groups to localize error tuples
==== Ideas ====
* report error lines and continue
** concern of subtransaction overhead
* batch update of indexes
** ACCESS EXCLUSIVE lock for a new relfilenode
* Parallel COPY (ongoing)
** COPY parser cannot be parallelized, should be replaced
** ring buffer to indicate split boundary of start position
*** allows workers a place to start reading data
*** there's a performance bottleneck here
** WAL writes may be the next bottleneck
** For DW, avoiding WAL may be worthwhile
* COPY UNLOGGED
** No WAL
** No copy of existing data
** under streaming replication, recreate standby based on backup /after/ data loading
** to recover data, empty and recreate the relation
==== Idea that Slony team had ====
* create a "smarter COPY" protocol
* this would allow the client to stream data, with the various merits of COPY
** A single stream of data, but in this case, where that stream contains data for many tables
** The stream effects flow control and avoids the need for clients to expressly allocate memory for the data being streamed
* This stream would split data in 2 fashions
** By table :: want to load multiple tables
*** initial protocol allows specifying tables to load
*** Table, action, columns involved
 | ID | FQTN | Action | WHERE | Col 1 | Col 2 | Col 3 | ... |
 |----+------+--------+-----------+-------+-------+-------+-----|
 | 1 | tbl1 | Insert | | col1 | col2 | col3 | |
 | 2 | tbl1 | Delete | col1 | | | | |
 | 3 | tbl1 | Update | col1 | col2 | col3 | | |
 | 4 | tbl1 | Update | col1 | col4 | | | |
 | 5 | tbl2 | Insert | | col1 | col2 | col3 | |
 | 6 | tbl2 | Delete | col2,col3 | | | | |
** Then there would be requests to inject data
 | ID | Where | Data |
 |----+-------+---------|
 | 1 | | 1,d1,d2 |
 | 1 | | 2,d2,d4 |
 | 2 | 1 | |
 | 5 | | 1,2,3 |
 | 6 | 2,3 | |
* The "New COPY" stream would consist of a series of requests shaped like this latter part; the stream of 5 requests are:
** Adding a new tuple for tbl1
** Another new tuple for tbl1
** Deleting the first tuple
** Adding a tuple to tbl2
** Deleting the tuple from tbl2
* Over time, it might be necessary to add/remove additional entries to the "table control" table; several possibilities emerge:
** Maybe the "table control" is handled as a regular table that is used as a parameter for NEW_COPY
*** create table copy_control (id serial, fqtn name, action text, where_cols string[], data_cols[]);
*** INSERT into copy_control() values (stuff);
*** NEW_COPY using copy_control from '/tmp/file-that-is-the-stream-of entries'
*** This approach requires knowing all of the tables and the shapes of requests on those tables in advance
*** If a new table comes along, it is probably necessary to terminate the stream, add new tuples to copy_control, and start another NEW_COPY stream
*** Using a table to carry the configuration is nicely "relational"
** Alternately, maybe the NEW_COPY protocol would include "metadata" requests
*** An "ID 0" request would be used to submit the metadata to indicate the shape of a new data injection request
*** That makes it natural to add new metadata while the NEW_COPY stream is running
There is a lot of handwaving here; it seemed like an interesting idea, perhaps more generalized than what was actually needed.
In view that this mechanism would have required an enormous amount of integration effort to get it into a new version of PostgreSQL, and would have been no help at all for earlier versions, the Slony team did not proceed with implementation efforts. It is likely that the very same issues would adversely affect others that might like this sort of functionality.
==== Present Pain Points ====
* Parser for COPY does perhaps 100x as much work to find end of record as it needs to do, it is getting to be a visible bottleneck
* Lots of places need to reproduce foreign key logic, index management
* Very nice if we could have AMs to do new things
** Just do partitioning
** Just do bulk updates to indexes
=== Which new illustrations do we need in documentation? ===
State of Art and Problems:
* We do have a way how to add pics into documentation: 
* But since we have it, we only have 2 very basic things: page structure and GIN diagramm
Ideas and TODO:
* Ilya will publish a list of Would-be-nice-to-have pics fro discussion
=== Schema Management ===
Only a small handful of attendees for this; it mostly reverted into discussions of "war stories"
==== Some approaches ====
* Old Timey: Throw SQL files into a directory
* Terrible Old Timey: Include some of the schema as queries embedded in a Microsoft WORD document, but probably not all of the schema...
* Newer Timey: Throw a shell script in as well
==== More modern tools ====
* Sqitch
* Liquibase
* Flyway
* DBSteward
==== Pains ====
* Sqitch
** trap is that either they must do things non-transactionally, and are error prone
** or via transactions, so very lock-happy
* Migrations will happily run terabyte index work
* Slony with EXECUTE SCRIPT where that has to be hyper customized
** Adding in a new table on the master versus replicas may have lags where app needs to be compatible with new and/or old versions
** documenting what tables are going to have potential for pain due to locks 
==== DB2 ESQL: A Curious Answer ====
* There some means on DB2 where if the program discovered that the schema in the target environment differed from what was apparently captured in the compiled code, then "KABOOM!!!!"
* It appears some aspects of the schema were carried inside the program, presumably compiled into object code by the ESQL compiler
* It's a very good question to ask, "how much schema?" was captured; answer is unknown
The interesting thing to do as consequence, in modern systems; it would be neat to have programs assert things about the schema that they need. The popular tools (Sqitch, Liquibase, Flyway) tend to use a version number as a surrogate for "do we have the right schema in place"
==== Other approaches ====
Other approaches include capturing some aspects of shapes of tables
* Which tables?
* Does it need to capture more about the tables
** Columns
** Data types
* is there a compact representation that we could run a runtime test on???
* given pl/pgsql now can manage COMMITs?
** does this change anything?
* someone added a column and want an index on that column
** what then happens?
* if the table is very big, and this needs to rewrite everything... ouch...
* UPDATE ... LIMIT ???
* nice to have throttling? Could SQL have a keyword added to support automatically doing a COMMIT every 50000 tuples?