nyuuzyou/wikis huggingface dataset:
Performance QA Testing

This page centralizes the efforts on performances QA testing: available hardware, available tools, continuous benchmarking effort...
The PostgreSQL Performance lab is being created to allow community members of the Open Source database PostgreSQL to have enterprise class hardware to test on.
The testing that will occur includes industry standard workloads such as OLTP, DSS and BI. Furthermore we will also use the hardware for other practical and customer oriented testing to improve scalability (processor utilization, i/o, load balancing, etc.) and managing large data sets (loading, backups, restores, replication, etc).
=== Donations ===
For donation inquiries, please contact Funds Group <funds-group @t lists.postgresql.org>.
== Tools ==
* Former OSDL work: Database Test Suite.
* pgbench-tools from Greg Smith. See Regression Testing with pgbench.
* Bristlecone from Continuent
* Tsung load injector allows to define sessions (containing queries and thinktime, etc) and replay them with very high concurrency setup. Can use many loading nodes at a time, multi OS support (written in erlang, extensible in this language)
* Tsung Plotter plots several tsung runs onto the same graphs set, for easy comparing. Uses python and matplotlib.
* Tsung DBT2 Implementation (tsung module in erlang), WIP, to get published asap.
== Ideas ==
* look into sysbench - it has some issues with locking on postgresql but at least read-only it seems to work fine. See SysBench for more info.
* collecting all the various small samples and testcases posted over the last few years on -performance, -hackers & -bugs and put them into a test set
* consider doing tests using pgbench -M (simple|extended|prepared) to catch regressions in one of those modes
* resurrect Jan Wiecks tpc-w implementation available on PgFoundry
* add full text search benchmarking by using ftsbench from teodor
* XML benchmarking ?
* investigate QuickCheck and http://advogato.org/person/nconway/diary/23.html
* Implement the Star Schema Benchmark.
== Datasets ==
see the sample databases page for some free datasources
== Information ==
* In depth performance articles on PostgreSQL
* DL380 Tuning Guide
* Videos on Performance and other topics
* Performance measurements between load and filesystems (Linux)
Category:Benchmarking
Permutations

Generation of all permutations of a given array (in this example, of 6 elements):
SELECT (WITH RECURSIVE r(n,p,a,b)
 AS (SELECT i, '{}'::integer[], ARRAY[1,2,3,4,5,6], 6
 UNION ALL
 SELECT n / b, p || a[n % b + 1], a[1:n % b] || a[n % b + 2:b], b-1
 FROM r
 WHERE b > 0)
 SELECT p FROM r WHERE b=0)
 FROM generate_series(0,6!::integer-1) i;
Wrapped in an SQL function:
CREATE FUNCTION permute(anyarray)
 RETURNS SETOF anyarray
 LANGUAGE SQL IMMUTABLE
AS $f$
 SELECT (WITH RECURSIVE r(n,p,a,b)
 AS (SELECT i, $1[1:0], $1, array_upper($1,1)
 UNION ALL
 SELECT n / b, p || a[n % b + 1], a[1:n % b] || a[n % b + 2:b], b-1
 FROM r
 WHERE b > 0)
 SELECT p FROM r WHERE b=0)
 FROM generate_series(0,(array_upper($1,1))!::integer-1) i;
$f$;
The logic behind this is as follows: the recursive subselect implements the variable-bit-encoding algorithm for generating the n'th permutation of a list (essentially converting n into the factorial base); the column p is the output generated so far, a is the array items not used so far, and b is the current base. At each recursion level, one element is chosen and appended to p, removed from a, and the base decremented. Recursion ends when b=0, and that row contains the desired result.
Iteration of this subselect for values over [0..n!-1] generates all permutations.
Category:SQL
Peru: PostgreSQL Day 2007

Introducción a PostgreSQL
Estandar SQL 99
Comparativas de PostgreSQL vs otors DBMS Libres
Instalación de PostgreSQL en Windows, Linux (Debian) y Bsd (FreeBsd)
Administración de PostgreSQL en diversas plataformas (GUI, Consola y Web)
Casos de éxito de uso de PostgreSQL
Category:PostgreSQL Events
Per-user log settings

