PostgreSQL 是什麼，跟 MySQL 有什麼差別

What's new in this version:

Server:
- Allow background worker processes to be dynamically registered, started and terminated
- The new worker_spi module shows an example of use of this feature.
- Allow dynamic allocation of shared memory segments
- This feature is illustrated in the test_shm_mq module.
- During crash recovery or immediate shutdown, send uncatchable termination signals (SIGKILL) to child processes that do not shut down promptly
- This reduces the likelihood of leaving orphaned child processes behind after postmaster shutdown, as well as ensuring that crash recovery can proceed if some child processes have become "stuck".
- Improve randomness of the database system identifier
- Make VACUUM properly report dead but not-yet-removable rows to the statistics collector
- Previously these were reported as live rows.

Indexes:
- Reduce GIN index size
- Indexes upgraded via pg_upgrade will work fine but will still be in the old, larger GIN format. Use REINDEX to recreate old GIN indexes in the new format
- Improve speed of multi-key GIN lookups
- Add GiST index support for inet and cidr data types
- Such indexes improve subnet and supernet lookups and ordering comparisons
- Fix rare race condition in B-tree page deletion
- Make the handling of interrupted B-tree page splits more robust

General Performance:
- Allow multiple backends to insert into WAL buffers concurrently
- This improves parallel write performance.
- Conditionally write only the modified portion of updated rows to WAL
- Improve performance of aggregate functions used as window functions
- Improve speed of aggregates that use numeric state values
- Attempt to freeze tuples when tables are rewritten with CLUSTER or VACUUM FULL
- This can avoid the need to freeze the tuples in the future.
- Improve speed of COPY with default nextval() columns
- Improve speed of accessing many different sequences in the same session
- Raise hard limit on the number of tuples held in memory during sorting and B-tree index builds
- Reduce memory allocated by PL/pgSQL DO blocks
- Make the planner more aggressive about extracting restriction clauses from mixed AND/OR clauses
- Disallow pushing volatile WHERE clauses down into DISTINCT subqueries
- Pushing down a WHERE clause can produce a more efficient plan overall, but at the cost of evaluating the clause more often than is implied by the text of the query; so don't do it if the clause contains any volatile functions.
- Auto-resize the catalog caches
- This reduces memory consumption for sessions accessing only a few tables, and improves performance for sessions accessing many tables.

Monitoring:
- Add pg_stat_archiver system view to report WAL archiver activity
- Add n_mod_since_analyze columns to pg_stat_all_tables and related system views
- These columns expose the system's estimate of the number of changed tuples since the table's last ANALYZE. This estimate drives decisions about when to auto-analyze.
- Add backend_xid and backend_xmin columns to the system view pg_stat_activity, and a backend_xmin column to pg_stat_replication

SSL:
- Add support for SSL ECDH key exchange
- This allows use of Elliptic Curve keys for server authentication. Such keys are faster and have better security than RSA keys. The new configuration parameter ssl_ecdh_curve controls which curve is used for ECDH.
- Improve the default ssl_ciphers setting
- By default, the server not the client now controls the preference order of SSL ciphers
- Previously, the order specified by ssl_ciphers was usually ignored in favor of client-side defaults, which are not configurable in most PostgreSQL clients. If desired, the old behavior can be restored via the new configuration parameter ssl_prefer_server_ciphers.
- Make log_connections show SSL encryption information (Andreas Kunert)
- Improve SSL renegotiation handling

Server Settings:
- Add new SQL command ALTER SYSTEM for changing postgresql.conf configuration file entries
- Previously such settings could only be changed by manually editing postgresql.conf.
- Add autovacuum_work_mem configuration parameter to control the amount of memory used by autovacuum workers
- Add huge_pages parameter to allow using huge memory pages on Linux
- This can improve performance on large-memory systems.
- Add max_worker_processes parameter to limit the number of background workers
- This is helpful in configuring a standby server to have the required number of worker processes (the same as the primary).
- Add superuser-only session_preload_libraries parameter to load libraries at session start
- In contrast to local_preload_libraries, this parameter can load any shared library, not just those in the $libdir/plugins directory.
- Add wal_log_hints parameter to enable WAL logging of hint-bit changes
- Hint bit changes are not normally logged, except when checksums are enabled. This is useful for external tools like pg_rewind.
- Increase the default settings of work_mem and maintenance_work_mem by four times
- The new defaults are 4MB and 64MB respectively.
- Increase the default setting of effective_cache_size to 4GB
- Allow printf-style space padding to be specified in log_line_prefix (David Rowley)
- Allow terabyte units (TB) to be used when specifying configuration variable values
- Show PIDs of lock holders and waiters and improve information about relations in log_lock_waits log messages
- Reduce server logging level when loading shared libraries
- The previous level was LOG, which was too verbose for libraries loaded per-session.
- On Windows, make SQL_ASCII-encoded databases and server processes (e.g., postmaster) emit messages in the character encoding of the server's Windows user locale
- Previously these messages were output in the Windows ANSI code page.

