PostgreSQL wiki - New pages [en]

Pgcon2013unconference:testing brainstorming

Dbs — Sat, 25 May 2013 18:29:08 GMT

Dbs: /* PostgreSQL testing brainstorming: PGCon2013 unconference notes */

== Build farm - composed of "animals" ==

* Currently run multiple instances per animal; each animal configures which contrib modules it builds (usually based on platform restrictions) but all tests are run
* No support for machine-to-machine testing at the moment (you can, however, run replication across two local instances)
* Peter Eisentraut had been doing a lot of work with Jenkins

Question around collecting data on where bugs are being found and fixed to determine a heat map of problem areas; discussion goes sideways about the conscious lack of a bug tracker for the project; possibility of using tagging in the bug emails to enable some level of analytics

Testing of clients (beyond psql); libpq is tested and that's what Python, PHP, Perl, etc generally build on, but there is likely not significant automated testing happening with these clients; however, that's generally considered out of scope for the PostgreSQL project proper

Automated fuzz testing of generated SQL, etc is apparently ongoing but the project only hears about it when they find a problem

== Performance farm ==

* Would like to have overall performance testing (with throughput / stress testing); Mark Wong is already doing this on a biweekly basis with DBT2 (because setup / teardown is hard)
* Would like to have individual SQL query regression testing to prevent future fiascos like the 9.2 subquery performance regression
* Only thing that's standing in the way is _time_ to write the performance testing infrastructure; hardware _is_ available (and can be dedicated for performance purposes to provide consistency)
* Build farm already has the ability to report basic profiling information, things like iozone would be useful to help filter out weirdness

2013UnconfFakingIt

Jehsom — Sat, 25 May 2013 17:50:29 GMT

Jehsom: Created page with "We discussed creating random data to fill a database that is to be used as an example. Some useful queries for generating random data: select random() from generate_series(1,3)…"

We discussed creating random data to fill a database that is to be used as an example.

Some useful queries for generating random data:

select random() from generate_series(1,3); -- Generates random numbers
select (array[...])[random() * nelems + 1]; -- Selects random elements from an array
select now() - ‘1 year ago’::interval * random() -- Gives you random dates over the last year
select uuid_generate_v4(); -- Generates a uuid

To affect a random subset of your rows, update where random() < 0.3 for example

We then decided it would be useful to have a sample database available for public consumption. There is already one example database available at github.com/pgexperts/librec, but we wanted one that implemented a lot more PostgreSQL features and had lots of random data in it. We could make one that would be used to store data about PGCon.

Demo database needs:
hstore
full text search
range type
auditing tables
foreign keys
views
roles
spatial data
functional indexes
array
constraint partitions
listen/notify

The start of this project is available on github at:
https://github.com/pvh/postgresql-sample-database

Lastly, we also discussed the desire for an extension for plotting and graphing data visually called e.g. pg_graph. One example function it might provide is something to plot x,y points using ascii art.

2013UnconfPluggableStorage

Aglio — Sat, 25 May 2013 15:58:23 GMT

Aglio: Created page with "Galy Lee from Huawei talked about their wanting a decoupled storage engine. They have several needs for special storage engine properties. They want MySQL-style pluggable stora…"

Galy Lee from Huawei talked about their wanting a decoupled storage engine.

They have several needs for special storage engine properties. They want MySQL-style pluggable storage engines. They tried to do this themselves in 9.1, but they couldn't do it themselves, because the interface wasn't clean and they couldn't get it to work.

Tom Lane: the first thing to talk about is why don't FDWs solve it? Who's going to store the data, asks Peter. We're abdicating responsibiliy, says Tom. If we go that route, we're pulling a MySQL. You could do it for some file-based mechanism where the FDW had complete control. And what about the FDW overhead? And what about transactional semantics?

Imagine a database with just FDWs and no native storage? How would it work? Peter's use case is the cloud, with horrible storage. Would PostgreSQL abdicate and just become a query broker?

Josh brought up how to we create and manage FDW tables? And what about maintenance? And security or changes to files?

How do you deal with indexes? FDWs need their own indexes. But what about GiST and GIN then?

What are we trying to replace with pluggable storage engines? We have a pluggable interface for the index, but not heap. Frost brings up lack of parallelism in FDW?

Porting the DML is hard. Like we couldn't make triggers work.

Citus is using FDWs and storing their stuff in Hadoop. They had to trim a lot of SQL though, and not support it. Likes the idea of having FDW vs. Native Data Wrappers. You need more guarantees for a NDW.

Use cases:
* Column store
* Telecom in-memory database with own MVCC mechanism and HA.
* Analytics database
* Streamining tables
* fractal index organized tables
* PostgreSQL on immutable storage
* in-memory database

Transactional semantics for Huawei database? Maybe FDW is better for them. They need special information for the planner. Need remote JOIN for FDWs.

For NDW, we need atomic commit. And index methods. And do we want each FDW to reinvent the wheel in that area? The spec is that the FDW is responsible for returning paths.

Jan gave case of compressed tables, storage engines should be lower level, like page-level. Also gave example of switching his storage to black hole for overloaded table.

Issuses with FDW as SE:
* transactions -- can't be solved without 2PC
* indexing -- can't use Postgres indexes
* performance
* backup -- would need hooks, flag for backup
* management -- would need hooks
* maintenance
* replication
* WAL/crash safety -- would require WAL API, replication API
* data types? seems easy to resolve

Questions for SE vs. FDW, set of binary choices, which can be any way:
* do your tuples look like tuples?
* do your transactions look like transactions?
* do your pages look like pages?
* does your indexes look like indexes?

Configuring planning/costs etc. on a per-FT basis is very painful. It would be better to have defaults by type. But maybe it is on the server basis. The wrapper is responsible for providing the costs.

Discussion of replacing COPY with FDWs.

Josh brough up fractal indexes case, with different licensing.

We should have a generic WAL API which allows an extension etc. to create WAL. The pages would need to look like pages, though. This limits you to specific types of storage.

We would also need a logical replication API too so that FDWs could support logical replication.

So, are FDWs suitable or not?

Could use background workers with files for in-memory database.

Much discussion ensued regarding FDW vs Native storage.

We need an API. FDW won't work for too many cases.

Oscon 2013 signup

Markwkm — Sat, 18 May 2013 00:25:21 GMT

Markwkm: fix a date

* Time slots are intended to allow people to have enough time to go to a session before it starts and to come to the booth after a session ends for people attending the conference.
* Please send t-shirt sizes and whether you prefer men's or women's cut to markwkm at postgresql.org. We will try to get t-shirts for all booth volunteers.

== Tue July 23 (17:00-18:00) (5pm - 6pm) - Opening Reception ==
* Mark Wong (markwkm at postgresql.org)

== Wed July 24 (10:00-16:30) (10am-4:40pm) ==

=== 10:00 - 11:15 ===
*
*

=== 11:15 - 12:50 LUNCH ===
*
*

=== 12:50 - 14:25 LUNCH ===
*
*

=== 14:25 - 15:15 ===
*
*

=== 15:15 - 16:40 ===
*
*

== Thurs July 25 10:00 - 17:00 (10am-5pm) ==

=== 10:00 - 11:25 ===
*
*

=== 11:25 - 12:50 LUNCH ===
*
*

=== 12:50 - 14:25 LUNCH ===
*
*

=== 14:25 - 16:00 ===
*
*

=== 16:00 - 17:00 ===
*
*

----

Thanks for participating!

[[Category:PostgreSQL Events]]