==Can we set log_statement on a per-user basis?==
e.g.
 ALTER USER [user] SET log_statement TO [none|ddl|mod|all];
Objective is to override the global setting in postgresql.conf; for example, we are required to log all activity from one set of users, but not others, and don't want our logs to be bigger than necessary.
Discussion with some devs and the docs imply that we can do this:
* http://postgresql.1045698.n5.nabble.com/per-user-logging-td1848980.html
* http://www.postgresql.org/docs/9.0/interactive/sql-alteruser.html
* http://www.postgresql.org/docs/9.0/interactive/sql-alterrole.html
* http://www.postgresql.org/docs/9.0/interactive/sql-set.html
Unfortunately, we can't get this to work. Overview of tests below.
So:
* is this a bug in that it should be allowed, but isn't?
* or, is this expected behavior in that it's a "session" setting, and doesn't affect the unprivileged user who's setting has been changed (because it's not that user's session?)
* or, is this a bug in the tab completion? (It would be better if we'd give an error if this isn't allowed.)
* or incomplete docs?
* or we're just doing it wrong?
===Additional questions:===
* if this worked, how would we find out which user has which setting?
* are there other settings that *do* work?
==Initial tests==
Here is the SQL I ran as an unprivileged user:
 -- this should appear under 'ddl' level
 CREATE TABLE testy
 (id serial primary key,
 name text);
 -- this should only appear under 'mod' level
 INSERT INTO testy (name)
 VALUES
 ('gabrielle'),
 ('selena'),
 ('dan');
 -- this should only appear under 'all' level
 SELECT * FROM testy;
 -- this should only appear under 'mod' level
 DROP TABLE testy;
Test 1:
 log_statement = 'mod' #in postgresql.conf
 postgres=# ALTER USER gabrielle SET log_statement TO 'ddl';
 LOG: statement: ALTER USER gabrielle SET log_statement TO 'ddl';
 ALTER ROLE
This led me to believe I was successful setting this param.
However, the user's ddl and mod statements were logged; I expected only the ddl statement.
Same results setting user's log_statement to none and all.
Test 2:
 log_statement = 'all' #in postgresql.conf
This time, all the user's statements were logged, regardless of what the user's log_statement was set to. IOW, same results as before.
===Additional thing to check, per Rob Treat's request:===
 gabrielle=> ALTER USER gabrielle SET log_statement TO 'mod';
 ERROR: permission denied to set parameter "log_statement"
So, the user can't change it for herself. However, the tab completion implies it's an option. :)
Category:Howto
Pgbench

==pgbench==
pgbench is a popular benchmarking tool used by many developers and hackers to do quick performance test with PostgreSQL on a system setup.
On this page are recommendations from community on best ways to test with pgbench on a relatively bigger scale so that the frame of reference is consistent for the rest of the community reading into the results of pgbench
==pgbench options==
* -s = At minimum should be greater than the number of concurrent clients (-c) option and typically be bigger so as to be more meaningful
* -c = Generally pgbench client becomes cpu limited somewhere between 150-250 concurrent users. Also using less than 2 will not generally show contentions in lock much
* -t = Should be reasonably high. For example "HOT" benefit of limiting bloat in 8.3 is not clearly visible if the benchmark ran for short duration of time
==Additional helpful information about parameters==
* Determining the right pgbench database size scale (archived article from 2009)
==postgresql.conf parameters which need to be bumped up from defaults==
* wal_buffers 
* shared_buffers
* max_connections
==Setup Tuning==
For a relatively bigger setup, it is recommended to run pgbench client (the program pgbench itself) from a separate system than the system being tested. This would mean that network between the two system needs to be optimized. Monitor pgbench client itself to see that it does not become CPU saturated process (either via top or prstat -am) 
==Disk Tuning==
* Separate out Log (pg_xlog) to different file system)
== install note ==
In the 9.1 package install for ubuntu pgbench is not wrapped for user, you can use the following to make it accessible
sudo ln -s ../share/postgresql-common/pg_wrapper /usr/bin/pgbench
Category:Benchmarking
Pgbench shell command

