It’s very cool, but at high throughput you really see the difference. This bitmap scan can take huge amounts of cpu, reduced to nothing (and much faster) when setting up a proper multi column index.

On small/medium tables and lowish throughout though, yeah it’s often good enough and avoids having many indexes for specific use cases (which is a cost in itself, in memory/cpu/storage)

Bitmap Scan sounds a lot like Rushmore technology in Foxpro[1]. Are they the same?

1) https://en.wikipedia.org/wiki/FoxPro

It is difficult to find a complete explanation for Rushmore nowadays, from what I remember, it would create a bitmap where each bit represented the nth record of the table you wanted to search, then with a single, fast sequential scan it would set the nth bit to 1 if the record satisfied all clauses of your search, 0 otherwise.

Try to see if this makes any sense to you: http://www.foxpert.com/docs/howfoxproworks.en.htm

Is there any potential that indexes could be created over foreign key joins in the future? I know that as of today, no multi-table indices or statistics exist for Postgres, which has had led me to do some further denormalisations.

>>> This is feasible in cases where the total number of distinct values in the column 'a' is reasonably small (think tens or hundreds, perhaps even thousands for very large composite indexes).

It's great this is improving, but this is a fairly narrow improvement. Personally, the multi-column indexes I use would not be improved by this change since column 'a' does not store a "reasonably small" distribution of values.

> This (index ordering resulting in useless indices) is not true, at least not in newer versions of Postgres (I just tried with 15).

> While the query will take much longer (for me, it was about 47 msec vs 0.6 msec for 2,000,000 rows), it still uses the index.

I'd argue that something being 78x slower can make it pretty useless, but it is indeed at least used, in some cases.

Sure, having less padding increases I/O efficiency. I was just commenting on the author's apparent confusion as to why the padding is there in the first place.

Here is the full(er) quote:

  Postgres will happily add padding to the underlying data in order to make sure it is properly aligned at the physical layer. Having the data aligned ensures faster access time when retrieving pages from disk.

SQLite made the opposite tradeoff.

The wording implies to me that Postgres is doing the padding for alignment to reduce IO costs which, as branko_d suggests, would do the opposite. You are reading it as the intervention of the DBA reordering columns to remove the padding will improve IO efficiency by fitting more rows into each page, which you are right would be beneficial in that way.

Postgres will be performing padding for alignment to improve processing speed once data is in local memory – CPUs are usually much faster at reading & writing aligned data⁰. This is trading off memory use and IO efficiency for CPU gain, which is the right optimisation if you assume that your core working set fits nicely into RAM and that your CPU(s) have large enough cache that you don't create the same problem there¹. Other DBs don't do this padding at all either because they didn't think of it or, more likely in the case of the big ones, because they are optimising more for IO than being concerned about CPU bottlenecks, or perhaps they natively rearrange the fields where it makes a difference instead of being beholden to the column ordering given by the user².

----

[0] in fact some architectures don't directly support unaligned access at all, though probably not any architectures Postgres supports

[1] causing extra cache evictions if cache segment width aligns badly with the padding such that less data fits in the available cache

[2] if the user needs to care about physical ordering like this, you have a leaky abstraction

Looked nice so I automatically did git clone && cd postgres_dba && make all install installcheck... and failed :D

Creatures of habit... any plans to make this an extension?

Thanks for the tool! It has helped me out of the mire more than once.

Not OP but in similar situation. Main reason is that the primary tables represent various official declarations, and they have a lot of fields. In addition, our customers are required by law to keep the data readily available for 5-10 years. Also the law states our customers are responsible for the data they send through our systems, so they want to see what they're sending.

So just to give an example of how field count "blows up", there can be up to 8 different parties related to a single declaration, and for each we want to have for display/print purposes our internal id, id in customers system, organization number, name, address, postal code, location, country code, contact person, contact person phone and email.

So we're talking about almost 100 fields just to store parties.

We want to store all that separate from say our "companies" table as a company might change name, location etc, and we're required to show/print the details from today 5+ years down the line.

We could put that in a separate table, but that means 8 joins to fetch data for a single record just there. And that's just one piece of the declarations, and at least 3-4 of these parties our customers want to see in overview grids and be able to filter on there.

Now tack on 20+ years of doing this, with various official systems coming and going with different technical requirements, and it adds up.

Glad you asked!

This system contains measurements and state of physical devices (time series). It’s designed for both heavy write and read, with slight emphasis on write. Each table is one type of device and contains 1 to 5 different measurements/states. But here’s the trick: because data is queried with minimum bucket size of 15minutes I figured we could just create a column for each measurement + quarter of the day (i.e. measure0000, measure0015), so that’s 100 columns for each measurement (96 quarter + 4 for DST), include the date in the key, et voila: excellent write performance (because it’s mainly UPDATE queries) and good read performance.

Okay, the queries to make sense of the data aren’t pretty, but can be generated.

I find it really cool how effective this is for time-series data without Postgres extensions (we’re on RDS).

I don't know what they're doing, but highly-denormalized tables are very common, and sometimes even the norm, depending on how things are set up, especially in OLAP contexts.

Hey, author here.

> Indexes are, by nature, a tradeoff where you give up space to gain time, so this mindset doesn't really apply there.

I agree that (re)aligning indexes are a different beast entirely, but (as mentioned in my recommendation) ideally the developer should keep this in mind when creating the index initially.

Factors like cardinality and even readability should take precedence over perfect alignment, but all else being equal, aligning your indexes from the very moment they are introduced in the codebase is the ideal scenario IMO.