select * from tbl TABLESAMPLE system (5)
The SYSTEM method is significantly faster than the BERNOULLI method when small sampling percentages are specified, but it may return a less-random sample of the table as a result of clustering effects.
As a database developer, you want these sub-transactions or autonomous transactions to be processed and committed regardless of what happens to the larger surrounding transaction block.
The pg_background Postgres extension originally written by Robert Haas starts a separate worker to process a SQL statement, which is a nice way to achieve an autonomous transaction.
CREATE TABLE books (
book_id serial NOT NULL,
data jsonb
);
INSERT INTO books VALUES (1, '{"title": "Sleeping Beauties", "genres": ["Fiction", "Thriller", "Horror"], "published": false}');
SELECT data->'title' AS title FROM books WHERE data->'published' = 'false';
CREATE INDEX idx_published ON books ((data->'published'));
pgloader will keep a separate file of rejected data, but continue trying to copy good data in your database.
pgloader also implements data reformatting, a typical example of that being the transformation of MySQL datestamps 0000-00-00 and 0000-00-00 00:00:00 to PostgreSQL NULL value
SELECT * FROM pg_stat_activity WHERE state = 'active';
So you can identify the PID of the hanging query you want to terminate, run this:
SELECT pg_cancel_backend(PID);
Partial Indexes
A partial index is an index built over a subset of a table; the subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries for only those table rows that satisfy the predicate.
CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
WHERE NOT (client_ip > inet '192.168.100.0' AND client_ip < inet '192.168.100.255');