These shell-linked features were submitted at PostgreSQL's Commit Fest for 8.5 version in September and November 2009.
== \setshell option ==
=== Description ===
This function permits to set a parameter as an output of a shell or C script.
*Details of this feature
**For a given transaction, it is possible to launch a customized script to set as the user wants a parameter for this transaction. pgbench takes the first output from the script and uses it as a parameter. If the output is not an integer, an error is returned and the client process stops. the same error is returned if commands are too long. 
**As a default option, the maximum size of those commands is limited to 256 characters
**Please note that the number of arguments for the script is on the contrary not limited
**The default prototype can be defined as "\setshell parameter_name scripts_name scripts_options"
*This feature can play an essential role in using statistical laws instead of the current \set and \setrandom options implemented so as to simulate the case of a group of accounts being used more than others for instance. The user is also completely free in setting parameters without having to modify pgbench's code.
=== Application example ===
Possibilities are multiple, applicable to all the existing statistic distributions: Preto, Gaussian or Poisson.
It only depends on the user's needs.
== \shell option ==
=== Description ===
This function permits to launch shell commands in a transaction file.
*Details of this feature
**For a given transaction, it is possible to launch a shell command by preceding the command with the prefix "\shell". Please note that it is even possible to launch such commands between to common SQL sentences also.
**By configuration default, shell commands are limited to 256 characters.
=== Example of 2PC transaction transaction ===
*This transaction is a 2PC generating a random prepare ID
\set nbranches :scale
\set ntellers 10 * :scale
\set naccounts 100000 * :scale
\setrandom aid 1 :naccounts
\setrandom bid 1 :nbranches
\setrandom tid 1 :ntellers
\setrandom delta -5000 5000
\setrandom txidrand 0 10000
START TRANSACTION;
UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;
UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;
INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
PREPARE TRANSACTION ':txidrand';
\shell ls -ll >> /tmp/log_data`date '+%Y%m%d'`_`date '+%k%M'`
COMMIT PREPARED ':txidrand';
Between prepared and commit statements, the user can scan pg_twophase so as to see the size of 2PC state files.
=== Example of common transaction === 
*As an possible application, it is possible to have a look at 2PC state files in pg_twophase with a simple ls -ll.
Begin;
[Transaction instructions]
Prepare transaction ‘TXID’;
\shell ls ~/pg_twophase;
Commit prepared ‘TXID’;
Category:HowtoCategory:Benchmarking
PGCAC Board Meeting 2015

= PostgreSQL Community Association of Canada Meeting 2015 =
The PGCAC is a Canadian Non-profit which holds the PostgreSQL trademark, domains, and certificates in trust for the community.
This meeting will take place during the pgCon unconference. During the meeting we will discuss the finances and actitivities of the PGCAC for the previous year. Non-board members will be allowed to attend, but not to speak.
Required attendees: Josh Berkus, Chris Browne, Steve Singer, Dan Langille, Dave Page
PgCluster

