5 ms·

FTA : >>> and a gaping hole in Postgres’ operational story is filled Are there any other holes one should be aware of ? I'm looking forward to migrate a datab

by wiz21c 8y ago

FTA : >>> and a gaping hole in Postgres’ operational story is filled Are there any other holes one should be aware of ? I'm looking forward to migrate a database with ~100 tables many of which having ~100 millions rows and lot's of FK to Postgresql... The operational side of things is what I know the least. Any pointers ?

minxomat 8y ago
Lots of "Big Data" optimizations, like partitions becoming a first class feature, parallel queries and LLVM JIT on CPU-bound queries. HPE did an excellent technical, no marketing BS writeup on PG11[0]. I highly recommend everyone read this. [0] https://h50146.www5.hpe.com/products/software/oe/linux/mainstream/support/lcc/pdf/PostgreSQL_11_New_Features_beta1_en_20180525-1.pdf https://h50146.www5.hpe.com/products/software/oe/linux/mains...
- brightball 8y ago
  I'm about halfway through this now, but great read. Thank you for the link.
snaky 8y ago
While 100 millions rows is not exactly big number for PostgreSQL, so you might be well without partitioning, but still. > Despite the flexibility, this approach forces the planner to perform an exhaustive search and to check constraints on each partition to determine whether it should be present in the plan or not. Large amount of partitions may result in significant planning overhead. > The pg_pathman module features partition managing functions and optimized planning mechanism which utilizes knowledge of the partitions' structure. https://github.com/postgrespro/pg_pathman https://github.com/postgrespro/pg_pathman
andruby 8y ago
We have tables with 5B rows and don't use partitioning (yet). I do recommend testing and optimising the vacuum settings for large or often updated tables.
- sandGorgon 8y ago
  could you share some info about the vacuum settings ? especially for tables that have bulk updates
  - gldalmaso 8y ago
    I believe parent is referring essentially to the auto vacuum scale factor parameter (https://www.postgresql.org/docs/9.6/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-SCALE-FACTOR https://www.postgresql.org/docs/9.6/static/runtime-config-au...) For a big table with frequent writes, the default is too much (20% dead tuples).
  - andruby 8y ago
    We increased "vacuum_cost_limit" globally and decreased "autovacuum_vacuum_scale_factor" and "autovacuum_analyze_scale_factor" for large tables. Most of them by a factor 4~5x. The docs explain the different settings: https://www.postgresql.org/docs/9.6/static/runtime-config-autovacuum.html https://www.postgresql.org/docs/9.6/static/runtime-config-au... When tweaking performance settings, always benchmark, especially with your specific load characteristics.
lozenge 8y ago
Is it OLTP (frequent writes) or analytics? If OLTP the best advice is create a reasonable load tester and use it for all future schema migrations. This will find all unexpected table locks due to DDL commands like CREATE INDEX, ALTER TABLE etc.

by wiz21c 8y ago

minxomat 8y ago

Lots of "Big Data" optimizations, like partitions becoming a first class feature, parallel queries and LLVM JIT on CPU-bound queries. HPE did an excellent technical, no marketing BS writeup on PG11[0]. I highly recommend everyone read this. [0] https://h50146.www5.hpe.com/products/software/oe/linux/mainstream/support/lcc/pdf/PostgreSQL_11_New_Features_beta1_en_20180525-1.pdf https://h50146.www5.hpe.com/products/software/oe/linux/mains...

brightball 8y ago
I'm about halfway through this now, but great read. Thank you for the link.

snaky 8y ago

While 100 millions rows is not exactly big number for PostgreSQL, so you might be well without partitioning, but still. > Despite the flexibility, this approach forces the planner to perform an exhaustive search and to check constraints on each partition to determine whether it should be present in the plan or not. Large amount of partitions may result in significant planning overhead. > The pg_pathman module features partition managing functions and optimized planning mechanism which utilizes knowledge of the partitions' structure. https://github.com/postgrespro/pg_pathman https://github.com/postgrespro/pg_pathman

andruby 8y ago

We have tables with 5B rows and don't use partitioning (yet). I do recommend testing and optimising the vacuum settings for large or often updated tables.

sandGorgon 8y ago
could you share some info about the vacuum settings ? especially for tables that have bulk updates
- gldalmaso 8y ago
  I believe parent is referring essentially to the auto vacuum scale factor parameter (https://www.postgresql.org/docs/9.6/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-SCALE-FACTOR https://www.postgresql.org/docs/9.6/static/runtime-config-au...) For a big table with frequent writes, the default is too much (20% dead tuples).
- andruby 8y ago
  We increased "vacuum_cost_limit" globally and decreased "autovacuum_vacuum_scale_factor" and "autovacuum_analyze_scale_factor" for large tables. Most of them by a factor 4~5x. The docs explain the different settings: https://www.postgresql.org/docs/9.6/static/runtime-config-autovacuum.html https://www.postgresql.org/docs/9.6/static/runtime-config-au... When tweaking performance settings, always benchmark, especially with your specific load characteristics.

lozenge 8y ago

Is it OLTP (frequent writes) or analytics? If OLTP the best advice is create a reasonable load tester and use it for all future schema migrations. This will find all unexpected table locks due to DDL commands like CREATE INDEX, ALTER TABLE etc.