> That's assuming that toasting is evenly spread between tables. In my
> experience, that's not a great bet...
Time to create a test:
SELECT chunk_id::bigint/100000 as id_range, count(*), count(*)/(100000::float)
density FROM (SELECT chunk_id FROM pg_toast.pg_toast_39000165 WHERE chunk_id <
100000000 AND chunk_seq = 0) f GROUP BY id_range ORDER BY id_range;
The machine in question was restored in parallel in Sept 2013 as part of an
upgrade from 8.4. It has about 2000 tables, so its definitely not dominated by
a couple tables. Progress towards oid wrap around is about 25.6%.
With minimal effort, I found 2 bad examples, and I’m sure I can easily find
more. I attached the results for those two.
There were runs of 1,100,000+ and 600,000+ chunk_ids where more than 99% of the
chunk_id are taken. After restore completion, oid densities averaged less than
20 per 100,000 and 400 per 100,000 respectively. The only reasons those runs
seem to be so short is because the tables were much smaller back then. I
expect that next time I dump restore (necessary for upgrading OS versions due
to the collation issue), I’m going to have runs closer to 20,0000,000.
> ... this "fix" would actually make things enormously worse. With the
> single counter feeding all tables, you at least have a reasonable
> probability that there are not enormously long runs of consecutive OIDs in
> any one toast table. With a sequence per table, you are nearly guaranteed
> that there are such runs, because inserts into other tables don't create a
> break.
It makes each toast table independent (and far less likely to wrap) . It would
wrap when the sum(mods on THIS toast table) > 2^32. Right now the function
looks like:
sum(mods on ALL toast tables in cluster) + sum(created normal tables in cluster
* k) + sum(created temp tables in cluster * k) + [...] > 2^32,
WHERE k average number of ids consumed for pg_class, pg_type, etc...
In the case of an insert only table (which is a common use case for
partitions), the id would only wrap when the TOAST table was “full”. On the
other hand currently, it would wrap into its pg_restored section when the
combined oid consuming operations on the cluster surpassed 4 billion.
That being said, I’m certainly not attached to that solution. My real argument
is that although its not a problem today, we are only about 5 years from it
being a problem for large installs and the first time you’ll hear about it is
after someone has a 5 minute production outage on a database thats been taking
traffic for 2 years.
- Matt K.
id_range | count | density
-------------+-------+---------
390 | 92188 | 0.92188
391 | 99186 | 0.99186
392 | 99826 | 0.99826
393 | 99101 | 0.99101
394 | 99536 | 0.99536
395 | 99796 | 0.99796
396 | 99321 | 0.99321
397 | 99768 | 0.99768
398 | 99744 | 0.99744
399 | 99676 | 0.99676
400 | 98663 | 0.98663
401 | 40690 | 0.4069
403 | 92 | 0.00092
404 | 491 | 0.00491
407 | 74 | 0.00074
408 | 54 | 0.00054
415 | 152 | 0.00152
416 | 47 | 0.00047
419 | 59 | 0.00059
422 | 2 | 2e-05
423 | 14 | 0.00014
424 | 5 | 5e-05
425 | 11 | 0.00011
426 | 7 | 7e-05
427 | 5 | 5e-05
428 | 6 | 6e-05
517 | 5 | 5e-05
518 | 9 | 9e-05
519 | 6 | 6e-05
520 | 12 | 0.00012
521 | 17 | 0.00017
522 | 5 | 5e-05
588 | 15 | 0.00015
589 | 10 | 0.0001
590 | 19 | 0.00019
591 | 12 | 0.00012
592 | 12 | 0.00012
593 | 2 | 2e-05
617 | 4 | 4e-05
618 | 9 | 9e-05
619 | 7 | 7e-05
620 | 14 | 0.00014
621 | 5 | 5e-05
622 | 11 | 0.00011
682 | 8 | 8e-05
683 | 13 | 0.00013
684 | 17 | 0.00017
685 | 6 | 6e-05
686 | 17 | 0.00017
687 | 4 | 4e-05
767 | 5 | 5e-05
768 | 10 | 0.0001
769 | 9 | 9e-05
770 | 2 | 2e-05
771 | 14 | 0.00014
772 | 2 | 2e-05
773 | 11 | 0.00011
774 | 13 | 0.00013
775 | 10 | 0.0001
776 | 3 | 3e-05
914 | 7 | 7e-05
915 | 7 | 7e-05
916 | 1 | 1e-05
917 | 3 | 3e-05
918 | 3 | 3e-05
919 | 5 | 5e-05
920 | 4 | 4e-05
921 | 9 | 9e-05
922 | 9 | 9e-05
923 | 1 | 1e-05
(70 rows)
id_range | count | density
-------------+-------+---------
402 | 96439 | 0.96439
403 | 99102 | 0.99102
404 | 98787 | 0.98787
405 | 99351 | 0.99351
406 | 99574 | 0.99574
407 | 97537 | 0.97537
408 | 15480 | 0.1548
409 | 9934 | 0.09934
410 | 5725 | 0.05725
411 | 121 | 0.00121
412 | 7862 | 0.07862
413 | 21880 | 0.2188
414 | 39723 | 0.39723
415 | 45654 | 0.45654
416 | 46915 | 0.46915
417 | 51022 | 0.51022
418 | 45203 | 0.45203
419 | 54357 | 0.54357
420 | 18297 | 0.18297
421 | 946 | 0.00946
422 | 826 | 0.00826
423 | 924 | 0.00924
424 | 1075 | 0.01075
425 | 897 | 0.00897
426 | 826 | 0.00826
427 | 1041 | 0.01041
428 | 1005 | 0.01005
429 | 900 | 0.009
430 | 737 | 0.00737
431 | 269 | 0.00269
432 | 825 | 0.00825
433 | 470 | 0.0047
434 | 570 | 0.0057
435 | 622 | 0.00622
436 | 608 | 0.00608
437 | 810 | 0.0081
438 | 637 | 0.00637
439 | 653 | 0.00653
440 | 723 | 0.00723
441 | 799 | 0.00799
442 | 511 | 0.00511
443 | 676 | 0.00676
444 | 666 | 0.00666
445 | 296 | 0.00296
446 | 778 | 0.00778
447 | 891 | 0.00891
448 | 566 | 0.00566
449 | 741 | 0.00741
450 | 917 | 0.00917
451 | 616 | 0.00616
452 | 589 | 0.00589
453 | 712 | 0.00712
454 | 483 | 0.00483
455 | 228 | 0.00228
456 | 796 | 0.00796
457 | 381 | 0.00381
458 | 759 | 0.00759
459 | 254 | 0.00254
460 | 824 | 0.00824
461 | 846 | 0.00846
462 | 756 | 0.00756
463 | 822 | 0.00822
464 | 715 | 0.00715
465 | 621 | 0.00621
466 | 628 | 0.00628
467 | 472 | 0.00472
468 | 792 | 0.00792
469 | 742 | 0.00742
470 | 774 | 0.00774
471 | 490 | 0.0049
472 | 542 | 0.00542
473 | 602 | 0.00602
474 | 746 | 0.00746
475 | 1000 | 0.01
476 | 546 | 0.00546
477 | 464 | 0.00464
478 | 960 | 0.0096
479 | 706 | 0.00706
480 | 620 | 0.0062
481 | 576 | 0.00576
482 | 680 | 0.0068
483 | 417 | 0.00417
484 | 575 | 0.00575
485 | 658 | 0.00658
486 | 627 | 0.00627
487 | 633 | 0.00633
488 | 529 | 0.00529
489 | 692 | 0.00692
490 | 395 | 0.00395
491 | 483 | 0.00483
492 | 540 | 0.0054
493 | 646 | 0.00646
494 | 295 | 0.00295
495 | 388 | 0.00388
496 | 544 | 0.00544
497 | 459 | 0.00459
498 | 406 | 0.00406
499 | 695 | 0.00695
500 | 506 | 0.00506
501 | 493 | 0.00493
502 | 577 | 0.00577
503 | 412 | 0.00412
504 | 498 | 0.00498
505 | 473 | 0.00473
506 | 471 | 0.00471
507 | 451 | 0.00451
508 | 450 | 0.0045
509 | 555 | 0.00555
510 | 620 | 0.0062
511 | 248 | 0.00248
512 | 832 | 0.00832
513 | 766 | 0.00766
514 | 644 | 0.00644
515 | 317 | 0.00317
516 | 434 | 0.00434
517 | 412 | 0.00412
518 | 508 | 0.00508
519 | 337 | 0.00337
520 | 450 | 0.0045
521 | 475 | 0.00475
522 | 351 | 0.00351
523 | 509 | 0.00509
524 | 470 | 0.0047
525 | 708 | 0.00708
526 | 479 | 0.00479
527 | 507 | 0.00507
528 | 537 | 0.00537
529 | 616 | 0.00616
530 | 598 | 0.00598
531 | 551 | 0.00551
532 | 573 | 0.00573
533 | 386 | 0.00386
534 | 434 | 0.00434
535 | 477 | 0.00477
536 | 578 | 0.00578
537 | 645 | 0.00645
538 | 506 | 0.00506
539 | 585 | 0.00585
540 | 492 | 0.00492
541 | 606 | 0.00606
542 | 375 | 0.00375
543 | 624 | 0.00624
544 | 488 | 0.00488
545 | 485 | 0.00485
546 | 609 | 0.00609
547 | 707 | 0.00707
548 | 604 | 0.00604
549 | 623 | 0.00623
550 | 464 | 0.00464
551 | 701 | 0.00701
552 | 563 | 0.00563
553 | 276 | 0.00276
554 | 353 | 0.00353
555 | 520 | 0.0052
556 | 651 | 0.00651
557 | 441 | 0.00441
558 | 476 | 0.00476
559 | 563 | 0.00563
560 | 455 | 0.00455
561 | 406 | 0.00406
562 | 653 | 0.00653
563 | 562 | 0.00562
564 | 937 | 0.00937
565 | 328 | 0.00328
566 | 260 | 0.0026
567 | 384 | 0.00384
568 | 158 | 0.00158
569 | 397 | 0.00397
570 | 396 | 0.00396
571 | 675 | 0.00675
572 | 541 | 0.00541
573 | 671 | 0.00671
574 | 600 | 0.006
575 | 375 | 0.00375
576 | 634 | 0.00634
577 | 523 | 0.00523
578 | 592 | 0.00592
579 | 525 | 0.00525
580 | 479 | 0.00479
581 | 577 | 0.00577
582 | 773 | 0.00773
583 | 660 | 0.0066
584 | 340 | 0.0034
585 | 777 | 0.00777
586 | 695 | 0.00695
587 | 590 | 0.0059
588 | 779 | 0.00779
589 | 913 | 0.00913
590 | 760 | 0.0076
591 | 766 | 0.00766
592 | 691 | 0.00691
593 | 505 | 0.00505
594 | 378 | 0.00378
595 | 419 | 0.00419
596 | 311 | 0.00311
597 | 663 | 0.00663
598 | 144 | 0.00144
599 | 496 | 0.00496
600 | 237 | 0.00237
601 | 307 | 0.00307
602 | 398 | 0.00398
603 | 537 | 0.00537
604 | 814 | 0.00814
605 | 665 | 0.00665
606 | 748 | 0.00748
607 | 600 | 0.006
608 | 758 | 0.00758
609 | 1058 | 0.01058
610 | 888 | 0.00888
611 | 761 | 0.00761
612 | 546 | 0.00546
613 | 489 | 0.00489
614 | 356 | 0.00356
615 | 550 | 0.0055
616 | 549 | 0.00549
617 | 642 | 0.00642
618 | 792 | 0.00792
619 | 944 | 0.00944
620 | 548 | 0.00548
621 | 566 | 0.00566
622 | 544 | 0.00544
623 | 434 | 0.00434
624 | 337 | 0.00337
625 | 346 | 0.00346
626 | 350 | 0.0035
627 | 260 | 0.0026
628 | 261 | 0.00261
629 | 268 | 0.00268
630 | 427 | 0.00427
631 | 477 | 0.00477
632 | 456 | 0.00456
633 | 535 | 0.00535
634 | 540 | 0.0054
635 | 590 | 0.0059
636 | 583 | 0.00583
637 | 390 | 0.0039
638 | 628 | 0.00628
639 | 584 | 0.00584
640 | 661 | 0.00661
641 | 812 | 0.00812
642 | 580 | 0.0058
643 | 1015 | 0.01015
644 | 769 | 0.00769
645 | 957 | 0.00957
646 | 727 | 0.00727
647 | 618 | 0.00618
648 | 541 | 0.00541
649 | 500 | 0.005
650 | 712 | 0.00712
651 | 637 | 0.00637
652 | 566 | 0.00566
653 | 676 | 0.00676
654 | 600 | 0.006
655 | 762 | 0.00762
656 | 665 | 0.00665
657 | 620 | 0.0062
658 | 639 | 0.00639
659 | 671 | 0.00671
660 | 713 | 0.00713
661 | 507 | 0.00507
662 | 539 | 0.00539
663 | 639 | 0.00639
664 | 618 | 0.00618
665 | 685 | 0.00685
666 | 608 | 0.00608
667 | 538 | 0.00538
668 | 683 | 0.00683
669 | 684 | 0.00684
670 | 795 | 0.00795
671 | 537 | 0.00537
672 | 640 | 0.0064
673 | 441 | 0.00441
674 | 446 | 0.00446
675 | 473 | 0.00473
676 | 635 | 0.00635
677 | 447 | 0.00447
678 | 520 | 0.0052
679 | 563 | 0.00563
680 | 546 | 0.00546
681 | 393 | 0.00393
682 | 601 | 0.00601
683 | 535 | 0.00535
684 | 593 | 0.00593
685 | 549 | 0.00549
686 | 521 | 0.00521
687 | 658 | 0.00658
688 | 644 | 0.00644
689 | 668 | 0.00668
690 | 500 | 0.005
691 | 646 | 0.00646
692 | 547 | 0.00547
693 | 684 | 0.00684
694 | 505 | 0.00505
695 | 413 | 0.00413
696 | 461 | 0.00461
697 | 215 | 0.00215
698 | 477 | 0.00477
699 | 585 | 0.00585
700 | 506 | 0.00506
701 | 607 | 0.00607
702 | 608 | 0.00608
703 | 669 | 0.00669
704 | 411 | 0.00411
705 | 336 | 0.00336
706 | 575 | 0.00575
707 | 553 | 0.00553
708 | 538 | 0.00538
709 | 467 | 0.00467
710 | 465 | 0.00465
711 | 571 | 0.00571
712 | 477 | 0.00477
713 | 514 | 0.00514
714 | 553 | 0.00553
715 | 471 | 0.00471
716 | 531 | 0.00531
717 | 325 | 0.00325
718 | 496 | 0.00496
719 | 514 | 0.00514
720 | 572 | 0.00572
721 | 518 | 0.00518
722 | 577 | 0.00577
723 | 517 | 0.00517
724 | 458 | 0.00458
725 | 528 | 0.00528
726 | 532 | 0.00532
727 | 521 | 0.00521
728 | 449 | 0.00449
729 | 563 | 0.00563
730 | 557 | 0.00557
731 | 492 | 0.00492
732 | 473 | 0.00473
733 | 532 | 0.00532
734 | 551 | 0.00551
735 | 480 | 0.0048
736 | 529 | 0.00529
737 | 497 | 0.00497
738 | 428 | 0.00428
739 | 394 | 0.00394
740 | 427 | 0.00427
741 | 379 | 0.00379
742 | 524 | 0.00524
743 | 442 | 0.00442
744 | 532 | 0.00532
745 | 538 | 0.00538
746 | 489 | 0.00489
747 | 423 | 0.00423
748 | 645 | 0.00645
749 | 1001 | 0.01001
750 | 541 | 0.00541
751 | 609 | 0.00609
752 | 1029 | 0.01029
753 | 1109 | 0.01109
754 | 902 | 0.00902
755 | 806 | 0.00806
756 | 651 | 0.00651
757 | 759 | 0.00759
758 | 842 | 0.00842
759 | 519 | 0.00519
760 | 882 | 0.00882
761 | 599 | 0.00599
762 | 265 | 0.00265
763 | 446 | 0.00446
764 | 586 | 0.00586
765 | 1454 | 0.01454
766 | 1426 | 0.01426
767 | 502 | 0.00502
768 | 247 | 0.00247
769 | 244 | 0.00244
770 | 236 | 0.00236
771 | 52 | 0.00052
772 | 67 | 0.00067
773 | 90 | 0.0009
774 | 165 | 0.00165
775 | 101 | 0.00101
776 | 49 | 0.00049
777 | 30 | 0.0003
778 | 147 | 0.00147
779 | 550 | 0.0055
780 | 382 | 0.00382
781 | 665 | 0.00665
782 | 735 | 0.00735
783 | 421 | 0.00421
784 | 431 | 0.00431
785 | 424 | 0.00424
786 | 613 | 0.00613
787 | 633 | 0.00633
788 | 474 | 0.00474
789 | 349 | 0.00349
790 | 523 | 0.00523
791 | 418 | 0.00418
792 | 690 | 0.0069
793 | 885 | 0.00885
794 | 604 | 0.00604
795 | 391 | 0.00391
796 | 324 | 0.00324
797 | 287 | 0.00287
798 | 476 | 0.00476
799 | 191 | 0.00191
800 | 90 | 0.0009
801 | 206 | 0.00206
802 | 255 | 0.00255
803 | 227 | 0.00227
804 | 276 | 0.00276
805 | 289 | 0.00289
806 | 513 | 0.00513
807 | 215 | 0.00215
808 | 223 | 0.00223
809 | 293 | 0.00293
810 | 343 | 0.00343
811 | 252 | 0.00252
812 | 292 | 0.00292
813 | 269 | 0.00269
814 | 351 | 0.00351
815 | 195 | 0.00195
816 | 48 | 0.00048
817 | 405 | 0.00405
818 | 350 | 0.0035
819 | 254 | 0.00254
820 | 144 | 0.00144
821 | 354 | 0.00354
822 | 258 | 0.00258
823 | 333 | 0.00333
824 | 252 | 0.00252
825 | 206 | 0.00206
826 | 233 | 0.00233
827 | 374 | 0.00374
828 | 125 | 0.00125
829 | 121 | 0.00121
830 | 241 | 0.00241
831 | 315 | 0.00315
832 | 85 | 0.00085
833 | 217 | 0.00217
834 | 79 | 0.00079
835 | 175 | 0.00175
836 | 399 | 0.00399
837 | 485 | 0.00485
838 | 386 | 0.00386
839 | 547 | 0.00547
840 | 459 | 0.00459
841 | 227 | 0.00227
842 | 215 | 0.00215
843 | 223 | 0.00223
844 | 234 | 0.00234
845 | 223 | 0.00223
846 | 350 | 0.0035
847 | 489 | 0.00489
848 | 430 | 0.0043
849 | 552 | 0.00552
850 | 293 | 0.00293
851 | 201 | 0.00201
852 | 181 | 0.00181
853 | 224 | 0.00224
854 | 264 | 0.00264
855 | 223 | 0.00223
856 | 161 | 0.00161
857 | 254 | 0.00254
858 | 541 | 0.00541
859 | 487 | 0.00487
860 | 220 | 0.0022
861 | 261 | 0.00261
862 | 231 | 0.00231
863 | 166 | 0.00166
864 | 387 | 0.00387
865 | 302 | 0.00302
866 | 381 | 0.00381
867 | 379 | 0.00379
868 | 213 | 0.00213
869 | 171 | 0.00171
870 | 261 | 0.00261
871 | 340 | 0.0034
872 | 297 | 0.00297
873 | 316 | 0.00316
874 | 527 | 0.00527
875 | 366 | 0.00366
876 | 342 | 0.00342
877 | 393 | 0.00393
878 | 151 | 0.00151
879 | 96 | 0.00096
880 | 79 | 0.00079
881 | 82 | 0.00082
882 | 131 | 0.00131
883 | 112 | 0.00112
884 | 88 | 0.00088
885 | 123 | 0.00123
886 | 125 | 0.00125
887 | 117 | 0.00117
888 | 102 | 0.00102
889 | 77 | 0.00077
890 | 240 | 0.0024
891 | 299 | 0.00299
892 | 275 | 0.00275
893 | 317 | 0.00317
894 | 294 | 0.00294
895 | 423 | 0.00423
896 | 255 | 0.00255
897 | 208 | 0.00208
898 | 209 | 0.00209
899 | 268 | 0.00268
900 | 220 | 0.0022
901 | 196 | 0.00196
902 | 266 | 0.00266
903 | 301 | 0.00301
904 | 249 | 0.00249
905 | 247 | 0.00247
906 | 392 | 0.00392
907 | 180 | 0.0018
908 | 115 | 0.00115
909 | 135 | 0.00135
910 | 331 | 0.00331
911 | 288 | 0.00288
912 | 261 | 0.00261
913 | 409 | 0.00409
914 | 386 | 0.00386
915 | 358 | 0.00358
916 | 237 | 0.00237
917 | 377 | 0.00377
918 | 443 | 0.00443
919 | 588 | 0.00588
920 | 413 | 0.00413
921 | 453 | 0.00453
922 | 468 | 0.00468
923 | 467 | 0.00467
924 | 301 | 0.00301
925 | 409 | 0.00409
926 | 343 | 0.00343
927 | 306 | 0.00306
928 | 350 | 0.0035
929 | 289 | 0.00289
930 | 342 | 0.00342
931 | 493 | 0.00493
932 | 406 | 0.00406
933 | 407 | 0.00407
934 | 444 | 0.00444
935 | 477 | 0.00477
936 | 313 | 0.00313
937 | 490 | 0.0049
938 | 497 | 0.00497
939 | 341 | 0.00341
940 | 563 | 0.00563
941 | 423 | 0.00423
942 | 322 | 0.00322
943 | 102 | 0.00102
944 | 325 | 0.00325
945 | 432 | 0.00432
946 | 462 | 0.00462
947 | 279 | 0.00279
948 | 449 | 0.00449
949 | 320 | 0.0032
950 | 493 | 0.00493
951 | 214 | 0.00214
952 | 459 | 0.00459
953 | 244 | 0.00244
954 | 467 | 0.00467
955 | 371 | 0.00371
956 | 409 | 0.00409
957 | 388 | 0.00388
958 | 409 | 0.00409
959 | 288 | 0.00288
960 | 309 | 0.00309
961 | 383 | 0.00383
962 | 332 | 0.00332
963 | 347 | 0.00347
964 | 267 | 0.00267
965 | 155 | 0.00155
966 | 411 | 0.00411
967 | 443 | 0.00443
968 | 433 | 0.00433
969 | 362 | 0.00362
970 | 180 | 0.0018
971 | 316 | 0.00316
972 | 291 | 0.00291
973 | 252 | 0.00252
974 | 77 | 0.00077
975 | 334 | 0.00334
976 | 323 | 0.00323
977 | 387 | 0.00387
978 | 303 | 0.00303
979 | 370 | 0.0037
980 | 231 | 0.00231
981 | 308 | 0.00308
982 | 272 | 0.00272
983 | 345 | 0.00345
984 | 318 | 0.00318
985 | 342 | 0.00342
986 | 397 | 0.00397
987 | 300 | 0.003
988 | 332 | 0.00332
989 | 308 | 0.00308
990 | 309 | 0.00309
991 | 260 | 0.0026
992 | 254 | 0.00254
993 | 301 | 0.00301
994 | 338 | 0.00338
995 | 290 | 0.0029
996 | 72 | 0.00072
997 | 241 | 0.00241
998 | 298 | 0.00298
999 | 313 | 0.00313
(598 rows)
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