== Project Overview ==
PGCluster is a multi-master and synchronous replication tool of PostgreSQL based on shared-nothing architecture, developed by Atsushi Mitani and PGCluster developer's team.
== Project Status ==
Status: Production/Retired
The lead developer of pgCluster has moved on to pgCluster2, and all development on pgCluster has halted. While pgCluster's code is not fully mature, it is being used in production at a few sites.
Archived project code is available in PgFoundry archives
== Project Contacts ==
* Atsushi Mitani, original developer.
== General Information ==
* Scalability: 3-6
* Read Scaling: some
* Write Scaling: negative scaling
* Triggers/procedures: Yes
* Parallel Query: No
* Failover/HA: Yes
* Online Provisioning: with issues
* PostgreSQL Upgrades: No
* Detached Node/WAN: No
* PostgreSQL Core Modifications Required: Yes
* Programming Languages: C
* Licensing: OSS, BSD license
* Complete Cluster: Yes
== Clustering Model ==
General Model: Synchronous multi-master binary replication with load-balancing and automated failover
== Use Case ==
* Multimaster: all nodes appear identical, without lag
* Rapid failover
* No single point of failure (when fully implemented)
The primary feature of pgCluster is preventing data loss or the system going offline. All nodes are synchronous, and in a 6-server configuration any machine may be lost with no loss of data. It is intended to supply some read scaling as well, but does not in practice for most users.
Intended Features:
# HA
#* DB Service does not stop during fail over, auto recovery, dynamic add and upgrade server etc.
#* No Single point of failure.
#* Auto adjust data transfer speed based on network bandwidth.
#* Load balance server and monitoring server are including.
# Synchronous multi‐master configuration
#* Any update to any master is visible from other masters immediately.
#* No change in transaction handling.
#* Value of now() and random() are replicated.
# Based upon PostgreSQL
# Same API to Apps. as PostgreSQL
== Drawbacks ==
* Solution not mature
* Negative scalability
* Lack of online provisioning
* Expense/complexity of setup
* Fork of PostgreSQL
pgCluster had a number of architectural issues which caused its primary developer to start over with a new approach before it ever reached real maturity. Thus, a large number of issues and corner cases are simply not handled, and and many major features such as online provisioning were never completed.
A complete pgcluster requires at least 6 machines (3 database servers, 2 load balancers and a monitoring/failover controller). Also, it requires modifications to PostgreSQL core, so the Postgres version of pgCluster is always behind the mainstream version.
== Project Sponsors ==
== Support ==
Cybertec.at offers support for pgCluster through their "CyberCluster" product.
== Other Information ==
Category:Replication
Category:Clustering
PgCon 2009 Lightning talks

== Schedule of Talks ==
1. postgresqlfs (demo!) Peter Eisentraut
2. PostGIS Leo Hsu and Regina Obe
3. LFI: Using Fault Injection to Test PostgreSQL Emmanuel Cecchet, Aster Data Systems
4. EPIC: Testing inside the database Robert Brewer, aminus.org
5. Monitoring PostgreSQL from the JBoss Application Server Jesper Pedersen, JBoss a division of Red Hat
6. Page Costs and the Buffer Cache, Robert Haas 
7. Updated Postgres Tools from myYearbook.com Jonah H. Harris, myYearbook.com
8. DTrace Probes in PostgreSQL Robert Lor
9. New TIGER Geocoder Stephen Frost, Noblis
10. PGQ, PDQ Dimitri Fontaine Dimitri's blog
11. How not to Review a Patch Joshua Tolley, End Point
12. Primary Keyvil Josh Berkus, PostgreSQL Experts
== Proposed/Backup Talks ==
"Drivolution: Storing drivers in the database" Emmanuel Cecchet, FrogThinker
"Dolly: Cloning instead of pg_dump/pg_restore" Emmanuel Cecchet, UMass Amherst
Add your name here if you'd like to give a talk.
Category:PostgreSQL Events
PgCon 2009/Steven Singer Openstreetmap notes

= Open Street Map =
* Project to create a free, editable map of the world
* Anything on the map is editable in a *wiki* fashion
* All data is licensed under the CC-by-SA license (may change to an shared database license (?))
* Basic freedoms to share and copy are all available through Open Street Map: can completely reuse their database and render maps in many ways, a lot of reuse examples in embedded systems :)
Category:PostgreSQL Events
Category:Open Street Map
PgCon 2010 Lightning talks

