Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lXUGE-0007SP-Mh for pgsql-admin@arkaria.postgresql.org; Fri, 16 Apr 2021 19:36:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lXUGD-0000yo-CV for pgsql-admin@arkaria.postgresql.org; Fri, 16 Apr 2021 19:36:13 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lXUGD-0000yg-2B for pgsql-admin@lists.postgresql.org; Fri, 16 Apr 2021 19:36:13 +0000 Received: from mout.perfora.net ([74.208.4.197]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lXUG6-0002ql-14 for pgsql-admin@lists.postgresql.org; Fri, 16 Apr 2021 19:36:12 +0000 Received: from [192.168.1.165] ([98.169.214.254]) by mrelay.perfora.net (mreueus002 [74.208.5.2]) with ESMTPSA (Nemesis) id 0Lk6Xc-1m4u8Q1M54-00c6vn; Fri, 16 Apr 2021 21:36:00 +0200 Subject: Re: Reordering the fields in a table To: Google FezaCakir Cc: "Campbell, Lance" , "pgsql-admin@lists.postgresql.org" References: <6ac2c50de3403c50b87ea4e160d3f8d3f8df1892.camel@gmail.com> From: MichaelDBA Message-ID: <0c8b2a33-0df6-d60f-6f3b-970f5058ff59@sqlexec.com> Date: Fri, 16 Apr 2021 15:35:57 -0400 User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64; rv:52.0) Gecko/20100101 PostboxApp/7.0.47 MIME-Version: 1.0 In-Reply-To: <6ac2c50de3403c50b87ea4e160d3f8d3f8df1892.camel@gmail.com> Content-Type: multipart/alternative; boundary="------------8D050E23E7CE266011EA7630" Content-Language: en-US X-Provags-ID: V03:K1:bh11UBDVPRaRq6AZMxVEDfIPO7NgiqlVuCeQ/4c69KIFY68ZPw7 fYNlwJhJrD7kVCtSAY80omBnlIFKTIQNWB+jqMcdDAmCPa6vyCzmzaXE6Ig4cCYiRziZcoB gUsSSOS474v/3SI5fHAA1KAXMO6ogxQqc87a8/Ak18bS0f1x85fdGw/eLwo3niwUaoNn943 SRNOPoG2WMYH1RADcjj2g== X-Spam-Flag: NO X-UI-Out-Filterresults: notjunk:1;V03:K0:KjgrgXuUU8c=:hbqvMuO0f+6O9RTQGUDTr5 uK9ty7I6b0UDUb5OYvGePNXjGC7irX/gLA/r7gHWG3wd1Ss7xb33x6xHU+r10k0Wm6YhRn/La xANxdupjFwgkbvqIgh0X7FGyaZEqXsGSBTWBuL9gGusrqclbMDJAuev89BlaFPzlSxHxPR9Qd 8m8dvwBgGIoewHk506rxtiIogNiYa4Kb0QiqMqgmDfMnwL6YZA8XcHKG3R/qYdYjqtawNrIQD jzV4dE/SbkH/ysBWuXtOJYhHc/+IJPaJikbbqgLLS1751HrF+ljvOdTx9C+ps9TfILWraX35H iJrawmjDDeg/pzAbI3J2uwU6iXMJwqKq4pl91owknNvHQHefMw4vMt2B5AGkBfFUrHdGrdQYH fL8qqYOwo8s6C0fyvoQiEmZhOh0tFftxJWjVqdFGe/0wcxHbTgTtk/dJVX5e05jEW8z60d/7a 2bA39f0mvugmBDtSKzPU6epUgiQ/zWN/iUvBLqv8ZVAdVDwOFHS4Rg0HgmR1qioMglpmXXSgU 16DXUXFxz1KRJ5N61JYkhE= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------8D050E23E7CE266011EA7630 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Also, since you are reordering your table, you might want to consider ordering based on saving some alignment bytes per row since everything is aligned on an 8 byte boundary. For instance a table with a smallint, bigint, smallint, int  would use 24 bytes Reordering like this would save you 8 bytes per row! smallint, smallint, int, bigint would use 16 bytes Regards, Michael Vitale Google FezaCakir wrote on 4/16/2021 2:34 PM: > There is no way. You should drop and re-create table with your order. > Regards. > > Cum, 2021-04-16 tarihinde 16:15 +0000 saatinde, Campbell, Lance yazdı: >> >> If there is a way to do this please let me know. >> >> *PostgreSQL Request* >> >> I would love to see an enhancement to PostgreSQL where administrators >> had some way to reorder the fields in their tables.  As a software >> developer I can easily write a tool to reorder the fields in a table >> if there were no foreign keys. But foreign keys throw an enormous >> hurdle in my way. >> >> *Why is this a big deal?* >> >> Sixteen years ago when I started working at the University of >> Illinois I told my boss, “if you give me thirty days I can build a >> platform that will allow me to cut at least 50% off of the >> development time of your standard internal web business app.”  I >> built it.  Today I personally created/manage/develop 17 services that >> provide over 120 million pages of web content a year.  I can jump >> into this infrastructure and make a change in an hour or two that >> would take most people weeks to change.  The one issue that causes me >> the most grief is the readability of tables.  When you manage 17 >> services where you are continually tweaking the system you find the >> tables are very unreadable.  New fields are continually added to the >> bottom of tables. They are just not readable. >> >> The most underappreciated elements in software design is the ongoing >> need to be able to easily read what you are working with.  Table can >> really become a mess.  It would be the most amazing enhancement to be >> able to order the fields of any table with a command.  I know today >> this is probably not a top priority to most developers and DBAs.  >> They work in an environment where one person might work on just one >> or two services.  But as development strategies change over time more >> people will be developing software faster and faster. Table field >> readability will become more important. >> >> Thanks, >> >> *LANCE CAMPBELL * >> >> /Software Architect/ >> >> Web Services >> >> Public Affairs >> >> Contact the Webtools Team >> >> 217.333.0382 >> >> lance@illinois.edu >> >> /var/folders/wp/1f6l7hw95y718z976kgnl5f9kr5rtc/T/com.microsoft.Outlook/WebArchiveCopyPasteTempFiles/signature_logo.png >> >> >> /Under the Illinois Freedom of Information Act any written >> communication to or from university employees regarding university >> business is a public record and may be subject to public disclosure./ >> > --------------8D050E23E7CE266011EA7630 Content-Type: multipart/related; boundary="------------FA42233087A3774168F36DA6" --------------FA42233087A3774168F36DA6 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit Also, since you are reordering your table, you might want to consider ordering based on saving some alignment bytes per row since everything is aligned on an 8 byte boundary.

