Postgresql – Any workarounds for lack of primary key on partitioned tables

partitioningpostgresql-10

I am currently looking at implementing declarative partitioning with PostgreSQL 10. However, one sticking point is that we need to have a foreign key between two such partitioned tables, which is not supported. Is there any workaround or suggestions for alternative ways of doing this anyone can suggest? Or should we use table inheritance instead.

Thanks.

Best Answer

The most common solution is to make an exception and not add the foreign key. I guess this must be very large tables and there are usually not that many of them, so a few exceptions for better performance are typically acceptable. You could try to enforce the foreign key relationship with triggers, though it is messy. I don't know of a better alternative for PostgreSQL 10.

Related Solutions

Postgresql – Setting up a Postgresql Partition with foreign key

First, I would suggest reading this: http://ledgersmbdev.blogspot.com/2012/08/postgresql-or-modelling-part-3-table.html because although it doesn't cover partitioning, you are doing partitioning using table inheritance and this will cover a lot of details.

In general there are three ways you can handle this. The first is to use specialized key tables (managed with triggers) and reference these tables in your fkey constraints. This is often the simplest approach but it has significant costs in some cases.

The second possibility is you can partition your joining table by the foreign key. This can lead to major complexity problems though and so aside from rare cases, it is my least-favored approach.

A final possibility is you can write your own constraint triggers to manage foreign key enforcement.

Which approach you choose will be dependent on the complexity of joining information, particularly transitively joining information. The second approach allows you to do things entirely declaratively at a significant (possibly major) complexity cost, while the first and third move away from declarative approaches, meaning you may also want to use pgTAP to run test cases on your triggers.....

None of these solutions are without significant complexity. You may want to rethink whether partitioning is the best way to go here and whether partial indexes and other approaches will get you where you need to go.

Mysql – effective thesql table/index design for 35 million rows+ table, with 200+ corresponding columns (double), any combination of which may be queried

coincidently I am also looking into one of the client support where we designed key-value pair structure for flexibility and currently table is over 1.5B rows and ETL is way too slow. well there are lot of other things in my case but have you thought about that design. you will have one row with all 200 columns present value, that row will convert in to 200 rows in Key-Value pair design. you will gain space advantage with this design depending on for a given AssetID and Date how many rows has actually all 200 f1 to f200 values present? if you say even 30% od columns have NULL value than that is your space saving. because in key-value pair design if value id NULL that row doesn't need to be in table. but in existing column structure design even NULL takes space.(I am not 100% sure but if you have more that 30 columns NULL in table then NULL take 4bytes). if you see this design and assume that all 35M rows has values in all 200 columns then you current db will become 200*35M=700M rows in table right away. but it will not be much high in table space what you had with all columns in single table as we are just Transposing the Columns in to row. in this transpose operation actually we will not have rows where the values are NULL. so you can actually run query against this table and see how many nulls are there and estimate you target table size before you actually implement it.

second advantage is read performance. as you mentioned that new way of querying the data is any combination this f1 to f200 column in where clause. with key value pair design f1 to f200 are present in one column lets say "FildName" and their values are present in second column lets say "FieldValue". you can have CLUSTERED index on both columns. your query will be UNION of those Selects.

WHERE (FiledName = 'f1' and FieldValue BETWEEN 5 AND 6)

UNION

(FiledName = 'f2' and FieldValue BETWEEN 8 AND 10)

etc.....

I will give you some performance numbers form actual prod server. we have 75 price columns for each security TICKER.

Best Answer

Related Solutions

Postgresql – Setting up a Postgresql Partition with foreign key

Mysql – effective thesql table/index design for 35 million rows+ table, with 200+ corresponding columns (double), any combination of which may be queried

Related Question