Replication and Recovery:
- Add replication slots to coordinate activity on streaming standbys with the node they are streaming from
- Replication slots allow preservation of resources like WAL files on the primary until they are no longer needed by standby servers.
- Add recovery parameter recovery_min_apply_delay to delay replication
- Delaying replay on standby servers can be useful for recovering from user errors.
- Add recovery_target option immediate to stop WAL recovery as soon as a consistent state is reached
- Improve recovery target processing
- The timestamp reported by pg_last_xact_replay_timestamp() now reflects already-committed records, not transactions about to be committed. Recovering to a restore point now replays the restore point, rather than stopping just before the restore point.
- pg_switch_xlog() now clears any unused trailing space in the old WAL file
- This improves the compression ratio for WAL files.
- Report failure return codes from external recovery commands
- Reduce spinlock contention during WAL replay
- Write WAL records of running transactions more frequently
- This allows standby servers to start faster and clean up resources more aggressively.
- Logical Decoding - allows database changes to be streamed in a configurable format. The data is read from the WAL and transformed into the desired target format. To implement this feature, the following changes were made:
- Add support for logical decoding of WAL data, to allow database changes to be streamed out in a customizable format
- Add new wal_level setting logical to enable logical change-set encoding in WAL
- Add table-level parameter REPLICA IDENTITY to control logical replication
- Add relation option user_catalog_table to identify user-created tables involved in logical change-set encoding
- Add pg_recvlogical application to receive logical-decoding data
- Add test_decoding module to illustrate logical decoding at the SQL level

Queries:
- Add WITH ORDINALITY syntax to number the rows returned from a set-returning function in the FROM clause
- This is particularly useful for functions like unnest().
- Add ROWS FROM() syntax to allow horizontal concatenation of set-returning functions in the FROM clause
- Allow SELECT to have an empty target list
- This was added so that views that select from a table with zero columns can be dumped and restored correctly.
- Ensure that SELECT ... FOR UPDATE NOWAIT does not wait in corner cases involving already-concurrently-updated tuples

Utility Commands:
- Add DISCARD SEQUENCES command to discard cached sequence-related state
- DISCARD ALL will now also discard such information.
- Add FORCE NULL option to COPY FROM, which causes quoted strings matching the specified null string to be converted to NULLs in CSV mode
- Without this option, only unquoted matching strings will be imported as null values.
- Issue warnings for commands used outside of transaction blocks when they can have no effect
- New warnings are issued for SET LOCAL, SET CONSTRAINTS, SET TRANSACTION and ABORT when used outside a transaction block.

EXPLAIN:
- Make EXPLAIN ANALYZE show planning time
- Make EXPLAIN show the grouping columns in Agg and Group nodes
- Make EXPLAIN ANALYZE show exact and lossy block counts in bitmap heap scans

Views:
- Allow a materialized view to be refreshed without blocking other sessions from reading the view meanwhile
- This is done with REFRESH MATERIALIZED VIEW CONCURRENTLY.
- Allow views to be automatically updated even if they contain some non-updatable columns
- Previously the presence of non-updatable output columns such as expressions, literals, and function calls prevented automatic updates. Now INSERTs, UPDATEs and DELETEs are supported, provided that they do not attempt to assign new values to any of the non-updatable columns.
- Allow control over whether INSERTs and UPDATEs can add rows to an auto-updatable view that would not appear in the view
- This is controlled with the new CREATE VIEW clause WITH CHECK OPTION.
- Allow security barrier views to be automatically updatable