For instance a table with a smallint, bigint, smallint, int  would use 24 bytes
Reordering like this would save you 8 bytes per row!
smallint, smallint, int, bigint would use 16 bytes

Regards,
Michael Vitale

Google FezaCakir wrote on 4/16/2021 2:34 PM:
There is no way. You should drop and re-create table with your order.
Regards.

Cum, 2021-04-16 tarihinde 16:15 +0000 saatinde, Campbell, Lance yazdı:

If there is a way to do this please let me know.

 

PostgreSQL Request

I would love to see an enhancement to PostgreSQL where administrators had some way to reorder the fields in their tables.  As a software developer I can easily write a tool to reorder the fields in a table if there were no foreign keys.  But foreign keys throw an enormous hurdle in my way.

 

Why is this a big deal?

Sixteen years ago when I started working at the University of Illinois I told my boss, “if you give me thirty days I can build a platform that will allow me to cut at least 50% off of the development time of your standard internal web business app.”  I built it.  Today I personally created/manage/develop 17 services that provide over 120 million pages of web content a year.  I can jump into this infrastructure and make a change in an hour or two that would take most people weeks to change.  The one issue that causes me the most grief is the readability of tables.  When you manage 17 services where you are continually tweaking the system you find the tables are very unreadable.  New fields are continually added to the bottom of tables. They are just not readable.

 

The most underappreciated elements in software design is the ongoing need to be able to easily read what you are working with.  Table can really become a mess.  It would be the most amazing enhancement to be able to order the fields of any table with a command.  I know today this is probably not a top priority to most developers and DBAs.  They work in an environment where one person might work on just one or two services.  But as development strategies change over time more people will be developing software faster and faster.  Table field readability will become more important.

 