Lightning talks for PgCon 2010!
# PostgreSQL Developer Meeting in Five Minutes - Bruce Momjian
# -Slony 1 to 2.0 Steve Singer
# PostgreSQL and Twisted - Jan Urbański
# The FlyMine Project - Matthew Wakeling
# Enhanced Tools for PostgreSQL - Tomonari Katsumata
# Servoy - Christophe Pettus
# Tail_n_mail - Greg Sabino Mullane
# GSOC - Robert Treat
# pgstaging - Dimitri Fontaine
# Serializable Transaction Isolation - Kevin Grittner 
# 10 ways to wreck your database - Josh Berkus
Category:PGCon
PgCon 2011 Lightning talks

== Talks ==
* Gavin Roy - Elephants and Rabbits
* Andrew Dunstan - Build Farm Changes
* David Fetter - Lightning
* 
* Gabrielle Roth - pg_pronounce
* 
* Devrim Gunduz - PostgreSQL with RedHat Cluster Suite
* Christopher Browne - Slony
* Josh Berkus - Scale Fail
* Jan Urbanski - PL/Python
* Jehan-Guillaume de Rorthais - pgshark
* Aaron Thul - Drinkers guide to PostgreSQL
Category:PGCon
PgCon 2011 PL Summit

What: PL Summit
Where: PgCon, Ottawa
Date: Saturday May 21, 2011
Time: 9:30am - 5pm
Room: DMS 1110
= Agenda =
9:30am: Introductions
10am: State of PLs
* Jan Urbański - PL/Python
* Andrew Dunstan - PL/Perl and PL/TCL
* Luis Carvalho - PL/Lua
* Tim Child - PL/OpenCL
* Alexey Klyukin - PL/PHP
* David Fetter - PL/Parrot
10:30am: PLs as Extensions - Dimitri Fontaine
10:45am: Discussion of documentation for PL supported features
http://wiki.postgresql.org/wiki/PL_Matrix
http://wiki.postgresql.org/wiki/PL_Features_Matrix
11:30am: Discussion of improvements to PostgreSQL PL interface
12:30am: Break for lunch
1:30pm-3:30pm: Small groups for detailed discussion and hacking
By 4pm: retire to the pub. :)
= Notes =
9:30am: Introductions
* David Fetter: PL/Parrot, 
* Jan Urbanski: PL/Python, psycopg2, 
* Jan Wieck: its his fault for PL language handler, ported pg 4.2 TCL interpreter (about 6.4) PL/TCL, and then THEY wanted something without an external library - so that became pl/pgsql
* Steve Singer: patched a few, reviewed the pl/python, user
* Andrew Dunstan: PL/Perl, touch the other core PLs
* Tim Child: developing PL/OpenCL, speaking AMD fusion summit in Seattle
* Heikki: work mostly on backend, also work on commercial advanced server has a PL/SQL compat library :)
* Dimitri: Worked on extension feature for 9.1 - crossroad for extension and languages :)
* Brian Decker: user :)
* Aaron Thul: a user, trying to install all 30
* Tarmo: user :)
* Luis: Developer of PL/Lua
* Peter: user, speaking in eu on Pl/python soon 
* Josh Berkus: write lots of pl/pgsql, perl, python every year 
10am: State of PLs
* Jan Urbański - PL/Python
* Andrew Dunstan - PL/Perl and PL/TCL
* Luis Carvalho - PL/Lua
* Tim Child - PL/OpenCL
* Alexey Klyukin - PL/PHP
* David Fetter - PL/Parrot
10:30am: PLs as Extensions - Dimitri Fontaine
10:45am: Discussion of documentation for PL supported features
* PL_Matrix
* PL_Features_Matrix
11:35am: DDL Trigger usage - Jan Wieck
11:50am: Trusted vs Untrusted - Steve Singer 
12:15am: Discussion of improvements to PostgreSQL PL interface
12:30am: Break for lunch
1:30pm-3:30pm: Small groups for detailed discussion and hacking
By 4pm: retire to the pub. :)
= Related wiki pages =
PL_Matrix
PL_Features_Matrix
= Attendees =
Selena Deckelmann
Leon Starr
Dimitri Fontaine
David E. Wheeler
David Fetter
Aaron Thul
Alexey Klyukin
Brian Decker
Jan Urbański
Steve Singer
Alvaro Herrera
Jan Wieck
Heikki Linnakangas
Andrew Dustan
Category:PGCon
PgCon2012CanadaInCoreReplicationMeeting