Object Manipulation:
- Support triggers on foreign tables
- Allow moving groups of objects from one tablespace to another using the ALL IN TABLESPACE ... SET TABLESPACE form of ALTER TABLE, ALTER INDEX, or ALTER MATERIALIZED VIEW
- Allow changing foreign key constraint deferrability via ALTER TABLE ... ALTER CONSTRAINT
- Reduce lock strength for some ALTER TABLE commands
- Specifically, VALIDATE CONSTRAINT, CLUSTER ON, SET WITHOUT CLUSTER, ALTER COLUMN SET STATISTICS, ALTER COLUMN SET (attribute_option), ALTER COLUMN RESET (attribute_option) no longer require ACCESS EXCLUSIVE locks.
- Allow tablespace options to be set in CREATE TABLESPACE (Vik Fearing)
- Formerly these options could only be set via ALTER TABLESPACE.
- Allow CREATE AGGREGATE to define the estimated size of the aggregate's transition state data
- Proper use of this feature allows the planner to better estimate how much memory will be used by aggregates.
- Fix DROP IF EXISTS to avoid errors for non-existent objects in more cases
- Improve how system relations are identified
- Previously, relations once moved into the pg_catalog schema could no longer be modified or dropped.

Data Types:
- Fully implement the line data type
- The line segment data type (lseg) has always been fully supported. The previous line data type (which was enabled only via a compile-time option) is not binary or dump-compatible with the new implementation.
- Add pg_lsn data type to represent a WAL log sequence number (LSN)
- Allow single-point polygons to be converted to circles (Bruce Momjian)
- Support time zone abbreviations that change UTC offset from time to time
- Previously, PostgreSQL assumed that the UTC offset associated with a time zone abbreviation (such as EST) never changes in the usage of any particular locale. However this assumption fails in the real world, so introduce the ability for a zone abbreviation to represent a UTC offset that sometimes changes. Update the zone abbreviation definition files to make use of this feature in timezone locales that have changed the UTC offset of their abbreviations since 1970 (according to the IANA timezone database). In such timezones, PostgreSQL will now associate the correct UTC offset with the abbreviation depending on the given date.
- Allow 5+ digit years for non-ISO timestamp and date strings, where appropriate

JSON:
- Add jsonb, a more capable and efficient data type for storing JSON data
- This new type allows faster access to values within a JSON document, and faster and more useful indexing of JSON columns. Scalar values in jsonb documents are stored as appropriate scalar SQL types, and the JSON document structure is pre-parsed rather than being stored as text as in the original json data type.
- Add new JSON functions to allow for the construction of arbitrarily complex JSON trees
- New functions include json_array_elements_text(), json_build_array(), json_object(), json_object_agg(), json_to_record(), and json_to_recordset().
- Add json_typeof() to return the data type of a json value (Andrew Tipton)
- Add checks for overflow/underflow of interval values

Functions:
- Add pg_sleep_for(interval) and pg_sleep_until(timestamp) to specify delays more flexibly
- The existing pg_sleep() function only supports delays specified in seconds.
- Add cardinality() function for arrays
- This returns the total number of elements in the array, or zero for an array with no elements.
- Add SQL functions to allow large object reads/writes at arbitrary offsets
- Allow unnest() to take multiple arguments, which are individually unnested then horizontally concatenated
- Add functions to construct times, dates, timestamps, timestamptzs, and intervals from individual values, rather than strings
- These functions' names are prefixed with make_, e.g. make_date().
- Make to_char()'s TZ format specifier return a useful value for simple numeric time zone offsets
- Previously, to_char(CURRENT_TIMESTAMP, 'TZ') returned an empty string if the timezone was set to a constant like -4.
- Add timezone offset format specifier OF to to_char()
- Improve the random seed used for random()
- Tighten validity checking for Unicode code points in chr(int)
- This function now only accepts values that are valid UTF8 characters according to RFC 3629.

System Information Functions:
- Add functions for looking up objects in pg_class, pg_proc, pg_type, and pg_operator that do not generate errors for non-existent objects
- For example, to_regclass() does a lookup in pg_class similarly to the regclass input function, but it returns NULL for a non-existent object instead of failing.
- Add function pg_filenode_relation() to allow for more efficient lookup of relation names from filenodes
- Add parameter_default column to information_schema.parameters view
- Make information_schema.schemata show all accessible schemas
- Previously it only showed schemas owned by the current user.

