https://www.cybertec-postgresql.com/en/column-order-in-postgresql-does-matter/ * EN [github][svg] Blog Cybertec LogoCybertec Logo * Services + Requirement Analysis + PostgreSQL consulting + PostgreSQL migration o Migration from Oracle o Migrating from MySQL / MariaDB o CYBERTEC Migrator + PostgreSQL Infrastructure o Setup & Installation o Kubernetes o Database architecture + PostgreSQL development o Database modeling o Functions & Features + Update & Upgrade + Optimization & Security o PostgreSQL Health Check o Performance Tuning o Enterprise Security o Security Audit + Troubleshooting & Data Recovery + PostgreSQL clustering and HA o Clustering and failover o High availability with Patroni o Synchronous and asynchronous replication o Scaling with PL/Proxy + Spatial Services o GIS Tooling o PostGIS OSM Downloader + CYBERTEC Partner Network * Support + Standard PostgreSQL Support o Product Support o 9/5 Basic Support o 24/7 Basic Support o 24/7 Enterprise Support + Advanced PostgreSQL Support o 9/5 Remote DBA o 24/7 Remote DBA o 9/5 Dedicated DBA o 24/7 Cloud-based Support + Support for Reseller o 3rd Level Support o CYBERTEC Partner Network * Products + Our Products o CYPEX - Build Apps & Forms o CYBERTEC PostgreSQL Enterprise Edition o CYBERTEC MIGRATOR o PostgreSQL Transparent Data Encryption o Scalefield - Private Cloud o Data Masking for PostgreSQL o PL/pgSQL_sec - Fully encrypted stored procedures + PostgreSQL Tools & Extensions o pg_timetable - Advanced Job Scheduling o pg_show_plans - Monitoring Execution Plans o pgwatch - PostgreSQL Monitoring Tool o pg_squeeze - Shrinks Tables o Walbouncer - Enterprise Grade Partial Replication o PGConfigurator - Visual PostgreSQL Configuration o ora_migrator o Patroni Environment Setup + Assessment Packages o Data Science Assessment Package o Start-Up Assessment Package o Spatial Data Assessment Package + CYBERTEC Partner Network * Training * PostgreSQL + Advantages of PostgreSQL + PostgreSQL Books + Solutions - Who uses PostgreSQL o PostgreSQL for Startups o PostgreSQL for governments and public services o Longlife solutions + Business Cases o Fraud Detection o PostgreSQL for biotech and scientific applications * Data Science + Data Science Overview + Machine Learning + Big Data Analytics * Contact Column order in PostgreSQL does matter Posted on 2022-07-12 by Hans-Jurgen Schonig column order large tables postgresql I've recently seen some really broad tables (hundreds of columns) in a somewhat inefficiently structured database. Our PostgreSQL support customer complained about strange runtime behavior which could not be easily explained. To help other PostgreSQL users in this same situation, I decided to reveal the secrets of a fairly common performance problem many people don't understand: Column order and column access. Creating a large table The first question is: How can we create a table containing many columns? The easiest way is to simply generate the CREATE TABLE statement using generate_series: test=# SELECT 'CREATE TABLE t_broad (' || string_agg('t_' || x || ' varchar(10) DEFAULT ''a'' ', ', ') || ' )' FROM generate_series(1, 4) AS x; ?column? ---------------------------------------------------------- CREATE TABLE t_broad ( t_1 varchar(10) DEFAULT 'a' , t_2 varchar(10) DEFAULT 'a' , t_3 varchar(10) DEFAULT 'a' , t_4 v archar(10) DEFAULT 'a' ) (1 row) test=# \gexec CREATE TABLE For the sake of simplicity I have only used 4 columns here. Once the command has been generated we can use \gexec to execute the string we have just compiled. \gexec is a really powerful thing: It treats the previous result as SQL input which is exactly what we want here. It leaves us with a table containing 4 columns. However, let's drop the table and create a really large one. test=# DROP TABLE t_broad ; DROP TABLE Create an extremely wide table The following statement creates a table containing 1500 columns. Mind that the upper limit is 1600 columns: test=# SELECT 'CREATE TABLE t_broad (' || string_agg('t_' || x || ' varchar(10) DEFAULT ''a'' ', ', ') || ' )' FROM generate_series(1, 1500) AS x; In real life such a table is far from efficient and should usually not be used to store data. It will simply create too much overhead and in most cases it is not good modelling in the first place. Let's populate the table and add 1 million rows: test=# \timing Timing is on. test=# INSERT INTO t_broad SELECT 'a' FROM generate_series(1, 1000000); INSERT 0 1000000 Time: 67457,107 ms (01:07,457) test=# VACUUM ANALYZE ; VACUUM Time: 155935,761 ms (02:35,936) Note that the table has default values so we can be sure that those columns actually contain something. Finally I have executed VACUUM to make sure that all hint bits and alike are set. The table we have just created is roughly 4 GB in size which can easily be determined using the following line: test=# SELECT pg_size_pretty(pg_total_relation_size('t_broad')); pg_size_pretty ---------------- 3907 MB (1 row) Accessing various columns PostgreSQL stores data in rows. As you might know data can be stored column- or row-oriented. Depending on your use case one or the other option might be beneficial. In the case of OLTP a row-based approach is usually far more efficient. Let's do a count(*) and see how long it takes: test=# SELECT count(*) FROM t_broad; count --------- 1000000 (1 row) Time: 416,732 ms We can run the query in around 400 ms which is quite ok. As expected, the optimizer will go for a parallel sequential scan: test=# explain SELECT count(*) FROM t_broad; QUERY PLAN -------------------------------------------------------------------- Finalize Aggregate (cost=506208.55..506208.56 rows=1 width=8) -> Gather (cost=506208.33..506208.54 rows=2 width=8) Workers Planned: 2 -> Partial Aggregate (cost=505208.33..505208.34 rows=1 width=8) -> Parallel Seq Scan on t_broad (cost=0.00..504166.67 rows=416667 width=0) JIT: Functions: 4 Options: Inlining true, Optimization true, Expressions true, Deforming true (8 rows) Let's compare this to a count on the first column. You'll see a small difference in performance. The reason is that count(*) has to check for the existence of the row while count(column) has to check if a NULL value is fed to the aggregate or not. In case of NULL the value has to be ignored: test=# SELECT count(t_1) FROM t_broad; count --------- 1000000 (1 row) Time: 432,803 ms But, let's see what happens if we access column number 100? The time to do that will differ significantly: test=# SELECT count(t_100) FROM t_broad; count --------- 1000000 (1 row) Time: 857,897 ms The execution time has basically doubled. The performance is even worse if we do a count on column number 1000: test=# SELECT count(t_1000) FROM t_broad; count --------- 1000000 (1 row) Time: 8570,238 ms (00:08,570) Wow, we are already 20 times slower than before. This is not a small difference but a major problem which has to be understood. Debunking PostgreSQL performance issues: column order To understand why the problem happens in the first place we need to take a look at how PostgreSQL stores data: After the tuple header which is present in every row we got a couple of varchar columns. We just used varchar here to prove the point. The same issues will happen with other data types - the problem is simply more apparent with varchar as it is more complicated internally than, say, integer. How does PostgreSQL access a column? It will fetch the row and then dissect this tuple to calculate the position of the desired column inside the row. So if we want to access column #1000 it means that we have to figure out how long those first 999 columns before our chosen one really are. This can be quite complex. For integer we simply have to add 4, but in case of varchar, the operation turns into something really expensive. Let's inspect how PostgreSQL stores varchar (just to see why it is so expensive): * 1 bit indicating short (127 bytes) vs. long string (> 127 bit) * 7 bit or 31 bit length (depending on first bit) * "data" + \0 (to terminate the string ) * alignment (to make sure the next column starts at a multiple of CPU-word length) Now imagine what that means if we need to loop over 1000 columns? It does create some non-trivial overhead. Finally ... The key insight here is that using extremely large tables is often not beneficial from a performance standpoint. It makes sense to use sensible table layouts to have a good compromise between performance and convenience. If you are interested in other ways to improve performance, read my blog on CLUSTER. Hans-Jurgen Schonig Hans-Jurgen Schonig has experience with PostgreSQL since the 90s. He is CEO and technical lead of CYBERTEC, which is one of the market leaders in this field and has served countless customers around the globe since the year 2000. Posted on 2022-07-12 by Hans-Jurgen Schonig logologo CYBERTEC PostgreSQL International GmbH Romerstrasse 19 2752 Wollersdorf AUSTRIA +43 (0) 2622 93022-0 office@cybertec.at twitter.com/PostgresSupport [github][svg]github.com/cybertec-postgresql [facebook][svg][linkedin][svg][xing][svg][yt_icon_mo][svg] Our Services * Administration * Replication * Consulting * Database Design * Support * Migration * Development SUPPORT CUSTOMERS Go to the support platform >> Newsletter Check out previous newsletters! Stay well informed about PostgreSQL by subscribing to our newsletter. [ ] [ ] Ja, ich mochte regelmassig Informationen uber neue Produkte, aktuelle Angebote und Neuigkeiten rund ums Thema PostgreSQL per E-Mail erhalten. Ich kann diese Zustimmung jederzeit widerrufen. Weitere Informationen finden Sie in der Datenschutzerklarung. Yes, I would like to receive information about new products, current offers and news about PostgreSQL via e-mail on a regular basis. Granting consent to receive the CYBERTEC Newsletter by electronic means is voluntary and can be withdrawn free of charge at any time. Further information can be found in the privacy policy. Yes, I would like to receive information about new products, current offers and news about PostgreSQL via e-mail on a regular basis. Granting consent to receive the Cybertec Newsletter by electronic means is voluntary and can be withdrawn free of charge at any time. Further information can be found in the privacy policy. Tak, chce regularnie otrzymywac wiadomosci e-mail o nowych produktach, aktualnych ofertach i nowosciach dotyczacych PostgreSQL. Wyrazenie zgody na otrzymywanie Newslettera Cybertec droga elektroniczna jest dobrowolne i moze zostac w kazdej chwili bezplatnie odwolane.Wiecej informacji mozna znalezc w polityce prywatnosci. Yes, I would like to receive information about new products, current offers and news about PostgreSQL via e-mail on a regular basis. Granting consent to receive the CYBERTEC Newsletter by electronic means is voluntary and can be withdrawn free of charge at any time. Further information can be found in the privacy policy. Jah, ma soovin saada regulaarselt e-posti teel teavet uute toodete, praeguste pakkumiste ja uudiste kohta PostgreSQLi kohta. Cyberteci uudiskirja elektroonilisel teel vastuvotmiseks nousoleku andmine on vabatahtlik ja seda saab igal ajal tasuta tagasi votta. Lisateavet leiate privaatsuseeskirjadest. Leave this field empty if you're human: [ ] © 2000-2022 CYBERTEC PostgreSQL International GmbH * IMPORTANT INFORMATION ABOUT COVID-19 * Contact * Data protection policy * Imprint * Terms and Conditions [ ]