Postgresql – Is it a good idea to create an index just for new data to increase index creation performance

database-designindex-tuningpostgresqlpostgresql-9.5

I have a table with about 350 M rows with 45 GB, it has around 10 columns ,simplified version below

CREATE TABLE PAYMENT (
    ID NUMERIC(20) PRIMARY KEY,
    CREATION TIMESTAMP,
    VALUE DECIMAL(10, 2),
    USER_ID NUMERIC(20)
    ....
)

Once known that

Table have a really high concurrency
It receives about 3M INSERTS and 5M UPDATES per day, update uses the PK
By company policies reasons
- I must keep old data to grant history
- I can't partitioning that table
- I'm really going to have this one table with all data and there is not chance to change that
I have to create an index for (CREATION, USER_ID) columns

Is it a good idea to create an index excluding old data to get the index created faster, avoiding table locks and save storage knowing that my queries will just hit the new data?

CREATE INDEX ON PAYMENT(CREATION, USER_ID) WHERE CREATION > TIMESTAMP '2018-03-25 00:00:00';

Best Answer

if you create a partial index then your queries will have also include the condition used to create the index. if they do not the index will be of no use.

when creating the index there will probably need to be a scan of the entire table your only win is a smaller index size.

What index will help queries most depends on what information you want from the table. are you sure that there are enough payments with the exact same creation that including userid as the second term in the index is justified? bear in mind that timestamp is 8 bytes while numeric(20) is 16 bytes, so an index on timestamp only will also be more compact.

An index on payment (userid, creation) will be more useful in the case where you want you find the most recent record, or the payment history, for any particular userid.

Related Solutions

Postgresql – Want to add a “variable” to a table

Timestamping each row doesn't seem to make a lot of sense to me. I would just keep a log of the events that are interesting. Such a log could be reusable for other events too. E.g. (sorry this is SQL Server dialect, you may have to adjust slightly):

CREATE TABLE dbo.EventLog
(
  EventDate SMALLDATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  [Description] NVARCHAR(2000)
);

Whenever you reload the table, also call a stored procedure that does this:

INSERT dbo.EventLog([Description])
  SELECT N'Re-populated table x';

If you need to know the date of the last refresh, you can say:

SELECT MAX(EventDate) FROM dbo.EventLog
  WHERE [Description] = N'Re-populated table x';

If all you ever care about is the last time, then before the insert above, you could just:

DELETE dbo.EventLog
  WHERE [Description] = N'Re-populated table x';

Sql-server – Should I use many single field indexes, instead of specific multi column indexes

What you need are covering indexes, ie. indexes that can satisfy a query on their own. But a 'covering' index has one problem: it is covering a specific query. So in order to develop a good indexing strategy, you need to understand your workload: what queries are hitting the database, which ones are critical and which ones are not, how often each type of query is run, etc etc etc. And then you balance this against the write and update cost of each index, and there you have your indexing strategy. If it sounds complicated that is because it is complicated.

However you can apply some rules of thumb. The MSDN covers the basics quite well:

There is also a myriad of articles contributed by the community, eg. Webcast Recording – DBA Darwin Awards: Index Edition.

And to answer your question specifically: separate indexes on each column can work, provided that each column has a high selectivity (many distinct values, each values appearing only a few times in the database). The resulted access plan using hash join between two index range scans usually works quite well. Columns with low selectivity (few distinct values, each value appearing many times in the database) make no sense to be indexed on their own, the query optimizer will simply ignore them. However, low selectivity columns many times make good composite keys when they are paired with a high selectivity column.

Best Answer

Related Solutions

Postgresql – Want to add a “variable” to a table

Sql-server – Should I use many single field indexes, instead of specific multi column indexes

Related Question