Basically there are 4 steps:
Rename old table
Create new table
with partitioning
Add constraint on old table for it's proposed partition ranges
Attach old table as a partition to new partition table
Sounds easy right?
What about indexes? What about Triggers?
And guess what, there are other traps to watch out for!
Postgres allows:
UPDATE dummy
SET customer=subquery.customer,
address=subquery.address,
partn=subquery.partn
FROM (SELECT address_id, customer, address, partn
FROM /* big hairy SQL */ ...) AS subquery
WHERE dummy.address_id=subquery.address_id;
This syntax is not standard SQL
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'));
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.
Probably the only valid reason for using
set enable_seqscan=false
is when you're writing queries and want to quickly see what the query plan would actually be were there large amounts of data in the table(s).