PostgreSQL

Enums

Create an enum type with:

CREATE TYPE user_status AS ENUM ('online', 'offline', 'dormant');

Use it in a table:

CREATE TABLE users (
  name text,
  status user_status
);

Add values to it:

INSERT INTO users VALUES ('Alice', 'online');
-- INSERT 0 1
INSERT INTO users VALUES ('Bob', 'invalid');
-- ERROR:  22P02: invalid input value for enum user_status: "invalid"

Docs

Postgres sequences are incremented even when INSERTs fail

  • nextval() on a sequence is called before the INSERT is attempted.
  • The sequence number is not rolled back if the transaction fails, or in the event of a CONFLICT.
  • Sequences guarantee that a given value is never repeated, but there may be “holes” in the sequence.

References

Index-Only Scans

Index-only scans try returning data for a query directly from the index, without accessing the heap. However, in addition to the data itself, pg needs to check if a given row in the index is visible to the transaction’s MVCC snapshot; this information is only stored in the heap, not in the index.

As a workaround, pg uses a visibility map, which stores a bit for each page in the heap; the bit is set if all rows in the page are old enough to be visible to all current/future transactions. If the visibility map’s bit is set for a row, it is definitely visible. If it is not set, it might or might not be visible, so the heap has to be visited to figure it out.

A visibility map is 4 orders of magnitude smaller than the heap it describes, so this is a significant win regardless.

References

Edit