Aggregates:
- Add control over which rows are passed into aggregate functions via the FILTER clause
- Support ordered-set (WITHIN GROUP) aggregates
- Add standard ordered-set aggregates percentile_cont(), percentile_disc(), mode(), rank(), dense_rank(), percent_rank(), and cume_dist()
- Support VARIADIC aggregate functions
- Allow polymorphic aggregates to have non-polymorphic state data types
- This allows proper declaration in SQL of aggregates like the built-in aggregate array_agg().

Server-Side Languages:
- Add event trigger support to PL/Perl and PL/Tcl
- Convert numeric values to decimal in PL/Python
- Previously such values were converted to Python float values, risking loss of precision.

PL/pgSQL Server-Side Language:
- Add ability to retrieve the current PL/PgSQL call stack using GET DIAGNOSTICS
- Add option print_strict_params to display the parameters passed to a query that violated a STRICT constraint
- Add variables plpgsql.extra_warnings and plpgsql.extra_errors to enable additional PL/pgSQL warnings and errors
- Currently only warnings/errors about shadowed variables are available.

libpq:
- Make libpq's PQconndefaults() function ignore invalid service files
- Previously it returned NULL if an incorrect service file was encountered.
- Accept TLS protocol versions beyond TLSv1 in libpq

Client Applications:
- Add createuser option -g to specify role membership (Chistopher Browne)
- Add vacuumdb option --analyze-in-stages to analyze in stages of increasing granularity
- This allows minimal statistics to be created quickly.
- Make pg_resetxlog with option -n output current and potentially changed values
- Make initdb throw error for incorrect locale settings, rather than silently falling back to a default choice
- Make pg_ctl return exit code 4 for an inaccessible data directory
- This behavior more closely matches the Linux Standard Base (LSB) Core Specification.
- On Windows, ensure that a non-absolute -D path specification is interpreted relative to pg_ctl's current directory
- Previously it would be interpreted relative to whichever directory the underlying Windows service was started in.
- Allow sizeof() in ECPG C array definitions
- Make ECPG properly handle nesting of C-style comments in both C and SQL text

psql:
- Suppress "No rows" output in psql expanded mode when the footer is disabled
- Allow Control-C to abort psql when it's hung at connection startup

Backslash Commands:
- Make psql's db+ show tablespace options
- Make do+ display the functions that implement the operators
- Make d+ output an OID line only if an oid column exists in the table
- Previously, the presence or absence of an oid column was always reported.
- Make d show disabled system triggers
- Previously, if you disabled all triggers, only user triggers would show as disabled.
- Fix copy to no longer require a space between stdin and a semicolon
- Output the row count at the end of copy, just like COPY already did
- Fix conninfo to display the server's IP address for connections using hostaddr
- Previously conninfo could not display the server's IP address in such cases.
- Show the SSL protocol version in conninfo
- Add tab completion for pset
- Allow pset with no arguments to show all settings
- Make s display the name of the history file it wrote without converting it to an absolute path
- The code previously attempted to convert a relative file name to an absolute path for display, but frequently got it wrong.

pg_dump:
- Allow pg_restore options -I, -P, -T and -n to be specified multiple times
- This allows multiple objects to be restored in one operation
- Optionally add IF EXISTS clauses to the DROP commands emitted when removing old objects during a restore
- This change prevents unnecessary errors when removing old objects. The new --if-exists option for pg_dump, pg_dumpall, and pg_restore is only available when --clean is also specified

pg_basebackup:
- Add pg_basebackup option --xlogdir to specify the pg_xlog directory location
- Allow pg_basebackup to relocate tablespaces in the backup copy
- This is particularly useful for using pg_basebackup on the same machine as the primary.
- Allow network-stream base backups to be throttled
- This can be controlled with the pg_basebackup --max-rate parameter.