Thanks,

 

LANCE CAMPBELL

Software Architect

 

Web Services

Public Affairs

Contact the Webtools Team

217.333.0382

lance@illinois.edu

 

 

/var/folders/wp/1f6l7hw95y718z976kgnl5f9kr5rtc/T/com.microsoft.Outlook/WebArchiveCopyPasteTempFiles/signature_logo.png

 

Under the Illinois Freedom of Information Act any written communication to or from university employees regarding university business is a public record and may be subject to public disclosure.

 



--------------FA42233087A3774168F36DA6 Content-Type: image/png; name="image.png" Content-Transfer-Encoding: base64 Content-ID: Content-Disposition: inline; filename="image.png" iVBORw0KGgoAAAANSUhEUgAAAHcAAAAVCAYAAAB8BXHbAAAIjUlEQVRoge1afWwcRxWflIYm sX13+3a2aRI3iX17O7NfThq3sQJJ69jnu52Z3ftyTagCgkpgQOLjr4IQqipUARKgVqgSoS0S VKoIqkBClUCKFBQpED5CoW2kClJIaZKGpMRJXCdglYMMf+ztec+5Oy/wR5LiJ80fvjdv5u37 zceb3zPSTPaibnnHdauYqBmWd3ydyY/3ZkcdtCw3ttzt5uf/Ikx5mlvyZII2G1D54Mhuie6o 3XO9fV+WJWRkKD/7lp+TF4UhZxK0ekmXH9l+r0TrJu9OOMWKVQNjm1LZop7KFnUEI6m4cg2e WB/p+taP4iQD9gyMr41segbG1ybyon8HRDar9dH+Fp021Rvp1mzata7jGJj0Rf1WDYxtQgit 6DZl2hWKYng7wWB7wBJTqVxhBKGhnq5+Tk29K71RDDbjNTy8pkPPFRmjuEWhLFAMjyvEG0JI v62lx8hQfvZvfk7ONcA7y4k8t6id5USe50TOCkPKki6nt+9KDu7w9EqV8ldVK6irdqUOtPjR uBoIO6Da5Tq2K3Ug7PEkQyqUPaPalbpql+tA2f4kNkC9R7BdqatWqQ6U/TKuSxN2v2qX62CK OljBhbRe3NZ+Xv6AapfrqhXUgfATCE29u12/9J1jWTD9p1Tqv4ntksROJWxWSYLpnwIafB2t fc/t7Wx7BsbXAhGXwPRDXwn3rvHD8N4PZvCyagUS22WJ7bJULV8C9U8AFV9bvaGxeLe6E1df YUPyRW+LfI2Z8jw3rgH3r5zINxiVv/OG5Anhyr0jeYn6J0eSBBUNT68Ek5/ETllityYVvfCJ FkcJO4TdqtTcmlQM8USSIRWD7cduTWK3KsHgP0xiA4Q/qrk1iZ2KVAj7bVyXJsX3YbcqVcuX 2KlIMMWrSLN7r5mXeB/AblVipyyBsjPtwM3kvBpYwSXNrUlsBWFrAhD+rbk1CVZwShnM71xs 3zMwvhYon1cbi0IxPB7XYyqmNbcajmkFLQsH2yWpuTWpmsFc34BHECbsJxss9tMMYQcntu6+ dI4ZVxcDfMXPyentO+f7DHG432IHNVMc6hvYTZIEFQ1PrwTKXwtXcFUq1PtYa9DZQexUpOZU JRD+jSRDKoQ9i51qCAQRzyWxAcq+qDlhUICwoy06S0xhpyJVU0jVFI2F5n2vzbx7sVOR2C5J oPzkYnDTucIEtsMgq2ZjoVA2A4T/HCg/CpTPR/NguyJVM7jcq7/Xjo8RgisuR7tSMRiLdH3r RzFQcRnbJalagQTKLyqUPQ+UHwAqZrATbhKg/LgymE+3OE+diaMnhS1nxAK4b3Ii53wii3ft Po/QZ/uSBLJFbkJwVVNI7FQlJsWPL5q3M7gwkgIqzkRHJLYCqeTEF1Asj0jrY1mg4jtNgJ1q 5MstUZ9u4GZ0r4SdSgNYMZcanMhFut47dmk4xx/CduVEeLcvkm1u4YVTHcAtbRudQb0PakkC 2SI3Hbi+VC0/DKAV/COj5++KzdsR3EyOfyY8EoXETkVmiPfpzr6I7y4AXJEpo9AEsCu4hvch 7FQlDsGdbZ9MdkjYlgQXfbBtEtBVbipwfQmUv6VS/vcouED5H6KAdQMXCD8c6spSIfylbr40 jtdZ1fKj7/52pOu+c9l94XEuQoBNcTJDxMMpw7unU3LXlP93cLFVkkD5sUzO+zC2ywv3L2HP IoSQkvMeaA/uUA9Qfg5bgdScqlQM/uUE3/DjaDEAFb+Ifu8GLkL6bUD5seYJYYUxVa1Aqqb4 k2KKb2JaGG474TK4JQmEn0IIoQz1HouCqDlVmc4VJ9OGyLcFd8O4CoTPRuBmKPvU0v6Ip3DD H4XyY9Hv3cFFKJUd1VXT/3X4/eERrVp+I1OuStUKrioG+9I1Ey6DW5JA+GmE0K0IoVuA8t8s JDD8Qoawx8H0r7bfuWJh5xLvq0t+A2XPL+xcfiT6fSlwG3JLmrD7wfT3AxWno9hG+YLmTkpF Z0GLxTK4EbghK5TWx7Kq6c9FuyO8k9nVxvHdeudSfgTbzV39Slf/B6fSQPmF5mKg7MlIlxDc mAz1ZHR2n2ryfWD6b8eeYAdaul33bJnyx5IM2Qou+34Sm/8M3B2rmzqjsCdKtprPpDbg4hx/ KJ4tK4R9rrP/fF+408K+aUPkI91S4KY3isG2Tx2EUIZ6j0RHtULZH1uU1Clex3duRQLh30Kb RzNpVyjxhvp3AMqMZlCDy42DqxD+o3Y2TdvGG/K/BRchhBTKnoiA6wQu6t8BQMX5MBEL70Aw +aMoW2iedspgfqNq8n1RsoadqlQI+xmKcdRdwR0eXgOUv6xapX+BWXx4MQ8OhD8dy9gP3zAM lWoKCZTPA+XngYqZeFNNfwYI/z3Spnrj4DZs3m5nA6aYASrOREWC/wVchNCtQMULzbdpO3AR QorOgpAWjDNU/CJQfgQo/xVQfiX+vgXTvxQnIpYAdwVY4rnbt0w1aNKqBMpnFcoOAeU/UAh/ CYdZc3jnGuyTNwy3HLE6EX0Xb1oYpLmoohRxyxHZ0M4GOyFT1KjeJOKWG8nN2TbgolS2qIPp z0U8cSduWdELe1UruKK5tSaXHNmEXHMp4pZPpAbHrymbduOWwRJTYPqva25NRouoyS03uGvN nZRA+TE0PLzmhqkKAeUdm2r6dSD8DYRJH0ILVaFuNmCKOlBxeXW2cCdCyapCqhnUgfA/twMX IYTSRmGPapXml6oK9W7Km4rpPwOmuIDtcozcDySY/usK9b+C+ndAO9slq0KZrRmF8s+DKY4B Ff8MF04jkTP9i2D6TzcpzxulnpvWx7KdWipb1FdtHt2MGvdnVM/tZpPWWTbtikEUPmsS13OX qtOu2jy6OWk9t2/9KM7kCvdqVmkqneOTIZPUfuE0JXk9F6UGJ3JpQ+QzpFhWjOKu+P2OEFr+ T4x3tCz/D9U7V/4N7PWIDJlHDWwAAAAASUVORK5CYII= --------------FA42233087A3774168F36DA6-- --------------8D050E23E7CE266011EA7630--