For PostgreSQL 10, I have worked on a feature called "identity columns". Depesz already wrote a blog post about it and showed that it works pretty much like serial columns:
CREATE TABLE test_old (
id serial PRIMARY KEY,
INSERT INTO test_old (payload) VALUES ('a'), ('b'), ('c') RETURNING *;
CREATE TABLE test_new (
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
INSERT INTO test_new (payload) VALUES ('a'), ('b'), ('c') RETURNING *;
do pretty much the same thing, except that the new way is more verbose. ;-)
So why bother?
The new syntax conforms to the SQL standard. Creating auto-incrementing columns has been a notorious area of incompatibility between different SQL implementations. Some have lately
In Postgres-XL, sequences are maintained at the Global Transaction Manager (GTM) to ensure that they are assigned non-conflicting values when they are incremented from multiple nodes. This adds significant overhead for a query doing thousands of INSERTs in a table with a serial column, incrementing sequence one at a time and making a network roundtrip to the GTM, for every INSERT.
Shaun Thomas in a recent blog complained about INSERTs running a magnitude slower on Postgres-XL as compared to vanilla PostgreSQL. There is already a way to improve performance for sequences, but it’s clearly not well advertised. I thought this is a good opportunity to explain the facility.
Postgres-XL provides a user-settable GUC called sequence_range. Every backend requests a block of sequence values as