Source Code:
- Improve the way tuples are frozen to preserve forensic information
- This change removes the main objection to freezing tuples as soon as possible. Code that inspects tuple flag bits will need to be modified.
- No longer require function prototypes for functions marked with the PG_FUNCTION_INFO_V1 macro
- This change eliminates the need to write boilerplate prototypes. Note that the PG_FUNCTION_INFO_V1 macro must appear before the corresponding function definition to avoid compiler warnings.
- Remove SnapshotNow and HeapTupleSatisfiesNow()
- All existing uses have been switched to more appropriate snapshot types. Catalog scans now use MVCC snapshots.
- Add an API to allow memory allocations over one gigabyte
- Add psprintf() to simplify memory allocation during string composition
- Support printf() size modifier z to print size_t values
- Change API of appendStringInfoVA() to better use vsnprintf()
- Allow new types of external toast datums to be created
- Add single-reader, single-writer, lightweight shared message queue
- Improve spinlock speed on x86_64 CPUs
- Remove spinlock support for unsupported platforms SINIX, Sun3, and NS32K
- Remove IRIX port
- Reduce the number of semaphores required by --disable-spinlocks builds
- Rewrite duplicate_oids Unix shell script in Perl
- Add Test Anything Protocol (TAP) tests for client programs
- Currently, these tests are run by make check-world only if the --enable-tap-tests option was given to configure. This might become the default behavior in some future release.
- Add make targets check-tests and installcheck-tests, which allow selection of individual tests to be run
- Remove maintainer-check makefile rule
- The default build rules now include all the formerly-optional tests.
- Improve support for VPATH builds of PGXS modules
- Upgrade to Autoconf 2.69
- Add a configure flag that appends custom text to the PG_VERSION string
- This is useful for packagers building custom binaries.
- Improve DocBook XML validity
- Fix various minor security and sanity issues reported by the Coverity scanner
- Improve detection of invalid memory usage when testing PostgreSQL with Valgrind
- Improve sample Emacs configuration file emacs.samples
- Also add .dir-locals.el to the top of the source tree.
- Allow pgindent to accept a command-line list of typedefs
- Make pgindent smarter about blank lines around preprocessor conditionals
- Avoid most uses of dlltool in Cygwin and Mingw builds
- Support client-only installs in MSVC (Windows) builds

Additional Modules:
- Add pg_prewarm extension to preload relation data into the shared buffer cache at server start
- This allows reaching full operating performance more quickly.
- Add UUID random number generator gen_random_uuid() to pgcrypto
- This allows creation of version 4 UUIDs without requiring installation of uuid-ossp.
- Allow uuid-ossp to work with the BSD or e2fsprogs UUID libraries, not only the OSSP UUID library
- This improves the uuid-ossp module's portability since it no longer has to have the increasingly-obsolete OSSP library. The module's name is now rather a misnomer, but we won't change it.
- Add option to auto_explain to include trigger execution time
- Fix pgstattuple to not report rows from uncommitted transactions as dead
- Make pgstattuple functions use regclass-type arguments
- While text-type arguments are still supported, they may be removed in a future major release.
- Improve consistency of pgrowlocks output to honor snapshot rules more consistently
- Improve pg_trgm's choice of trigrams for indexed regular expression searches
- This change discourages use of trigrams containing whitespace, which are usually less selective.
- Allow pg_xlogdump to report a live log stream with --follow
- Store cube data more compactly
- Existing data must be dumped/restored to use the new format. The old format can still be read.
- Reduce vacuumlo client-side memory usage by using a cursor
- Dramatically reduce memory consumption in pg_upgrade
- Pass pg_upgrade's user name (-U) option to generated analyze scripts

pgbench:
- Remove line length limit for pgbench scripts
- The previous line limit was BUFSIZ
- Add long option names to pgbench
- Add pgbench option --rate to control the transaction rate
- Add pgbench option --progress to print periodic progress reports

pg_stat_statements:
- Make pg_stat_statements use a file, rather than shared memory, for query text storage
- This removes the previous limitation on query text length, and allows a higher number of unique statements to be tracked by default
- Allow reporting of pg_stat_statements's internal query hash identifier
- Add the ability to retrieve all pg_stat_statements information except the query text
- This allows monitoring tools to fetch query text only for just-created entries, improving performance during repeated querying of the statistics
- Make pg_stat_statements ignore DEALLOCATE commands
- It already ignored PREPARE, as well as planning time in general, so this seems more consistent
- Save the statistics file into $PGDATA/pg_stat at server shutdown, rather than $PGDATA/global