= PostgreSQL In-Core Replication meeting, pgCon 2012 =
== Time and Place ==
Wednesday, May 16th, 6pm to 10pm
Ottawa somewhere, room TBA
== Agenda ==
Draft agenda follows. Please let me know of any contributions/changes to the agenda you have:
# Discussion of Multi-Master Theory (Simon)
# Demonstration of prototypes (Andres)
# Performance comparisons
# Use Cases
## My use case (Keaton)
## Social Media use case (Simon)
## Other User Requirement stories (volunteer...)
Broad and general discussion throughout. Notes and actions will be taken. Volunteers for tasks welcome.
The meeting will be from 6pm to 10pm, with various forms of food and possibly a drink or two, sponsored by 2ndQuadrant.
== Attendees (alphabetical) ==
# Keaton Adams
# Josh Berkus (prefer vegetarian)
# Daniel Farina
# David Fetter
# Dimitri Fontaine
# Andres Freund (vegetarian)
# Peter Geoghegan
# Magnus Hagander [CHAIR]
# Jim Mlodgenski
# Bruce Momjian
# Jim Nasby (plus guest)
# Michael Paquier
# Simon Riggs
# Steve Singer
# Mason Sharp
# Mark Sloan
# Greg Smith
# Koichi Suzuki
# Hannu Valtonen
# Peter van Hardenberg
# David Wheeler
...
Meeting limit about 20-25 people
=== Joining the Meeting ===
If you will be able to attend, please email Simon (simon@2ndQuadrant.com) with the following:
* Your Name
* What pizza topping you like
and please come armed with detailed information about your future replication requirements.
=== Presentation/Results ===
The presentation is linked from the BDR Project article.
Category:PGCon
Pgcon2014unconference

= pgCon 2014 Unconference Day =
The PostgreSQL Community is having our second Unconference Day at pgCon 2014 in Ottawa! This event, sponsored by Salesforce.com, extends pgCon and gives us additional opportunities to share, collaborate, and discuss about PostgreSQL development, hacking, usage, and community.
For more about unconference days and why we're doing this, see the Unconference Day FAQ.
If you are interested in holding a session, or you want someone else to do a session on a particular topic on the Unconference day, please add your proposal to "session ideas" below.
== Basic Information ==
pgCon Unconference Day
Saturday, May 24th
10am to 5:30pm
Rooms: TBD
Lunch with Pizza Provided
Sponsored by Salesforce.com
== Unconference Schedule ==
Important: if you are planning to propose as session, you need to be at the unconference at 10am sharp!
Time 	Room1 (202) 	Room2 (221) 	Room3 (265) 	Area4
10am 	Session Pitches and Scheduling - Coffee and snacks	 	 	
11:45AM 	pluggable storage engines, FDW, columnar storage 	 	 	Open Hacking
12:00 Noon	In core logical replication 	More granular superuser permissions model 	PG.conf 	Open Hacking
2:30PM 	VODKA && JSQuery 	SysAdmin Best practices 	Business intelligence 	Open Hacking
3:30PM 	overview of source code 	extension system wishlist 	wait interface/performance events	Open Hacking
4:30PM 	Spillover Session 1 	Building appliances with PstgreSQL (Clusters, HA etc)	Bitmap only scan 	Open Hacking
== Session Ideas ==
Please post your ideas for Unconference Sessions below. This could include either sessions you want to lead, or sessions you wish someone else would lead. Sessions which are proposed in advance and receive upvotes here on the wiki will be given priority when it comes time to pitch them.
Please use this template:
 === My Session Idea Name Here ===
 
 Proposed By: 
 
 Session Leader: 
 
 Description: 
 
 People who want to attend this session please put your names below:
 
 *
Category:PGCon
Category:Unconference